//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;
//SQL Statement to be Tuned
SELECT e.*, d.*
FROM emp e
JOIN dept d
ON e.deptno = d.deptno
WHERE NVL(empno, ''0'') = :empno;
//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;
//Executing a SQL Tuning task
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name =>'emp_dept_tuning_task');
END;
//To check the status of the SQL Tuning task
SELECT task_name, status FROM dba_advisor_log WHERE owner = 'SCOTT';
//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;
// 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.
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
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