Adding Hints to Existing SQL Profiles (10g)
By Jon Adams, Sr. Technical Consultant and Jack Augustin, Sr. Technical Consultant
The SQL Tuning Advisor was introduced in Oracle 10g. Its purpose is to take a poorly-executing SQL statement and perform an examination process looking at object optimizer statistics, index usage, and other aspects of the query to determine how to make it perform more efficiently. The result of executing this tool often results in a SQL profile being recommended on the SQL statement.
Simply put, a SQL Profile is a list of hints that will influence the execution of the SQL statement that it is attached to. Unfortunately, SQL profiles do not prevent the optimizer from choosing a different plan for several reasons including bind variable peeking, out-of-date or insufficient segment statistics or histograms, configuration settings (index cost, optimizer, etc.), or simply that Oracle thinks another plan is better. There is a way to add hints to existing SQL profiles to ignore features like bind variable peeking. Altering an existing SQL Profile is not officially supported by Oracle, as the SQL Profile itself is only supposed to be created by the SQL Tuning Advisor, so caution and testing is highly recommended.
For our test case, I will take a query that had nine different plans associated with it, observed by querying the AWR (the output has been compressed for sanity’s sake):
