Query optimization

[badrinathn]

Hi,

I am looking at some one to review this QEP as this is taking more than 40 seconds to run this query.
Query :
Tables used : AGGR_PL_FISCAL_PERIOD = 8million rows
LU_ACCOUNT = 4million rows
LU_ACCOUNT = 1Million rows


select a11.TIME_PERIOD_IND_ID TIME_PERIOD_IND_ID,
a11.LEDGER_TYPE_ID LEDGER_TYPE_ID,
a11.FISCAL_PERIOD_ID FISCAL_PERIOD_ID,
sum(a11.PL_PERIOD_AMT_LOCAL) WJXBFS1
from AGGR_PL_FISCAL_PERIOD a11,
LU_ACCOUNT a12,
LU_ACCOUNT_LOD6 a13
where a11.ACCOUNT_ID = a12.ACCOUNT_ID and
a12.LOD6_ID = a13.LOD6_ID
and (a11.FISCAL_PERIOD_ID between TO_CHAR(ADD_MONTHS(TO_DATE( '200405','yyyymm'),-11),'yyyymm') and TO_CHAR(ADD_MONTHS(TO_DATE( '200405','yyyymm'),-0),'yyyymm')
and a11.LEDGER_TYPE_ID in (1877)
and a11.TIME_PERIOD_IND_ID in (1)
and a13.LOD6_OBJECT_ACCOUNT_SRCCD = '65100'
and ((a12.BUSINESS_UNIT_ID in (156569, 156590, 156591, 156947, 156948, 156949, 156950, 156951)
and a12.COMPANY_ID in (3023))
or (a12.BUSINESS_UNIT_ID in (156571, 156989, 156990, 156991, 157007, 157008, 157009, 157010)
and a12.COMPANY_ID in (3023))
or (a12.BUSINESS_UNIT_ID in (156607, 157011, 157027, 157028, 157029, 157030, 157031, 157047)
and a12.COMPANY_ID in (3023))
or (a12.BUSINESS_UNIT_ID in (156570, 156967, 156968, 156969, 156970, 156971, 156987, 156988)
and a12.COMPANY_ID in (3023))
or (a12.BUSINESS_UNIT_ID in (157514, 157497, 157498, 157499, 157500, 157501, 157512, 157513)
and a12.COMPANY_ID in (3023))
or (a12.BUSINESS_UNIT_ID in (156568, 156608, 156609, 156610, 156611, 156587, 156588, 156589)
and a12.COMPANY_ID in (3023))))
group by a11.TIME_PERIOD_IND_ID,
a11.LEDGER_TYPE_ID,
a11.FISCAL_PERIOD_ID
/
Will post the trace file in the next thread

[badrinathn]

TKPROF: Release 9.2.0.4.0 - Production on Mon Jun 14 08:27:14 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Trace file: biasia_ora_10975.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

Error in CREATE TABLE of EXPLAIN PLAN table: BIUSER.prof$plan_table
ORA-00955: name is already used by an existing object

parse error offset: 20
EXPLAIN PLAN option disabled.
********************************************************************************

alter session set events '10046 trace name context forever , level 12'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 62

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 17.56 17.56
********************************************************************************

alter session set events '10053 trace name context forever, level 1'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 62

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 4.44 4.44
********************************************************************************

