Oracle SQL Tuning

In many scenarios, I have been changing and executing SQL statement to meet the performance requirement posed by client.

But normaly, i did not have any direction how to tune and whom to ask,

So I write an adhoc query and try to execute it, but I will not be sure whether Iam in right path or not.

In this scenario, I used Oracle database to give the tuning recommendations, rather than explaining all the tables and schemas to another person and trying adhoc queries.

From Oracle database 1og we have a package called DBMS_SQLTUNE to tune a query and get the recommendations from the database in form of a report.

So let's Start.

Following are the steps

1. Getting the Grants for accessing the SQL Tuning Advisor

2. Create a SQL Tuning task.

3. Execute a SQL Tuning Task.

4. Display the results of SQL Tuning task.

5. Implement the recommendations.

1.Getting the Grants for accessing the SQL Tuning Advisor

We need to get the following Grants before accesing the SQL Advisor

features of Oracle 10g.

//Grants to be given
// Log in as sys and give the following grants to the developer users
// Here Iam using SCOTT as the Developer user
GRANT ADVISOR to SCOTT;
GRANT EXECUTE ON DBMS_SQLTUNE to SCOTT;
GRANT SELECT ON DBMS_ADVISOR_LOG to SCOTT;

2. Create a SQL Tuning task

Lets take for example the following is the SQL Statement to be tuned.

//SQL Statement to be Tuned
SELECT e.*, d.* 
FROM   emp e 
JOIN     dept d 
ON e.deptno = d.deptno 
WHERE  NVL(empno, ''0'') = :empno;                           

After identifying the SQL statement to be tuned,

create an anonymous block

as below and call the 'CREATE_TUNING_TASK' function of the

'DBMS_SQL_TUNE' package.

//Creating the tuning task
DECLARE
  l_sql               VARCHAR2(500);
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql := 'SELECT e.*, d.* ' ||'FROM   emp e JOIN dept d ON e.deptno = d.deptno '
                ||'WHERE  NVL(empno, ''0'') = :empno';
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                                                                            sql_text    => l_sql,
                                                                            bind_list   => sql_binds(anydata.ConvertNumber(100)),
                                                                            user_name   => 'scott',
                                               scope       => DBMS_SQLTUNE.scope_comprehensive,
                                               time_limit  => 60,
                                               task_name   => 'emp_dept_tuning_task',
                                               description =>'Tuning task for an EMP to DEPT join query.');
 DBMS_OUTPUT.put_line('l_sql_tune_task_id: '|| l_sql_tune_task_id);
END;

3. Execute a SQL Tuning Task

After the creation of the SQLtuning task, we can execute the SQL Tuning task

to be given to the SQL Tuning Advisor present in the Oracle database 10g,

to get the tuning recommendations.

//Executing a SQL Tuning task
BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name =>'emp_dept_tuning_task');
END;

Did you notice above that we call the 'EXECUTE_TUNING_TASK' function of the 'DBMS_SQLTUNE' package.

You could see that we are giving some inputs to the 'EXEUTE_TUNING_TASK' function

which is the name of the task we created in the previous step.

4. Generating the report

We have executed the Tuning task

and what happened to my Tuning report.

Will it be text format or excel format or directlywritten to database....

To check the status of the SQL Tuning task , we can use the following SQL statement.

//To check the status of the SQL Tuning task
SELECT task_name, status  FROM dba_advisor_log WHERE owner = 'SCOTT';

To have the report in text format . log in into SQL * plus and run the following statement

//To get the report for the SQL Tuning task
SET LONG 1000 
SET LONGCHUNKSIZE 1000 
SET LINESIZE 100 
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task') FROM DUAL;

We would be getting the report in the following format in the SQL * plus interface.

// report generated from SQL * plus     
RECOMMENDATIONS

--------------------------------------------------------------------------------

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name   : emp_dept_tuning_task

Scope              : COMPREHENSIVE

Time Limit(seconds): 60

Completion Status  : COMPLETED

Started at         : 05/06/2004 09:29:13

Completed at       : 05/06/2004 09:29:15



-------------------------------------------------------------------------------

SQL ID  : 0wrmfv2yvswx1

SQL Text: SELECT e.*, d.* FROM   emp e JOIN dept d ON e.deptno = d.deptno

WHERE  NVL(empno, '0') = :empno



-------------------------------------------------------------------------------

FINDINGS SECTION (2 findings)

-------------------------------------------------------------------------------






1- Statistics Finding

---------------------
Table "SCOTT"."EMP" and its indices were not analyzed.



Recommendation

--------------

Consider collecting optimizer statistics for this table and its indices.

execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>

'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE)



Rationale

---------

The optimizer requires up-to-date statistics for the table and its indices

in order to select a good execution plan.



2- Restructure SQL finding (see plan 1 in explain plans section)

----------------------------------------------------------------

The predicate NVL("E"."EMPNO",0)=:B1 used at line ID 2 of the execution plan

contains an expression on indexed column "EMPNO". This expression prevents

the optimizer from selecting indices on table "SCOTT"."EMP".



Recommendation

--------------

Rewrite the predicate into an equivalent form to take advantage of

indices. Alternatively, create a function-based index on the expression.



Rationale

---------

The optimizer is unable to use an index if the predicate is an inequality

condition or if there is an expression or an implicit data type conversion

on the indexed column.



-------------------------------------------------------------------------------

EXPLAIN PLANS SECTION

-------------------------------------------------------------------------------



1- Original

-----------

Plan hash value: 1863486531



----------------------------------------------------------------------------------------

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |         |     1 |   107 |     4   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                |         |     1 |   107 |     4   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL          | EMP     |     1 |    87 |     3   (0)| 00:00:01 |

|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |

|   4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement
-------------------------------------------------------------------------------
1 row selected. 

5. Implement the recommendations

From the recommendations we found that the indexes for the tables were not used,

so it leads to the full scan of the table thus leading to the perfoemance degradation.

So as per the tuning report create a function based index so the indexes are used, thus

reducing the time to fetch a row.

So plung into action thus tuning your SQL statement.

For Complete Description about the Oracle Automatic SQL Tuning features
follow the link
Oracle Automatic SQL Tuning - Oracle Site
Improving SQL statement usining Automatic SQL Tuning
Automatic SQL Tuning in Oracle database 10g - Oracle Base.com
Note
Kindly post your comments for any Typo errors or any ambiguities.
Thanks in Advance