执行计划绑定

• 这种方法不能指定outline的具体名字，由系统自动生成，可以通过alter outline SYS_OUTLINE_14061114223605901 rename to dh_test2修改
• 不指定类别的话默认为default，而且此处创建时，不能指定为default类别（会报错）。
• 我们使用outline固定执行计划时，一般都是选用此种方法

参考：
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;

验证省略。。。。。。。。。。。

语法：
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 format1. 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 formatSQL> alter session set statistics_level=all;Session altered.SQL> set serveroutput offSQL> 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                    20SQL> 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=20Plan 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 10000set longchunksize 1000set linesize 100select dbms_sqltune.report_tuning_task('test_sql_tuning') from dual;...........省略--根据建议accept_sql_profileexecute dbms_sqltune.accept_sql_profile(.....)--完成后删除sql tunning任务exec dbms_sqltune.drop_tuning_task('test_sql_tuning');--查看SQL Tuning Advisor的进展（task执行很久）set lines 200col opname for a20col ADVISOR_NAME for a20select sid,serial#,username,opname,advisor_name,target_desc,start_time sofar,totalwork from v$advisor_progress where username = 'SYS';