Jump to content
tipsu

Performance query question

Recommended Posts

Hi!

I have a query that in 12c perfoms very well, but bad in 10g.

If I change manually the from clause to:

FROM BTBB_PO_HEADER_V t1
LEFT OUTER JOIN BTBB_PO_DRS_ORDER_INFO_V t0 ON (t0.PO_HEADER_ID = t1.PO_HEADER_ID)
LEFT OUTER JOIN BTBB_PO_LINE_V t2 ON (t2.PO_HEADER_ID = t1.PO_HEADER_ID)
LEFT OUTER JOIN BTBB_PO_LINE_LOC_V t3 ON ((t3.PO_LINE_ID = t2.PO_LINE_ID)
AND
(t3.PO_HEADER_ID = t2.PO_HEADER_ID)) LEFT OUTER JOIN BTBB_PO_DISTRIBUTION_V t4 ON ((t4.PO_LINE_ID = t3.PO_LINE_ID)
AND
((t4.PO_HEADER_ID = t3.PO_HEADER_ID)
AND (t4.PO_LINE_LOC_ID = t3.PO_LINE_LOCATION_ID)))
WHERE (t1.PO_HEADER_ID =:B1)

TO THIS one:
FROM BTBB_PO_HEADER_V t1
LEFT OUTER JOIN BTBB_PO_DRS_ORDER_INFO_V t0 ON (t0.PO_HEADER_ID = t1.PO_HEADER_ID)
LEFT OUTER JOIN BTBB_PO_LINE_V t2  ON (t2.PO_HEADER_ID = t1.PO_HEADER_ID)
LEFT OUTER JOIN BTBB_PO_LINE_LOC_V t3  ON (    (t3.PO_LINE_ID = t2.PO_LINE_ID)
AND
(t3.PO_HEADER_ID = t1.PO_HEADER_ID)) LEFT OUTER JOIN BTBB_PO_DISTRIBUTION_V t4  ON ((t4.PO_LINE_ID = t3.PO_LINE_ID)
AND
((t4.PO_HEADER_ID = t1.PO_HEADER_ID)
AND (t4.PO_LINE_LOC_ID = t3.PO_LINE_LOCATION_ID)))
WHERE (t1.PO_HEADER_ID =:B1);

 

Then  the query is fast in 10g as well. I cannot change the source code..  The outline export from 12c->10g  or profile export not working..

Is it possible to  translate this code change into HINTS ??

 I need direct hints to push this trough and then I create a outline in 10g. Can you pls help me ?

Thanks, Laurel

Share this post


Link to post
Share on other sites

Hi Laurel and welcome to the forum!

The main objective is to get an identical execution plan on 12c and 10g!

I any surprised that stored outlines do not work for you, but hints are optimizer directives that can change execution plans.

http://www.dba-oracle.com/t_sql_execution_plan_finished_query.htm

I would play around with hints.  The 10g database to replicate the 12c plan.

http://www.dba-oracle.com/t_sql_hints_tuning.htm

Without seeing the plans, I suspect that the poor performance in 10h is due to an unnecessary full table scan.  Have a look at your optimizer_index_cost_adj parameter parameter on 10g.

Good luck!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×