In Oracle, a SQL Profile creates extra information about a particular SQL that the optimizer can use at run time to select the optimal plan to ensure the best performance. In essence, the SQL Profile enables dynamic behavior where the optimizer has multiple plans to choose from at run time based on run time bind variables, etc. When you run the SQL Tuning Advisor for the list of recommendations you will see that the recommendation specifies whether a SQL can be improved by creating a SQL Profile or SQL Baseline. It is preferable to choose a SQL Profile simply because it allows the optimizer to pick the best execution plans at run time.

SQL Baseline on the other hand is more of a brute force method when you simply marry a particular SQL to stay with a specific SQL execution plan. So no matter what the run time bind variables are for a given SQL, the optimizer will always try to use the SQL Baseline plan. This may work fine for most cases but in instances, where data skew, is high it is preferable that one pick more efficient plans based on bind variable values passed at run time instead of always picking the same plan as generated by the SQL Baseline.

In any case knowledge of the application, and data may make you prefer one over the other, in case you are clueless about this, then I would suggest you stick with the SQL Profile.

A SQL patch is a SQL manageability object that can be generated by the SQL Repair Advisor, in order to circumvent a plan which causes a failure.  In essence, a SQL patch tells the optimizer to change the plan in some way or avoid an optimization so that the failure does not occur.  For instance, if a failure happens during index access, then generating a plan without that index (or without any index) may work around the failure

Oracle provides three mechanisms to deal with execution plans: SQL Profiles, SQL Baselines, and SQL Patches. Profiles are proposed by the Tuning Advisor and it’s mostly based on adapting the cardinalities to match reality. Baselines allow us to provide a list of the accepted execution plans for a statement.SQL Patches are part of the SQL Repair Advisor and add hints to a specific statement.

Please find out all of our articles send us the Invitation or Following us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8