Skip to Main Content
  • Questions
  • SQL Profiles Long Term Benefits and Issues

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Uma.

Asked: September 16, 2020 - 7:49 pm UTC

Last updated: September 21, 2020 - 2:03 am UTC

Version: 12.1.

Viewed 1000+ times

You Asked

Hello,

I researched your Q/A bank before asking this question.

What are some of the things to keep in mind, where a benefit to using a sql profile
turns into a curse. What are the things that can cause the use of sql profile to go bad or
problems that can go un noticed as a result of using the profile ?

Thank you.

and Chris said...

The thing to remember is that a SQL profile is essentially an extended set of hints to help the optimizer get better row estimates. Unlike baselines, they don't lock the statement to a specific plan.

Over time your data will change. If this leads to operations returning a signfiicantly different number of rows than the profile suggests, you may get a slower plan.

For example, say your company starts selling a set of new products which bring in many new customers. So the distribution and relationship between these values change notably. In which case the cardinality tweaks in the profile may no longer be accurate.

Or maybe some world-changing event happens (see: 2020), which cause a huge shift in buying patterns. Again, the profile may now give misleading estimates.

Ultimately as long as the statement the SQL profile is on meets your performance targets, you're fine. If the query takes too long, refreshing the profile (may) help the optimizer find a faster plan.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Refresh?

David D., September 18, 2020 - 8:43 am UTC

Hello Chris,

How can you refresh a SQL Profile?

In the doc I read :
https://docs.oracle.com/database/121/TGSQL/tgsql_profiles.htm#TGSQL599
"If either the optimizer environment or SQL profile changes, then the optimizer can create a new plan. As tables grow, or as indexes are created or dropped, the plan for a SQL profile can change. The profile continues to be relevant even if the data distribution or access path of the corresponding statement changes. In general, you do not need to refresh SQL profiles."

Best regards,

David D. from Paris-FRANCE


Chris Saxon
September 18, 2020 - 3:29 pm UTC

I don't think there is a way to "refresh" it as such - you create a new one and drop the existing.

sql profile opt_estimate hints

Narendra, September 18, 2020 - 6:20 pm UTC

Hello Chris,

When we run SQL Tuning Advisor for a specific sql id and it recommends a sql profile, the sql profile contains a set of OPT_ESTIMATE hints.
Is there a way to generate a set of OPT_ESTIMATE hints for any of the existing execution plans of a sql?

Thanks in advance

David Dubois, September 20, 2020 - 6:40 pm UTC

Hi Chris,

You say "you create a new one and drop the existing." : I am very surprised because I thought it was impossible for a human to create a SQL Profile; I thought it was only the SQL Tuning Advisor that were able to create one, based on its internal algorithm but we cannot say to it : "Create a SQL Profile" but "Enfance this SQL order and find a solution"..

David D.
Connor McDonald
September 21, 2020 - 2:03 am UTC

You can use DBMS_SQLTUNE.IMPORT_SQL_PROFILE to create sql profile.

David Kurtz has a nice post on it

https://blog.go-faster.co.uk/2017/12/hints-patches-force-matching-and-sql.html

A reader, September 29, 2020 - 7:36 am UTC


More to Explore

Performance

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