sql tunning advisor 使用的主要步驟:

1 建立tunning task

2 執行task

3 顯示tunning 結果

4 根據建議來執行相應的調優方法

下面來按照這個順序來實施一遍:

1 建立測試表以及索引

SQL> CREATE TABLE test_sql_advisor AS SELECT OWNER,OBJECT_NAME,OBJECT_ID FROM DBA_OBJECTS;

Table created

SQL> select count(*) from test_sql_advisor;

COUNT(*)
----------
757229

2 授權 SYSDBA許可權登入

SQL> GRANT ADVISOR TO noap;

Grant succeeded
SQL> GRANT SELECT_CATALOG_ROLE TO noap;

Grant succeeded
SQL> GRANT EXECUTE ON DBMS_SQLTUNE TO noap;

Grant succeeded

3 CREATE TASK

You can create tuning tasks from the following:
- SQL statement selected by SQL identifier from the cursor cache
- SQL Tuning Set containing multiple statements
- Text of a single SQL statement
- SQL statement selected by SQL identifier from the Automatic Workload Repository.

上面翻譯可以理解為建立調優任務可以透過以下幾種方式:

   1 透過取得來自cursor cache 的sql_id來指定sql語句來建立任務
   2 sql調優的集合包括的多個語句來建立任務
   3 單一sql語句的文字來建立任務
   4 透過用awr中相應的sql_id來取得sql語句建立任務

建立任務主要用的是DBMS_SQLTUNE.CREATE_TUNING_TASK 這個函式，該函式存在重寫，下面的貼出來介面
1 基於SQL文字建立任務

-------------------- create_tuning_task - sql text format ------------------

-- NAME:

-- create_tuning_task - CRATE a TUNING TASK in order to tune a single SQL

-- statement (sql text format)

--

-- DESCRIPTION

-- This function is called to prepare the tuning of a single statement

-- given its text.

-- The function mainly creates an advisor task and sets its parameters.

--

-- PARAMETERS:

-- sql_text (IN) - text of a SQL statement

-- bind_list (IN) - a set of bind values

-- user_name (IN) - the username for who the statement will be tuned

-- scope (IN) - tuning scope (limited/comprehensive)

-- time_limit (IN) - maximum duration in second for the tuning session

-- task_name (IN) - optional tuning task name

-- description (IN) - maximum of 256 SQL tuning session description

--

-- RETURNS:

-- SQL tuning task unique name

--

-- EXCEPTIONS:

-- To be done

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

FUNCTION create_tuning_task(

sql_text IN CLOB,

bind_list IN sql_binds := NULL,

user_name IN VARCHAR2 := NULL,

scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,

time_limit IN NUMBER := TIME_LIMIT_DEFAULT,

task_name IN VARCHAR2 := NULL,

description IN VARCHAR2 := NULL)

RETURN VARCHAR2;

2 基於sql_id建立任務
--------------------- create_tuning_task - sql_id format --------------------

-- NAME:

-- create_tuning_task - sql_id format

--

-- DESCRIPTION

-- This function is called to prepare the tuning of a single statement

-- from the Cursor Cache given its identifier.

-- The function mainly creates an advisor task and sets its parameters.

--

-- PARAMETERS:

-- sql_id (IN) - identifier of the statement

-- plan_hash_value (IN) - hash value of the sql execution plan

-- scope (IN) - tuning scope (limited/comprehensive)

-- time_limit (IN) - maximum tuning duration in second

-- task_name (IN) - optional tuning task name

-- description (IN) - maximum of 256 SQL tuning session description

--

-- RETURNS:

-- SQL tuning task unique name

--

-- EXCEPTIONS:

-- To be done

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

FUNCTION create_tuning_task(

sql_id IN VARCHAR2,

plan_hash_value IN NUMBER := NULL,

scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,

time_limit IN NUMBER := TIME_LIMIT_DEFAULT,

task_name IN VARCHAR2 := NULL,

description IN VARCHAR2 := NULL)

RETURN VARCHAR2;

3 基於AWR快照間隔以及相應SQL_ID建立任務

-------------- create_tuning_task - workload repository format --------------

-- NAME:

-- create_tuning_task - workload repository format

--

-- DESCRIPTION

-- This function is called to prepare the tuning of a single statement

-- from the workload repository given a range of snapshot identifiers.

-- The function mainly creates an advisor task and sets its parameters.

--

-- PARAMETERS:

-- begin_snap (IN) - begin snapshot identifier

-- end_snap (IN) - end snapshot identifier

-- sql_id (IN) - identifier of the statement

-- plan_hash_value (IN) - plan hash value

-- scope (IN) - tuning scope (limited/comprehensive)

-- time_limit (IN) - maximum duration in second for tuning

-- task_name (IN) - optional tuning task name

-- description (IN) - maximum of 256 SQL tuning session description

--

-- RETURNS:

-- SQL tuning task unique name

--

-- EXCEPTIONS:

-- To be done

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

FUNCTION create_tuning_task(

begin_snap IN NUMBER,

end_snap IN NUMBER,

sql_id IN VARCHAR2,

plan_hash_value IN NUMBER := NULL,

scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,

time_limit IN NUMBER := TIME_LIMIT_DEFAULT,

task_name IN VARCHAR2 := NULL,

description IN VARCHAR2 := NULL)

RETURN VARCHAR2;

當然還有基於sqlset，以及SQL Performance Analyzer (SPA) task 建立任務的函式這個以後再寫專題吧

sample的建立是基於sql文字來做實驗的，如下所示

DECLARE
MY_TASK_NAME VARCHAR2(30);
   MY_SQLTEXT CLOB;
BEGIN
MY_SQLTEXT :='SELECT * FROM TEST_OBJECT_TTX WHERE OBJECT_ID = :BND';
MY_TASK_NAME := DBMS_SQLTUNE.CREATE_TUNING_TASK(SQL_TEXT => MY_SQLTEXT,
                  BIND_LIST=>SQL_BINDS(ANYDATA.CONVERTNUMBER(9)),
                  USER_NAME => 'NOAP',
                  SCOPE=>'COMPREHENSIVE',
                  TIME_LIMIT => 60,
                  TASK_NAME => 'SQL_TUNING_TEST',
                  DESCRIPTION=>'TUNING TASK'
                  );

END;

建立後的狀態為INITIAL 因為還沒執行

4 EXECUTE TASK

SQL> BEGIN DBMS_SQLTUNE.EXECUTE_TUNING_TASK('SQL_TUNING_TEST'); END;
2 /

PL/SQL procedure successfully completed

SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name ='SQL_TUNING_TEST';

STATUS
-----------
COMPLETED

5 查詢建議結果

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST') FROM DUAL;

該語句是一個CLOB欄位的結果點選自行檢視根據相應的建議最佳化sql

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : SQL_TUNING_TEST
Tuning Task Owner : NOAP
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at         : 06/24/2011 12:45:20
Completed at       : 06/24/2011 12:45:22
-------------------------------------------------------------------------------
Schema Name: NOAP
SQL ID     : 5k6fk8cynf60x
SQL Text   : SELECT * FROM TEST_SQL_ADVISOR WHERE OBJECT_ID = :BND
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
尚未分析表 "NOAP"."TEST_SQL_ADVISOR"。
Recommendation
--------------
- 考慮收集此表的最佳化程式統計資訊。
    execute dbms_stats.gather_table_stats(ownname => 'NOAP', tabname =>
            'TEST_SQL_ADVISOR', estimate_percent =>
            DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE
            AUTO');
Rationale
---------
    為了選擇好的執行計劃, 最佳化程式需要此表的最新統計資訊。
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 719217330
----------------------------------------------------------------------------------------------------
| Id | Operation                   | Name                 | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |     1 |    96 |     2   (0)| 00:03:18 |
|   1 | TABLE ACCESS BY INDEX ROWID| TEST_SQL_ADVISOR     |     1 |    96 |     2   (0)| 00:03:18 |
|* 2 |   INDEX RANGE SCAN          | TEST_SQL_ADVISOR_IDX |     1 |       |     1   (0)| 00:01:39 |
----------------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=:BND)
-------------------------------------------------------------------------------

6 刪除任務的方法

BEGIN dbms_sqltune.drop_tuning_task('SQL_TUNING_TEST'); END;

7 可以用到的檢視

            SELECT * FROM USER_ADVISOR_TASKS T WHERE TASK_NAME='SQL_TUNING_TEST';
            SELECT * FROM DBA_SQLTUNE_STATISTICS
            SELECT * FROM DBA_SQLTUNE_BINDS
            SELECT * FROM DBA_SQLTUNE_PLANS WHERE TASK_ID=13009

[原創]ORACLE SQL TUNING ADVISOR 使用方法

相關文章