Partition view leads to 'snapshot too old' error

Hi,

We are facing one problem in a query because it uses partition view.

When we execute the query, this runs for more than 6 hours and gives 'snapshot too old' error.

1) Below is the explain plan:
SELECT STATEMENT Optimizer=CHOOSE (Cost=64555 Card=1142237 Bytes=470601644)
SORT (GROUP BY) (Cost=64555 Card=1142237 Bytes=470601644)
HASH JOIN (Cost=39157 Card=1142237 Bytes=470601644)
TABLE ACCESS (FULL) OF MAP_PRODUCT (Cost=65 Card=27120 Bytes=2467920)
HASH JOIN (Cost=37722 Card=1142237 Bytes=366658077)
TABLE ACCESS (FULL) OF MAP_TIME1 (Cost=6 Card=868 Bytes=59024)
NESTED LOOPS (Cost=37598 Card=4601846 Bytes=1164267038)
HASH JOIN (Cost=129 Card=787 Bytes=151104)
TABLE ACCESS (FULL) OF MAP_ORGANISATION_PS (Cost=124 Card=540 Bytes=89100)
INDEX (FAST FULL SCAN) OF IDX_79289055_IDX0 (UNIQUE) (Cost=4 Card=29652 Bytes=800604)
VIEW OF V_SEC_SALES_INVC_MRG (Cost=37598 Card=5848 Bytes=356728)
UNION-ALL (PARTITION)
PARTITION LIST (ALL)
TABLE ACCESS (FULL) OF TRANST_SEC_SALES_INVOICE (Cost=1840 Card=50403 Bytes=2671359)
PARTITION LIST (ALL)
TABLE ACCESS (FULL) OF TRANSACTIONT_SS (Cost=65887 Card=1523949 Bytes=96008787)

Few points:
- v_sec_sales_mrg view does 'union all' two identical tables which is mentioned above i.e. TRANST_SEC_SALES_INVOICE and TRANSACTIONT_SS.
- Both the tables are going for FULL tablescan and the result is joined with a nested loop.


2) Now, I tried with giving FULL(V_SEC_SALES_INVC_MRG) hint in the query and it got executed successfully in 25-30 minutes.

Here is the explain plan:
SELECT STATEMENT Optimizer=CHOOSE (Cost=102678 Card=1142237 Bytes=559696130)
SORT (GROUP BY) (Cost=102678 Card=1142237 Bytes=559696130)
HASH JOIN (Cost=72628 Card=1142237 Bytes=559696130)
TABLE ACCESS (FULL) OF MAP_PRODUCT (Cost=65 Card=27120 Bytes=2467920)
HASH JOIN (Cost=70874 Card=1142237 Bytes=455752563)
TABLE ACCESS (FULL) OF MAP_TIME1 (Cost=6 Card=868 Bytes=59024)
HASH JOIN (Cost=70717 Card=4601846 Bytes=1523211026)
HASH JOIN (Cost=129 Card=787 Bytes=151104)
TABLE ACCESS (FULL) OF MAP_ORGANISATION_PS (Cost=124 Card=540 Bytes=89100)
INDEX (FAST FULL SCAN) OF IDX_79289055_IDX0 (UNIQUE) (Cost=4 Card=29652 Bytes=800604)
VIEW OF V_SEC_SALES_INVC_MRG (Cost=67727 Card=118995501 Bytes=16540374639)
UNION-ALL
PARTITION LIST (ALL)
TABLE ACCESS (FULL) OF TRANST_SEC_SALES_INVOICE (Cost=1840 Card=3175398 Bytes=168296094)
PARTITION LIST (ALL)
TABLE ACCESS (FULL) OF TRANSACTIONT_SS (Cost=65887 Card=115820103 Bytes=7296666489)

Differences between both the explain plans:
1) Expplan-1 goes for partition view (UNION-ALL (PARTITION) hint is there). So, it filters the rows first and then put a union all. The Cardinality of TRANST_SEC_SALES_INVOICE and TRANSACTIONT_SS are less.
Expplan - 2 doesn't go for partition view. It gets all the rows in both the tables and then put union all. The cardinality is more.

2) Expplan-1 uses nested loop.
Expplan-2 uses hash join.

3) Even though cost is less for expplan-1, it takes 6 hours and then gives snapshot too old error.
Even though cost is high for expplan-2, it retrieves records successfully in 25-30 minutes.

Since we are giving the query through reporting tools, we can't
give the FULL table hint. We should make optimizer to use the second explain plan( i.e not to use the partition view ) without giving any hint.

