Hi ,
Oracle 8.1.7.0.0 on HP-UX 11.0
I am a newbie as far as PL-SQL is concerned . I have Sybase/MSSQL
T-SQL background.
We have a report which uses a select statement . This select statement
joins 15 tables . SOme of the tables are outer joined.
It runs much slow when parameters (From & To Date) are for a month.
Like From Date = Jun 1 , To Date = Jun 30. Report does not complete
even after 30 mins.
I was thinking to break this query in smaller parts. I am trying to
minimise no of tables used in single statement to around 6.
I want to use Global Temporary table , insert data in temp table by
joining few tables. Rest of the columns of Temporary tables will be
updated thru subsequent updates. Finally a select statement from
temporary table will populate the report .
Will it be a good approach (in Oracle PL-SQL) as far as performance is
concrened ? Are there any performance issues using Global temporary
table ?
Any help in this regards is highly appreciated ....
Thanks & Regards,
Mahesh Hardikar
6  4482 
What are the explain plan and the tkprof results? It is rare to have to put
results to a temp table to get better performance. My guess is that there
is a function on a column and that forces a full table scan. But I have no
way of knowing without seeing the query and the explain plan results.
Jim
--
Replace part of the email address: ke************************@attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Mahesh Hardikar" <ha*******@yahoo.com> wrote in message
news:4a**************************@posting.google.c om... Hi ,
Oracle 8.1.7.0.0 on HP-UX 11.0
I am a newbie as far as PL-SQL is concerned . I have Sybase/MSSQL
T-SQL background.
We have a report which uses a select statement . This select statement
joins 15 tables . SOme of the tables are outer joined.
It runs much slow when parameters (From & To Date) are for a month.
Like From Date = Jun 1 , To Date = Jun 30. Report does not complete
even after 30 mins.
I was thinking to break this query in smaller parts. I am trying to
minimise no of tables used in single statement to around 6.
I want to use Global Temporary table , insert data in temp table by
joining few tables. Rest of the columns of Temporary tables will be
updated thru subsequent updates. Finally a select statement from
temporary table will populate the report .
Will it be a good approach (in Oracle PL-SQL) as far as performance is
concrened ? Are there any performance issues using Global temporary
table ?
Any help in this regards is highly appreciated ....
Thanks & Regards,
Mahesh Hardikar ha*******@yahoo.com (Mahesh Hardikar) wrote in message news:<4a**************************@posting.google. com>... Hi ,
Oracle 8.1.7.0.0 on HP-UX 11.0
I am a newbie as far as PL-SQL is concerned . I have Sybase/MSSQL
T-SQL background.
We have a report which uses a select statement . This select statement
joins 15 tables . SOme of the tables are outer joined.
It runs much slow when parameters (From & To Date) are for a month.
Like From Date = Jun 1 , To Date = Jun 30. Report does not complete
even after 30 mins.
I was thinking to break this query in smaller parts. I am trying to
minimise no of tables used in single statement to around 6.
I want to use Global Temporary table , insert data in temp table by
joining few tables. Rest of the columns of Temporary tables will be
updated thru subsequent updates. Finally a select statement from
temporary table will populate the report .
Will it be a good approach (in Oracle PL-SQL) as far as performance is
concrened ? Are there any performance issues using Global temporary
table ?
PL/SQL is wrap-up procedural language. Queries are written in SQL.
Approaches to tuning:
1. Short approach: run explain plan on the query and post results.
2. Long (and proper) approach: read 2 Oracle books at least, Concepts
and Tuning.
Any help in this regards is highly appreciated ....
Thanks & Regards,
Mahesh Hardikar
Hi ,
Thanks for your inputs.
Here goes the query :
/************************************************** *********
SELECT DISTINCT
A.ORGNSTNCODE AS OULCODE,
K.DESCRIPTION AS OUL,
K1.DESCRIPTION AS LOCCODE,
A.SALESMANCODE AS SALESMANCODE,
B.DESCRIPTION AS SALESPERSON,
A.AGNTCODE AS AGENTCODE,
D.PRINTDESCRIP AS AGENT,
A.AWBNO AS MAWBNO,
A.WONO AS WONO,
A.WOKEY AS WOKEY,
A.MODECODE ,
A.HAWBNO AS HAWBNO,
A.HAWBDATE AS HAWBDATE,
N.INVKEY ,
N.INVNO AS BILLNO,
N.INVDATE AS BILLDATE,
A.CARRIERCODE AS CARRIERCODE,
E.DESCRIPTION AS CARRIER,
A.ORIPORTCODE AS ORIGINPORTCODE,
P1.DESCRIPTION AS ORIGIN,
A.DESTPORTCODE AS DESTPORTCODE,
P2.DESCRIPTION AS DESTINATION,
A.ORGNSTNCODE REVENUESTN,
A.CUSTOMERCODE AS CUSTOMERCODE,
DECODE(A.CUSTOMERDESC, NULL, A1.DESCRIPTION,
A.CUSTOMERDESC) AS CUSTOMER,
A.PONO AS PONO,
H.AFLCMDTY AS COMMCODE,
I.DESCRIPTION AS COMMODITY,
H.NOPKGS AS PKG,
H.AFLGROSSWT AS GROSSWT,
A.GROSSUOM AS GROSSUOMCODE,
J.DESCRIPTION AS GROSSUOM,
H.AFLCHRGWT AS CHRGQTY,
H.CONTTYPE AS CONTTYPE,
H.CONTSIZE AS CONTSIZE,
M.PPCC AS PPCC,
A.FFCURR AS FFCURR,
NVL(M.FRTAMT,0) AS FRTAMT,
A.FFEXCHRATE ,
A.ORIEXCHRATE ,
DECODE(A.RTDBYCODE, '1', 'AGENT', '2', 'AFL', '') AS RTDBYCODE
FROM
ICWOIMP A,
COCOPHDR A1,
COSALESMAN B,
ICAGENTHDR D,
ICCARRIERHEADER E,
ICCONTDTLS H,
ICCOMMODITY I,
ICPORT P1,
ICPORT P2,
COUOM J,
COOUL K,
COPRODUCT L,
ICWOFRTDTL M,
ICINVHDR N,
COOUL K1
WHERE TRUNC(A.CANDATE) BETWEEN '01-jan-2003' AND '30-jan-2003'
AND A.STATUS = 'C'
AND A.STAGE NOT IN ('S','C')
AND A.ORGNSTNCODE = K.OULCODE
AND A.LOCCODE = K1.OULCODE
AND A.CUSTOMERCODE = A1.CUSTOMERCODE
AND A.SALESMANCODE = B.SALESMANCODE
AND A.AGNTCODE = D.AGENTCODE
AND A.CARRIERCODE = E.CARRIERCODE
AND A.WOKEY = H.WOKEY(+)
AND H.AFLCMDTY = I.COMMCODE(+)
AND A.GROSSUOM = J.UOMCODE(+)
AND A.ORIPORTCODE = P1.PORTCODE
AND A.DESTPORTCODE = P2.PORTCODE
AND A.PRODUCTCODE = L.PRODUCTCODE
AND L.PARENT = 'IMP'
AND A.WOKEY = M.WOKEY(+)
AND A.WOKEY = N.LINKREF1KEY(+)
AND N.TRANTYPE(+) = 'ICCAN'
ORDER BY A.WONO
************************************************** ****/
Here is the execution plan :
/*******************************
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1989 Card=24 Bytes=1
1088)
1 0 SORT (UNIQUE) (Cost=1984 Card=24 Bytes=11088)
2 1 HASH JOIN (OUTER) (Cost=1978 Card=24 Bytes=11088)
3 2 NESTED LOOPS (OUTER) (Cost=1976 Card=24 Bytes=10416)
4 3 NESTED LOOPS (Cost=1930 Card=23 Bytes=9407)
5 4 HASH JOIN (OUTER) (Cost=1907 Card=23 Bytes=8671)
6 5 HASH JOIN (OUTER) (Cost=1611 Card=23 Bytes=7751)
7 6 NESTED LOOPS (OUTER) (Cost=1297 Card=23 Bytes=
7498)
8 7 HASH JOIN (Cost=1274 Card=23 Bytes=6785)
9 8 HASH JOIN (Cost=1268 Card=23 Bytes=6486)
10 9 HASH JOIN (Cost=1262 Card=23 Bytes=6187)
11 10 HASH JOIN (Cost=1258 Card=23
Bytes=5267)
12 11 HASH JOIN (Cost=1255 Card=23
Bytes=4669)
13 12 HASH JOIN (Cost=1251 Card=23
Bytes=4370)
14 13 HASH JOIN (Cost=1247 Card=23
Bytes=4071)
15 14 HASH JOIN (Cost=1245 Card=23
Bytes=3634)
16 15 TABLE ACCESS (FULL) OF
'COPRODUCT' (Cost=2 Card=2 Bytes=12)
17 15 TABLE ACCESS (FULL) OF
'ICWOIMP' (Cost=1242 Card=23 Bytes=3496)
18 14 TABLE ACCESS (FULL) OF
'COSALESMAN' (Cost=1 Card=72 Bytes=1368)
19 13 TABLE ACCESS (FULL) OF 'COOUL'
(Cost=3 Card=237 Bytes=3081)
20 12 TABLE ACCESS (FULL) OF 'COOUL'
(Cost=3 Card=237 Bytes=3081)
21 11 TABLE ACCESS (FULL) OF
'ICCARRIERHEADER' (Cost=2 Card=263 Bytes=6838)
22 10 TABLE ACCESS (FULL) OF 'ICAGENTHDR'
(Cost=3 Card=267 Bytes=10680)
23 9 TABLE ACCESS (FULL) OF 'ICPORT' (Cost=5
Card=1160 Bytes=15080)
24 8 TABLE ACCESS (FULL) OF 'ICPORT' (Cost=5
Card=1160 Bytes=15080)
25 7 TABLE ACCESS (BY INDEX ROWID) OF 'COUOM'
(Cost=1 Card=1245 Bytes=38595)
26 25 INDEX (UNIQUE SCAN) OF 'SYS_C003223'
(UNIQUE)
27 6 TABLE ACCESS (FULL) OF 'ICWOFRTDTL'
(Cost=311Card=43971 Bytes=483681)
28 5 TABLE ACCESS (FULL) OF 'ICINVHDR' (Cost=295
Card=6572 Bytes=262880)
29 4 TABLE ACCESS (BY INDEX ROWID) OF 'COCOPHDR'
(Cost=1 Card=11027 Bytes=352864)
30 29 INDEX (UNIQUE SCAN) OF 'UK_COCOPHDR' (UNIQUE)
31 3 TABLE ACCESS (BY INDEX ROWID) OF 'ICCONTDTLS'
(Cost=2 Card=51807 Bytes=1295175)
32 31 INDEX (RANGE SCAN) OF 'INDX_ICCONTDTLS_WOKEY'
(NON-UNIQUE) (Cost=1 Card=51807)
33 2 TABLE ACCESS (FULL) OF 'ICCOMMODITY' (Cost=1 Card=59
Bytes=1652)
**************************************************/
The main table ICWOIMP (Rows 36000) has index on Column STATUS but
execution plan is not using it . Instead it is using TABLE SCAN.
Surprisingly when I ran the query thru SQL-Plus , it was pretty fast.
Gave me o/p in a min. But many times I see this report getting
cancelled after 30 Mins theu 9iAS Report Server Jobs.
I will also talk to developer if they are doing something at Report
level which is killing the process.
Regards,
Mahesh
"Jim Kennedy" <kennedy-down_with_spammers@no_spam.comcast.net> wrote in message news:<cnbRa.79047$H17.22579@sccrnsc02>... What are the explain plan and the tkprof results? It is rare to have to put
results to a temp table to get better performance. My guess is that there
is a function on a column and that forces a full table scan. But I have no
way of knowing without seeing the query and the explain plan results.
Jim
--
Replace part of the email address: ke************************@attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Mahesh Hardikar" <ha*******@yahoo.com> wrote in message
news:4a**************************@posting.google.c om...
get rid of the distinct and replace: WHERE TRUNC(A.CANDATE) BETWEEN '01-jan-2003' AND '30-jan-2003'
with WHERE A.CANDATE BETWEEN to_date('01-jan-2003','dd-mmm-yyyy') AND
to_date('30-jan-2003','dd-mmm-yyyy')
(don't rely on the implicit sting to date conversion, it will bite you
later.)The reson it is full scanning table A is the trunc on the column.
With the between clause you don't need it.
See if those things help.
Jim
--
Replace part of the email address: ke************************@attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Mahesh Hardikar" <ha*******@yahoo.com> wrote in message
news:4a**************************@posting.google.c om... Hi ,
Thanks for your inputs.
Here goes the query :
/************************************************** *********
SELECT DISTINCT
A.ORGNSTNCODE AS OULCODE,
K.DESCRIPTION AS OUL,
K1.DESCRIPTION AS LOCCODE,
A.SALESMANCODE AS SALESMANCODE,
B.DESCRIPTION AS SALESPERSON,
A.AGNTCODE AS AGENTCODE,
D.PRINTDESCRIP AS AGENT,
A.AWBNO AS MAWBNO,
A.WONO AS WONO,
A.WOKEY AS WOKEY,
A.MODECODE ,
A.HAWBNO AS HAWBNO,
A.HAWBDATE AS HAWBDATE,
N.INVKEY ,
N.INVNO AS BILLNO,
N.INVDATE AS BILLDATE,
A.CARRIERCODE AS CARRIERCODE,
E.DESCRIPTION AS CARRIER,
A.ORIPORTCODE AS ORIGINPORTCODE,
P1.DESCRIPTION AS ORIGIN,
A.DESTPORTCODE AS DESTPORTCODE,
P2.DESCRIPTION AS DESTINATION,
A.ORGNSTNCODE REVENUESTN,
A.CUSTOMERCODE AS CUSTOMERCODE,
DECODE(A.CUSTOMERDESC, NULL, A1.DESCRIPTION,
A.CUSTOMERDESC) AS CUSTOMER,
A.PONO AS PONO,
H.AFLCMDTY AS COMMCODE,
I.DESCRIPTION AS COMMODITY,
H.NOPKGS AS PKG,
H.AFLGROSSWT AS GROSSWT,
A.GROSSUOM AS GROSSUOMCODE,
J.DESCRIPTION AS GROSSUOM,
H.AFLCHRGWT AS CHRGQTY,
H.CONTTYPE AS CONTTYPE,
H.CONTSIZE AS CONTSIZE,
M.PPCC AS PPCC,
A.FFCURR AS FFCURR,
NVL(M.FRTAMT,0) AS FRTAMT,
A.FFEXCHRATE ,
A.ORIEXCHRATE ,
DECODE(A.RTDBYCODE, '1', 'AGENT', '2', 'AFL', '') AS RTDBYCODE
FROM
ICWOIMP A,
COCOPHDR A1,
COSALESMAN B,
ICAGENTHDR D,
ICCARRIERHEADER E,
ICCONTDTLS H,
ICCOMMODITY I,
ICPORT P1,
ICPORT P2,
COUOM J,
COOUL K,
COPRODUCT L,
ICWOFRTDTL M,
ICINVHDR N,
COOUL K1
WHERE TRUNC(A.CANDATE) BETWEEN '01-jan-2003' AND '30-jan-2003'
AND A.STATUS = 'C'
AND A.STAGE NOT IN ('S','C')
AND A.ORGNSTNCODE = K.OULCODE
AND A.LOCCODE = K1.OULCODE
AND A.CUSTOMERCODE = A1.CUSTOMERCODE
AND A.SALESMANCODE = B.SALESMANCODE
AND A.AGNTCODE = D.AGENTCODE
AND A.CARRIERCODE = E.CARRIERCODE
AND A.WOKEY = H.WOKEY(+)
AND H.AFLCMDTY = I.COMMCODE(+)
AND A.GROSSUOM = J.UOMCODE(+)
AND A.ORIPORTCODE = P1.PORTCODE
AND A.DESTPORTCODE = P2.PORTCODE
AND A.PRODUCTCODE = L.PRODUCTCODE
AND L.PARENT = 'IMP'
AND A.WOKEY = M.WOKEY(+)
AND A.WOKEY = N.LINKREF1KEY(+)
AND N.TRANTYPE(+) = 'ICCAN'
ORDER BY A.WONO
************************************************** ****/
Here is the execution plan :
/*******************************
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1989 Card=24 Bytes=1
1088)
1 0 SORT (UNIQUE) (Cost=1984 Card=24 Bytes=11088)
2 1 HASH JOIN (OUTER) (Cost=1978 Card=24 Bytes=11088)
3 2 NESTED LOOPS (OUTER) (Cost=1976 Card=24 Bytes=10416)
4 3 NESTED LOOPS (Cost=1930 Card=23 Bytes=9407)
5 4 HASH JOIN (OUTER) (Cost=1907 Card=23 Bytes=8671)
6 5 HASH JOIN (OUTER) (Cost=1611 Card=23 Bytes=7751)
7 6 NESTED LOOPS (OUTER) (Cost=1297 Card=23 Bytes=
7498)
8 7 HASH JOIN (Cost=1274 Card=23 Bytes=6785)
9 8 HASH JOIN (Cost=1268 Card=23 Bytes=6486)
10 9 HASH JOIN (Cost=1262 Card=23 Bytes=6187)
11 10 HASH JOIN (Cost=1258 Card=23
Bytes=5267)
12 11 HASH JOIN (Cost=1255 Card=23
Bytes=4669)
13 12 HASH JOIN (Cost=1251 Card=23
Bytes=4370)
14 13 HASH JOIN (Cost=1247 Card=23
Bytes=4071)
15 14 HASH JOIN (Cost=1245 Card=23
Bytes=3634)
16 15 TABLE ACCESS (FULL) OF
'COPRODUCT' (Cost=2 Card=2 Bytes=12)
17 15 TABLE ACCESS (FULL) OF
'ICWOIMP' (Cost=1242 Card=23 Bytes=3496)
18 14 TABLE ACCESS (FULL) OF
'COSALESMAN' (Cost=1 Card=72 Bytes=1368)
19 13 TABLE ACCESS (FULL) OF 'COOUL'
(Cost=3 Card=237 Bytes=3081)
20 12 TABLE ACCESS (FULL) OF 'COOUL'
(Cost=3 Card=237 Bytes=3081)
21 11 TABLE ACCESS (FULL) OF
'ICCARRIERHEADER' (Cost=2 Card=263 Bytes=6838)
22 10 TABLE ACCESS (FULL) OF 'ICAGENTHDR'
(Cost=3 Card=267 Bytes=10680)
23 9 TABLE ACCESS (FULL) OF 'ICPORT' (Cost=5
Card=1160 Bytes=15080)
24 8 TABLE ACCESS (FULL) OF 'ICPORT' (Cost=5
Card=1160 Bytes=15080)
25 7 TABLE ACCESS (BY INDEX ROWID) OF 'COUOM'
(Cost=1 Card=1245 Bytes=38595)
26 25 INDEX (UNIQUE SCAN) OF 'SYS_C003223'
(UNIQUE)
27 6 TABLE ACCESS (FULL) OF 'ICWOFRTDTL'
(Cost=311Card=43971 Bytes=483681)
28 5 TABLE ACCESS (FULL) OF 'ICINVHDR' (Cost=295
Card=6572 Bytes=262880)
29 4 TABLE ACCESS (BY INDEX ROWID) OF 'COCOPHDR'
(Cost=1 Card=11027 Bytes=352864)
30 29 INDEX (UNIQUE SCAN) OF 'UK_COCOPHDR' (UNIQUE)
31 3 TABLE ACCESS (BY INDEX ROWID) OF 'ICCONTDTLS'
(Cost=2 Card=51807 Bytes=1295175)
32 31 INDEX (RANGE SCAN) OF 'INDX_ICCONTDTLS_WOKEY'
(NON-UNIQUE) (Cost=1 Card=51807)
33 2 TABLE ACCESS (FULL) OF 'ICCOMMODITY' (Cost=1 Card=59
Bytes=1652)
**************************************************/
The main table ICWOIMP (Rows 36000) has index on Column STATUS but
execution plan is not using it . Instead it is using TABLE SCAN.
Surprisingly when I ran the query thru SQL-Plus , it was pretty fast.
Gave me o/p in a min. But many times I see this report getting
cancelled after 30 Mins theu 9iAS Report Server Jobs.
I will also talk to developer if they are doing something at Report
level which is killing the process.
Regards,
Mahesh
"Jim Kennedy" <kennedy-down_with_spammers@no_spam.comcast.net> wrote in
message news:<cnbRa.79047$H17.22579@sccrnsc02>... What are the explain plan and the tkprof results? It is rare to have to
put results to a temp table to get better performance. My guess is that
there is a function on a column and that forces a full table scan. But I have
no way of knowing without seeing the query and the explain plan results.
Jim
--
Replace part of the email address: ke************************@attbi.com
with family. Remove the negative part, keep the minus sign. You can
figure it out.
"Mahesh Hardikar" <ha*******@yahoo.com> wrote in message
news:4a**************************@posting.google.c om...
Hi ,
I tried eliminating trunc . but still it is doing FULL Scan of Table
ICWOIMP.
We are anyway trying to eliminate SUbtotals & SUm at Report level . I
have asked them to put this into main query in Oracle.
Thanks for the inputs ....
Mahesh
"Jim Kennedy" <kennedy-down_with_spammers@no_spam.comcast.net> wrote in message news:<XctRa.80274$ye4.60537@sccrnsc01>... get rid of the distinct and replace: WHERE TRUNC(A.CANDATE) BETWEEN '01-jan-2003' AND '30-jan-2003'
with WHERE A.CANDATE BETWEEN to_date('01-jan-2003','dd-mmm-yyyy') AND
to_date('30-jan-2003','dd-mmm-yyyy')
(don't rely on the implicit sting to date conversion, it will bite you
later.)The reson it is full scanning table A is the trunc on the column.
With the between clause you don't need it.
See if those things help.
Jim
--
Replace part of the email address: ke************************@attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Mahesh Hardikar" <ha*******@yahoo.com> wrote in message
news:4a**************************@posting.google.c om... Hi ,
Thanks for your inputs.
Here goes the query :
/************************************************** *********
SELECT DISTINCT
A.ORGNSTNCODE AS OULCODE,
K.DESCRIPTION AS OUL,
K1.DESCRIPTION AS LOCCODE,
A.SALESMANCODE AS SALESMANCODE,
B.DESCRIPTION AS SALESPERSON,
A.AGNTCODE AS AGENTCODE,
D.PRINTDESCRIP AS AGENT,
A.AWBNO AS MAWBNO,
A.WONO AS WONO,
A.WOKEY AS WOKEY,
A.MODECODE ,
A.HAWBNO AS HAWBNO,
A.HAWBDATE AS HAWBDATE,
N.INVKEY ,
N.INVNO AS BILLNO,
N.INVDATE AS BILLDATE,
A.CARRIERCODE AS CARRIERCODE,
E.DESCRIPTION AS CARRIER,
A.ORIPORTCODE AS ORIGINPORTCODE,
P1.DESCRIPTION AS ORIGIN,
A.DESTPORTCODE AS DESTPORTCODE,
P2.DESCRIPTION AS DESTINATION,
A.ORGNSTNCODE REVENUESTN,
A.CUSTOMERCODE AS CUSTOMERCODE,
DECODE(A.CUSTOMERDESC, NULL, A1.DESCRIPTION,
A.CUSTOMERDESC) AS CUSTOMER,
A.PONO AS PONO,
H.AFLCMDTY AS COMMCODE,
I.DESCRIPTION AS COMMODITY,
H.NOPKGS AS PKG,
H.AFLGROSSWT AS GROSSWT,
A.GROSSUOM AS GROSSUOMCODE,
J.DESCRIPTION AS GROSSUOM,
H.AFLCHRGWT AS CHRGQTY,
H.CONTTYPE AS CONTTYPE,
H.CONTSIZE AS CONTSIZE,
M.PPCC AS PPCC,
A.FFCURR AS FFCURR,
NVL(M.FRTAMT,0) AS FRTAMT,
A.FFEXCHRATE ,
A.ORIEXCHRATE ,
DECODE(A.RTDBYCODE, '1', 'AGENT', '2', 'AFL', '') AS RTDBYCODE
FROM
ICWOIMP A,
COCOPHDR A1,
COSALESMAN B,
ICAGENTHDR D,
ICCARRIERHEADER E,
ICCONTDTLS H,
ICCOMMODITY I,
ICPORT P1,
ICPORT P2,
COUOM J,
COOUL K,
COPRODUCT L,
ICWOFRTDTL M,
ICINVHDR N,
COOUL K1
WHERE TRUNC(A.CANDATE) BETWEEN '01-jan-2003' AND '30-jan-2003'
AND A.STATUS = 'C'
AND A.STAGE NOT IN ('S','C')
AND A.ORGNSTNCODE = K.OULCODE
AND A.LOCCODE = K1.OULCODE
AND A.CUSTOMERCODE = A1.CUSTOMERCODE
AND A.SALESMANCODE = B.SALESMANCODE
AND A.AGNTCODE = D.AGENTCODE
AND A.CARRIERCODE = E.CARRIERCODE
AND A.WOKEY = H.WOKEY(+)
AND H.AFLCMDTY = I.COMMCODE(+)
AND A.GROSSUOM = J.UOMCODE(+)
AND A.ORIPORTCODE = P1.PORTCODE
AND A.DESTPORTCODE = P2.PORTCODE
AND A.PRODUCTCODE = L.PRODUCTCODE
AND L.PARENT = 'IMP'
AND A.WOKEY = M.WOKEY(+)
AND A.WOKEY = N.LINKREF1KEY(+)
AND N.TRANTYPE(+) = 'ICCAN'
ORDER BY A.WONO
************************************************** ****/
Here is the execution plan :
/*******************************
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1989 Card=24 Bytes=1
1088)
1 0 SORT (UNIQUE) (Cost=1984 Card=24 Bytes=11088)
2 1 HASH JOIN (OUTER) (Cost=1978 Card=24 Bytes=11088)
3 2 NESTED LOOPS (OUTER) (Cost=1976 Card=24 Bytes=10416)
4 3 NESTED LOOPS (Cost=1930 Card=23 Bytes=9407)
5 4 HASH JOIN (OUTER) (Cost=1907 Card=23 Bytes=8671)
6 5 HASH JOIN (OUTER) (Cost=1611 Card=23 Bytes=7751)
7 6 NESTED LOOPS (OUTER) (Cost=1297 Card=23 Bytes=
7498)
8 7 HASH JOIN (Cost=1274 Card=23 Bytes=6785)
9 8 HASH JOIN (Cost=1268 Card=23 Bytes=6486)
10 9 HASH JOIN (Cost=1262 Card=23 Bytes=6187)
11 10 HASH JOIN (Cost=1258 Card=23
Bytes=5267)
12 11 HASH JOIN (Cost=1255 Card=23
Bytes=4669)
13 12 HASH JOIN (Cost=1251 Card=23
Bytes=4370)
14 13 HASH JOIN (Cost=1247 Card=23
Bytes=4071)
15 14 HASH JOIN (Cost=1245 Card=23
Bytes=3634)
16 15 TABLE ACCESS (FULL) OF
'COPRODUCT' (Cost=2 Card=2 Bytes=12)
17 15 TABLE ACCESS (FULL) OF
'ICWOIMP' (Cost=1242 Card=23 Bytes=3496)
18 14 TABLE ACCESS (FULL) OF
'COSALESMAN' (Cost=1 Card=72 Bytes=1368)
19 13 TABLE ACCESS (FULL) OF 'COOUL'
(Cost=3 Card=237 Bytes=3081)
20 12 TABLE ACCESS (FULL) OF 'COOUL'
(Cost=3 Card=237 Bytes=3081)
21 11 TABLE ACCESS (FULL) OF
'ICCARRIERHEADER' (Cost=2 Card=263 Bytes=6838)
22 10 TABLE ACCESS (FULL) OF 'ICAGENTHDR'
(Cost=3 Card=267 Bytes=10680)
23 9 TABLE ACCESS (FULL) OF 'ICPORT' (Cost=5
Card=1160 Bytes=15080)
24 8 TABLE ACCESS (FULL) OF 'ICPORT' (Cost=5
Card=1160 Bytes=15080)
25 7 TABLE ACCESS (BY INDEX ROWID) OF 'COUOM'
(Cost=1 Card=1245 Bytes=38595)
26 25 INDEX (UNIQUE SCAN) OF 'SYS_C003223'
(UNIQUE)
27 6 TABLE ACCESS (FULL) OF 'ICWOFRTDTL'
(Cost=311Card=43971 Bytes=483681)
28 5 TABLE ACCESS (FULL) OF 'ICINVHDR' (Cost=295
Card=6572 Bytes=262880)
29 4 TABLE ACCESS (BY INDEX ROWID) OF 'COCOPHDR'
(Cost=1 Card=11027 Bytes=352864)
30 29 INDEX (UNIQUE SCAN) OF 'UK_COCOPHDR' (UNIQUE)
31 3 TABLE ACCESS (BY INDEX ROWID) OF 'ICCONTDTLS'
(Cost=2 Card=51807 Bytes=1295175)
32 31 INDEX (RANGE SCAN) OF 'INDX_ICCONTDTLS_WOKEY'
(NON-UNIQUE) (Cost=1 Card=51807)
33 2 TABLE ACCESS (FULL) OF 'ICCOMMODITY' (Cost=1 Card=59
Bytes=1652)
**************************************************/
The main table ICWOIMP (Rows 36000) has index on Column STATUS but
execution plan is not using it . Instead it is using TABLE SCAN.
Surprisingly when I ran the query thru SQL-Plus , it was pretty fast.
Gave me o/p in a min. But many times I see this report getting
cancelled after 30 Mins theu 9iAS Report Server Jobs.
I will also talk to developer if they are doing something at Report
level which is killing the process.
Regards,
Mahesh
"Jim Kennedy" <kennedy-down_with_spammers@no_spam.comcast.net> wrote in
message news:<cnbRa.79047$H17.22579@sccrnsc02>... What are the explain plan and the tkprof results? It is rare to have to put results to a temp table to get better performance. My guess is that there is a function on a column and that forces a full table scan. But I have no way of knowing without seeing the query and the explain plan results.
Jim
--
Replace part of the email address: ke************************@attbi.com
with family. Remove the negative part, keep the minus sign. You can figure it out.
"Mahesh Hardikar" <ha*******@yahoo.com> wrote in message
news:4a**************************@posting.google.c om...
was it faster?
--
Replace part of the email address: ke************************@attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Mahesh Hardikar" <ha*******@yahoo.com> wrote in message
news:4a**************************@posting.google.c om... Hi ,
I tried eliminating trunc . but still it is doing FULL Scan of Table
ICWOIMP.
We are anyway trying to eliminate SUbtotals & SUm at Report level . I
have asked them to put this into main query in Oracle.
Thanks for the inputs ....
Mahesh
"Jim Kennedy" <kennedy-down_with_spammers@no_spam.comcast.net> wrote in
message news:<XctRa.80274$ye4.60537@sccrnsc01>... get rid of the distinct and replace: WHERE TRUNC(A.CANDATE) BETWEEN '01-jan-2003' AND '30-jan-2003'
with WHERE A.CANDATE BETWEEN to_date('01-jan-2003','dd-mmm-yyyy') AND
to_date('30-jan-2003','dd-mmm-yyyy')
(don't rely on the implicit sting to date conversion, it will bite you
later.)The reson it is full scanning table A is the trunc on the column.
With the between clause you don't need it.
See if those things help.
Jim
--
Replace part of the email address: ke************************@attbi.com
with family. Remove the negative part, keep the minus sign. You can
figure it out.
"Mahesh Hardikar" <ha*******@yahoo.com> wrote in message
news:4a**************************@posting.google.c om... Hi ,
Thanks for your inputs.
Here goes the query :
/************************************************** *********
SELECT DISTINCT
A.ORGNSTNCODE AS OULCODE,
K.DESCRIPTION AS OUL,
K1.DESCRIPTION AS LOCCODE,
A.SALESMANCODE AS SALESMANCODE,
B.DESCRIPTION AS SALESPERSON,
A.AGNTCODE AS AGENTCODE,
D.PRINTDESCRIP AS AGENT,
A.AWBNO AS MAWBNO,
A.WONO AS WONO,
A.WOKEY AS WOKEY,
A.MODECODE ,
A.HAWBNO AS HAWBNO,
A.HAWBDATE AS HAWBDATE,
N.INVKEY ,
N.INVNO AS BILLNO,
N.INVDATE AS BILLDATE,
A.CARRIERCODE AS CARRIERCODE,
E.DESCRIPTION AS CARRIER,
A.ORIPORTCODE AS ORIGINPORTCODE,
P1.DESCRIPTION AS ORIGIN,
A.DESTPORTCODE AS DESTPORTCODE,
P2.DESCRIPTION AS DESTINATION,
A.ORGNSTNCODE REVENUESTN,
A.CUSTOMERCODE AS CUSTOMERCODE,
DECODE(A.CUSTOMERDESC, NULL, A1.DESCRIPTION,
A.CUSTOMERDESC) AS CUSTOMER,
A.PONO AS PONO,
H.AFLCMDTY AS COMMCODE,
I.DESCRIPTION AS COMMODITY,
H.NOPKGS AS PKG,
H.AFLGROSSWT AS GROSSWT,
A.GROSSUOM AS GROSSUOMCODE,
J.DESCRIPTION AS GROSSUOM,
H.AFLCHRGWT AS CHRGQTY,
H.CONTTYPE AS CONTTYPE,
H.CONTSIZE AS CONTSIZE,
M.PPCC AS PPCC,
A.FFCURR AS FFCURR,
NVL(M.FRTAMT,0) AS FRTAMT,
A.FFEXCHRATE ,
A.ORIEXCHRATE ,
DECODE(A.RTDBYCODE, '1', 'AGENT', '2', 'AFL', '') AS RTDBYCODE
FROM
ICWOIMP A,
COCOPHDR A1,
COSALESMAN B,
ICAGENTHDR D,
ICCARRIERHEADER E,
ICCONTDTLS H,
ICCOMMODITY I,
ICPORT P1,
ICPORT P2,
COUOM J,
COOUL K,
COPRODUCT L,
ICWOFRTDTL M,
ICINVHDR N,
COOUL K1
WHERE TRUNC(A.CANDATE) BETWEEN '01-jan-2003' AND '30-jan-2003'
AND A.STATUS = 'C'
AND A.STAGE NOT IN ('S','C')
AND A.ORGNSTNCODE = K.OULCODE
AND A.LOCCODE = K1.OULCODE
AND A.CUSTOMERCODE = A1.CUSTOMERCODE
AND A.SALESMANCODE = B.SALESMANCODE
AND A.AGNTCODE = D.AGENTCODE
AND A.CARRIERCODE = E.CARRIERCODE
AND A.WOKEY = H.WOKEY(+)
AND H.AFLCMDTY = I.COMMCODE(+)
AND A.GROSSUOM = J.UOMCODE(+)
AND A.ORIPORTCODE = P1.PORTCODE
AND A.DESTPORTCODE = P2.PORTCODE
AND A.PRODUCTCODE = L.PRODUCTCODE
AND L.PARENT = 'IMP'
AND A.WOKEY = M.WOKEY(+)
AND A.WOKEY = N.LINKREF1KEY(+)
AND N.TRANTYPE(+) = 'ICCAN'
ORDER BY A.WONO
************************************************** ****/
Here is the execution plan :
/*******************************
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1989 Card=24 Bytes=1
1088)
1 0 SORT (UNIQUE) (Cost=1984 Card=24 Bytes=11088)
2 1 HASH JOIN (OUTER) (Cost=1978 Card=24 Bytes=11088)
3 2 NESTED LOOPS (OUTER) (Cost=1976 Card=24 Bytes=10416)
4 3 NESTED LOOPS (Cost=1930 Card=23 Bytes=9407)
5 4 HASH JOIN (OUTER) (Cost=1907 Card=23 Bytes=8671)
6 5 HASH JOIN (OUTER) (Cost=1611 Card=23 Bytes=7751)
7 6 NESTED LOOPS (OUTER) (Cost=1297 Card=23 Bytes=
7498)
8 7 HASH JOIN (Cost=1274 Card=23 Bytes=6785)
9 8 HASH JOIN (Cost=1268 Card=23 Bytes=6486)
10 9 HASH JOIN (Cost=1262 Card=23 Bytes=6187)
11 10 HASH JOIN (Cost=1258 Card=23
Bytes=5267)
12 11 HASH JOIN (Cost=1255 Card=23
Bytes=4669)
13 12 HASH JOIN (Cost=1251 Card=23
Bytes=4370)
14 13 HASH JOIN (Cost=1247 Card=23
Bytes=4071)
15 14 HASH JOIN (Cost=1245 Card=23
Bytes=3634)
16 15 TABLE ACCESS (FULL) OF
'COPRODUCT' (Cost=2 Card=2 Bytes=12)
17 15 TABLE ACCESS (FULL) OF
'ICWOIMP' (Cost=1242 Card=23 Bytes=3496)
18 14 TABLE ACCESS (FULL) OF
'COSALESMAN' (Cost=1 Card=72 Bytes=1368)
19 13 TABLE ACCESS (FULL) OF 'COOUL'
(Cost=3 Card=237 Bytes=3081)
20 12 TABLE ACCESS (FULL) OF 'COOUL'
(Cost=3 Card=237 Bytes=3081)
21 11 TABLE ACCESS (FULL) OF
'ICCARRIERHEADER' (Cost=2 Card=263 Bytes=6838)
22 10 TABLE ACCESS (FULL) OF 'ICAGENTHDR'
(Cost=3 Card=267 Bytes=10680)
23 9 TABLE ACCESS (FULL) OF 'ICPORT' (Cost=5
Card=1160 Bytes=15080)
24 8 TABLE ACCESS (FULL) OF 'ICPORT' (Cost=5
Card=1160 Bytes=15080)
25 7 TABLE ACCESS (BY INDEX ROWID) OF 'COUOM'
(Cost=1 Card=1245 Bytes=38595)
26 25 INDEX (UNIQUE SCAN) OF 'SYS_C003223'
(UNIQUE)
27 6 TABLE ACCESS (FULL) OF 'ICWOFRTDTL'
(Cost=311Card=43971 Bytes=483681)
28 5 TABLE ACCESS (FULL) OF 'ICINVHDR' (Cost=295
Card=6572 Bytes=262880)
29 4 TABLE ACCESS (BY INDEX ROWID) OF 'COCOPHDR'
(Cost=1 Card=11027 Bytes=352864)
30 29 INDEX (UNIQUE SCAN) OF 'UK_COCOPHDR' (UNIQUE)
31 3 TABLE ACCESS (BY INDEX ROWID) OF 'ICCONTDTLS'
(Cost=2 Card=51807 Bytes=1295175)
32 31 INDEX (RANGE SCAN) OF 'INDX_ICCONTDTLS_WOKEY'
(NON-UNIQUE) (Cost=1 Card=51807)
33 2 TABLE ACCESS (FULL) OF 'ICCOMMODITY' (Cost=1 Card=59
Bytes=1652)
**************************************************/
The main table ICWOIMP (Rows 36000) has index on Column STATUS but
execution plan is not using it . Instead it is using TABLE SCAN.
Surprisingly when I ran the query thru SQL-Plus , it was pretty fast.
Gave me o/p in a min. But many times I see this report getting
cancelled after 30 Mins theu 9iAS Report Server Jobs.
I will also talk to developer if they are doing something at Report
level which is killing the process.
Regards,
Mahesh
"Jim Kennedy" <kennedy-down_with_spammers@no_spam.comcast.net> wrote
in message news:<cnbRa.79047$H17.22579@sccrnsc02>... > What are the explain plan and the tkprof results? It is rare to
have to put > results to a temp table to get better performance. My guess is that
there > is a function on a column and that forces a full table scan. But I
have no > way of knowing without seeing the query and the explain plan
results. > Jim
>
> --
> Replace part of the email address:
ke************************@attbi.com > with family. Remove the negative part, keep the minus sign. You
can figure > it out.
> "Mahesh Hardikar" <ha*******@yahoo.com> wrote in message
> news:4a**************************@posting.google.c om...
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Marcus |
last post by:
Hi
I have a very complex sql query and a explain plan. I found there is a
full table scan in ID=9
9 8 TABLE ACCESS (FULL) OF 'F_LOTTXNHIST'
(Cost=84573 Card=185892...
|
by: Starbuck |
last post by:
OK, first let me say that I am no DB person.
But I have a user here who keeps getting this error
whenever she does, whatever it is she does, with
databases...
A google search takes me to...
|
by: Mikel |
last post by:
I am trying to get around the problem "The expression you have entered
is too complex" for a select query. (The example below is not the
expression that is giving me headaches.)
So I am thinking...
|
by: ED |
last post by:
I am attempting to to write a query that has a numerous nested IIf
statements. The problem that I am having is that it is to long of a
query to be built in design mode and when I build it in sql...
|
by: Ross A. Finlayson |
last post by:
Hi,
I'm scratching together an Access database. The development box is
Office 95, the deployment box Office 2003.
So anyways I am griping about forms and global variables. Say for
example...
|
by: phlype.johnson |
last post by:
Suppose we have to design a database for a recruitment agency. There
will be a table "candidates" with fields "candidateid","last
name","first name" ; the languages mastered by a candidate as
well...
|
by: Slower Than You |
last post by:
I am trying to write an SQL UPDATE statement for an MSAccess table and
am having some problems getting my head around it. Can anyone help?
TableName: CustTransactions
TransactionKey AutoNumber ...
|
by: Mahesh Hardikar |
last post by:
Hi ,
Oracle 8.1.7.0.0 on HP-UX 11.0
I am a newbie as far as PL-SQL is concerned . I have Sybase/MSSQL
T-SQL background.
We have a report which uses a select statement . This select...
|
by: Rudolf Bargholz |
last post by:
Hi,
We have created triggers to log modifications to tables in our application.
The triggers work fine, just on one of the tables in our database the
triggers fail with the error message...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |
