| 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 Question (Long)
Hi All,
I'm new on this list and need help on tuning the following query. I hope the gurus on this list will help me with ideas so that I can attack the problem with a much wider knowledge.
I'm providing you with all the details i can manage now, but 10046 trace data is not available since we don't have access to the servers. We have requested the client to provide us with 10046 trace data.
The following query runs in 6 seconds and the client wants it to perform faster.
The statistics below are from the production servers where this query was already run before, so it need not be parsed again, and all the fetches were logical fetches (no disk fetch).
I'm sorry for the loss of formatting (if any)....
Here goes the details:
SQL> set autotrace on SQL> set timing on SQL> SELECT
UNIQUE MPAI_NAV_MOD ,
MPAI_NAV_MODS, MPAI_SYS_NO, MPAI_PAI_SYS_NO, MPAI_AS_OF_DATE,
MPAI_AS_OF_DATE IN (to_date('03/31/2003','MM/DD/YYYY') ) and
PRODUCT_INSTR_ID in (1321,1339,1341,1340) and
CUR_SYS_NO in (200,226)
no rows selected
Elapsed: 00:00:05.66
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=482 Card=1 Bytes=68) 1 0 SORT (UNIQUE) (Cost=471 Card=1 Bytes=68)
2 1 CONCATENATION 3 2 NESTED LOOPS (Cost=230 Card=4 Bytes=272) 4 3 NESTED LOOPS (Cost=230 Card=4 Bytes=260) 5 4 NESTED LOOPS (Cost=218 Card=12 Bytes=684) 6 5 TABLE ACCESS (BY INDEX ROWID) OF 'EPR_CURRENCIES' (Cost=1 Card=1 Bytes=21) 7 6 INDEX (UNIQUE SCAN) OF 'CUR_PK' (UNIQUE) 8 5 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'MOD_EPR_PRICING_ASSET_INFO' (Cost=217 Card=244 Bytes=8784) 9 8 INDEX (RANGE SCAN) OF 'MPAI_CUR_FK_I' (NON-UNIQUE) (Cost=216 Card=244) 10 4 TABLE ACCESS (BY INDEX ROWID) OF 'EPR_PRODUCTS' (Cost=1 Card=9 Bytes=72) 11 10 INDEX (UNIQUE SCAN) OF 'ISS_PK' (UNIQUE) 12 3 INDEX (UNIQUE SCAN) OF 'GEO_PK' (UNIQUE) 13 2 NESTED LOOPS (Cost=230 Card=4 Bytes=272) 14 13 NESTED LOOPS (Cost=230 Card=4 Bytes=260) 15 14 NESTED LOOPS (Cost=218 Card=12 Bytes=684) 16 15 TABLE ACCESS (BY INDEX ROWID) OF 'EPR_CURRENCIES' (Cost=1 Card=1 Bytes=21) 17 16 INDEX (UNIQUE SCAN) OF 'CUR_PK' (UNIQUE) 18 15 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'MOD_EPR_PRICING_ASSET_INFO' (Cost=217 Card=244 Bytes=8784) 19 18 INDEX (RANGE SCAN) OF 'MPAI_CUR_FK_I' (NON-UNIQUE) (Cost=216 Card=244) 20 14 TABLE ACCESS (BY INDEX ROWID) OF 'EPR_PRODUCTS' (Cost=1 Card=9 Bytes=72) 21 20 INDEX (UNIQUE SCAN) OF 'ISS_PK' (UNIQUE) 22 13 INDEX (UNIQUE SCAN) OF 'GEO_PK' (UNIQUE)
Statistics
0 recursive calls
0 db block gets
60507 consistent gets
0 physical reads
0 redo size
616 bytes sent via SQL*Net to client
283 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
PRODUCTS.ISS_SYS_NO PRODUCT_ISS_SYS_NO, PRODUCTS.ISS_INSTR_ID PRODUCT_INSTR_ID, PRODUCTS.ISS_SHARE_CLASS SHARE_CLASS, PRODUCTS.ISS_DISPLAY_SHARE_CLASS DISPLAY_SHARE_CLASS, PRODUCTS.ISS_EFFECTIVE_START_DATE PRODUCT_START_DATE, PRODUCTS.ISS_EFFECTIVE_END_DATE PRODUCT_END_DATE, PRODUCTS.ISS_PRODUCT_NAME_DEFAULT PRODUCT_DEFAULT_NAME, GEO.GEO_SYS_NO GEO_SYS_NO, GEO.GEO_EFFECTIVE_START_DATE GEO_START_DATE, GEO.GEO_EFFECTIVE_END_DATE GEO_END_DATE, GEO.GEO_GEOGRAPHY_CODE GEOGRAPHY_CODE, GEO.GEO_GEOGRAPHY_ISO_CODE GEOGRAPHY_ISO_CODE, GEO.GEO_GEOGRAPHY_TYPE GEOGRAPHY_TYPE, GEO.GEO_GEOGRAPHY_NAME GEOGRAPHY_NAME, GEO.GEO_GEOGRAPHY_DESCRIPTION GEOGRAPHY_DESCRIPTION, GEO.GEO_LEAD_LANGUAGE_CODE LEAD_LANGUAGE_CODE, GEO.GEO_LEAD_CURRENCY_CODE LEAD_CURRENCY_CODE, GEO.GEO_DEFAULT_DATE_FORMAT DEFAULT_DATE_FORMAT, GEO.GEO_DEFAULT_NUMBER_FORMAT DEFAULT_NUMBER_FORMAT, GEO.GEO_COUNTRY_FLAG_URL COUNTRY_FLAG_URL, CUR.CUR_SYS_NO CUR_SYS_NO, CUR.CUR_EFFECTIVE_START_DATE CUR_EFFECTIVE_START_DATE, CUR.CUR_EFFECTIVE_END_DATE CUR_EFFECTIVE_END_DATE, CUR.CUR_CURRENCY_CODE CURRENCY_CODE, CUR.CUR_ISO_CURRENCY_CODE ISO_CURRENCY_CODE, CUR.CUR_CURRENCY_NAME CURRENCY_NAME, MEPAI.MPAI_SYS_NO MPAI_SYS_NO, MEPAI.MPAI_AS_OF_DATE MPAI_AS_OF_DATE, MEPAI.MPAI_STATUS MPAI_STATUS, MEPAI.MPAI_SOURCE_SYSTEM MPAI_SOURCE_SYSTEM, MEPAI.MPAI_TOTAL_NET_ASSETS_SOR MPAI_TOTAL_NET_ASSETS_SOR, MEPAI.MPAI_TOTAL_NET_ASSETS_MOD MPAI_TOTAL_NET_ASSETS_MOD, MEPAI.MPAI_TOTAL_NET_ASSETS_MODS MPAI_TOTAL_NET_ASSETS_MODS, MEPAI.MPAI_MANDATE_ASSETS_SOR MPAI_MANDATE_ASSETS_SOR, MEPAI.MPAI_MANDATE_ASSETS_MOD MPAI_MANDATE_ASSETS_MOD, MEPAI.MPAI_MANDATE_ASSETS_MODS MPAI_MANDATE_ASSETS_MODS, MEPAI.MPAI_NAV_SOR MPAI_NAV_SOR, MEPAI.MPAI_NAV_MOD MPAI_NAV_MOD, MEPAI.MPAI_NAV_MODS MPAI_NAV_MODS, MEPAI.MPAI_POP_SOR MPAI_POP_SOR, MEPAI.MPAI_POP_MOD MPAI_POP_MOD, MEPAI.MPAI_POP_MODS MPAI_POP_MODS, MEPAI.MPAI_HIGH_NAV_CALENDAR_SOR MPAI_HIGH_NAV_CALENDAR_SOR, MEPAI.MPAI_HIGH_NAV_CALENDAR_MOD MPAI_HIGH_NAV_CALENDAR_MOD, MEPAI.MPAI_HIGH_NAV_CALENDAR_MODS MPAI_HIGH_NAV_CALENDAR_MODS, MEPAI.MPAI_HIGH_NAV_ROLLING_SOR MPAI_HIGH_NAV_ROLLING_SOR, MEPAI.MPAI_HIGH_NAV_ROLLING_MOD MPAI_HIGH_NAV_ROLLING_MOD, MEPAI.MPAI_HIGH_NAV_ROLLING_MODS MPAI_HIGH_NAV_ROLLING_MODS, MEPAI.MPAI_HIGH_NAV_SINCE_INCEP_SOR MPAI_HIGH_NAV_SINCE_INCEP_SOR, MEPAI.MPAI_HIGH_NAV_SINCE_INCEP_MOD MPAI_HIGH_NAV_SINCE_INCEP_MOD, MEPAI.MPAI_HIGH_NAV_SINCE_INCEP_MODS MPAI_HIGH_NAV_SINCE_INCEP_MODS, MEPAI.MPAI_LOW_NAV_CALENDER_SOR MPAI_LOW_NAV_CALENDER_SOR, MEPAI.MPAI_LOW_NAV_CALENDER_MOD MPAI_LOW_NAV_CALENDER_MOD, MEPAI.MPAI_LOW_NAV_CALENDER_MODS MPAI_LOW_NAV_CALENDER_MODS, MEPAI.MPAI_LOW_NAV_ROLLING_SOR MPAI_LOW_NAV_ROLLING_SOR, MEPAI.MPAI_LOW_NAV_ROLLING_MOD MPAI_LOW_NAV_ROLLING_MOD, MEPAI.MPAI_LOW_NAV_ROLLING_MODS MPAI_LOW_NAV_ROLLING_MODS, MEPAI.MPAI_LOW_NAV_SINCE_INCEP_SOR MPAI_LOW_NAV_SINCE_INCEP_SOR, MEPAI.MPAI_LOW_NAV_SINCE_INCEP_MOD MPAI_LOW_NAV_SINCE_INCEP_MOD, MEPAI.MPAI_LOW_NAV_SINCE_INCEP_MODS MPAI_LOW_NAV_SINCE_INCEP_MODS, MEPAI.MPAI_MARKET_PRICE_SOR MPAI_MARKET_PRICE_SOR, MEPAI.MPAI_MARKET_PRICE_MOD MPAI_MARKET_PRICE_MOD, MEPAI.MPAI_MARKET_PRICE_MODS MPAI_MARKET_PRICE_MODS, MEPAI.MPAI_CENDPERF_PREM_DISC_SOR MPAI_CENDPERF_PREM_DISC_SOR, MEPAI.MPAI_CENDPERF_PREM_DISC_MOD MPAI_CENDPERF_PREM_DISC_MOD, MEPAI.MPAI_CENDPERF_PREM_DISC_MODS MPAI_CENDPERF_PREM_DISC_MODS, MEPAI.MPAI_PAI_SYS_NO MPAI_PAI_SYS_NO, MEPAI.MPAI_EPR_ORIG_LOAD_DATE MPAI_EPR_ORIG_LOAD_DATE FROM EPR_PRODUCTS PRODUCTS, EPR_GEOGRAPHIES GEO, EPR_CURRENCIES CUR, 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_NOStatistics of the tables involved:
----------------------------------------------
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN --------------------------- ---------- -------- ------------ ---------- ---------- ----------- MOD_EPR_PRICING_ASSET_INFO 16991315 758559 9425 555 0 333 EPR_CURRENCIES 227 2 253 784 0 62 EPR_PRODUCTS 215330 6599 56 605 612 230 EPR_GEOGRAPHIES 148 2 253 2728 0 71Statistics of the indexes involved:
-------------------------------------------------
INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS ------------------------------ --------------------------- ------------------------------ --------- ---------- ----------- ------------- ----------------------- ----------------------- ----------------- -------- ---------- CUR_PK NORMAL EPR_CURRENCIES UNIQUE 0 1 227 1 1 2 VALID 227 MPAI_CUR_FK_I NORMAL MOD_EPR_PRICING_ASSET_INFO NONUNIQUE 2 76430 354 215 1 947732 VALID 16952174 ISS_PK NORMAL EPR_PRODUCTS UNIQUE 1 470 230143 1 1 7833 VALID 230143.333 GEO_PK NORMAL EPR_GEOGRAPHIES UNIQUE 0 1 148 1 1 2 VALID 148
I want to get ideas from the list on what can be done to increase the performance of 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 servicesReceived on Thu Sep 18 2003 - 08:59:37 CDT
---------------------------------------------------------------------
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).
 |
 |