Systems Engineering and RDBMS

DBMS_SQLTUNE

Posted by decipherinfosys on April 11, 2007

From Oracle database version 10g, Oracle has introduced dbms_sqltune to facilitate one of the most challenging tasks catered by Database Administrators — SQL tuning.  The steps required to use this package for SQL tuning are listed below.  You need to ensure that the ADVISOR privilege is granted to your user before you can start using this package.

Step 1: Create tuning task: We can use create_tuning_task function to do this.  You need to have either the actual SQL or SQL_ID to create the tuning task.

Step 2: Execute tuning task: We can use execute_tuning_task procedure to do this step.  This will generate an optimized execution plan and the rationale for advice, estimated performance benefit, and the command(s) to implement the advice.

Step 3: Report tuning task: We can use report_tuning_task function to get tuning information generated in Step 2.

Below, we will cover an example illustrating tuning a SQL using DBMS_SQLTUNE (by explicitly specifying actual SQL).

Here is a SQL that needs to be tuned and it’s execution plan before using the tuning advice.  Here we have used NO_INDEX hint to explicitly generate bad execution plan and tried to demonstrate how dbms_sqltune makes use of profile to tune SQL “without any code change”,

Also we have used stale statistics to demonstrate that how DBMS_SQLTUNE captures such information and reports it. Also it is vital to note down that the SQL Profile is specific to a particular query, and its benefit over stored outline is that it “does not freeze the execution plan” of a SQL statement, that means — as tables grow or indexes are created or dropped, the execution plan can change with the same SQL Profile.  That is the biggest advantage.

This also provides a way for the DBAs/Database Developers to tune canned applications without requiring them to ask their vendors to make changes to the applications (or without having to wait for those changes to be provided by the vendors).

SQL> set autotrace traceonly
SQL> l
1  select /*+NO_INDEX(e)*/ empno, ename from emp e, dept d where
2* d.deptno = e.deptno and d.deptno < 20
SQL>
SQL> /

Execution Plan
———————————————————-
Plan hash value: 2796403804

——————————————————————————
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————
|   0 | SELECT STATEMENT   |         |     1 |     9 |    56   (8)| 00:00:01 |
|   1 |  NESTED LOOPS      |         |     1 |     9 |    56   (8)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP     |     7 |    42 |    55   (8)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     1 |     3 |     1   (0)| 00:00:01 |
——————————————————————————

Predicate Information (identified by operation id):
—————————————————

2 – filter(“E”.”DEPTNO”<20)
3 – access(“E”.”DEPTNO”=”D”.”DEPTNO”)
filter(“D”.”DEPTNO”<20)

Commands needed for dropping a tuning task (if task with same name already exists), creating tuning task (Step 1 – using actual SQL), execute tuning task (Step 2) and report tuning task (Step 3) are listed below:

— drop tuning task
exec dbms_sqltune.drop_tuning_task(‘DecipherTuningDemoTask1’);

— create tuning task using actual SQL
DECLARE
v_tuning_task VARCHAR2(30);
v_sql_stmt     CLOB;
BEGIN
v_sql_stmt :=   ‘select /*+NO_INDEX(e)*/ empno, ename from emp e, dept d where d.deptno = e.deptno and d.deptno < 20’;
v_tuning_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text    => v_sql_stmt,
task_name   => ‘DecipherTuningDemoTask1’);
END;
/

— Execute tuning task
exec dbms_sqltune.execute_tuning_task(‘DecipherTuningDemoTask1’);

— Get report generated in Step-2
select dbms_sqltune.report_tuning_task(‘DecipherTuningDemoTask1’) from dual
/

Here is a report generated by the execution of the tuning task (Step 2).  Pay attention to the Finding Section, namely statistics and profile findings, statistics finding 1 & 2 point outs about stale table and index statistics and provides command to collect statistics, profile finding (Finding 3) suggest potentially a better execution plan and the command to implement it using a SQL profile.

SQL>
SQL> select dbms_sqltune.report_tuning_task(‘DecipherTuningDemoTask1’) from dual
2  /

