Hi All,
I need help in tuning the following query. It takes around 6-7 minutes to run. I hope that someone will be able to go through the details and give me a few pointers.
I have gathered a few statistics, but don't know where to go from here.
Please view the mail in a fixed size font e.g. courier to preserve the formatting. If the lines wrap over copying and pasting in a text editor might help, though I'm not sure.
I apologize for the long message in advance.
Following is the query:
SELECT UNIQUE
MEPAI.MPAI_NAV_MOD ,
MEPAI.MPAI_NAV_MODS,
MEPAI.MPAI_SYS_NO,
MEPAI.MPAI_PAI_SYS_NO,
MEPAI.MPAI_AS_OF_DATE,
PRODUCTS.ISS_INSTR_ID PRODUCT_INSTR_ID,
CUR.CUR_CURRENCY_NAME,
CUR.CUR_CURRENCY_CODE,
CUR.CUR_SYS_NO
FROM
EPR_CURRENCIES CUR,
EPR_GEOGRAPHIES GEO,
EPR_PRODUCTS PRODUCTS,
MOD_EPR_PRICING_ASSET_INFO MEPAI
WHERE &nb!
sp;
MEPAI.MPAI_ISS_SYS_NO = PRODUCTS.ISS_SYS_NO
AND MEPAI.MPAI_GEO_SYS_NO = GEO.GEO_SYS_NO
AND MEPAI.MPAI_CUR_SYS_NO = CUR.CUR_SYS_NO
AND MEPAI.MPAI_AS_OF_DATE IN
(
to_date('03/01/2003','MM/DD/YYYY'), to_date('03/02/2003','MM/DD/YYYY')
, to_date('03/03/2003','MM/DD/YYYY'), to_date('03/04/2003','MM/DD/YYYY')
, to_date('03/05/2003','MM/DD/YYYY'), to_date('03/06/2003','MM/DD/YYYY')
, to_date('03/07/2003','MM/DD/YYYY'), to_date('03/08/2003','MM/DD/YYYY')
, to_date('03/09/2003','MM/DD/YYYY'), to_date('03/10/2003','MM/DD/YYYY')
, to_date('03/11/2003','MM/DD/YYYY'), to_date('03/12/2003','MM/DD/YYYY')
, to_date('03/13/2003','MM/DD/YYYY'), to_date('03/14/2003','MM/DD/YYYY')
, to_date('03/15/2003','MM/DD/YYYY'), to_date('03/16/2003','MM/DD/YYYY')
, to_date('03/17/2003','MM/DD/YYYY'), to_date('03/18/2003','MM/DD/YYYY')
 !
;,
to_date('03/19/2003','MM/DD/YYYY'), to_date('03/20/2003','MM/DD/YYYY')
, to_date('03/21/2003','MM/DD/YYYY'), to_date('03/22/2003','MM/DD/YYYY')
, to_date('03/23/2003','MM/DD/YYYY'), to_date('03/24/2003','MM/DD/YYYY')
, to_date('03/25/2003','MM/DD/YYYY'), to_date('03/26/2003','MM/DD/YYYY')
, to_date('03/27/2003','MM/DD/YYYY'), to_date('03/28/2003','MM/DD/YYYY')
, to_date('03/29/2003','MM/DD/YYYY'), to_date('03/30/2003','MM/DD/YYYY')
, to_date('03/31/2003','MM/DD/YYYY')
)
AND PRODUCTS.ISS_INSTR_ID in (1321,1339,1344,1342,1343,1341,1340)
AND CUR.CUR_SYS_NO in (200,226)
Order By MEPAI.MPAI_SYS_NO
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=515 Card=122 Bytes=8296)
1 0 SORT (UNIQUE) (Cost=503 Card=122 Bytes=8296)
2 1 CONCATENATION
3 2 NESTED LOOPS (Cost=223 Card=61 Bytes=4148)
4 3 HASH JOIN (Cost=223 Card=61 Bytes=3965)
5 4 INLIST ITERATOR
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'EPR_PRODUCTS' (Cost=3 Card=16 Bytes=128)
7
6 INDEX (RANGE SCAN) OF 'ISS_ISS_INSTR_ID' (NON-UNIQUE) (Cost=2 Card=16)
8 4 NESTED LOOPS (Cost=219 Card=4415 Bytes=251655)
9 8 TABLE ACCESS (BY INDEX ROWID) OF 'EPR_CURRENCIES' (Cost=1 Card=1 Bytes=21)
10 9 INDEX (UNIQUE SCAN) OF 'CUR_PK' (UNIQUE)
11 8 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'MOD_EPR_PRICING_ASSET_INFO' (Cost=218 Card=92720 Bytes=3337920)
12 11 INDEX (RANGE SCAN) OF
'MPAI_CUR_FK_I' (NON-UNIQUE) (Cost=217 Card=92720)
13 3 INDEX (UNIQUE SCAN) OF 'GEO_PK' (UNIQUE)
14 2 NESTED LOOPS (Cost=223 Card=61 Bytes=4148)
15 14 HASH JOIN (Cost=223 Card=61 Bytes=3965)
16 15 INLIST ITERATOR
17 16 TABLE ACCESS (BY INDEX ROWID) OF 'EPR_PRODUCTS' (Cost=3 Card=16 Bytes=128)
18 17 INDEX (RANGE SCAN) OF 'ISS_ISS_INSTR_ID' (NON-UNIQUE) (Cost=2 Card=16)
19 15 NESTED LOOPS (Cost=219 Card=4415
Bytes=251655)
20 19 TABLE ACCESS (BY INDEX ROWID) OF 'EPR_CURRENCIES' (Cost=1 Card=1 Bytes=21)
21 20 INDEX (UNIQUE SCAN) OF 'CUR_PK' (UNIQUE)
22 19 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'MOD_EPR_PRICING_ASSET_INFO' (Cost=218 Card=92720 Bytes=3337920)
23 22 INDEX (RANGE SCAN) OF 'MPAI_CUR_FK_I' (NON-UNIQUE) (Cost=217 Card=92720)
24 14 INDEX (UNIQUE SCAN) OF 'GEO_PK' (UNIQUE)
The output of the following query before running the SQL and after running the SQL are as follows:
SQL> select names.name, stats.value
2 from v$session sessions, v$sesstat stats, v$statname names
3 where stats.statistic# = names.statistic#
4 and stats.sid = sessions.sid
5 and sessions.audsid = userenv('SESSIONID')
6 and value != 0
7 order by value desc;
Before running the problem query:
NAME VALUE
------------------------------------------------- ----------
bytes sent via SQL*Net to client 3640
bytes received via SQL*Net from client 2587
SQL*Net roundtrips to/from client 40
user
calls 38
recursive calls 37
session logical reads 29
opened cursors
cumulative 17
buffer is not pinned count 17
db block gets 16
execute count
15
parse count (total) 15
consistent gets 13
no work - consistent read gets 10
calls to get snapshot scn: kcmgss 9
table fetch by
rowid 7
table scans (short tables) 4
opened cursors current 3
table scan rows gotten 2
free buffer
requested 2
parse time cpu 2
parse time elapsed 2
physical
reads 2
table scan blocks gotten 2
logons cumulative 1
parse count
(hard) 1
enqueue requests 1
hot buffers moved to head of LRU 1
CPU used when call started 1
CPU used by this
session 1
total file opens 1
enqueue releases 1
logons
current 1
After running the problem query:
NAME VALUE
------------------------------------------------- ----------
buffer is pinned count 5366822
table fetch by rowid 2850954
session logical
reads 560183
consistent gets 559985
no work - consistent read gets 559871
buffer is not pinned count 547204
table fetch continued
row 212027
free buffer requested 161921
physical reads 161920
hot buffers moved to head of LRU 41068
bytes sent via SQL*Net to
client 20455
redo size 17844
sorts (rows) 14977
bytes received via SQL*Net from client 13124
CPU used by this
session 7368
CPU used when call started 7368
recursive calls 929
table scan rows gotten 704
db block
gets 198
sorts (memory) 103
db block changes 103
user
calls 93
execute count 87
calls to get snapshot scn: kcmgss 82
SQL*Net roundtrips to/from client 76
table scan blocks
gotten 57
opened cursors cumulative 55
parse count (total) 54
redo entries 51
total file
opens 44
table scans (short tables) 36
parse time elapsed 18
parse time
cpu 17
rows fetched via callback 14
cluster key scans 14
cluster key scan block gets 14
parse count
(hard) 6
opened cursors current 3
enqueue requests 3
recursive cpu
usage 2
logons cumulative 1
messages sent 1
cleanouts only - consistent read gets 1
switch current t!
o new
buffer 1
free buffer inspected 1
enqueue releases 1
dirty buffers inspected 1
immediate (CR) block cleanout
applications 1
logons current 1
The output of the following query after running the problem query:
SQL> select event, total_waits, time_waited, average_wait
2 from v$session_event events, v$session sessions
3 where sessions.sid = events.sid
4 and sessions.audsid = USERENV('SESSIONID')
5 and time_waited !=0
6 and event not like 'SQL*Net%'
7 order by time_waited desc;
EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------------------------------------------------------------- ----------- ----------- ------------
db file sequential read 161920 33038 .204039032
file
open 44 1 .022727273
I shall appreciate if someone can help me in tuning this query.
Regards
New DBA
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software