select a11.TIME_PERIOD_IND_ID TIME_PERIOD_IND_ID,
a11.LEDGER_TYPE_ID LEDGER_TYPE_ID,
a11.FISCAL_PERIOD_ID FISCAL_PERIOD_ID,
sum(a11.PL_PERIOD_AMT_LOCAL) WJXBFS1
from AGGR_PL_FISCAL_PERIOD a11,
LU_ACCOUNT a12,
LU_ACCOUNT_LOD6 a13
where a11.ACCOUNT_ID = a12.ACCOUNT_ID and
a12.LOD6_ID = a13.LOD6_ID
and (a11.FISCAL_PERIOD_ID between TO_CHAR(ADD_MONTHS(TO_DATE( '200405','yyyymm'),-11),'yyyymm') and TO_CHAR(ADD_MONTHS(TO_DATE( '200405','yyyymm'),-0),'yyyymm')
and a11.LEDGER_TYPE_ID in (1877)
and a11.TIME_PERIOD_IND_ID in (1)
and a13.LOD6_OBJECT_ACCOUNT_SRCCD = '63900'
and ((a12.BUSINESS_UNIT_ID in (156569, 156590, 156591, 156947, 156948, 156949, 156950, 156951)
and a12.COMPANY_ID in (3023))
or (a12.BUSINESS_UNIT_ID in (156571, 156989, 156990, 156991, 157007, 157008, 157009, 157010)
and a12.COMPANY_ID in (3023))
or (a12.BUSINESS_UNIT_ID in (156607, 157011, 157027, 157028, 157029, 157030, 157031, 157047)
and a12.COMPANY_ID in (3023))
or (a12.BUSINESS_UNIT_ID in (156570, 156967, 156968, 156969, 156970, 156971, 156987, 156988)
and a12.COMPANY_ID in (3023))
or (a12.BUSINESS_UNIT_ID in (157514, 157497, 157498, 157499, 157500, 157501, 157512, 157513)
and a12.COMPANY_ID in (3023))
or (a12.BUSINESS_UNIT_ID in (156568, 156608, 156609, 156610, 156611, 156587, 156588, 156589)
and a12.COMPANY_ID in (3023))))
group by a11.TIME_PERIOD_IND_ID,
a11.LEDGER_TYPE_ID,
a11.FISCAL_PERIOD_ID

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.12 0.13 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 14.60 15.24 0 1340441 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 14.72 15.38 0 1340441 0 7

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 62

Rows Row Source Operation
------- ---------------------------------------------------
7 SORT GROUP BY (cr=1340441 r=0 w=0 time=15247163 us)
357 HASH JOIN (cr=1340441 r=0 w=0 time=15244187 us)
14893 TABLE ACCESS BY INDEX ROWID LU_ACCOUNT_LOD6 (cr=3070 r=0 w=0 time=82395 us)
14893 INDEX RANGE SCAN LOD6_OBJ_IDX_01 (cr=49 r=0 w=0 time=12499 us)(object id 42908)
13293 NESTED LOOPS (cr=1337371 r=0 w=0 time=15115938 us)
391755 TABLE ACCESS BY INDEX ROWID AGGR_PL_FISCAL_PERIOD (cr=162104 r=0 w=0 time=2573807 us)
391755 BITMAP CONVERSION TO ROWIDS (cr=538 r=0 w=0 time=218600 us)
190 BITMAP INDEX RANGE SCAN AGGR_PL_FISCAL_INDX1 (cr=538 r=0 w=0 time=13603 us)(object id 49228)
13293 TABLE ACCESS BY INDEX ROWID LU_ACCOUNT (cr=1175267 r=0 w=0 time=11337688 us)
391755 INDEX UNIQUE SCAN SYS_C0014576 (cr=783512 r=0 w=0 time=3733693 us)(object id 42896)

[badrinathn]

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 4.33 4.41
********************************************************************************

