|
P: n/a
|
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
| |
Share this Question
|
P: n/a
|
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 | |
|
P: n/a
| 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 | |
|
P: n/a
|
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... | |
|
P: n/a
|
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...
| |
|
P: n/a
|
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...
| |
|
P: n/a
|
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 discussion thread is closed Replies have been disabled for this discussion. | |  Question stats - viewed: 3895
- replies: 6
- date asked: Jul 19 '05
|
