SQL Profiles是在Oracle 10g中就引入的，並且透過dbms_sqltune包或EM來進行管理是自動SQL調整程式的一部分。

自動SQL調整
查詢最佳化器會有時會因為缺少資訊而對語句的一個屬性產生不精確的評估，進而導致低效的執行計劃傳統上來說，使用者不得不透過手動增加hint到程式碼中來修改這個剖從而指導最佳化器產生正確的決定。對於第三方應用程式，改變應用程式碼是不現實的。

自動SQL調整使用SQL Profile來處理這個問題。自動調整最佳化器將會為SQL語句建立一個SQL Profile，它是由語句相關的輔助統計資訊組成。查詢最佳化器在正常模式下對基數，選擇率與成本的評估有時會有嚴重的差異而導致生成低效的執行計劃。SQL Profile透過收集額外的資訊，比如使用抽樣與特定的執行技術來調整評估可以用來解決這個問題。

在自動SQL調整時，最佳化器也會使用SQL語句的歷史執行資訊來合理設定最佳化器引數，比如改變optimizer_mode，將其引數從all_rows改成first_rows。

分析報告的輸出建議接受SQL Profile。一旦接受SQL Profile它將會永久儲存在資料字典中。一個SQL Profile被指定到一個特定的查詢。如果接受，最佳化器在正常模式下使用SQL Profile中的資訊與常規的資料庫統計資訊來對SQL進行解析。SQL Profile所提供的額外資訊可以用來生成更好的執行計劃。

SQL Profile
SQL Profile是儲存在資料字典中的資訊集合能讓查詢最佳化器為SQL語句建立一個最優執行計劃。SQL Profile包含了自動SQL調整所發現的低效最佳化器評估的修正。這種資訊能提高最佳化器對基數與選擇率評估精確度，從而導致最佳化器選擇一個更好的執行計劃。

SQL Profile不包含單獨的執行計劃，當選擇執行計劃時最佳化器有以下資訊源:
1.環境，包含資料庫配置，繫結變數值，統計資訊，資料集等等
2.SQL Profile所提供了附加統計資訊

重要的是SQL Profile不會凍結一個SQL語句的執行計劃，這一點與stored outlines不一樣。當表記錄增加或增加與刪除索引時，使用相同的SQL Profile執行計劃也會發生改變。當資料分佈或相關語句的訪問路徑發生改變，SQL Profile中儲存的資訊仍然與SQL語關聯。然而，隨著時間的推移，SQL Profile的內容將會過時並且不得不重新生成。可以再次執行自動SQL調整來重新生成SQL Profile。

如何控制SQL Profile的使用範圍
SQL Profile的使用範圍可以由CATEGORY屬性來進行控制。這個屬性決定那個使用者會話可以應用這個SQL Profile。可以透過查詢dba_sql_profiles檢視的category列來檢視SQL Profile的category屬性

SQL> select name,category from dba_sql_profiles;
 
NAME                           CATEGORY
------------------------------ ------------------------------
SYS_SQLPROF_0152b233d518c007   DEFAULT
SYS_SQLPROF_015470e31c248001   DEFAULT
coe_bcyatm4910qb1_725332378    DEFAULT
coe_3yy1wbuvsxm93_1849931106   DEFAULT
SYS_SQLPROF_0152b11b33e6c006   DEFAULT
coe_a69pw2vj989zm_3709683508   DEFAULT
SYS_SQLPROF_0151ed60f3d28000   DEFAULT
coe_6rfqq1bjwcdx9_1360313219   DEFAULT
SYS_SQLPROF_0152b33048a8c009   DEFAULT
coe_36cbabzyq13gy_1849931106   DEFAULT
SYS_SQLPROF_015470e298fd0000   DEFAULT
SYS_SQLPROF_0152b0a82393c003   DEFAULT
SYS_SQLPROF_0152ba15c21e800b   DEFAULT
coe_6rfqq1bjwcdx9_1360313219_1 DEFAULT

預設情況下，所有SQL Profile都是建立在DEFAULT目錄中。這意味著當sqltune_category設定為default時所有的使用者會話都能使用這個SQL Profile。

SQL> show parameter sqltune_category
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sqltune_category                     string      DEFAULT

透過修改SQL Profile的category屬性，可以決定那個會話將使用所建立的SQL Profile。例如，透過將一個SQL Profile的category屬性設定為DEV，那麼只有當sqltune_category設定為DEV時這些會話才能使用這個SQL Profile。所有其它的會話將不能訪問這個SQL Profile並且SQL語句的執行計劃將不會受這個SQL Profile的影響。這種技術能在SQL Profile被其它會話使用之前讓你在一個受限的環境下測試SQL Profile。

SQL Profile可以應用的語句型別
.select語句
.update語句
.insert語句(只包含select子句)
.delete語句
.create table語句(只包含as select子句)
.merge語句(update或insert操作)

SQL Profile的管理
SQL Profile可以透過EM或dbms_sqltune來進行管理

