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

Home -> Community -> Usenet -> c.d.o.misc -> Re: View vs Underlying Query Performance Question

Re: View vs Underlying Query Performance Question

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 08 Sep 2006 11:12:35 -0700
Message-ID: <1157739164.532703@bubbleator.drizzle.com>


Soup wrote:

This

> ---------------------------------------------------------------------------------------------------
>
> | Id | Operation | Name
> | Rows | Bytes | Cost |
> ---------------------------------------------------------------------------------------------------
>
> | 0 | SELECT STATEMENT |
> | 1830 | 541K| 60590 |
> |* 1 | VIEW | GPC_TP_QUERY_VW
> | 1830 | 541K| 60590 |
> | 2 | COUNT |
> | | | |
> | 3 | CONCATENATION |
> | | | |
> |* 4 | HASH JOIN |
> | 1 | 165 | 34 |
> | 5 | TABLE ACCESS FULL | GPC_BUSINESS
> | 1 | 14 | 1 |
> |* 6 | HASH JOIN |
> | 1 | 119 | 29 |
> |* 7 | HASH JOIN |
> | 1 | 151 | 33 |
> | 8 | TABLE ACCESS FULL | GPC_PRODUCT_LBM
> | 398 | 12736 | 4 |
> |* 9 | HASH JOIN |
> | 1 | 228 | 37 |
> |* 10 | HASH JOIN |
> | 8 | 448 | 5 |
> | 11 | TABLE ACCESS FULL | GPC_BATCH_TEMP
> | 4 | 168 | 2 |
> | 12 | TABLE ACCESS FULL | GPC_ENTITY_XREF
> | 1013 | 14182 | 2 |
> |* 13 | TABLE ACCESS FULL | GPC_ENTITY_COST
> | 1 | 63 | 3 |
> | 14 | TABLE ACCESS FULL | GPC_PRODUCT
> | 1 | 63 | 3 |
> | 15 | NESTED LOOPS |
> | 1 | 165 | 34 |
> | 16 | NESTED LOOPS |
> | 1 | 228 | 37 |
> | 17 | NESTED LOOPS |
> | 1 | 151 | 33 |
> | 18 | NESTED LOOPS |
> | 1013 | 14182 | |
> | 19 | NESTED LOOPS |
> | 1 | 119 | 29 |
> | 20 | TABLE ACCESS FULL | GPC_BATCH_TEMP
> | 4 | 168 | 2 |
> | 21 | TABLE ACCESS BY INDEX ROWID| GPC_PRODUCT
> | 1 | 63 | 3 |
> |* 22 | INDEX UNIQUE SCAN | GPC_PRODUCT_PK
> | 1 | | 2 |
> | 23 | TABLE ACCESS BY INDEX ROWID | GPC_ENTITY_XREF
> | 1013 | 14182 | 2 |
> |* 24 | INDEX RANGE SCAN |
> GPC_COST_ENTITY_PRD_EI_IDX | | | |
>
> | 25 | TABLE ACCESS BY INDEX ROWID | GPC_PRODUCT_LBM
> | 398 | 12736 | 4 |
> |* 26 | INDEX RANGE SCAN | GPC_PRODUCT_LBM_PK
> | 1 | | 2 |
> |* 27 | TABLE ACCESS BY INDEX ROWID | GPC_ENTITY_COST
> | 1 | 63 | 3 |
> |* 28 | INDEX UNIQUE SCAN | GPC_ENTITY_COST_PK
> | 1 | | 2 |
> |* 29 | TABLE ACCESS BY INDEX ROWID | GPC_BUSINESS
> | 1 | 14 | 1 |
> |* 30 | INDEX UNIQUE SCAN | GPC_BUSINESS_PK
> | 1 | | |
> | 31 | NESTED LOOPS |
> | 1 | 228 | 37 |
> | 32 | NESTED LOOPS |
> | 1 | 165 | 34 |
> | 33 | NESTED LOOPS |
> | 1 | 151 | 33 |
> | 34 | NESTED LOOPS |
> | 1 | 119 | 29 |
> |* 35 | HASH JOIN |
> | 8 | 448 | 5 |
> | 36 | TABLE ACCESS FULL | GPC_BATCH_TEMP
> | 4 | 168 | 2 |
> | 37 | TABLE ACCESS FULL | GPC_ENTITY_XREF
> | 1013 | 14182 | 2 |
> |* 38 | TABLE ACCESS BY INDEX ROWID | GPC_PRODUCT
> | 1 | 63 | 3 |
> |* 39 | INDEX RANGE SCAN | GPC_PRODUCT_PRODNO_IDX
> | 1 | | 2 |
> | 40 | TABLE ACCESS BY INDEX ROWID | GPC_PRODUCT_LBM
> | 398 | 12736 | 4 |
> |* 41 | INDEX RANGE SCAN | GPC_PRODUCT_LBM_PK
> | 1 | | 2 |
> |* 42 | TABLE ACCESS BY INDEX ROWID | GPC_BUSINESS
> | 1 | 14 | 1 |
> |* 43 | INDEX UNIQUE SCAN | GPC_BUSINESS_PK
> | 1 | | |
> |* 44 | TABLE ACCESS BY INDEX ROWID | GPC_ENTITY_COST
> | 1 | 63 | 3 |
> |* 45 | INDEX UNIQUE SCAN | GPC_ENTITY_COST_PK
> | 1 | | 2 |
> ---------------------------------------------------------------------------------------------------
>
>
>
> Predicate Information (identified by operation id):
>
> ---------------------------------------------------
>
>
>
> 1 - filter("GPC_TP_QUERY_VW"."USERID"='campbk2')
>
> 4 - access("BUS"."FIN_BUSINESS_ID"=NVL("TMP"."BUSINESS",' ') AND
>
> "PRODLBM"."FIN_PROD_ID"="BUS"."FIN_PROD_ID" AND
> "PRODLBM"."FISCAL_YEAR_ID"="BUS"."FISCAL_YEAR_ID")
> 6 - access("PROD"."GPC_PROD_ID"="PRODLBM"."GPC_PROD_ID")
>
> 7 - access("PRODLBM"."FISCAL_YEAR_ID"=TO_NUMBER("TMP"."FISCALYEAR")
> AND
> "PRODLBM"."GPC_PROD_ID"="ETYCST"."GPC_PROD_ID" AND
>
> "PRODLBM"."FISCAL_YEAR_ID"="ETYCST"."FISCAL_YEAR_ID" AND
> "PRODLBM"."END_FM"="ETYCST"."LBM_END_FM")
> 9 - access("ETYCST"."ENTITY_ID"="X"."COST_ENTITY_ID" AND
>
> "ETYCST"."FISCAL_YEAR_ID"="X"."FISCAL_YEAR_ID")
>
> 10 - access("X"."ENTITY_ID"="TMP"."ENTITYID")
>
> 13 - filter("ETYCST"."END_FM"=12)
>
> 22 - access("PROD"."GPC_PROD_ID"=NVL("TMP"."UPN",' '))
>
> 24 - access("X"."ENTITY_ID"="TMP"."ENTITYID")
>
> 26 - access("PROD"."GPC_PROD_ID"="PRODLBM"."GPC_PROD_ID" AND
>
> "PRODLBM"."FISCAL_YEAR_ID"=TO_NUMBER("TMP"."FISCALYEAR"))
>
> 27 - filter("PRODLBM"."END_FM"="ETYCST"."LBM_END_FM")
>
> 28 - access("PRODLBM"."GPC_PROD_ID"="ETYCST"."GPC_PROD_ID" AND
>
> "ETYCST"."ENTITY_ID"="X"."COST_ENTITY_ID" AND
> "PRODLBM"."FISCAL_YEAR_ID"="ETYCST"."FISCAL_YEAR_ID"
> AND "ETYCST"."END_FM"=12)
>
> filter("ETYCST"."FISCAL_YEAR_ID"="X"."FISCAL_YEAR_ID")
>
> 29 - filter(LNNVL("BUS"."FIN_BUSINESS_ID"=NVL("TMP"."BUSINESS",' ')))
>
> 30 - access("PRODLBM"."FIN_PROD_ID"="BUS"."FIN_PROD_ID" AND
>
> "PRODLBM"."FISCAL_YEAR_ID"="BUS"."FISCAL_YEAR_ID")
>
> 35 - access("X"."ENTITY_ID"="TMP"."ENTITYID")
>
> 38 - filter(LNNVL("PROD"."GPC_PROD_ID"=NVL("TMP"."UPN",' ')))
>
> 39 - access("PROD"."PRODNO"=NVL("TMP"."CATALOG",' '))
>
> 41 - access("PROD"."GPC_PROD_ID"="PRODLBM"."GPC_PROD_ID" AND
>
> "PRODLBM"."FISCAL_YEAR_ID"=TO_NUMBER("TMP"."FISCALYEAR"))
>
> 42 - filter(LNNVL("BUS"."FIN_BUSINESS_ID"=NVL("TMP"."BUSINESS",' ')))
>
> 43 - access("PRODLBM"."FIN_PROD_ID"="BUS"."FIN_PROD_ID" AND
>
> "PRODLBM"."FISCAL_YEAR_ID"="BUS"."FISCAL_YEAR_ID")
>
> 44 - filter("PRODLBM"."END_FM"="ETYCST"."LBM_END_FM")
>
> 45 - access("PRODLBM"."GPC_PROD_ID"="ETYCST"."GPC_PROD_ID" AND
>
> "ETYCST"."ENTITY_ID"="X"."COST_ENTITY_ID" AND
> "PRODLBM"."FISCAL_YEAR_ID"="ETYCST"."FISCAL_YEAR_ID"
> AND "ETYCST"."END_FM"=12)
>
> filter("ETYCST"."FISCAL_YEAR_ID"="X"."FISCAL_YEAR_ID")

Is not the result of this:

>>> 1) SELECT * FROM myview WHERE userid = '<the users userid>' >>> 2) SELECT * FROM myview

Which is what you originally presented as the issue. I don't see how anyone can help you if they have no idea what it is you are really doing. Could you repost, as a separate thread, your actual problem with your actual explain plans.

But before you do I'd suggest applying the 4+ patches to 9i Oracle has provided and make sure your statistics are current and gathered with DBMS_STATS.

Then perhaps we can help.

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group
Received on Fri Sep 08 2006 - 13:12:35 CDT

Original text of this message

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