Re: Question on adaptive plans
Date: Mon, 27 Dec 2021 08:42:12 -0500
Message-ID: <23d6069a-d138-68b1-df11-40935e214c05_at_gmail.com>
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-lReceived on Mon Dec 27 2021 - 14:42:12 CET