Oracle10g SQL tune adviser簡單介紹

本文簡單介紹下SQL Tuning Adviser的配置使用方法和一些相關知識點，如果了解SQL Tuning Adviser詳細信息，參看Oracle聯機文檔。本文對分析結果沒有詳細分析。

一、自動SQL Tuning簡單介紹：

1、優化模式：

10G增強的優化模式有兩種：

a、Normal mode

在普通優化模式下，優化器編譯sql然后產生執行計劃。普通優化模式下優化器能夠快速的為sql語句產生可行的執行計劃。

b、Tuning mode

在tuning mode模式下，優化器將花費額外的時間檢查一個普通模式下產生的執行計劃是否可以優化。優化器的輸出結果將不僅僅是產生一個執行計劃，

而將執行一系列的動作，在該模式下優化器也許化肥幾分鐘去調整一個語句。每次一個sql語句被硬解析后將在自動調整優化上花費更多的時間和資源。

sql自動調整優化更適用于有復雜sql或者high-load sql的系統(例如addm中標記為高負載的sql就非常適合作為sql自動調整的目標)。

2、? ? ? ? SQL Tuning類型

Automatic SQL Tuning包含四種類型的分析:

a、Statistics Analysis

b、SQL Profiling

c、Access Path Analysis

d、SQL Structure Analysis

二、SQL Tuning Adviser：

1、授予用戶相應權限：

CONN sys/password AS SYSDBA

GRANT ADVISOR TO TEST;

CONN TEST/TEST

2、創建Tuning任務：

可以通過以下方式創建Tuning任務：

a、Automatic Workload Repository (AWR)

b、the cursor cache

c、SQL tuning set

d、specified manually

SET SERVEROUTPUT ON

--a、通過AWR設置Tuning任務.

SQL> conn /as sysdba

已連接。

--查看AWR的SNAPSHOT信息：

SQL> SELECT max(snap_id) FROM WRM$_SNAPSHOT;

MAX(SNAP_ID)

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

201

--查看SNAP間隔：

SQL> select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL? ?? ?? ?? ?? ?? ?RETENTION

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

+00000 01:00:00.0? ?? ?? ?? ???+00007 00:00:00.0

--我們可以手工縮短AWR采樣時間間隔(加快測試速度，本利采用手工執行創建SNAPSHOT的方法)：

begin

dbms_workload_repository.modify_snapshot_settings (

interval => 10,

retention => 10*24*60

);

end;

SQL> conn test/test

已連接。

--執行目標SQL：

SQL> select /*We will trace this sql*/ name,addr,insertdata from (select name,ad

dr,insertdata,rank() over(partition by name,addr order by insertdata desc) rn fr

om t ) where rn = 1;

NAME? ?? ? ADDR? ?? ?? ?? ?? ???INSERTDATA

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

王? ?? ?? ?上海? ?? ?? ?? ?? ???19-12-2006 10:09:33

王1? ?? ???上海? ?? ?? ?? ?? ???16-12-2006 10:11:15

王2? ?? ???上海? ?? ?? ?? ?? ???16-12-2006 10:11:15

張? ?? ?? ?北京? ?? ?? ?? ?? ???19-12-2006 10:08:42

--查找SQL_ID：

SQL> select sql_id,executions from v$sqlarea where lower(sql_text) like '%we wil

l trace this sql%';

SQL_ID? ?? ???EXECUTIONS

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

8zu31x4adn76f? ?? ?? ? 1

1k659753fzcxn? ?? ?? ? 1

SQL> /

SQL_ID? ?? ???EXECUTIONS

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

8zu31x4adn76f? ?? ?? ? 2

1k659753fzcxn? ?? ?? ? 1? ?--我們將分析該SQL

SQL> select sql_id from DBA_HIST_SQLSTAT where sql_id='1k659753fzcxn';

未選定行

--手工創建新的AWR SNAPSHOT：

SQL> execute dbms_workload_repository.create_snapshot;