DBMS_SQLTUNE.REPORT_TUNING_TAS
—————————————————————————————————-
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name                  : DecipherTuningDemoTask1
Tuning Task Owner                 : SCOTT
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 1800
Completion Status                 : COMPLETED
Started at                        : 04/10/2007 14:36:45
Completed at                      : 04/10/2007 14:36:46
Number of Statistic Findings      : 2
Number of SQL Profile Findings    : 1

DBMS_SQLTUNE.REPORT_TUNING_TAS
—————————————————————————————————-

——————————————————————————-
Schema Name: SCOTT
SQL ID     : 6yrdazbx31vqz
SQL Text   : select /*+NO_INDEX(e)*/ empno, ename from emp e, dept d where
d.deptno = e.deptno and d.deptno < 20

——————————————————————————-
FINDINGS SECTION (3 findings)
——————————————————————————-

DBMS_SQLTUNE.REPORT_TUNING_TAS
—————————————————————————————————-
1- Statistics Finding
———————
Optimizer statistics for index “SCOTT”.”FK_EMP_TO_DEPT” are stale.

Recommendation
————–
– Consider collecting optimizer statistics for this index.
execute dbms_stats.gather_index_stats(ownname => ‘SCOTT’, indname =>
‘FK_EMP_TO_DEPT’, estimate_percent =>
DBMS_STATS.AUTO_SAMPLE_SIZE);

DBMS_SQLTUNE.REPORT_TUNING_TAS
—————————————————————————————————-
Rationale
———
The optimizer requires up-to-date statistics for the index in order to
select a good execution plan.

2- Statistics Finding
———————
Optimizer statistics for table “SCOTT”.”EMP” and its indices are stale.

Recommendation
————–

DBMS_SQLTUNE.REPORT_TUNING_TAS
—————————————————————————————————-
– Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => ‘SCOTT’, tabname =>
‘EMP’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’);

Rationale
———
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.

3- SQL Profile Finding (see explain plans section below)

DBMS_SQLTUNE.REPORT_TUNING_TAS
—————————————————————————————————-
——————————————————–
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 33.32%)
——————————————
– Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
‘DecipherTuningDemoTask1′, replace => TRUE);

——————————————————————————-
EXPLAIN PLANS SECTION

DBMS_SQLTUNE.REPORT_TUNING_TAS
—————————————————————————————————-
——————————————————————————-

1- Original With Adjusted Cost
——————————
Plan hash value: 2796403804

——————————————————————————
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————
|   0 | SELECT STATEMENT   |         |  2273 | 31822 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |         |  2273 | 31822 |     3   (0)| 00:00:01 |

DBMS_SQLTUNE.REPORT_TUNING_TAS
—————————————————————————————————-
|*  2 |   TABLE ACCESS FULL| EMP     |     1 |    11 |     2   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     1 |     3 |     1   (0)| 00:00:01 |
——————————————————————————

Predicate Information (identified by operation id):
—————————————————

2 – filter(“E”.”DEPTNO”<:SYS_B_0)
3 – access(“D”.”DEPTNO”=”E”.”DEPTNO”)
filter(“D”.”DEPTNO”<:SYS_B_0)

DBMS_SQLTUNE.REPORT_TUNING_TAS
—————————————————————————————————-
2- Using SQL Profile
——————–
Plan hash value: 2239856395

———————————————————————————————–
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————————–
|   0 | SELECT STATEMENT             |                |  2273 | 31822 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                |  2273 | 31822 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP            |     1 |    11 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | FK_EMP_TO_DEPT |       |       |     1   (0)| 00:00:01 |

DBMS_SQLTUNE.REPORT_TUNING_TAS
—————————————————————————————————-
|*  4 |   INDEX UNIQUE SCAN          | PK_DEPT        |     1 |     3 |     1   (0)| 00:00:01 |
———————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

3 – access(“E”.”DEPTNO”<:SYS_B_0)
4 – access(“E”.”DEPTNO”=”D”.”DEPTNO”)
filter(“D”.”DEPTNO”<:SYS_B_0)

——————————————————————————-

DBMS_SQLTUNE.REPORT_TUNING_TAS
—————————————————————————————————-