1) Ours is Oracle version 9.2.0.5.0
2) partition_view_enabled parameter is FALSE.


Please suggest me either
1) how to disable the usage of partition view. I think if I create an extra index in one of the above 2 tables, the structure of tables will not be identical and it will not use partition view. But the index may affect other reports. Is there any other way?
or
2) make optimizer calculate that partition view is expensive than not using partition view so that it will not use partition view.

Thanks in Advance.

Regards,
Sahay

What is the query?

Quote:

---

3) Even though cost is less for expplan-1, it takes 6 hours and then gives snapshot too old error.
Even though cost is high for expplan-2, it retrieves records successfully in 25-30 minutes.

---

Did you analyze all tables and indexes?
Do you see the number of rows returned in each operation are in line with your expectation?

Tamil

All the tables and indexes are analyzed using gather statistics. Number of records in each operation are same.
Here is the query:
SELECT
PRIM_CUST.RSM_NAME,
PRIM_CUST.GSM_NAME,
PRIM_CUST.ASM_KAM_NAME,
PRIM_CUST.CUSTOMER_NAME,
PRIM_CUST.CUSTOMER_CODE,
PRIM_CUST.SHIPTO_NAME,
PRIM_CUST.K1ORGANISATION_CODE,
SUM(V_SEC_SALES_INVC_MRG.ACTUAL_SALES_VALUE),
MAP_PRODUCT.BU_GROUP_NAME,
MAP_PRODUCT.BU_PRODUCT_GROUP_NAME,
MAP_PRODUCT.BU_SUB_PRODUCT_NAME,
MAP_TIME1.CYCLE_YEAR_NAME,
MAP_TIME1.CYCLE_MONTH_NAME
FROM
PRIM_CUST,
V_SEC_SALES_INVC_MRG,
MAP_PRODUCT,
MAP_TIME1,
ATT_SHIPTO
WHERE
( V_SEC_SALES_INVC_MRG.PRODUCT_UNIT_SKU_=MAP_PRODUCT.BE_ID AND MAP_PRODUCT.START_DATE<=SYSDATE AND MAP_PRODUCT.END_DATE > SYSDATE )
AND ( V_SEC_SALES_INVC_MRG.DAY=MAP_TIME1.BE_ID AND MAP_TIME1.END_DATE > SYSDATE AND MAP_TIME1.START_DATE )
AND ( V_SEC_SALES_INVC_MRG.ORGANISATION_PRIM_CUST=ATT_SHIPTO.BE_ID AND ATT_SHIPTO.start_dateSYSDATE
)
AND ( PRIM_CUST.SHIPTO=ATT_SHIPTO.BE_ID AND ATT_SHIPTO.start_dateSYSDATE )
AND (
PRIM_CUST.GSM_NAME IN ('GENERAL TRADE')
AND MAP_TIME1.CYCLE_YEAR_NAME IN ('2005 Cycle Year', '2006 Cycle Year')
)
GROUP BY
PRIM_CUST.RSM_NAME,
PRIM_CUST.GSM_NAME,
PRIM_CUST.ASM_KAM_NAME,
PRIM_CUST.CUSTOMER_NAME,
PRIM_CUST.CUSTOMER_CODE,
PRIM_CUST.SHIPTO_NAME,
PRIM_CUST.K1ORGANISATION_CODE,
MAP_PRODUCT.BU_GROUP_NAME,
MAP_PRODUCT.BU_PRODUCT_GROUP_NAME,
MAP_PRODUCT.BU_SUB_PRODUCT_NAME,
MAP_TIME1.CYCLE_YEAR_NAME,
MAP_TIME1.CYCLE_MONTH_NAME

collect historgams and then see if there is any improvement in the plan..

Did you try "stored outline"?

Tamil

I didn't try stored outline.
I have raised the request to create histograms. DBAs will do this at the time of maintenance activity.
Will update after that.

Thanks for your inputs.

Sahay

I guess that the problem is related to the "nested loop" and not directly to the partitioned view.

So I would rather play around with a "use_hash" hint then with a "full" hint.

Can you rewrite the querry to use the underlying tables instead of the view?
The goal is to elminate the most number of rows first. So it would be best to use the two view tables as driving tables. (ie the optimizer has it backwards)...

- I tried with USE_HASH hint. This explain plan is same as the explain plan of FULL hint(explain plan in the first posting).
But I can't give the hints in the query directly since they are generated from tool. I should make modifications so that optimizer itselfwill use the above explain plan.

- I tried with changing the tables instead of view. But no change.