PL/SQL 過程已成功完成。

SQL> select sql_id from DBA_HIST_SQLSTAT where sql_id='1k659753fzcxn';

SQL_ID

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

1k659753fzcxn

--創建Tuning task：

SQL> DECLARE

2? ?? ?? ?? ?? ?l_sql_tune_task_id??VARCHAR2(100);

3? ?? ?? ?? ? BEGIN

4? ?? ?? ?? ?? ?l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

5? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?begin_snap??=> 201,

6? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?end_snap? ? => 202,

7? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?sql_id? ?? ?=> '1k659753fzcxn',

8? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?scope? ?? ? => DBMS_SQLTUNE.scope_comprehensive,

9? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?time_limit??=> 60,

10? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?task_name? ?=> '1k659753fzcxn_awr_tuning_task',

11? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?description => 'Tuning task for statement 1k659753fzcxn in AWR.');

12? ?? ?? ?? ?? ?DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

13? ?? ?? ?? ? END;

14??/

PL/SQL 過程已成功完成。

SQL> SELECT task_name, status FROM dba_advisor_log WHERE owner = 'TEST';

TASK_NAME? ?? ?? ?? ?? ?? ?? ? STATUS

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

ADDM:1184202927_1_201? ?? ?? ? COMPLETED

ADDM:1184202927_1_202? ?? ?? ? COMPLETED

1k659753fzcxn_awr_tuning_task??INITIAL

--執行Tuning task：

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '1k659753fzcxn_awr_tuning_task');

PL/SQL 過程已成功完成。

SQL> SET LONG 999999;

SQL> SET PAGESIZE 1000

SQL> SET LINESIZE 200

--查看Tuning advice：

SQL> SELECT DBMS_SQLTUNE.report_tuning_task('1k659753fzcxn_awr_tuning_task') AS recommendations FROM dual;

RECOMMENDATIONS

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

GENERAL INFORMATION SECTION

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

Tuning Task Name? ?? ?? ?? ?? ?? ?: 1k659753fzcxn_awr_tuning_task

Tuning Task Owner? ?? ?? ?? ?? ???: TEST

Scope? ?? ?? ?? ?? ?? ?? ?? ?? ???: COMPREHENSIVE

Time Limit(seconds)? ?? ?? ?? ?? ?: 60

Completion Status? ?? ?? ?? ?? ???: COMPLETED

Started at? ?? ?? ?? ?? ?? ?? ?? ?: 07/09/2008 22:40:27

Completed at? ?? ?? ?? ?? ?? ?? ? : 07/09/2008 22:40:28

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

Schema Name: TEST

SQL ID? ???: 1k659753fzcxn

SQL Text? ?: select /*We will trace this sql*/ name,addr,insertdata from

(select name,addr,insertdata,rank() over(partition by name,addr

order by insertdata desc) rn from t ) where rn = 1

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

ADDITIONAL INFORMATION SECTION

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

-優化程序不能合并位于執行計劃的行 ID 1 處的視圖。.

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

EXPLAIN PLANS SECTION

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

1- Original

-----------

Plan hash value: 3047187157

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

-

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

|

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

-

|? ?0 | SELECT STATEMENT? ?? ?? ?|? ?? ?|? ???8 |? ?368 |? ???4??(25)| 00:00:01

|

|*??1 |??VIEW? ?? ?? ?? ?? ?? ???|? ?? ?|? ???8 |? ?368 |? ???4??(25)| 00:00:01

|

|*??2 |? ?WINDOW SORT PUSHED RANK|? ?? ?|? ???8 |? ?136 |? ???4??(25)| 00:00:01

|

|? ?3 |? ? TABLE ACCESS FULL? ???| T? ? |? ???8 |? ?136 |? ???3? ?(0)| 00:00:01

|

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

-

Predicate Information (identified by operation id):

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

1 - filter("RN"=1)