1 row selected.

You can execute below command for accepting the recommended SQL profile from SYS user.

exec dbms_sqltune.accept_sql_profile(task_name =>’DecipherTuningDemoTask1′, replace => TRUE, TASK_OWNER =>’SCOTT’); — Need to be executed under SYS schema

Here is the same original SQL and it’s execution plan after accepting the profile and without any code changes, note that even we have used NO_INDEX hint for emp table, it is getting accessed via FK_EMP_TO_DEPT index.

SQL> select /*+NO_INDEX(e)*/ empno, ename from emp e, dept d where
1* d.deptno = e.deptno and d.deptno < 20
SQL>
SQL> /

Execution Plan
———————————————————-
Plan hash value: 2239856395

———————————————————————————————–
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————————–
|   0 | SELECT STATEMENT             |                |  2273 | 31822 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                |  2273 | 31822 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP            |     1 |    11 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | FK_EMP_TO_DEPT |       |       |     1   (0)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN          | PK_DEPT        |     1 |     3 |     1   (0)| 00:00:01 |
———————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

3 – access(“E”.”DEPTNO”<20)
4 – access(“E”.”DEPTNO”=”D”.”DEPTNO”)
filter(“D”.”DEPTNO”<20)

Note
—–
–    SQL profile “SYS_SQLPROF_014476b214476b29” used for this statement

If you do not find a profile useful, you can drop it, here is command to do that

exec dbms_sqltune.DROP_SQL_PROFILE(‘SYS_SQLPROF_014476b214476b29’);

As we mentioned in Step 1,  we can also use SQL_ID to create tuning task(s), here are SQLs to find SQL_ID of top n SQLs on the basis of elapsed time (you can also use cpu time or disk reads or any other criteria to locate top SQLs) and commands to create tuning task using that SQL_ID, rest of the steps remains same.

— Finding top 10 expensive SQL in the workload repository
select * from (
select dhss1.sql_id as sql_id, sum(elapsed_time_delta) / 1000000 as elapsed_time_in_sec,
(select dhst2.sql_text
from dba_hist_sqltext dhst2
where dhst2.dbid = dhss1.dbid and dhst2.sql_id = dhss1.sql_id) as sql_text
from dba_hist_sqlstat dhss1, dba_hist_sqltext dhst1
where dhss1.sql_id = dhst1.sql_id and
dhss1.dbid   = dhst1.dbid
group by dhss1.dbid, dhss1.sql_id
order by elapsed_time_in_sec desc
) where ROWNUM < 11
/
— Create tuning task using SQL_ID which does not exists in cursor cache
undefine sqlid;
DECLARE
v_task_name varchar2(30);
v_min_snap_id      NUMBER;
v_max_snap_id      NUMBER;
BEGIN
— If it’s not in V$SQL we will have to query the workload repository
select min(snap_id) into v_min_snap_id
from   dba_hist_sqlstat
where  sql_id = ‘&&sqlid’;
select max(snap_id) into v_max_snap_id
from   dba_hist_sqlstat
where  sql_id = ‘&&sqlid’;
v_task_name := dbms_sqltune.create_tuning_task(begin_snap => v_min_snap_id,
end_snap => v_max_snap_id,
sql_id => ‘&&sqlid’,
task_name => ‘DecipherTuningDemoTask3’);
END;

— Only valid for 10g Release 2
— Finding top 10 expensive SQL in the cursor cache by elapsed time

select * from (
select sql_id, elapsed_time / 1000000 as elapsed_time_in_sec, sql_text as sql_text
from   v$sqlstats
order by elapsed_time_in_sec desc
) where rownum < 11
/
— Create tuning task using SQL_ID which already exists in cursor cache

DECLARE
v_tuning_task VARCHAR2(30);
BEGIN
v_tuning_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id      => ’19v5guvsgcd1v’,
task_name   =>’DecipherTuningDemoTask2′);
END;
/

Also from Oracle 10g R2 onwards, Oracle supplies sqltrpt.sql script which you can use to tune expensive SQL statements from the cursor cache and the workload repository.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: