Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Jaco.

Asked: September 28, 2018 - 1:00 pm UTC

Last updated: October 04, 2018 - 10:12 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi Tom,

My understanding of using SQL Profiles has always been that they would prevent (frequent) changes in access paths of SQL statement.
This morning I noticed that, despite the fact that an SQL profile was connected to a statement and statiastics are gathered on a regular basis, the database all of a sudden decided to use a different access path which was terribly worse than what I expected.
My questions to you:
1. Is my general understanding of the use of SQL profiles incorrect?
2. If so, why should they be used at all?

Regards, Jaco de Graaf

and Chris said...

SQL profiles are an extended set of hints. These help fix incorrect row estimates. But, like regular hints, still allow the query to come up with a new plan.

On the other hand, SQL plan baselines do lock the query to a specific plan (or set of plans). If there's a better plan available it won't use it (until you accept it to the baseline).

You can create a baseline and a profile for one query. If the baseline contains two plans, the profile can help the optimizer choose the correct one.

You may also want to create the profile to get the "good" plan. Then capture it in the baseline.

You can read more about these at:

https://blogs.oracle.com/optimizer/what-is-the-difference-between-sql-profiles-and-sql-plan-baselines
https://www.oracle.com/technetwork/database/manageability/sql-profiles-technical-overview-128535.pdf

Is this answer out of date? If it is, please let us know via 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 latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.