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
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