2 - filter(RANK() OVER ( PARTITION BY "NAME","ADDR" ORDER BY

INTERNAL_FUNCTION("INSERTDATA") DESC )<=1)

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

--中斷Tuning task：

EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => '1k659753fzcxn_awr_tuning_task');

--繼續Tuning task：

EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => '1k659753fzcxn_awr_tuning_task');

--取消Tuning task：

EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => '1k659753fzcxn_awr_tuning_task');

--重置Tuning task：

SQL> SELECT task_name, status FROM dba_advisor_log WHERE owner = 'TEST';

TASK_NAME? ?? ?? ?? ?? ?? ?? ? STATUS

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

ADDM:1184202927_1_201? ?? ?? ? COMPLETED

ADDM:1184202927_1_202? ?? ?? ? COMPLETED

1k659753fzcxn_awr_tuning_task??COMPLETED

SQL> EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => '1k659753fzcxn_awr_tuning_task');

PL/SQL 過程已成功完成。

SQL> SELECT task_name, status FROM dba_advisor_log WHERE owner = 'TEST';

TASK_NAME? ?? ?? ?? ?? ?? ?? ? STATUS

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

ADDM:1184202927_1_201? ?? ?? ? COMPLETED

ADDM:1184202927_1_202? ?? ?? ? COMPLETED

1k659753fzcxn_awr_tuning_task??INITIAL

--刪除Tuning task：

SQL> BEGIN

2? ? DBMS_SQLTUNE.drop_tuning_task (task_name => '1k659753fzcxn_awr_tuning_task');

3??END;

4??/

PL/SQL 過程已成功完成。

SQL> SELECT task_name, status FROM dba_advisor_log WHERE owner = 'TEST';

TASK_NAME? ?? ?? ?? ?? ?? ?? ? STATUS

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

ADDM:1184202927_1_201? ?? ?? ? COMPLETED

ADDM:1184202927_1_202? ?? ?? ? COMPLETED

--b、通過cursor cache設置Tuning任務.

--方法大致如下，這里我們就不再另外舉例了。

DECLARE

l_sql_tune_task_id??VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id? ?? ?=> '1k659753fzcxn',

scope? ?? ? => DBMS_SQLTUNE.scope_comprehensive,

time_limit??=> 60,

task_name? ?=> '1k659753fzcxn_tuning_task',

description => 'Tuning task for statement 1k659753fzcxn.');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

--c、通過SQL tuning set設置Tuning任務.

我們可以創建調整SQL的集合：

SQL> CONN /AS SYSDBA

已連接。

SQL> GRANT ADMINISTER ANY SQL TUNING SET TO TEST;

授權成功。

SQL> CONN TEST/TEST

已連接。

SQL> BEGIN

2? ? DBMS_SQLTUNE.create_sqlset (

3? ?? ?sqlset_name??=> 'test_sql_tuning_set',

4? ?? ?description??=> 'A test SQL tuning set.');

5??END;

6??/

PL/SQL 過程已成功完成。

SQL> SELECT OWNER,NAME FROM DBA_SQLSET;

OWNER? ?? ?? ?? ?? ?? ?? ?? ???NAME

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

TEST? ?? ?? ?? ?? ?? ?? ?? ?? ?test_sql_tuning_set

SQL> declare

2? ???cur dbms_sqltune.sqlset_cursor;

3??begin

4? ???open cur for select value(p) from table(dbms_sqltune.select_cursor_cache('parsing_schema_name = ''TEST'' ',null,null,null,null,1,null,'ALL')) P;

5? ???dbms_sqltune.load_sqlset(sqlset_name=>'test_sql_tuning_set',populate_cursor=>cur);

6??end;

7??/

PL/SQL 過程已成功完成。

SQL> select count(*) from table(dbms_sqltune.select_sqlset('test_sql_tuning_set'

, 'parsing_schema_name = ''TEST'' '));

COUNT(*)

----------

17

SQL> declare

2? ???my_task_name varchar2(30);

