Sometimes Oracle DBAs need to create Tuning Task for SQL statements need tuning to get recommendations to fix performance issues.
In this post we will discuss how to manually create a tuning task for SQL statement if you have its SQLID.
1- Generate a task name for the SQLID (4m2qynnrbmwgc) for example:
SQL>
SET SERVEROUTPUT ON
declare
stmt_task VARCHAR2(40);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '4m2qynnrbmwgc');
DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
end;
/
the output for this PL/SQL Block will be the task name you will use later to view the tuning recommendations and in this example it will be "TASK_5795".
2- Execute the task name you got in first step:
begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_5795');
end;
/
3- Run the following SQL statement to view Oracle tuning recommendations:
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_5795') AS recommendations FROM dual;
4- The output will be something like this:
RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_5795
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 : 4m2qynnrbmwgc
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.
