dbms_sqltune

作為ORACLE DBA對SQL最佳化是必備的技能，常用的方法就是看執行計劃，加hint或者修改SQL寫法，但現實的工作中，往往修改應用的SQL是非常困難的，特別是 對電信、銀行這個行業，修改應用程式過程十分繁雜，有沒有辦法不修改SQL就可以達到最佳化SQL執行計劃的方法呢？答案是肯定的，從ORACLE 10g開始ORACLE提供了自動最佳化功能，經過一段時間的總結運用，和大家分享一下如何運用DBMS_SQLTUNE對SQL進行最佳化。

    在實際工作中，我們常遇到三種情況下的SQL型別的SQL最佳化

    1.完整的SQL語句，無繫結變更（最簡單的一種，也是不常見的一種）

    2.透過AWR獲得的SQL語句，這種SQL有繫結變數

    3.在V$SQL不存在的SQL語句

    針對以上三種情況的最佳化，和大家分享一下我的最佳化語句，既然要用這個包，必須要有相應的許可權

一、執行DBMS_SQLTUNE所需要的許可權

    CREATE ANY SQL_PROFILE,

    DROP ANY SQL_PROFILE

    ALTER ANY SQL_PROFILE

    因此要想普通使用者執行DBMS_SQLTUNE包進行最佳化，需要賦相應的許可權

二、無繫結變數SQL的最佳化

    這種是最簡單的一種，通常開發的同事用的比較多，詳細SQL如下：

執行最佳化任務：

declare
  my_task_name VARCHAR2(30);
  my_sqltext   CLOB;
BEGIN
  my_sqltext   := 'select * from orabpel.cube_scope';
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text    => my_sqltext,
                                                  user_name   => 'orabpel',
                                                  scope       => 'COMPREHENSIVE',
                                                  time_limit  => 60,  --最佳化限時60s
                                                  task_name   => 'wxw_sql_tuning_task',
                                                  description => 'tune the bad sql');
  dbms_sqltune.Execute_tuning_task(task_name => 'TEST_sql_tuning_task');
END;

--檢視最佳化結果

set long 10000  --SQL*PLUS中不要忘記執行，否則看不全最佳化結果（切記）
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('wxw_sql_tuning_task') FROM DUAL;

三、透過SQL_ID進行最佳化

    這種情況通常常用於v$sql中的異常SQL最佳化

declare
  l_tuning_task varchar2(30);
begin
  l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => '9qjctmkwvny7j');
  dbms_sqltune.execute_tuning_task(l_tuning_task);
  dbms_output.put_line(l_tuning_task);--A需要跟據此處輸出值進行查詢
end;
--檢視執行計劃
select dbms_sqltune.report_tuning_task('task_27888') from dual; --此處標紅值是--A此的輸出

     注意，在實際執行過程中，如果sql_id已經不在v$sql中會報錯，此時這種方法已經無用武之地了，怎麼辦呢，下面我們看第三種方法，指定AWR報告中的snap_id進行最佳化

四、透過指定AWR中的Snap_id進行最佳化

declare
     my_task_name varchar2(30);
begin
     dbms_sqltune.drop_tuning_task(task_name => 'wxw_sql_tuning_task');
     my_task_name := dbms_sqltune.create_tuning_task(
          begin_snap      => 24365,
          end_snap        => 24366,
          sql_id          => 'd40kghyfbg8sj',
          plan_hash_value => null,
          scope           => 'comprehensive',
          time_limit      => 60,
          task_name       => 'wxw_sql_tuning_task',
          description     => 'tune the bad sql'
     );
     dbms_sqltune.execute_tuning_task (task_name => 'wxw_sql_tuning_task');
  end;
--檢視sql最佳化結果
 select dbms_sqltune.report_tuning_task('wxw_sql_tuning_task') from dual;
     注意，此方法仍有一個弊端，前提需要AWR報告中的Snap_id中在資料庫中存在，我們知道，在10g中 預設保留七天，11g中預設保留八天，一旦過了這個時間，這種方法也無用武之地，既不存v$sql中，AWR也過期，此時我們還有一種方法進行最佳化，那就 是透過無繫結變數SQL進行最佳化，但此時會帶來一個問題，我們AWR報告中的SQL是帶有繫結變數的都是：1 ，：2這種形式，如何轉換呢？繼續往下看

四、繫結變數還原

    此時分兩種情況，一種是在v$sql中存在的SQL，一種是在v$sql中不存在的SQL

1> 轉化v$sql中對應的繫結變數值

   跟據SQL_ID取v$sql中bind_data的值
select position, value_string
  from table(dbms_sqltune.extract_binds('beda0a200500521ffd700'));

2>如果v$sql中不存在，可以用此方法獲取繫結變數值
select dbms_sqltune.extract_bind(bind_data, 1).value_string || '-' ||
       dbms_sqltune.extract_bind(bind_data, 2).value_string || '-' ||
       dbms_sqltune.extract_bind(bind_data, 3).value_string || '-' ||
       dbms_sqltune.extract_bind(bind_data, 4).value_string || '-' ||
       dbms_sqltune.extract_bind(bind_data, 5).value_string || '-' ||
       dbms_sqltune.extract_bind(bind_data, 6).value_string