3??begin

4? ???my_task_name := dbms_sqltune.create_tuning_task(sqlset_name=>'test_sql_tuning_set',

5? ???time_limit=>600,

6? ???scope=>'COMPREHENSIVE',

7? ???task_name=>'test_tuning_task',

8? ???description=>'test tuning task');

9??end;

10??/

PL/SQL 過程已成功完成。

SQL> begin

2? ???dbms_sqltune.set_tuning_task_parameter(

3? ???task_name=> 'test_tuning_task',

4? ???parameter => 'TIME_LIMIT',

5? ???value=>800);

6??end;

7??/

PL/SQL 過程已成功完成。

SQL> exec dbms_sqltune.execute_tuning_task(task_name => 'test_tuning_task');

PL/SQL 過程已成功完成。

SQL> set long 999999

SQL> set longchunksize 1000

SQL> set linesize 200

SQL> select dbms_sqltune.report_tuning_task('test_tuning_task') from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_TUNING_TASK')

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

GENERAL INFORMATION SECTION

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

Tuning Task Name? ?? ?? ?? ?? ?? ?: test_tuning_task

Tuning Task Owner? ?? ?? ?? ?? ???: TEST

Scope? ?? ?? ?? ?? ?? ?? ?? ?? ???: COMPREHENSIVE

Time Limit(seconds)? ?? ?? ?? ?? ?: 800

Completion Status? ?? ?? ?? ?? ???: COMPLETED

Started at? ?? ?? ?? ?? ?? ?? ?? ?: 07/10/2008 12:38:55

Completed at? ?? ?? ?? ?? ?? ?? ? : 07/10/2008 12:38:59

SQL Tuning Set (STS) Name? ?? ?? ?: test_sql_tuning_set

SQL Tuning Set Owner? ?? ?? ?? ???: TEST

DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_TUNING_TASK')

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

Number of Statements in the STS? ?: 17

Number of Statements in the Report: 17

Number of Statements with Findings: 7

Number of Statistic Findings? ?? ?: 9

Number of SQL Profile Findings? ? : 2

Number of SQL Restructure Findings: 1

Number of Errors? ?? ?? ?? ?? ?? ?: 1

.........................................

.........................................

這里就不顯示分析結果了，上千行的分析結果。

和創建Tuning task類似，select_sqlset也可以從AWR中獲得sql集合，也可拷貝其他集合。這里不再詳細介紹。

--d、通過manually specified statement設置Tuning任務.

--沒有綁定變量的情況：

SQL> DECLARE

2? ? l_sql? ?? ?? ?? ?? ?VARCHAR2(500);

3? ? l_sql_tune_task_id??VARCHAR2(100);

4??BEGIN

5? ? l_sql := 'select /*We will trace this sql*/ name,addr,insertdata from

(select name,addr,insertdata,rank() over(partition by name,addr order by

insertdatadesc) rn from t ) where rn = 1';

7? ? l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

8? ?? ?? ?? ?? ?? ?? ?? ?? ? sql_text? ? => l_sql,

9? ?? ?? ?? ?? ?? ?? ?? ?? ? user_name? ?=> 'TEST',

10? ?? ?? ?? ?? ?? ?? ?? ?? ? scope? ?? ? => DBMS_SQLTUNE.scope_comprehensive,

11? ?? ?? ?? ?? ?? ?? ?? ?? ? time_limit??=> 60,

12? ?? ?? ?? ?? ?? ?? ?? ?? ? task_name? ?=> 'test_tuning_task',

13? ?? ?? ?? ?? ?? ?? ?? ?? ? description => 'Tuning task for an a simple query.');

14? ? DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

15??END;

16??/

PL/SQL 過程已成功完成。

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'test_tuning_task');

PL/SQL 過程已成功完成。

SQL> SELECT DBMS_SQLTUNE.report_tuning_task('test_tuning_task') AS recommendations FROM dual;