為了使用dbms_sqltune來管理SQL Profile，使用者必須有create any sql_profile,drop any sql_profile與alter any sql_profile系統許可權。

接受SQL Profile
使用dbms_sqltune.accept_sql_profile過程來接受由SQL調整指導所建立的SQL Profile。

DECLARE
 my_sqlprofile_name VARCHAR2(30);
 BEGIN
 my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( 
 task_name => 'my_sql_tuning_task',
 name => 'my_sql_profile');
 END;

my_sql_tuning_task是SQL調整任務的名稱，可以查詢dba_sql_profiles檢視來檢視SQL Profile的資訊

修改SQL Profile
使用dbms_sqltune.alter_sql_profile過程可以用來修改現有SQL Profile的status,name,description與category屬性

BEGIN
 DBMS_SQLTUNE.ALTER_SQL_PROFILE(
 name => 'my_sql_profile', 
 attribute_name => 'STATUS', 
 value => 'DISABLED');
 END;
 /

在這個例子中，要修改名為my_sql_profile的SQL Profile，將它的status屬性修改為disable這將意味著這個SQL Profile將不能在SQL編譯時使用了。

刪除SQL Profile
可以使用dbms_sqltune.drop_sql_profile過程來刪除SQL Profile

begin
 DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile');
 end;
 /

下面介紹使用SQL Profile來最佳化SQL語句的例子
會話1

SQL> create table test(n number);

Table created.

SQL> declare
  2             begin
  3              for i in 1 .. 10000 loop
  4                  insert into test values(i);
  5                  commit;
  6              end loop;
  7             end;
  8   /

PL/SQL procedure successfully completed.

SQL> create index test_idx on test(n);

Index created.

SQL> exec dbms_stats.gather_table_stats('','TEST');

PL/SQL procedure successfully completed.

SQL> set autotrace on
SQL> select /*+ no_index(test test_idx) */ * from test where n=1;

         N
----------
         1


Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N"=1)


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

會話2
建立一個SQL自動調整任務並執行報告調整任務並接受建議的SQL Profile

SQL> declare
  2     my_task_name VARCHAR2(30);
  3     my_sqltext CLOB;
  4     begin
  5        my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where n=1';
  6        my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
  7        sql_text => my_sqltext,
  8        user_name => 'SCOTT',
  9        scope => 'COMPREHENSIVE',
 10        time_limit => 60,
 11        task_name => 'my_sql_tuning_task_1',
 12        description => 'Task to tune a query on a specified table');
 13   end;
 14   /

PL/SQL procedure successfully completed.

SQL>  begin
  2   DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_1');
  3   end;
  4   /

PL/SQL procedure successfully completed.

SQL> set long 10000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> set heading off
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_1') from DUAL;
set heading on
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : my_sql_tuning_task_1
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 05/27/2016 16:58:11
Completed at       : 05/27/2016 16:58:28

-------------------------------------------------------------------------------
Schema Name: SCOTT

SQL ID     : d4wgpc5g0s0vu
SQL Text   : select /*+ no_index(test test_idx) */ * from test where n=1

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 90.91%)
  ------------------------------------------

  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'my_sql_tuning_task_1', task_owner => 'SYS', replace => TRUE);

  Validation results
  ------------------
  The SQL profile was tested by executing both its plan and the original plan
  and measuring their respective execution statistics. A plan may have been
  only partially executed if the other could be run to completion in less time.

                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:            COMPLETE          COMPLETE

  Elapsed Time (s):             .003464           .000405       88.3 %
  CPU Time (s):                 .003399           .000299       91.2 %
  User I/O Time (s):                  0                 0
  Buffer Gets:                       22                 2       90.9 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                     1                 1
  Fetches:                            1                 1
  Executions:                         1                 1

  Notes

  -----
  1. Statistics for the original plan were averaged over 10 executions.
  2. Statistics for the SQL profile plan were averaged over 10 executions.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 1357081020

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N"=1)

2- Using SQL Profile
--------------------

Plan hash value: 2882402178

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     4 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("N"=1)


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

SQL> DECLARE
  2   my_sqlprofile_name VARCHAR2(30);
  3   begin
  4   my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
  5   task_name => 'my_sql_tuning_task_1',
  6   name => 'my_sql_profile',
  7   force_match => true,
  8   replace =>true );
  9   end;
 10  /

PL/SQL procedure successfully completed.

會話1

SQL> set autotrace on
SQL> select /*+ no_index(test test_idx) */ * from test where n=1;

         N
----------
         1


Execution Plan
----------------------------------------------------------
Plan hash value: 2882402178

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     4 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("N"=1)

Note
-----
   - SQL profile "my_sql_profile" used for this statement


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         13  consistent gets
          1  physical reads
          0  redo size
        415  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

可以看到雖然我們指定了no_index來讓最佳化器不使用索引test_idx,但由於使用了SQL Profile還是使用索引test_idx,透過SQL Profile改變了SQL語句的執行計劃。

Automatic SQL Tuning and SQL Profiles

相關文章