Re: Question on adaptive plans

Thank you so much.

Yes this is an exadata system. And mostly OLTP kind of queries/applications hosted on this. So , do you mean "Adaptive plans' will not be of much use or help for OLTP but for warehousing type of queries/applications and thus in our case we should be good here disabling it. And i was thinking if this variation in execution path during run time (i.e. from nested loop to hash join and vice versa) because of the runtime cardinality estimation can result in instability of the execution path, if it's better to keep it disabled at least in this current 19.9 patch?

You mentioned *"Here is a good article from the Lady of the Data Lake which will help you understand adaptive plans and hand you the sword to fight the bad performing queries. " * Can you pass on the link/Url for the same?

On Mon, Dec 27, 2021 at 7:12 PM Gogala, Mladen <gogala.mladen_at_gmail.com> wrote:

>
> On 12/27/2021 7:15 AM, Lok P wrote:
> > Now , as we want to move ahead and make the OFE back to 19.1.0 at
> > system level. So my question was , is it okay to set the
> > optimizer_adaptive_plans set as 'FALSE' at system level in this
> > scenario? Or will it cause any other harm or say we end up missing
> > some related good 19C features?
> >
> Well, disabling adaptive statistics on 19c is contrary to the spirit of
> Christmas, which means that you can receive a visit from the Ghost of
> Christmas Future, version 22c. Versioning was improved a bit since
> Ebenezer Scrooge, they use Git these days. Other than that, you should
> know what the adaptive plans can do and cannot do. Adaptive plans can
> switch join method from the nested loops to hash or vice versa. That's
> about it. I am a development DBA for an OLTP application which mostly
> uses nested loops, so I do disable the adaptive plans because I don't
> need them.
>
> The answer to your question is the same as the answer on any other
> Oracle question: it depends. It depends primarily on the application mix
> your database is supporting. Personally, I find mixed type
> OLTP/Reporting database the hardest to maintain, especially if on RAC.
> You will need to develop the philosophy of collecting statistics fitting
> to your application and, when implemented, monitor the SQL statements.
> If you notice a SQL with the wrong join method, then switching on the
> adaptive plans may be a useful move. Here is a good article from the
> Lady of the Data Lake which will help you understand adaptive plans and
> hand you the sword to fight the bad performing queries. I believe that
> the sword is called Exadata or something like that. It costs quite a
> bit. In the original version, it was free.
>
> If you have an Oracle engineered system, you can also try with the real
> time statistics which will keep your statistics current and prevent "out
> of range" errors. BTW, limiting new features like real time statistics
> and zone maps looks like a very bad idea to me.
>
> --
> Mladen Gogala
> Oracle DBA
> Tel: (347) 321-1217
> Blog: https://dbwhisperer.wordpress.com
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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