31 July 2009

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

1 comment:

  1. Thanks for Information Teradata Online Training is one of the most emerging technologies in market. As more and more organization are moving their data warehouse in Teradata database, so the demand of Teradata Professionals are high. We at TeradataTech started giving online training sessions for all folks who are interested in learning this technology. People who are interested in learning the basics and advance features of Teradata Development can benefit from this training.

    ReplyDelete