189 8069 5689
参考：
http://www.bubuko.com/infodetail-216529.html
实验如下：
SQL> create table dh_stat as select rownum id ,object_name name ,object_type type from dba_objects;
Table created.
SQL> create index ind_1 on dh_stat(id) compute statistics;
Index created.
SQL> alter system flush shared_pool;
System altered.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'sys',TABNAME=>'dh_stat',ESTIMATE_PERCENT=>30,METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',NO_INVALIDATE=>FALSE,CASCADE=>TRUE,DEGREE => 1);
PL/SQL procedure successfully completed.
SQL> col name format a15
SQL> col name format a30
SQL> col sql_text for a55
SQL> select /* outlinetest1 */ * from dh_stat where id=771;
ID NAME TYPE
---------- ------------------------------ -------------------
771 SYS_IL0000000772C00002$$ INDEX
SQL> set lines 200
SQL> select sql_text,sql_id,hash_value,child_number from v$sql a where sql_text like '%outlinetest1%' and sql_text not like '%v$sql%';
SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER
------------------------------------------------------- ------------- ---------- ------------
select /* outlinetest1 */ * from dh_stat where id=771 9a69w18a8uuhk 344812050 0
SQL> select * from table(dbms_xplan.display_cursor('9a69w18a8uuhk',null));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID 9a69w18a8uuhk, child number 0
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DH_STAT | 1 | 39 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN | IND_1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=771)
19 rows selected.
---根据sql已经有的执行计划生成outline:
SQL> exec DBMS_OUTLN.create_outline(hash_value=>344812050,child_number => 0,category=>'TEST');
PL/SQL procedure successfully completed.
--查询outline情况：
SQL> select name,category,used,sql_text from dba_outlines;
NAME CATEGORY USED SQL_TEXT
------------------------------ ------------------------------ ------ -------------------------------------------------------
SYS_OUTLINE_17090216454529101 TEST UNUSED select /* outlinetest1 */ * from dh_stat where id=771
此处outline的USED状态没有改变，因为我们没有激活TEST类别的outline，再次申明，outline必须通过use_stored_outlines参数激活后，优化器才会使用outline
---下面这一步激活TEST类别的OUTLINE，也可以在系统级激活OUTLINE
SQL> alter session set use_stored_outlines=TEST;
验证省略。。。。。。。。。。。
创新互联专注于中大型企业的网站设计制作、网站设计和网站改版、网站营销服务，追求商业策划与数据分析、创意艺术与技术开发的融合，累计客户上千，服务满意度达97%。帮助广大客户顺利对接上互联网浪潮，准确优选出符合自己需要的互联网运用，我们将一直专注高端网站设计和互联网程序开发，在前进的路上，与客户一起成长！
语法：
SQL text format:
DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text IN CLOB, bind_list IN sql_binds := NULL, user_name IN VARCHAR2 := NULL, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL) RETURN VARCHAR2;
SQL ID format:
DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_id IN VARCHAR2, plan_hash_value IN NUMBER := NULL, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL) RETURN VARCHAR2;
Workload Repository format:
DBMS_SQLTUNE.CREATE_TUNING_TASK(
begin_snap IN NUMBER,
end_snap IN NUMBER,
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
RETURN VARCHAR2;
实验1：sql_id format
1. sys用户创建oracle优化任务(v_sqlid,task_name根据实际情况改变)
set long 10000000 longchunksize 1000000 linesize 150 pagesize 0 serveroutput on size 1000000 verify off
declare
my_task_name varchar2(30);
v_sqlid varchar2(50);
begin
v_sqlid:='11wrxmug9y4a7';
my_task_name := dbms_sqltune.create_tuning_task
(sql_id=> v_sqlid,
scope => 'comprehensive',
time_limit=>160,
task_name=>'task_00000',
description => 'tuning task');
dbms_sqltune.execute_tuning_task('task_00000');
end;
/
注：dbms_sqltune.execute_tuning_task('task_00000')，是执行优化任务
2. 打印优化任务，里面有一些具体的改进措施
select dbms_sqltune.report_tuning_task('task_00000') from dual;
3.根据优化建议，绑定profile，理论上可提高语句执行效率
execute dbms_sqltune.accept_sql_profile(task_name => 'task_00000',task_owner => 'sys', replace => true);
实验2：sql_text format
SQL> alter session set statistics_level=all;
Session altered.
SQL> set serveroutput off
SQL> select * from scott.emp where ename='SCOTT' and DEPTNO=20;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'runstats_last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 4dsqbp572auuu, child number 0
-------------------------------------
select * from scott.emp where ename='SCOTT' and DEPTNO=20
Plan hash value: 3956160932
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 7 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 1 | 1 |00:00:00.01 | 7 |
PLAN_TABLE_OUTPUT
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("ENAME"='SCOTT' AND "DEPTNO"=20))
18 rows selected.
SQL> DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select * from scott.emp where ename= :name and DEPTNO= :deptno';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
bind_list => sql_binds(anydata.convertvarchar2(10),anydata.convertnumber(2)),
user_name => 'SYS',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'test_sql_tuning',
description => 'Task to tune a query on emp');
END;
/
PL/SQL procedure successfully completed.
参数说明：
bind_list：多个绑定变量以','逗号分隔。参数值一定要根据绑定变量对应的列的类型书写.如：emp.ename类型是VARCHAR2(10)，那么就要写成 bind_list =>sql_binds(anydata.convertvarchar2(10)),
time_limit：执行的最长时间，默认是60。
scope：LIMITED，用大概1秒时间去优化SQL语句，但是并不进行SQL Profiling分析;COMPREHENSIVE，进行全面分析，包含SQL Profiling分析；比LIMITED用时更长。
--查看任务名
SQL> select task_name from dba_advisor_log where task_name='test_sql_tuning';
TASK_NAME
------------------------------
test_sql_tuning
--执行sql tuning任务
SQL> exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'test_sql_tuning' );
PL/SQL procedure successfully completed.
--查看sql tunning任务状态
SQL> select task_name,status from dba_advisor_log where task_name='test_sql_tuning';
TASK_NAME STATUS
------------------------------ -----------
test_sql_tuning COMPLETED
----展示sql tunning结果
set long 10000
set longchunksize 1000
set linesize 100
select dbms_sqltune.report_tuning_task('test_sql_tuning') from dual;
...........省略
--根据建议accept_sql_profile
execute dbms_sqltune.accept_sql_profile(.....)
--完成后删除sql tunning任务
exec dbms_sqltune.drop_tuning_task('test_sql_tuning');
--查看SQL Tuning Advisor的进展（task执行很久）
set lines 200
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 = 'SYS';