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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Tuning Answer (Short)

RE: Tuning Answer (Short)

From: New DBA <new_dba_on_the_block_at_yahoo.com>
Date: Thu, 18 Sep 2003 23:19:47 -0800
Message-ID: <F001.005D06B9.20030918231947@fatcity.com>


Following Stephane's advice, I rewrote the query to use underlying tables rather than the view.  

The execution plan looks much better and the elapsed time came down appreciably.  

My Question is , Since I was simply selecting from the view, eliminating a lot of columns and adding a few more predicates, why did Oracle not merge the view in the main query? (Oracle version 8.1.7.0.0)  

I was of the opinion, that Oracle will merge the view into the main query, since the main query was a simple select from a view with a few predicates.  

But I see the whole execution plan getting changed when I use the underlying tables directly rather than the view.  

Maybe, someone can enlighten me on this.  

Am interacting with the developers to see if the number of columns in the view can be brought down, or the query be used to select from the tables rather than the view itself.  

Following are the queries and the explain plan. If someone can study them and answer my question as to why view was not merged in the main query, I shall be thankful to him.  

I know the details are long and tedious. I can't help the formatting, Yahoo! spoils the formatting  

I rewrote the query



from

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
---
To 
---
SELECT 
MEPAI.MPAI_NAV_MOD                           MPAI_NAV_MOD,
MEPAI.MPAI_NAV_MODS                         MPAI_NAV_MODS,
MEPAI.MPAI_SYS_NO                              MPAI_SYS_NO,
MEPAI.MPAI_AS_OF_DATE                      MPAI_AS_OF_DATE,
MEPAI.MPAI_PAI_SYS_NO                       MPAI_PAI_SYS_NO,
PRODUCTS.ISS_INSTR_ID                        PRODUCT_INSTR_ID,
CUR.CUR_CURRENCY_CODE                  CURRENCY_CODE,
CUR.CUR_CURRENCY_NAME                  CURRENCY_NAME,
CUR.CUR_SYS_NO                                  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/31/2003','MM/DD/YYYY') ) 
AND           PRODUCTS.ISS_INSTR_ID IN (1321,1339,1341,1340)
AND           CUR.CUR_SYS_NO IN (200,226)
-----------------------------
The query of the view being:
-----------------------------
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
 
 
The execution plan and the time take came down drastically.
-------------------
Old 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)

-------------------
New Execution Plan
-------------------
 0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2551 Card=1 Bytes=68)
 1    0   NESTED LOOPS (Cost=2551 Card=1 Bytes=68)
 2    1     NESTED LOOPS (Cost=2551 Card=1 Bytes=65)
 3    2       NESTED LOOPS (Cost=2541 Card=10 Bytes=440)
 4    3         INLIST ITERATOR
 5    4           TABLE ACCESS (BY INDEX ROWID) OF 'EPR_PRODUCTS' (Cost=3 Card=9 Bytes=72)
 6    5             INDEX (RANGE SCAN) OF 'ISS_ISS_INSTR_ID' (NON-UNIQUE) (Cost=2 Card=9)
 7    3         TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'MOD_EPR_PRICING_ASSET_INFO' (Cost=282 Card=244 Bytes=8784)
 8    7           INDEX (RANGE SCAN) OF 'MPAI_ISS_FK_I' (NON-UNIQUE)(Cost=277 Card=244)
 9    2       INLIST ITERATOR
10    9         TABLE ACCESS (BY INDEX ROWID) OF 'EPR_CURRENCIES' (Cost=1 Card=2 Bytes=42)
11   10           INDEX (UNIQUE SCAN) OF 'CUR_PK' (UNIQUE)
12    1     INDEX (UNIQUE SCAN) OF 'GEO_PK' (UNIQUE)

 
Regards
New DBA
Stephane Faroult <sfaroult_at_oriolecorp.com> wrote:
Getting rid of views is quite often the first step on the path to Enlightenment. Compare the number of columns you want to be returned to the number of columns in the view: is it reasonable? Obviously not. If you absolutely want a view for security reasons, create a specific one for this query, which only returns what is needed. 
I must say that I am feeling a bit lazy to delve deeply into something which I see very poorly formatted. But first check whence (from which TABLE) come the columns you want to be returned, as well as those which much match the search criteria you are providing. Check whether you can join the required tables together; if not add the required tables to do the links to the stew. Stir. Determine which is the most selective of values from your input. If it really is selective, check that it is properly indexed. Check also that your joins can execute relatively fast. If you have a join on poorly selective columns, read about hash joins.
I am not pretending that by then you'll have a fast running query, but you'll have a sounder base for further tuning.

Regards,

Stephane Faroult
Oriole

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult INET: sfaroult_at_oriolecorp.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) --------------------------------- 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 Fri Sep 19 2003 - 02:19:47 CDT

Original text of this message

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