Sql tuning advisor was introduced by oracle from 10g onwards. DBMS_SQLTUNE is the package , which helps the DBA’s in generating recommendations for bad sql queries. Here we will use a simple query for sql tuning advisor and see what suggestion it is providing .
We will take below query:
select count(*) from bigtab where weight in ( select distinct( weight) from bigtab);
10:03:32 SQL > show user USER is "SYS" 10:03:35 SQL > select count(*) from bigtab where weight in ( select distinct( weight) from bigtab); COUNT(*) ---------- 13296640 Elapsed: 00:00:19.35
This query is taking around 19 seconds.
Lets generate the explain for this .
10:04:10 SQL > explain plan for
select count(*) from bigtab where weight in ( select distinct( weight) from bigtab);10:04:34 2
Explained.
Elapsed: 00:00:00.09
10:05:15 SQL > select plan_table_output from
table(dbms_xplan.display('plan_table',null,'typical -cost -bytes'));10:05:32 2
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1575844597
------------------------------------------------------------
| Id | Operation | Name | Rows | Time |
------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 00:11:11 |
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | HASH JOIN SEMI | | 13M| 00:11:11 |
| 3 | TABLE ACCESS FULL | BIGTAB | 13M| 00:02:28 |
| 4 | VIEW | VW_NSO_1 | 13M| 00:02:28 |
| 5 | TABLE ACCESS FULL| BIGTAB | 13M| 00:02:28 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("WEIGHT"="WEIGHT")
Note
-----
- dynamic sampling used for this statement (level=2)
21 rows selected.
Now time to use the sql tuning advisor manually. It has three steps.
1. creating the tuning task
2. executing the tuning task
3. reporting tuning task.
Creating tuning task:
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select count(*) from bigtab where weight in ( select distinct( weight) from bigtab)';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text = > my_sqltext,
scope = > 'COMPREHENSIVE',
time_limit = > 60,
task_name = > 'TEST_sql_tuning_task1',
description = > 'Task to tune a query on a specified PRODUCT');
END;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
Executing Tuning task:
10:06:08 SQL > Execute dbms_sqltune.Execute_tuning_task (task_name = > 'TEST_sql_tuning_task1'); PL/SQL procedure successfully completed. Elapsed: 00:00:00.20
Reporting tuning task:
10:06:21 SQL > set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('TEST_sql_tuning_task1') from dual;10:06:29
10:06:29 SQL >
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TEST_sql_tuning_task1
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 07/20/2015 10:06:20
Completed at : 07/20/2015 10:06:21
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : 22483tyaqab3r
SQL Text : select count(*) from bigtab where weight in ( select distinct(
weight) from bigtab)
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
----------------------------------------------------------------------------------------------------
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit<=10%) --------------------------------------- - Consider accepting the recommended SQL profile to use parallel execution for this statement. execute dbms_sqltune.accept_sql_profile(task_name =>
'TEST_sql_tuning_task1', task_owner => 'SYS', replace => TRUE,
profile_type => DBMS_SQLTUNE.PX_PROFILE);
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
----------------------------------------------------------------------------------------------------
Executing this query parallel with DOP 64 will improve its response time
-114609.79% over the original plan. However, there is some cost in enabling
parallel execution. It will increase the statement's resource consumption by
an estimated 7335026.74% which may result in a reduction of system
throughput. Also, because these resources are consumed over a much smaller
duration, the response time of concurrent statements might be negatively
impacted if sufficient hardware capacity is not available.
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
----------------------------------------------------------------------------------------------------
--------------------------------------------------
5 - access("WEIGHT"="WEIGHT")
-------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
----------------------------------------------------------------------------------------------------
Elapsed: 00:00:00.03
Below recommendation is provided in the report.
Recommendation (estimated benefit<=10%)
—————————————
– Consider accepting the recommended SQL profile to use parallel execution
for this statement.
execute dbms_sqltune.accept_sql_profile(task_name =>
‘TEST_sql_tuning_task1’, task_owner => ‘SYS’, replace => TRUE,
profile_type => DBMS_SQLTUNE.PX_PROFILE);
Lets implement this recommendation and check the performance again.
execute dbms_sqltune.accept_sql_profile(task_name = >'TEST_sql_tuning_task1', task_owner = > 'SYS', replace = > TRUE, profile_type = > DBMS_SQLTUNE.PX_PROFIL
E);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.17
10:08:23 SQL > select plan_table_output from
table(dbms_xplan.display('plan_table',null,'typical -cost -bytes'));10:26:14 2
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2143503231
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 00:00:14 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | PX COORDINATOR | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 1 | | Q1,02 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | Q1,02 | PCWP | |
|* 5 | HASH JOIN SEMI | | 13M| 00:00:14 | Q1,02 | PCWP | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 6 | PX RECEIVE | | 13M| 00:00:03 | Q1,02 | PCWP | |
| 7 | PX SEND HASH | :TQ10000 | 13M| 00:00:03 | Q1,00 | P->P | HASH |
| 8 | PX BLOCK ITERATOR | | 13M| 00:00:03 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL | BIGTAB | 13M| 00:00:03 | Q1,00 | PCWP | |
| 10 | PX RECEIVE | | 13M| 00:00:03 | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10001 | 13M| 00:00:03 | Q1,01 | P->P | HASH |
| 12 | VIEW | VW_NSO_1 | 13M| 00:00:03 | Q1,01 | PCWP | |
| 13 | PX BLOCK ITERATOR | | 13M| 00:00:03 | Q1,01 | PCWC | |
| 14 | TABLE ACCESS FULL| BIGTAB | 13M| 00:00:03 | Q1,01 | PCWP | |
-----------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("WEIGHT"="WEIGHT")
Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: skipped because of IO calibrate statistics are missing
- SQL profile "SYS_SQLPROF_014eabca6e060000" used for this statement
32 rows selected.
10:07:27 SQL > select count(*) from bigtab where weight in ( select distinct( weight) from bigtab);
COUNT(*)
----------
13296640
Elapsed: 00:00:11.13
We can see the changes in the execution plan . Also the query which was taking 19 seconds before, is now taking 11 seconds.
This is a very good article.