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.
