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

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Uma.

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

Answered by: Chris Saxon - Last updated: September 21, 2020 - 2:03 am UTC

Category: SQL - Version: 12.1.

Viewed 100+ 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 we 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.

and you rated our response

  (4 ratings)

Reviews

Refresh?

September 18, 2020 - 8:43 am UTC

Reviewer: David D. from Paris-FRANCE

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

Followup  

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

September 18, 2020 - 6:20 pm UTC

Reviewer: Narendra from UK

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

September 20, 2020 - 6:40 pm UTC

Reviewer: David Dubois from France

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

Followup  

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

September 29, 2020 - 7:36 am UTC

Reviewer: A reader


More to Explore

Performance

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