Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, subrata.

Asked: April 06, 2022 - 6:14 pm UTC

Last updated: April 20, 2022 - 4:28 am UTC

Version: 19c

Viewed 1000+ times

You Asked

Oracle SQL Tuning Advisor recommended a SQL-profile for a SQL in PROD environment (indicating 99% improvement). This SQL uses bind variable. The TEST environment is of different size, and the profile recommendation could not be replicated and tested in TEST environment. The recommended SQL-profile was validated and directly implemented in PROD environment. After implementing the SQL-profile in PROD, user reported of extreme performance degradation for the SQL. The SQL-profile was disabled. and things became normal.

Please suggest what to do in such scenarios with explanation, and when to avoid implementing SQL-profile.
Should SQL profiles mandatorily be tested in lower environment?
And what does this recommendation signify: "At least one important bind value was missing for this sql statement"


Thanks & regards
Subrata

and Chris said...

I'm not sure why you're getting that message. There is a bug related to this (Bug 31766499 - SQL Tuning Advisor Report Shows Missing Bind Value for a Variable with Datatype BINARY_DOUBLE) so it's possible this is the reason. Check MOS (Doc ID 31766499.8) for more info and patches.

In any case, an "important" value was missing from the analysis. So it's possible that the optimizer came up with the wrong plan.

For example, in this table 1 row has the value 1, all the others (99%+) have the value 99:

create table t as 
  select case level
           when 1 then 1
           else 99
         end c1
  from   dual
  connect by level <= 1000;
  
select * from t
where  c1 = :var;


If the tuning advisor excludes :VAR from its analysis, it may not spot this big skew in values. This could lead to it creating sub-optimal profiles.

Should SQL profiles mandatorily be tested in lower environment?

Whatever change you make, it's always a good idea to test it first!

That said, differences between test and production environments can make testing profiles tricky.

You can test profiles in production by setting their category. From the docs:

The CATEGORY attribute determines which sessions can apply a profile. View the CATEGORY attribute by querying DBA_SQL_PROFILES.CATEGORY. By default, all profiles are in the DEFAULT category, which means that all sessions in which the SQLTUNE_CATEGORY initialization parameter is set to DEFAULT can use the profile.

By altering the category of a SQL profile, you determine which sessions are affected by profile creation. For example, by setting the category to DEV, only sessions in which the SQLTUNE_CATEGORY initialization parameter is set to DEV can use the profile. Other sessions do not have access to the SQL profile and execution plans for SQL statements are not impacted by the SQL profile. This technique enables you to test a profile in a restricted environment before making it available to other sessions.

The example in this section assumes that you want to change the category of the SQL profile so it is used only by sessions with the SQL profile category set to TEST, run the SQL statement, and then change the profile category back to DEFAULT.


https://docs.oracle.com/en/database/oracle/oracle-database/21/tgsql/managing-sql-profiles.html#GUID-EF674EC7-7C8B-4EDC-A94C-62A4A16BC166

Rating

  (2 ratings)

Comments

subrata saha, April 12, 2022 - 5:30 pm UTC

Thank you for your response.
You said =" If the tuning advisor excludes :VAR from its analysis, it may not spot this big skew in values. This could lead to it creating sub-optimal profiles."

Please help me understand:
1. Is there a learning/adaptive element in the Tuning Advisor?
I mean, as it encounters more different values of the bind variable,
can the Tuning Advisor include these to recommend better
SQL-profiles?

2. In my experience, in most cases SQL-profiles work good. What are the common known circumstances when the SQL-Tuning Advisor generates suboptimal SQL-profiles ? And what is recommended to overcome this.

Thanks & regards


Chris Saxon
April 19, 2022 - 4:30 pm UTC

1. Not that I'm aware of - this is an issue you should take up with support.

2. When the current plan is already "good", it's possible profiles could give little or no benefit.

It's rare for the tuning advisor to give sub-optimal plans. When it does you should raise this with support so we can improve the product.

A reader, April 19, 2022 - 4:33 pm UTC

Thanks
Connor McDonald
April 20, 2022 - 4:28 am UTC

glad we could help

More to Explore

Performance

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