from sys.wrh$_sqlstat
 where sql_id = '1nnrufx6sw8sd'

     基本上透過以上幾種方法足可以應對我們工作需要最佳化SQL的情況，如何做到不修改SQL最佳化執行計劃，就是我們最佳化結果中sql_profile，關於最佳化結果中建議接受sql_profile檔案怎麼處理？他是怎麼影響執行計劃的？怎麼管理？這個放在第2講和大家分享！

 DBMS_SQLTUNE是10g引入的一個新特性，它可以透過自動最佳化效能較差SQL，並給出合理的最佳化建議，可以透過OEM進行管理，其中最佳化建議中 的sql_profile檔案它是一個儲存在資料字典中的資訊集合，在SQL執行過程中自動發現並修改較差的估值，進而影響最佳化器選擇最優的執行計劃，因 此sql_profile檔案可以達到不修改應用程式的情況下修改執行計劃。 

一、執行DBMS_SQLTUNE所需要的許可權

     執行dbms_sqltune需要advisor許可權   

     grant advisor to user;

     grant select_catalog_role to user;  --透過OEM管理必不可少
     grant execute on dbms_sqltune to user;

二、sql_profile檔案的作用

     sql_profile不包含單獨的執行計劃，但是，提供以下資訊供最佳化器選擇執行計劃

     A.資料庫配置、繫結變數、最佳化統計資訊、資料集等資訊

     B.追加的統計資訊

     值得一提的是，sql_profile並不會以outline方式儲存凍結執行計劃，當表中資料增長或索引被刪除或重建時，在sql_profile不變 的情況下執行計劃也可以發生變化，資訊的儲存和與資料的分佈或者訪問路徑有關，但是，經過長時間執行以後，儲存的內容就會過時，可以透過先前同樣的SQL 最佳化語句進行最佳化，重新產生sql_profile檔案。

三、sql_profile檔案相關設定

    sql_profile的控制範圍可以透過dba_sql_profiles中的category屬性進行控制，如果資料庫sqltune_category引數值為DEFAULT，那麼此時對所有使用者會話session有效，可以透過以下語句查詢 

    show parameter sqltune_category;

    select category,name from dba_sql_profiles;

    透過修改dba_sql_profile中的category屬性來控制sql_profile對那些會話進行控制，舉個例子來說，如果將 dba_sql_profile中的category屬性改為DEV，資料庫引數sqltune_category也改為DEV，那麼只有這個DEV使用者 的會話資訊受sql_profile的影響，其它使用者不受影響，這種設定常用於開發過程中的測試，生產庫中不常見

四、sql_profile檔案的管理

    sql_profile檔案可以透過兩種方式進行管理，一種是DBMS_SQLTUNE包進行管理，另一種是透過OEM控制檯進行管理

Using Enterprise Manager

1. On the Performance page, click Top Activity.
   The Top Activity page appears.
2. Under Top SQL, click the SQL ID link of the SQL statement that is using a SQL profile.
   The SQL Details page appears.
3. Click the Plan Control tab.
   A list of SQL profiles is displayed under SQL Profiles and Outlines.
4. Select the SQL profile you want to manage.
   Do one of the following:
   
   • To enable a SQL profile that is disabled, click Disable/Enable.
   • To disable a SQL profile that is enabled, click Disable/Enable.
   • To remove a SQL profile, click Delete.
5. A confirmation page appears.
   Click Yes to continue, or No to cancel the action

Using DBMS_SQLTUNE package.

To use the SQL Profiles APIs, you need the CREATE ANY SQL_PROFILE, DROP ANY SQL_PROFILE, and ALTER ANY SQL_PROFILE system privileges.

Accepting a SQL Profile

Use the DBMS_SQLTUNE.ACCEPT_SQL_PROFILE procedure to accept a SQL Profile recommended by the SQL Tuning Advisor. This creates and stores a SQL Profile in the database.

my_sql_tuning_task is the name of the SQL tuning task.
You can view information about a SQL Profile in the DBA_SQL_PROFILES view.

Altering a SQL Profile

Alter the STATUS, NAME, DESCRIPTION, and CATEGORY attributes of  an existing SQL Profile with the ALTER_SQL_PROFILE procedure.

In this example, my_sql_profile is the name of the SQL Profile that you want to alter.
The status attribute is changed to disabled which means the SQL Profile will not be used during SQL compilation.

Dropping a SQL Profile

A SQL Profile can be dropped using the DROP_SQL_PROFILE procedure.

需要注意的是，sql_profile檔案可以使SQL中的hint失效，因此當我們在管理資料庫中研究執行計劃時，如果發現hint沒有啟作用，有可能是sql_profile檔案影響的結果！

四、檢視所有最佳化任務集

SELECT 'SELECT d.id , d.owner , d.description , d.created , d.last_modified , d.statement_count, ss.*
FROM TABLE(DBMS_SQLTUNE.select_sqlset ('''||name||''')) ss, dba_sqlset d WHERE d.name='''||name||''';'
FROM dba_sqlset d
ORDER BY d.last_modified DESC