This shell script will help you tune a SQL Statement in Oracle by doing the following functions:

It will ask you for the SQL Statement sqlid, then it will do the following:

1- Display the SQL Statement Statistics like: Executions, ELAPSED/CPU time, Waits, I/O stats:

 

2- Display the SQL TEXT along with populating the bind variables:

 

3- Display the current Execution Plan:

 

4- Display the history of Execution Plans and execution statistics [if found]. [This section quoted from Tim Gorman’s sqlhistory.sql script after taking his permission (many thanks to Tim)]

This will help the user investigate the statement performance over the time by checking the change to the execution plan.

 

5- Display the SQL Plan Baseline, if the statement is already using a baseline for execution plan stability. [SQL Plan Baseline is an 11g new feature maintains the execution plan stability by storing the execution plans for a statement and use the best plan out of them in case the user didn’t fix a plan yet]

6- If the statement is not part of SQL Plan Baseline, the script will offer the option of fixing an execution plan by creating a new baseline for this statement: [If the DB version is 11g+]

7- If the statement is already in a SQL Plan Baseline, it will check the number of the available plans for this statement, if the plans are more than 1 plan, the script will display all the plans details, and will offer the option to fix a plan:

You can view the plan details in a tabular format using the shown Select statement: [in 12c+]

select * from table(dbms_xplan.display_sql_plan_baseline(‘&sql_handle’,’&plan_name’,’typical’));

 

Then you can get back to the script and decide which plan to FIX for this statement:

Then it will show the statement to use in case you want to rollback the change by dropping the Baseline (yes we can disable the baseline but disabling the baseline will not eliminate the optimizer of using it, disabling the baseline cannot be considered as a complete rollback here).

8- Finally the script will display the option of tuning the SQL Statement by submitting a tuning task using SQL Tuning Advisor feature [This feature is a licensed feature, please don’t use it unless your company is already acquired the Diagnostic & Tuning License to avoid licensing audit troubles in the future]:

This script is provided “AS IS” without any warranty. This script is tested on Linux environments but you have to test it first on a test environment before start to use it on production.

Special thanks to Farrukh Salman “Karkoor” for his contribution in this script.

You can Download the script from this link:

https://www.dropbox.com/s/fll8e7ybrjgsexo/sql_tune.sh?dl=0

 

GitHub version: