Re: Plan Baseline

Hi Jonathan,

Thanks for the detailed explanation. We are going to enable 10053 trace for the sql today and will keep you posted.

I have already verified the baseline and ENABLED, ACCEPTED , FIXED --> YES.

Sorry for the confusion, the baseline is "pinned" --> I meant SPB was created on the new PHV for the sql.

We are also planning to put the hints " opt_param('_optimizer_use_feedback' 'false') opt_param('optimizer_features_enable', '11.2.0.4') " to this sql code as an interim solution until the code rewrite. What is your thoughts on this?

Thanks,

Mani

On Tue, Aug 10, 2021 at 6:20 AM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

> The first problem is that you're running 12.1.0.2 which gives you plenty
> of scope for exposing optimizer gaps.
> In particular the one that leap to mind is the fact that you have several
> MERGE() hints in the outline - and there's an enhancement in 12.2.0.1 which
> deals with the problem that SPB's that contain MERGE() hints don't always
> reproduce the same plan because they don't specify what they merge into,
> only what they merge from. The unambiguous 12.2. syntax looks like:
> merge(_at_qb1 > qb2), e.g. merge(_at_SEL$12 > SEL$641071AC). So it's possible
> that with some change in estimated numbers the optimizer has merged query
> blocks in a different order - getting to the same final query block - and
> failed to reproduce the plan.
>
> The second thing that stand out in your good/bad plan txt is that one plan
> has an estimate of 8300 rows, the other has an estimate of 6.7M rows.
> Tracking back to source this is because there an estimate of 10 rows for
> f_in_list_num in one plan and 8,168 (the default collection size) in the
> other.
> This is a little surprising since there's a cardinality(10) hint in the
> text on the SQLT output. (I would change that from a table hint to a query
> block hint, possibly using opt_estimate() to be up to date; and I'd also
> bring the syntax up to date the "THE" is a very old requirement. It's odd
> that the hint has been obeyed in one case and not the other. Since
> baselines include the hint "IGNORE_OPTIM_EMBEDDED_HINTS" you would have
> expected the plan that followed the baseline to be using 8168, and the plan
> that bypassed the baseline to use 10.)
>
> This is actually busy sql and running 10053 trace may cause spikes in
> application in production. Correct me if I am wrong , I believe even though
> if we enable 10053 trace (alter system set events
> 'trace[rdbms.SQL_Optimizer.*][sql:gn1072x46d6sb]';) , we need to wait for
> the sql to re-parse with the bad plan.
>
> This should only dump the CBO trace file on a hard parse - how long does
> it take to parse the statement, and how often are you doing it. How much
> extra time would a hard parse take compared to the time taken and resources
> wasted by running the wrong plan. (Yes, you would have to wait, but you
> could flush a single cursor from the shared pool to force a hard parse:
> https://jonathanlewis.wordpress.com/2019/12/20/purge_cursor/ )
>
> Without getting the production CBO trace you're not likely to find why the
> baseline is being bypassed - I assume you've checked that the baseline is
> still accepted etc. in the data dictionary. What,exactly, do you mean by
> saying the baseline is "pinned", by the way.
>
>
>
> Regards
> Jonathan Lewis
>
>
>
>
>
> On Mon, 9 Aug 2021 at 19:56, manikandan <pvmanikandan_at_gmail.com> wrote:
>
>> Hi Jonathan,
>>
>>
>>
>> Thanks for the reply. The patch was not related to CBO (24764744: MORE
>> THAN ONE CURRENT BUFFER FOR A BLOCK IN EXTREAMLY RARE SCENARIO).
>>
>> This is actually busy sql and running 10053 trace may cause spikes in
>> application in production. Correct me if I am wrong , I believe even though
>> if we enable 10053 trace (alter system set events
>> 'trace[rdbms.SQL_Optimizer.*][sql:gn1072x46d6sb]';) , we need to wait for
>> the sql to re-parse with the bad plan.
>>
>>
>>
>> We are not facing this behaviour in our performance testing environment
>> which is prod like environment.
>>
>>
>>
>> Little background on the query
>>
>>
>>
>> We had a query which was picking bad execution plan due to de-correlated
>> lateral (DCL) views and causing potential performance issues to the
>> application. As per Oracle recommendation, we have added optimizer hint
>> opt_param('_optimizer_ansi_join_lateral_enhance', 'false') to the query to
>> skip DCL view. After the change, we see that the sql id is picking bad
>> plans along with good plans (3975766313 - SPB created after putting hint)
>> for few executions.
>>
>>
>>
>> After the DB bounce, good PHV is 2563251471 and we pinned SPB for this
>> PHV as sql was not picking the SPB which we created earlier(on 3975766313).
>>
>>
>>
>> I have attached good plans, bad plans , sql stats and 10053 trace from
>> SQLT (which is picking SPB) for your reference.
>>
>>
>>
>> One thing I noted is
>>
>>
>>
>> From Good Plan:-
>>
>>
>>
>> Note
>>
>> -----
>>
>> - SQL plan baseline SQL_PLAN_673798mksxnxm66e436ab used for this
>> statement
>>
>> - Warning: basic plan statistics not available. These are only
>> collected when:
>>
>> * hint 'gather_plan_statistics' is used for the statement or
>>
>> * parameter 'statistics_level' is set to 'ALL', at session or
>> system level
>>
>>
>>
>> From Bad Plan:-
>>
>>
>>
>> Note
>>
>> -----
>>
>> - statistics feedback used for this statement
>>
>> - Warning: basic plan statistics not available. These are only
>> collected when:
>>
>> * hint 'gather_plan_statistics' is used for the statement or
>>
>> * parameter 'statistics_level' is set to 'ALL', at session or
>> system level
>>
>>
>>
>> Do you think statistics feedback playing a role here and causing this
>> behaviour?
>>
>>
>>
>> Thanks,
>>
>> Mani
>>
>> On Mon, Aug 9, 2021 at 7:50 AM Jonathan Lewis <jlewisoracle_at_gmail.com>
>> wrote:
>>
>>> Mani,
>>>
>>> A couiple of thoughts:
>>>
>>> 1: What was the patch for? If it was something to do with wrong
>>> results, or CBO picking bad plan, then maybe the patch has blocked some
>>> transformation that the SPB depended on,
>>>
>>> 2: if you can enable the 10053 (CBO) trace and force re-optimization of
>>> the query (taking care to ensure that it's being called in exactly the way
>>> it gets called in producton) you may find some clue in the trace file
>>> explaining by the SPB has not been used.
>>>
>>>
>>> Regards
>>> Jonthan Lewis
>>>
>>>
>>>
>>>
>>> On Mon, 9 Aug 2021 at 07:22, manikandan <pvmanikandan_at_gmail.com> wrote:
>>>
>>>> Hi,
>>>>
>>>> We have implemented SPB for one query with a good plan. Post
>>>> implementation CBO was able to use the baseline without issue. We went for
>>>> DB bounce after that due to one-off patch maintenance and after the DB
>>>> bounce CBO is unable to use the BASELINE anymore and has created a new
>>>> plan. We have tried flushing the cursor multiple time but of no use. How we
>>>> can force the SQL to use the baseline? What would be the cause of query not
>>>> being used sql plan baseline post DB bounce?. Database version is 12.1.0.2
>>>> + RAC 5 nodes + AIX.
>>>>
>>>> Thanks,
>>>> Mani
>>>>
>>>

--
http://www.freelists.org/webpage/oracle-l