sql profile

1、準備測試資料

create table hy19 as select * from dba_objects;

2、建立索引并分析

CREATE INDEX INX_19 ON SCOTT.HY19(OBJECT_ID);

ANALYZE TABLE SCOTT.HY19 COMPUTE STATISTICS;

3. 執行SQL語句，模拟一個性能低下的執行計劃

SELECT /*+ no_index(HY19 INX_19)*/ * FROM HY19  WHERE OBJECT_ID=1;

（此處用别名通路表時發現hint不起作用）

SQL> SELECT /*+ no_index(HY19 INX_19)*/ * FROM HY19  WHERE OBJECT_ID=1;

Execution Plan

----------------------------------------------------------

Plan hash value: 3188441247

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

|   0 | SELECT STATEMENT  |      |     1 |    87 |   159   (2)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL| HY19 |     1 |    87 |   159   (2)| 00:00:02 |

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("OBJECT_ID"=1)

4、建立優化任務：

–使用SQL TEXT

DECLARE

  my_task_name VARCHAR2(30);

  my_sqltext   CLOB;

BEGIN

  my_sqltext := 'SELECT /*+ no_index(HY19 INX_19)*/ * FROM HY19  WHERE OBJECT_ID=1';

  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

         sql_text    => my_sqltext,

         user_name   => 'SCOTT',

         scope       => 'COMPREHENSIVE',

         time_limit  => 60,

         task_name   => 'tuning_task_1',

         description => 'Task to tune a query on a specified table');

END;

/

–使用SQL ID:

 my_task_name VARCHAR2(50);

 my_sql_id   VARCHAR2(64);

 my_sql_id := 'gh991ctttx3k7';

 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

         sql_id    => my_sql_id,

         task_name   => 'rockey_sql_tuning_task_001',

5、執行優化任務：

  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'rockey_sql_tuning_task_001');

end;

6、檢視優化建議：

SET WRAP ON

SET LONG 10000

SET LONGCHUNKSIZE 1000

SET LINESIZE 130

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'rockey_sql_tuning_task_001') from DUAL;

或者

SELECT dbms_advisor.GET_TASK_REPORT(task_name)

  FROM dba_advisor_tasks

 where task_name = 'rockey_sql_tuning_task_001';

7、SQL語句綁字SQL Profile：

begin

  dbms_sqltune.accept_sql_profile(task_name   => 'rockey_sql_tuning_task_001',

                                  name        => 'my_sql_profile_001');

8. 再次執行相同的語句，驗證結果

Plan hash value: 2066044106

--------------------------------------------------------------------------------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time

     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |        |     1 |    87 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| HY19   |     1 |    87 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | INX_19 |     1 |       |     1   (0)| 00:00:01 |

   2 - access("OBJECT_ID"=1)

這裡可以看出，Oracle已經使用了不同的執行計劃

9、檢視語句是否使用了SQL Profile：

select sql_text, sql_id, sql_profile, executions, plan_hash_value

  from v$sql

 where sql_profile is not null;

10、其它常用功能：

删除優化任務：

dbms_sqltune.drop_tuning_task('rockey_sql_tuning_task_001');

dbms_advisor.delete_task('rockey_sql_tuning_task_001');

删除SQL Profile：

  DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile_001');

與優化任務相關的常用視圖：

select * from dba_sql_profiles;

select * from DBA_ADVISOR_TASKS;

############

SQL Profiles可以看作是SQL語句的統計資訊。隻是這個統計資訊對特定SQL語句才能起作用，不對會語句的對象、其它語句産生影響。

使用SQL Profiles前要用SQL Tuning Advisor收集對語句的優化建議，再根據優化建議建立SQL Profiles。

SQL Profiles 使用也比較靈活，可以在會話級、系統級應用。

語句綁定SQL Profile後，測試了下SQL Profile與Bind Peeking的關系。測試發現，Bind Peeking的特性還是會起作用。這從另一方面說明SQL Profile與OUTLINE的不同：綁定OUTLINE後，執行計劃是被固化的；綁定SQL Profile後，執行計劃不是不變，而是優化器在執行該語句時，會參考SQL Profile中的資訊。