| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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,
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_NOAND 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')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 to 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
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: New DBA INET: new_dba_on_the_block_at_yahoo.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Sep 24 2003 - 02:39:44 CDT
 |
 |