select a11.TIME_PERIOD_IND_ID TIME_PERIOD_IND_ID,
a11.LEDGER_TYPE_ID LEDGER_TYPE_ID,
a11.FISCAL_PERIOD_ID FISCAL_PERIOD_ID,
sum(a11.PL_PERIOD_AMT_LOCAL) WJXBFS1
from AGGR_PL_FISCAL_PERIOD a11,
LU_ACCOUNT a12,
LU_ACCOUNT_LOD6 a13
where a11.ACCOUNT_ID = a12.ACCOUNT_ID and
a12.LOD6_ID = a13.LOD6_ID
and (a11.FISCAL_PERIOD_ID between TO_CHAR(ADD_MONTHS(TO_DATE( '200405','yyyymm'),-11),'yyyymm') and TO_CHAR(ADD_MONTHS(TO_DATE( '200405','yyyymm'),-0),'yyyymm')
and a11.LEDGER_TYPE_ID in (1877)
and a11.TIME_PERIOD_IND_ID in (1)
and a13.LOD6_OBJECT_ACCOUNT_SRCCD = '64100'
and ((a12.BUSINESS_UNIT_ID in (156569, 156590, 156591, 156947, 156948, 156949, 156950, 156951)
and a12.COMPANY_ID in (3023))
or (a12.BUSINESS_UNIT_ID in (156571, 156989, 156990, 156991, 157007, 157008, 157009, 157010)
and a12.COMPANY_ID in (3023))
or (a12.BUSINESS_UNIT_ID in (156607, 157011, 157027, 157028, 157029, 157030, 157031, 157047)
and a12.COMPANY_ID in (3023))
or (a12.BUSINESS_UNIT_ID in (156570, 156967, 156968, 156969, 156970, 156971, 156987, 156988)
and a12.COMPANY_ID in (3023))
or (a12.BUSINESS_UNIT_ID in (157514, 157497, 157498, 157499, 157500, 157501, 157512, 157513)
and a12.COMPANY_ID in (3023))
or (a12.BUSINESS_UNIT_ID in (156568, 156608, 156609, 156610, 156611, 156587, 156588, 156589)
and a12.COMPANY_ID in (3023))))
group by a11.TIME_PERIOD_IND_ID,
a11.LEDGER_TYPE_ID,
a11.FISCAL_PERIOD_ID

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.14 0.15 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 15.03 30.72 1113 1340462 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 15.17 30.87 1113 1340462 0 7

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 62

Rows Row Source Operation
------- ---------------------------------------------------
7 SORT GROUP BY (cr=1340462 r=1113 w=0 time=30722527 us)
616 HASH JOIN (cr=1340462 r=1113 w=0 time=30718502 us)
14924 TABLE ACCESS BY INDEX ROWID LU_ACCOUNT_LOD6 (cr=3091 r=1113 w=0 time=15486382 us)
14924 INDEX RANGE SCAN LOD6_OBJ_IDX_01 (cr=48 r=45 w=0 time=429005 us)(object id 42908)
13293 NESTED LOOPS (cr=1337371 r=0 w=0 time=15168234 us)
391755 TABLE ACCESS BY INDEX ROWID AGGR_PL_FISCAL_PERIOD (cr=162104 r=0 w=0 time=2611024 us)
391755 BITMAP CONVERSION TO ROWIDS (cr=538 r=0 w=0 time=217191 us)
190 BITMAP INDEX RANGE SCAN AGGR_PL_FISCAL_INDX1 (cr=538 r=0 w=0 time=14299 us)(object id 49228)
13293 TABLE ACCESS BY INDEX ROWID LU_ACCOUNT (cr=1175267 r=0 w=0 time=11406918 us)
391755 INDEX UNIQUE SCAN SYS_C0014576 (cr=783512 r=0 w=0 time=3752599 us)(object id 42896)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 1113 0.72 15.22
SQL*Net message from client 2 21.26 21.34
********************************************************************************

select a11.TIME_PERIOD_IND_ID TIME_PERIOD_IND_ID,
a11.LEDGER_TYPE_ID LEDGER_TYPE_ID,
a11.FISCAL_PERIOD_ID FISCAL_PERIOD_ID,
sum(a11.PL_PERIOD_AMT_LOCAL) WJXBFS1
from AGGR_PL_FISCAL_PERIOD a11,
LU_ACCOUNT a12,
LU_ACCOUNT_LOD6 a13
where a11.ACCOUNT_ID = a12.ACCOUNT_ID and
a12.LOD6_ID = a13.LOD6_ID
and (a11.FISCAL_PERIOD_ID between TO_CHAR(ADD_MONTHS(TO_DATE( '200405','yyyymm'),-11),'yyyymm') and TO_CHAR(ADD_MONTHS(TO_DATE( '200405','yyyymm'),-0),'yyyymm')
and a11.LEDGER_TYPE_ID in (1877)
and a11.TIME_PERIOD_IND_ID in (1)
and a13.LOD6_OBJECT_ACCOUNT_SRCCD = '65100'
and ((a12.BUSINESS_UNIT_ID in (156569, 156590, 156591, 156947, 156948, 156949, 156950, 156951)
and a12.COMPANY_ID in (3023))
or (a12.BUSINESS_UNIT_ID in (156571, 156989, 156990, 156991, 157007, 157008, 157009, 157010)
and a12.COMPANY_ID in (3023))
or (a12.BUSINESS_UNIT_ID in (156607, 157011, 157027, 157028, 157029, 157030, 157031, 157047)
and a12.COMPANY_ID in (3023))
or (a12.BUSINESS_UNIT_ID in (156570, 156967, 156968, 156969, 156970, 156971, 156987, 156988)
and a12.COMPANY_ID in (3023))
or (a12.BUSINESS_UNIT_ID in (157514, 157497, 157498, 157499, 157500, 157501, 157512, 157513)
and a12.COMPANY_ID in (3023))
or (a12.BUSINESS_UNIT_ID in (156568, 156608, 156609, 156610, 156611, 156587, 156588, 156589)
and a12.COMPANY_ID in (3023))))
group by a11.TIME_PERIOD_IND_ID,
a11.LEDGER_TYPE_ID,
a11.FISCAL_PERIOD_ID

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.13 0.13 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 15.58 25.78 1887 1340653 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 15.71 25.91 1887 1340653 0 7

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 62

