DBMS_SQLTUNE詳解(2)---sql_profile

dkey發表於2015-05-20
SQL

DBMS_SQLTUNE詳解(2)---sql_profile

---轉載http://blog.sina.com.cn/s/blog_61cd89f60102edlp.html


 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.

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 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.

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


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.

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

需要注意的是，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

五、更多相關資訊詳見：

NOTE:262687.1 - Using the DBMS_SQLTUNE package to Run the Sql Tuning Advisor
NOTE:457531.1 - How to Move SQL Profiles from One Database to Another (Including to Higher Versions)
NOTE:1253696.1 - How To Use SQL Profiles for Queries Using Different Literals
 

相關文章