Skip to Main Content
  • Questions

  • Impact on DB performance with enabling SQL Plan Baseline

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

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.

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library