RECOMMENDATIONS

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

GENERAL INFORMATION SECTION

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

Tuning Task Name? ?? ?? ?? ?? ?? ?: test_tuning_task

Tuning Task Owner? ?? ?? ?? ?? ???: TEST

Scope? ?? ?? ?? ?? ?? ?? ?? ?? ???: COMPREHENSIVE

Time Limit(seconds)? ?? ?? ?? ?? ?: 60

Completion Status? ?? ?? ?? ?? ???: COMPLETED

Started at? ?? ?? ?? ?? ?? ?? ?? ?: 07/10/2008 01:17:09

Completed at? ?? ?? ?? ?? ?? ?? ? : 07/10/2008 01:17:09

Number of Statistic Findings? ?? ?: 1

RECOMMENDATIONS

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

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

Schema Name: TEST

SQL ID? ???: 3zdbsrhb1mhuq? ?--該處的sql_id顯示不正確，可能是oracle的一個 bug

SQL Text? ?: select /*We will trace this sql*/ name,addr,insertdata from

(select name,addr,insertdata,rank() over(partition by name,addr

order by insertdata desc) rn from t ) where rn = 1

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

FINDINGS SECTION (1 finding)

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

RECOMMENDATIONS

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

1- Statistics Finding

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

尚未分析表 "TEST"."

Recommendation

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

- 考慮收集此表的優

execute dbms_stats.gather_table_stats(ownname => 'TEST', tabname => 'T',

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt =>

'FOR ALL COLUMNS SIZE AUTO');

RECOMMENDATIONS

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

Rationale

---------

為了選擇好的執行計劃, 優化程序需

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

ADDITIONAL INFORMATION SECTION

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

-優化程序不能合并位于執行計劃的行 ID 1 處的視圖。.

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

EXPLAIN PLANS SECTION

RECOMMENDATIONS

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

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

1- Original

-----------

Plan hash value: 3047187157

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

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

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

|? ?0 | SELECT STATEMENT? ?? ?? ?|? ?? ?|? ???8 |? ?368 |? ???4??(25)| 00:00:01

|*??1 |??VIEW? ?? ?? ?? ?? ?? ???|? ?? ?|? ???8 |? ?368 |? ???4??(25)| 00:00:01

RECOMMENDATIONS

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

|*??2 |? ?WINDOW SORT PUSHED RANK|? ?? ?|? ???8 |? ?264 |? ???4??(25)| 00:00:01

|? ?3 |? ? TABLE ACCESS FULL? ???| T? ? |? ???8 |? ?264 |? ???3? ?(0)| 00:00:01

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

Predicate Information (identified by operation id):

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

1 - filter("RN"=1)

2 - filter(RANK() OVER ( PARTITION BY "NAME","ADDR" ORDER BY

INTERNAL_FUNCTION("INSERTDATA") DESC )<=1)

RECOMMENDATIONS

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

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

--帶有綁定變量的情況：

SQL> variable var_1 number;

SQL> variable var_2 number;

SQL> variable var_3 number;

SQL> exec :var_1 := 5;

PL/SQL 過程已成功完成。

SQL> exec :var_2 := 4;

PL/SQL 過程已成功完成。

SQL> exec :var_3 := 3;

PL/SQL 過程已成功完成。

SQL> select id,name,addr from t where id<>:var_1 and id<:var_2 and id>

ID NAME? ?? ?? ?? ?? ???ADDR

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

1 張? ?? ?? ?? ?? ?? ? 北京

2 張? ?? ?? ?? ?? ?? ? 北京

SQL> SELECT task_name, status FROM dba_advisor_log WHERE owner = 'TEST';

TASK_NAME? ?? ?? ?? ?? ?? ?? ? STATUS

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

ADDM:1184202927_1_201? ?? ?? ? COMPLETED

ADDM:1184202927_1_202? ?? ?? ? COMPLETED

test_tuning_task? ?? ?? ?? ?? ?COMPLETED

