SQL Tuning Advision是Oracle提供的一个功能包，可以针对有性能问题的SQL给出优化建议。可以作为调优的辅助手段。
建立测试表和索引
create table t_1 as select * from dba_objects;
create table t_2 as select * from dba_tables;
create index idx_t_2_ts on t_2(tablespace_name);
begin
dbms_stats.gather_table_stats(ownname => user,tabname => 'T_1',cascade => true);
dbms_stats.gather_table_stats(ownname => user,tabname => 'T_2',cascade => true);
end;
/
假设的SQL。 这些语句运行很慢，需要优化
-- stmt 1
select t2.*
from t_1 t1, t_2 t2
where t1.owner = t2.owner
and t1.object_name = t2.table_name
and t2.blocks > 10
and t2.tablespace_name = 'USERS';
-- stmt 2
select t1.* from t_1 t1 where t1.object_type = 'TABLE';
使用SQL Tuning Advisor，察看系统提供的优化方案
Single SQL Statement
当仅仅需要察看单条语句的时候，可以直接把SQL作为参数创建一个Task
-- drop a sql tuning task
begin
dbms_sqltune.drop_tuning_task(task_name => 'my_sql_tuning_task');
end;
/
-- create a sql tuning task
declare
l_task_name varchar2(30);
l_sqltext clob;
begin
l_sqltext := --
'select t2.*
from t_1 t1, t_2 t2
where t1.owner = t2.owner
and t1.object_name = t2.table_name
and t2.blocks > :p1
and t2.tablespace_name = :p2';
l_task_name := dbms_sqltune.create_tuning_task --
(sql_text => l_sqltext,
bind_list => sql_binds(anydata.ConvertNumber(10),
anydata.ConvertVarchar2('USERS')),
user_name => user,
scope => dbms_sqltune.SCOPE_COMPREHENSIVE,
time_limit => 60,
task_name => 'my_sql_tuning_task');
end;
/
select l.task_name,l.status from dba_advisor_log l where l.owner = user;
-- execute a sql tuning task
begin
dbms_sqltune.execute_tuning_task(task_name => 'my_sql_tuning_task');
end;
/
-- check the running progress
select p.sofar, p.totalwork
from v$advisor_progress p, dba_advisor_log l
where p.task_id = l.task_id
and l.task_name = 'my_sql_tuning_task';
-- display the result
set long 1000000
set linesize 100
select dbms_sqltune.report_tuning_task('my_sql_tuning_task') from dual;
节选部分结果
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- 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: 100%)
----------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index T2_1_12_6_A.IDX$$_7DCA0001 on
T2_1_12_6_A.T_2('TABLESPACE_NAME','BLOCKS');
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index T2_1_12_6_A.IDX$$_7DCA0002 on
T2_1_12_6_A.T_1('OWNER','OBJECT_NAME');
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: 3244836478
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 250 | 364 (2)|
|* 1 | HASH JOIN | | 1 | 250 | 364 (2)|
|* 2 | TABLE ACCESS BY INDEX ROWID| T_2 | 1 | 216 | 3 (0)|
|* 3 | INDEX RANGE SCAN | IDX_T_2_TS | 43 | | 1 (0)|
| 4 | TABLE ACCESS FULL | T_1 | 148K| 4946K| 360 (1)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OWNER"="T2"."OWNER" AND "T1"."OBJECT_NAME"="T2"."TABLE_NAME"
2 - filter("T2"."BLOCKS">:P1)
3 - access("T2"."TABLESPACE_NAME"=:P2)
2- Using New Indices
--------------------
Plan hash value: 1060650565
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 250 | 3
| 1 | NESTED LOOPS | | 1 | 250 | 3
| 2 | TABLE ACCESS BY INDEX ROWID| T_2 | 1 | 216 | 2
|* 3 | INDEX RANGE SCAN | IDX$$_7DCA0001 | 1 | | 1
|* 4 | INDEX RANGE SCAN | IDX$$_7DCA0002 | 1 | 34 | 1
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."TABLESPACE_NAME"=:P2 AND "T2"."BLOCKS">:P1 AND "T2"."BLOCKS"
NOT NULL)
4 - access("T1"."OWNER"="T2"."OWNER" AND "T1"."OBJECT_NAME"="T2"."TABLE_NAME"
-------------------------------------------------------------------------------
SQL Tuning Set
当需要对多条语句进行察看的时候，可以使用STS。
STS可以从多种数据源(CursorCache, AWR, STS)获取SQL，这里我们从Cache中取得我们关心的几条。
SQL> select sql_id, sql_text from v$sql where lower(sql_text) like 'select t_.*%';
SQL_ID SQL_TEXT
------------- --------------------------------------------------------------------------------
7rucbfq8vcr7d select t1.* from t_1 t1 where t1.object_type = 'TABLE'
588rxmp05xt7g select t2.* from t_1 t1, t_2 t2 where t1.owner = t2.owner and t1.object_na
STS中实际包含的是一些sys.sqlset_row对象，他的属性可以作为筛选的条件
SQL> desc dbms_sqltune.select_cursor_cache
Parameter Type Mode Default?
----------------- ---------- ---- --------
(RESULT) SYS.SQLSET
BASIC_FILTER VARCHAR2 IN Y
OBJECT_FILTER VARCHAR2 IN Y
RANKING_MEASURE1 VARCHAR2 IN Y
RANKING_MEASURE2 VARCHAR2 IN Y
RANKING_MEASURE3 VARCHAR2 IN Y
RESULT_PERCENTAGE NUMBER IN Y
RESULT_LIMIT NUMBER IN Y
ATTRIBUTE_LIST VARCHAR2 IN Y
-- preview the STS contents
select t.*
from table(dbms_sqltune.select_cursor_cache('sql_id in (''588rxmp05xt7g'',''7rucbfq8vcr7d'')')) t;
找到我们关心的sql_id之后，用这个条件构造STS
-- create a STS
begin
dbms_sqltune.create_sqlset(sqlset_name => 'my_sts');
end;
/
-- load STS using cursor cache
declare
l_cur dbms_sqltune.sqlset_cursor;
begin
open l_cur for
select value(t)
from table(dbms_sqltune.select_cursor_cache('sql_id in (''588rxmp05xt7g'',''7rucbfq8vcr7d'')')) t;
dbms_sqltune.load_sqlset(sqlset_name => 'my_sts', populate_cursor => l_cur);
end;
/
-- display contents of STS
select * from table(dbms_sqltune.select_sqlset('my_sts'));
-- drop a sql tuning task
begin
dbms_sqltune.drop_tuning_task(task_name => 'my_sql_tuning_task');
end;
/
-- create a sql tuning task by using STS
declare
l_task_name varchar2(30);
l_sqltext clob;
begin
l_task_name := dbms_sqltune.create_tuning_task --
(sqlset_name => 'my_sts',
scope => dbms_sqltune.SCOPE_COMPREHENSIVE,
time_limit => 60,
task_name => 'my_sql_tuning_task');
end;
/
继续后面的执行和察看步骤（略）