執行計劃繫結

語法：
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';