A Layman's guide to fixing Bad SQL performance

The last two blogs that I had about a “Layman’s guide to AWR” and “How to find expensive SQL”? should help one identify or smell out probable causers for performance issues in an Oracle database. In most cases bad SQL is one of the prime reasons for the DB to go down the toilet. Once we identify a culprit SQL there are many ways to help improve it, some of them are: See if there are missing indexes that are needed for the joins.Re-write the SQL to either eliminate redundant joins, or choose columns that are indexed for joins etc.See if you can fool the Oracle Optimizer by monkeying with table & index statisticsUse the easy of actually Oracle telling you what to do and then implement it usually without any SQL changes. The first option is fairly straight forward; you create new indexes, collect statistics on the tables and run the SQL to see if it picked up the new indexes and if the issue is fixed.  The only caveat here is obviously creating indexes and testing its use by the SQL will all need to be verified in a Prod-Like environment. For more on why I obsess about Prod-Like environment, read this…. Options 2 and 3 are both intrusive. Many off the shelf applications like SAP, Oracle Apps, etc. do not allow for an easy altering of SQL. Similarly monkeying with statistics of tables and indexes by either selectively deleting some or adding your own is at best a trial and error iterative process. So what should one do? Luckily the new versions of Oracle (10g and up) have increasingly made the task of tuning a bad SQL easier. A common name give for this is SQL Profiles. In the old days we used to have Plan Stability which was cumbersome to use and implement. The new methods allow Oracle to look at the history of execution of a SQL, inspect table & index statistics and use a heuristic method to arrive at an optimal execution plan. This is how easy it is, see below: 1.       Start by creating what is called as a Tuning Task by calling one of the Oracle supplied PL/SQL procedure. The only input you will need to provide most often is the SQL ID of the bad SQL. If you look at my previous post you can see what it is. For e.g., it was cf6jd7kan5y25. Login as a user who has permission to run the stored procedure DBMS_SQLTUNE.CREATE_TUNING_TASK using SQL *Plus or some other SQL tool that connects to Oracle. The output you will get will be something like TASK_27339, meaning Oracle created a tuning task with ID = TASK_27339. SET SERVEROUTPUT ON declare stmt_task VARCHAR2(100); begin stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'cf6jd7kan5y25'); DBMS_OUTPUT.put_line('task_id: ' || stmt_task ); end; / 2.       Next, execute the tuning task you just created by calling another procedure: execute dbms_sqltune.execute_tuning_task (task_name => 'TASK_27339'); Once this completes, Oracle would have gone through a whole list of “if…then…else” to check what is the best recommendation that can be given for this particular SQL. 3.       Now, run a SQL to check the actual recommendations: spool /tmp/task27339 set long 65536 set longchunksize 65536 set linesize 100 select dbms_sqltune.report_tuning_task('TASK_27339') from dual; spool off Now open the file /tmp/task27339 and search for “Recommendation”. You may something like below – Recommendation (estimated benefit: 87.98%)   ------------------------------------------   - Consider accepting the recommended SQL profile. execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_27339',          task_owner => 'SYSADM', replace => TRUE); Viola! We now have a recommendation that suggest an improvement of 88% over the original execution plans for this SQL. 4.       Now, to implement the recommendation just run what Oracle suggested for example: execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_27339',             task_owner => 'SYSADM', replace => TRUE); 5.       Once implemented, you can check the AWR reports or run the awrsqrpt.sql to verify if indeed the recommendation suggested has improved key statistics like buffer gets/exec, elapsed time/exec, CPU time/exec etc. in the awrsqrpt.sql output. We will spend more time on SQL Profiles and SQL Baselines next time. Till then happy hunting the’ BAD SQLs!!