使用SQL Profile及SQL Tuning Advisor固定執行計劃

SQL Profile就是為某一SQL語句提供除了系統統計資訊、物件（表和索引等）統計資訊之外的其他資訊，比如執行環境、額外的更準確的統計資訊，以幫助優化器為SQL語句選擇更適合的執行計劃。
SQL Profiles可以說是Outlines的進化。Outlines能夠實現的功能SQL Profiles也完全能夠實現，而SQL Profiles具有Outlines不具備的優化，最重要的有二點：
    SQL Profiles更容易生成、更改和控制。
    SQL Profiles在對SQL語句的支援上做得更好，也就是適用範圍更廣。
使用SQL Profiles兩個目的：
    鎖定或者說是穩定執行計劃。
    在不能修改應用中的SQL的情況下使SQL語句按指定的執行計劃執行。
參考老熊的部落格，連結：http://www.laoxiong.net/sql-profiles-partii.html    http://www.laoxiong.net/sql-profiles-part.html   http://blog.sina.com.cn/s/blog_5037eacb01011mgu.htm

1.實驗環境構造語句：

create table bys.t1 as select * from dba_objects;
create index bys.t1_idx on t1(object_id);
exec dbms_stats.gather_table_stats('bys','t1',cascade=>true,degree=>4);
set autotrace trace;
select a.*,b.owner from t1 a,t1 b where a.object_name like '%T1%' and a.object_id=b.object_id;
select /*+ use_nl(a b) index(t2) */ a.*,b.owner from t1 a,t1 b where a.object_name like '%T1%' and a.object_id=b.object_id;
set autotrace off;
explain plan for select /*+ use_nl(a b) index(t2) */ a.*,b.owner from t1 a,t1 b where a.object_name like '%T1%' and a.object_id=b.object_id;
col sql_text for a100
SELECT SQL_ID,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%a.*,b.owner from t1 a,t1 b%';
####2t5xqt4d1dsaw
10g開始，v$sql_plan中就包括了SQL語句OUTLINE資料，也就是穩定執行計劃的Hints。如下：
set pagesize 1000
SQL> select * from table(dbms_xplan.display_cursor('2t5xqt4d1dsaw',null,'outline'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID  2t5xqt4d1dsaw, child number 0
-------------------------------------
select /*+ use_nl(a b) index(t2) */ a.*,b.owner from t1 a,t1 b where
a.object_name like '%T1%' and a.object_id=b.object_id
Plan hash value: 190596302
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |   979 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |     9 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |        |   471 | 43803 |   979   (1)| 00:00:12 |
|*  3 |    TABLE ACCESS FULL        | T1     |   471 | 39564 |    36   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | T1_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Outline Data              ----------這一部分： /*+     */ 之間的就是所需的資料了
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "A"@"SEL$1")
      INDEX(@"SEL$1" "B"@"SEL$1" ("T1"."OBJECT_ID"))
      LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
      USE_NL(@"SEL$1" "B"@"SEL$1")
      END_OUTLINE_DATA
  */

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("A"."OBJECT_NAME" LIKE '%T1%')
   4 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
###############################################################################

2.開始建立並應用SQL Profile

使用未加HINT的SQL語句，並指定SQL_PROFILE名字：SQLPROFILE_T1
注意事項：兩個引號---如('10.2.0.1')要寫成(''10.2.0.1'')--'%T1%'寫成''%T1%''
SQL語句結尾的;不要寫上,繫結變數值是'1'時，要輸入''1''
 declare
 v_hints sys.sqlprof_attr;
 begin
 v_hints:=sys.sqlprof_attr(
      'BEGIN_OUTLINE_DATA',
      'IGNORE_OPTIM_EMBEDDED_HINTS',
      'OPTIMIZER_FEATURES_ENABLE(''10.2.0.1'')',
      'ALL_ROWS',
      'OUTLINE_LEAF(@"SEL$1")',
      'FULL(@"SEL$1" "A"@"SEL$1")',
      'INDEX(@"SEL$1" "B"@"SEL$1" ("T1"."OBJECT_ID"))',
      'LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")',
      'USE_NL(@"SEL$1" "B"@"SEL$1")',
      'END_OUTLINE_DATA');
dbms_sqltune.import_sql_profile(
'select a.*,b.owner from t1 a,t1 b where a.object_name like ''%T1%'' and a.object_id=b.object_id',
v_hints,'SQLPROFILE_T1',                
force_match=>true,replace=>true);
end;
/
刪除語句：
exec dbms_sqltune.drop_sql_profile(name =>'SQLPROFILE_T1' );
######

3.驗證SQL語句是否使用上一步建立的SQL_PROFILE：

SQL> set autotrace trace;
SQL> select a.*,b.owner from t1 a,t1 b where a.object_name like '%T1%' and a.object_id=b.object_id;
23 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 190596302
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |   471 | 43803 |   979   (1)| 00:00:12 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |     9 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |        |   471 | 43803 |   979   (1)| 00:00:12 |
|*  3 |    TABLE ACCESS FULL        | T1     |   471 | 39564 |    36   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | T1_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("A"."OBJECT_NAME" LIKE '%T1%')
   4 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
Note
-----
   - SQL profile "SQLPROFILE_T1" used for this statement

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        163  consistent gets
          0  physical reads
          0  redo size
       2609  bytes sent via SQL*Net to client
        392  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         23  rows processed          0  sorts (disk)
#####################################################################      

使用Oracle的SQL Tuning Advisor固定執行計劃--SQL_ID

----通過sql_id的方式  sql_id => 'a2h6pzvqncfvg',--想調優的sql_id

show serveroutput
set serveroutput on;

DECLARE  
    a_tuning_task VARCHAR2(30);  
  BEGIN  
    a_tuning_task := dbms_sqltune.create_tuning_task(sql_id => 'a2h6pzvqncfvg',task_name => 'tuning_test');    
    dbms_sqltune.execute_tuning_task(a_tuning_task);   
  END;  
  /
set long 1000
SELECT dbms_sqltune.report_tuning_task('tuning_test') FROM dual;
 
這裡的輸出中的sql_id是從v$sql查出，對應的是沒有加hint的SQL。
如果SQL Tuning Advisor找到了理想的執行計劃，下一步就是：Accept SQL Profile，接受這個SQL Profile。

## execute dbms_sqltune.accept_sql_profile(task_name =>'tuning_test' ,replace => TRUE,force_match=>true);
 完成後驗證語句：
set autotrace trace;
select a.*,b.owner from t1 a,t1 b where a.object_name like '%T1%' and a.object_id=b.object_id;
set autotrace off;
刪除SQL語句：
exec dbms_sqltune.drop_tuning_task('tuning_test');  
 如果未找到理想的執行計劃，則參考下一個方法。
##################################################################

使用Oracle的SQL Tuning Advisor固定執行計劃--使用SQL_TEXT，參考以下：

DECLARE
 my_task_name VARCHAR2 (30);
 my_sqltext CLOB;
 BEGIN
 my_sqltext := 'select * from test where OBJECT_ID=15';  --想調優的sql
 my_task_name := dbms_sqltune.create_tuning_task (sql_text=> my_sqltext,
 --bind_list =>  'UNDO$',
 --user_name => 'SONG',
 --scope => 'COMPREHENSIVE',
 --time_limit => 60,
 task_name => 'sql_tuning_test'
 --description => 'Tuning Task'
 );
 END;
 /
 
 exec dbms_sqltune.execute_tuning_task('sql_tuning_test');   --執行調優的任務
  SELECT STATUS FROM USER_ADVISOR_TASKS WHERE task_name = 'sql_tuning_test'; --檢視調優作業的狀態
 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_test') FROM DUAL;--檢視調優的建議
 exec dbms_sqltune.drop_tuning_task('sql_tuning_test');--刪除任務