Rows Row Source Operation
------- ---------------------------------------------------
7 SORT GROUP BY (cr=1340653 r=1887 w=0 time=25782608 us)
161 HASH JOIN (cr=1340653 r=1887 w=0 time=25780659 us)
23049 TABLE ACCESS BY INDEX ROWID OBJ#(42907) (cr=3282 r=1887 w=0 time=9997512 us)
23049 INDEX RANGE SCAN OBJ#(42908) (cr=74 r=71 w=0 time=522124 us)(object id 42908)
13293 NESTED LOOPS (cr=1337371 r=0 w=0 time=15700208 us)
391755 TABLE ACCESS BY INDEX ROWID OBJ#(42832) (cr=162104 r=0 w=0 time=2660936 us)
391755 BITMAP CONVERSION TO ROWIDS (cr=538 r=0 w=0 time=266123 us)
190 BITMAP INDEX RANGE SCAN OBJ#(49228) (cr=538 r=0 w=0 time=14247 us)(object id 49228)
13293 TABLE ACCESS BY INDEX ROWID OBJ#(42893) (cr=1175267 r=0 w=0 time=11861617 us)
391755 INDEX UNIQUE SCAN OBJ#(42896) (cr=783512 r=0 w=0 time=3884812 us)(object id 42896)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 1887 0.19 9.60
SQL*Net message from client 2 4.39 4.46



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.39 0.42 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 6 45.21 71.75 3000 4021556 0 21
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15 45.60 72.18 3000 4021556 0 21

Misses in library cache during parse: 4
Misses in library cache during execute: 1

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 8 0.00 0.00
SQL*Net message from client 8 21.26 52.23
db file sequential read 3000 0.72 24.83
log file sync 1 0.01 0.01



Trace file: biasia_ora_10975.trc
Trace file compatibility: 9.00.01
Sort options: default

1 session in tracefile.
5 user SQL statements in trace file.
0 internal SQL statements in trace file.
5 SQL statements in trace file.
5 unique SQL statements in trace file.
13241 lines in trace file.

[marist89]

I would look at reducing your LIOs.

[slimdave]

Originally posted by marist89
I would look at reducing your LIOs.

Those LIO's are probably the result of the hash join ... v. LIO-intensive, hash joins.

[badrinathn]

Thanks, Marist,

Right now it's using the bitmap indexes. Is that causing the increase LIO.

There are no full table scans in the explain plan and all i seee is the index lookups.

Pl Suggest

Badrinath

[badrinathn]

Thanks, Marist,

Right now it's using the bitmap indexes. Is that causing the increase LIO.

There are no full table scans in the explain plan and all i seee is the index lookups.

Pl Suggest

Badrinath

[slimdave]

How about a little parallelism?

[marist89]

Originally posted by slimdave
How about a little parallelism?

for 40 seconds worth of query?

[slimdave]

40 seconds is in the eye of the beholder ... on some systems it's nothing, on some it's too much. If there's spare capacity on the box that's not being used, then use it to reduce the 40 seconds to, say, 8.