sql tuning advisor
我们现在创建一个测试表，看此时正确的执行计划
13:11:53 scott@orcl> select * from t2 where empno=200;
Elapsed: 00:00:00.01
Execution Plan[hide]
[/hide]
[hide]
[/hide]
[hide]
[/hide]
----------------------------------------------------------
Plan hash value: 2008370210
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 39 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T2 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=200)
x-span class="text-primary"[x-span class="{type}"]{content}/x-span{content}/x-span{content}/x-span{content}/x-span{content}/x-span{content}/x-span{content}[/x-span]
我们使用hint来强制走一个错误的执行计划
13:11:58 scott@orcl> select /*+ full(t2) */ * from t2 where empno=200;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 1 | 39 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=200)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
47 consistent gets
0 physical reads
0 redo size
1088 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
现在我们使用sqltune advisor来进行调整
创建TUNING_TASK并执行
declare
l_task_name varchar2(30);
l_sql clob;
begin
l_sql := 'select /*+ full(t2) */ * from t2 where empno=200';
l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => l_sql,
user_name => 'SCOTT',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'test01',
description => null);
end;
/
time_limit：执行的最长时间，默认是60。
scope：
LIMITED，用大概1秒时间去优化SQL语句，但是并不进行SQL Profiling分析。
COMPREHENSIVE，进行全面分析，包含SQL Profiling分析；比LIMITED用时更长。
**也可以用sql_id创建sql tunning任务，比用sql_text方便很多
FUNCTION CREATE_TUNING_TASK RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER IN DEFAULT
SCOPE VARCHAR2 IN DEFAULT
TIME_LIMIT NUMBER IN DEFAULT
TASK_NAME VARCHAR2 IN DEFAULT
DESCRIPTION VARCHAR2 IN DEFAULT
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
SQL_ID => 'ddw7j6yfnw0vz',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'tunning_task_ddw7j6yfnw0vz',
description => 'Task to tune a query on ddw7j6yfnw0vz');
END;
/
我们查看此时任务的状态
13:27:53 scott@orcl> select task_name,EXECUTION_START,EXECUTION_END,STATUS from DBA_ADVISOR_LOG where task_name like 'test%';
TASK_NAME EXECUTION_START EXECUTION_END STATUS
------------------------------ ------------------- ------------------- -----------
test01 INITIAL
执行sql tuning任务
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'test01' );
END;
/
展
SET LONG 10000示sql tunning结果
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('test01')
FROM DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST01')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : test01
Tuning Task Owner : SCOTT
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 12/21/2014 13:29:11
Completed at : 12/21/2014 13:29:15
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 3bgc9fc2fp597
SQL Text : select /*+ full(t2) */ * from t2 where empno=200
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 93.46%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'test01', task_owner
=> 'SCOTT', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .000378 .000098 74.07 %
CPU Time (s): .000299 .000099 66.88 %
User I/O Time (s): 0 0
Buffer Gets: 46 3 93.47 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1
Notes
-----
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 1 | 39 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=200)
2- Using SQL Profile
--------------------
Plan hash value: 2008370210
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 39 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T2 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=200)
-------------------------------------------------------------------------------
我们可以看到他提供的建议，执行sql_profile，我们根据他的建议执行这个profile
execute dbms_sqltune.accept_sql_profile(task_name => 'test01',task_owner=> 'SCOTT',replace => TRUE);
然后我们再来执行下原来的带hint的语句
select /+ full(t2) / * from t2 where empno=200;
13:39:32 scott@orcl> select /+ full(t2) / * from t2 where empno=200;
Elapsed: 00:00:00.01
Execution Plan
Plan hash value: 2008370210
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 39 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T2 | 1 | | 1 (0)| 00:00:01 |
Predicate Information (identified by operation id):
2 - access("EMPNO"=200)
Note
- SQL profile "SYS_SQLPROF_014a6b5a4a4a0000" used for this statement
Statistics
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1092 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed可以看到使用了sql_profile 从而走了正确的执行计划
现在我们再来看看其他的情况，我们原来的表上没有索引，看看tune advisor能提供什么样的建议
13:42:44 scott@orcl> select * from t4 where empno=200;
Elapsed: 00:00:00.04
Execution Plan
Plan hash value: 2560505625
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 100 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T4 | 1 | 100 | 15 (0)| 00:00:01 |
Predicate Information (identified by operation id):
1 - filter("EMPNO"=200)
Note
- dynamic sampling used for this statement (level=2)
Statistics
14 recursive calls
0 db block gets
114 consistent gets
50 physical reads
0 redo size
1088 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed此时表是没有索引的，走的全表扫描
我们使用sql_id的方式来创建task
13:45:41 scott@orcl> select sql_text,sql_id from v$sql where sql_text like 'select * from t4%';
SQL_TEXT SQL_ID
select * from t4 where empno=200 5avs113b5fn8v
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
SQL_ID => '5avs113b5fn8v',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'tunning_task_5avs113b5fn8v',
description => 'Task to tune a query on 5avs113b5fn8v');END;
/
启动这个task
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tunning_task_5avs113b5fn8v' );
END;
/
查看report
SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tunning_task_5avs113b5fn8v')
FROM DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNNING_TASK_5AVS113B5FN8V')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : tunning_task_5avs113b5fn8v
Tuning Task Owner : SCOTT
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 12/21/2014 13:48:02
Completed at : 12/21/2014 13:48:03
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 5avs113b5fn8v
SQL Text : select * from t4 where empno=200
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "SCOTT"."T4" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'T4', 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.
2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 86.7%)
-----------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index SCOTT.IDX$$_00540001 on SCOTT.T4("EMPNO");
Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 2560505625
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T4 | 1 | 100 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=200)
2- Using New Indices
--------------------
Plan hash value: 3508715929
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T4 | 1 | 100 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX$$_00540001 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=200)
-------------------------------------------------------------------------------
可以看到 sql_tune advisor提供了建议在empno 列上面创建索引，可见分析的还是很准确的
删除tune_tast
EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('test01');
其他
--sql tunning任务创建后，也可以修改参数
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
task_name => 'test_sql_tuning',
parameter => 'TIME_LIMIT', value => 300);
END;
/
--查看SQL Tuning Advisor的进展（task执行很久）
col opname for a20
col ADVISOR_NAME for a20
SELECT SID,SERIAL#,USERNAME,OPNAME,ADVISOR_NAME,TARGET_DESC,START_TIME SOFAR, TOTALWORK
FROM V$ADVISOR_PROGRESS
WHERE USERNAME = 'TEST';