SQL> BEGIN

2? ???DBMS_SQLTUNE.drop_tuning_task (task_name => 'test_tuning_task');

3??END;

4??/

PL/SQL 過程已成功完成。

SQL>

SQL>

SQL> DECLARE

2? ? l_sql? ?? ?? ?? ?? ?VARCHAR2(500);

3? ? l_sql_tune_task_id??VARCHAR2(100);

4??BEGIN

5? ? l_sql := 'select id,name,addr from t where id<>:var_1 and id<:var_2 and i>

d<:var_3>

6

7? ? l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

8? ?? ?? ?? ?? ?? ?? ?? ?? ? sql_text? ? => l_sql,

9? ?? ?? ?? ?? ?? ?? ?? ?? ? bind_list? ?=> sql_binds(anydata.ConvertNumber(5),

anydata.ConvertNumber(4),anydata.ConvertNumber(3)),

10? ?? ?? ?? ?? ?? ?? ?? ?? ? user_name? ?=> 'TEST',

11? ?? ?? ?? ?? ?? ?? ?? ?? ? scope? ?? ? => DBMS_SQLTUNE.scope_comprehensive,

12? ?? ?? ?? ?? ?? ?? ?? ?? ? time_limit??=> 60,

13? ?? ?? ?? ?? ?? ?? ?? ?? ? task_name? ?=> 'test_tuning_task',

14? ?? ?? ?? ?? ?? ?? ?? ?? ? description => 'Tuning task for an a simple query

.');

15? ? DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

16??END;

17??/

PL/SQL 過程已成功完成。

SQL> SELECT * FROM DBA_SQLTUNE_BINDS;

TASK_ID??OBJECT_ID? ?POSITION VALUE()

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

393? ?? ?? ? 1? ?? ?? ? 1 ANYDATA()

393? ?? ?? ? 1? ?? ?? ? 2 ANYDATA()

393? ?? ?? ? 1? ?? ?? ? 3 ANYDATA()

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'test_tuning_task');

PL/SQL 過程已成功完成。

SQL> SELECT DBMS_SQLTUNE.report_tuning_task('test_tuning_task') AS recommendatio

ns FROM dual;

RECOMMENDATIONS

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

GENERAL INFORMATION SECTION

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

Tuning Task Name? ?? ?? ?? ?? ?? ?: test_tuning_task

Tuning Task Owner? ?? ?? ?? ?? ???: TEST

Scope? ?? ?? ?? ?? ?? ?? ?? ?? ???: COMPREHENSIVE

Time Limit(seconds)? ?? ?? ?? ?? ?: 60

Completion Status? ?? ?? ?? ?? ???: COMPLETED

Started at? ?? ?? ?? ?? ?? ?? ?? ?: 07/10/2008 02:04:29

Completed at? ?? ?? ?? ?? ?? ?? ? : 07/10/2008 02:04:29

Number of Statistic Findings? ?? ?: 1

RECOMMENDATIONS

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

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

Schema Name: TEST

SQL ID? ???: 15c91q9b2sxvk? ?--該處的sql_id顯示不正確，可能是oracle的一個 bug

SQL Text? ?: select id,name,addr from t where id<>:var_1 and id<:var_2 and>

id<:var_3>

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

FINDINGS SECTION (1 finding)

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

1- Statistics Finding

RECOMMENDATIONS

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

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

尚未分析表 "TEST"."

Recommendation

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

- 考慮收集此表的優

execute dbms_stats.gather_table_stats(ownname => 'TEST', tabname => 'T',

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt =>

'FOR ALL COLUMNS SIZE AUTO');

Rationale

RECOMMENDATIONS

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

---------

為了選擇好的執行計劃, 優化程序需

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

EXPLAIN PLANS SECTION

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

1- Original

-----------

Plan hash value: 1601196873

RECOMMENDATIONS

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

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

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

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

