Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Tuning Question (Long)

Tuning Question (Long)

From: New DBA <new_dba_on_the_block_at_yahoo.com>
Date: Thu, 18 Sep 2003 05:59:37 -0800
Message-ID: <F001.005D05FD.20030918055937@fatcity.com>


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,

   PRODUCT_INSTR_ID,
   CURRENCY_NAME,
   CURRENCY_CODE,
   CUR_SYS_NO
FROM
   EPR_PRICING_ASSET_STATUS_VIEW
WHERE
   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)

Order By MPAI_SYS_NO;  

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

SQL> Definition of the view "EPR_PRICING_ASSET_STATUS_VIEW"

SELECT
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_NO
 
 

----------------------------------------------
Statistics 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          71 
 
 

-------------------------------------------------
Statistics 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



Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
-- 
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 Thu Sep 18 2003 - 08:59:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US