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@xxxxxxxxx>
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 atWell, disabling adaptive statistics on 19c is contrary to the spirit of
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?
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
--
//www.freelists.org/webpage/oracle-l