|? ?0 | SELECT STATEMENT??|? ?? ?|? ???8 |? ?296 |? ???3? ?(0)| 00:00:01 |

|*??1 |??TABLE ACCESS FULL| T? ? |? ???8 |? ?296 |? ???3? ?(0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("ID"<>:VAR_1 AND "ID"<:var_2 and>

RECOMMENDATIONS

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

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

三、SQL Profile：

由于缺少各種信息，優化器有時候會產生不正確的執行計劃，通常我們可以指定hints來干預執行計劃。

SQL AUTO TUNING通過SQL Profiling來解決類似的問題。自動調整優化器會創建SQL Profile，SQL Profile包含SQL語句的輔助統計信息。

普通優化模式下，優化器通過估算出一個集式、選擇性、cost來最后決定使用什么樣的執行計劃。SQL Profile利用存儲的額外的信息，

通過采樣或者部分執行的方式來驗證一個執行計劃是否為最優化，保存歷史運行統計信息。

如果一個tuning task accept SQL Profile，SQL Profile將被永久存儲在數據字典中。普通優化模式下，優化器在產生執行計劃的時候

將利用數據庫的統計信息結合SQL Profile的信息一起分析，最終產生最優化的執行計劃。

可以利用CATAGORY控制SQL Profile的使用權限，數據庫參數sqltune_category為默認DEFAULT。

SQL> SHOW PARAMETER SQLTUNE

NAME? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?TYPE? ?? ???VALUE

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

sqltune_category? ?? ?? ?? ?? ?? ?? ?string? ?? ?DEFAULT

我們也可以修改數據庫參數文件，指定我們自己的SQLTUNE_CATEGORY：

ALTER SYSTEM SET SQLTUNE_CATEGORY = PROD;

SQL Profiles apply to the following statement types:

SELECT statements

UPDATE statements

INSERT statements (only with a SELECT clause)

DELETE statements

CREATE TABLE statements (only with the AS SELECT clause)

MERGE statements (the update or insert operations)

SQL Profile基本操作：

SQL> conn /as sysdba

已連接。

SQL> GRANT CREATE ANY SQL_PROFILE TO TEST;

授權成功。

SQL> GRANT DROP ANY SQL PROFILE TO TEST;

授權成功。

SQL> GRANT ALTER ANY SQL PROFILE TO TEST;

授權成功。

SQL> CONN TEST/TEST

已連接。

SET SERVEROUTPUT ON

DECLARE

l_sql_tune_task_id??VARCHAR2(20);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (

task_name => 'test_tuning_task',

name? ?? ?=> 'test_profile');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

BEGIN

DBMS_SQLTUNE.alter_sql_profile (

name? ?? ?? ?? ?=> 'test_profile',

attribute_name??=> 'STATUS',

value? ?? ?? ???=> 'DISABLED');

END;

/

BEGIN

DBMS_SQLTUNE.drop_sql_profile (

name? ?=> 'test_profile',

ignore => TRUE);

END;

/

--example:

SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'test_tuning_task',object_id => 6, replace => TRUE);

SQL> select count(1) from DBA_SQL_PROFILES;

COUNT(1)

----------

1

PL/SQL 過程已成功完成。

四、幾個有用的automatic SQL tuning相關的數據字典:

DBA_ADVISOR_TASKS

DBA_ADVISOR_FINDINGS

DBA_ADVISOR_RECOMMENDATIONS

DBA_ADVISOR_RATIONALE

DBA_SQLTUNE_STATISTICS

DBA_SQLTUNE_BINDS

DBA_SQLTUNE_PLANS

DBA_SQLSET

DBA_SQLSET_BINDS

DBA_SQLSET_STATEMENTS

DBA_SQLSET_REFERENCES

DBA_SQL_PROFILES

V$SQL

V$SQLAREA

V$ACTIVE_SESSION_HISTORY

五、DBMS_SQLTUNE包的詳細方法可以參考：

http://download-west.oracle.com/ ... b10802/d_sqltun.htm