Thanks for the question, Srinivas.
Asked: October 31, 2016 - 7:10 pm UTC
Last updated: October 31, 2016 - 11:36 pm UTC
Version: 12.1.0.2
Viewed 1000+ times
You Asked
Hi Tom,
Our database version is 12.1.0.2, running on exadata platform. For stabilizing the performance, we are planning to enable SQL Plan baselines in our database.
By default, the parameter "optimizer_capture_sql_plan_baselines" is FALSE and i need to change it as TRUE inorder to tell my optimizer to capture the new plans into the SQL Log, and then into the plan baseline after evaluating it.
Could you throw some lite on the performance impact on a database, if we use automatic SQL Plan baseline.
Thanks
Srinivas
and Connor said...
I'm generally not a fan of turning it on at system level, because *every* sql will be captured, and it is very unlikely you need that to be the case.
I work along a "sliding scale"
1) Is it good enough to capture baselines/freeze plans for just a few problem SQL's ? If yes, then I'll load them from the cursor cache. If no, then (2)
2) Do I need to capture all baselines from an "application". If yes, then I'll use perhaps a login trigger to enable it at session/schema level for that application.
The other risk of capturing *all* sql's is that if you have any "bad" apps, which generate lots of different SQL's you might get a lot of baselines for queries that will not be of any real value, except to clutter your SYSAUX tablespace.
So my advice - start small and see how you go.
Is this answer out of date? If it is, please let us know via a Comment