In addition to the excellent advice you have already received,
let me congratulate you on taking the time to format your
code and trace data so that it is readable.
There are folks that are known to skip long posts such as
this when poorly formatted and difficult to read. ;)
Jared
New DBA <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED] 09/24/2003 12:39 AM
|
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Tuning help required |
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 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