透過SQL Tuning Advisor使用SQL profile

在11GR2上SQL Tuning Advisor已經變得非常好用，我非常喜歡將一些非常複雜的SQL語句交給SQL Tuning Advisor來調優，幾乎每次都不讓我失望，通常調優結束後，SQL Tuning Advisor都會給你一些建議，例如建議你建立索引或者收集統計資訊，或者建議你接受SQL Profile並且給出了接受SQL Profile後效能將得到的提升。本節將會給出一個示例來演示如何透過SQL Tuning Advisor來使用SQL Profile。首先我們需要構建一下需要用到的測試表：

SQL>CREATE TABLE test

2 AS

3 SELECT ROWNUM id,

4 DBMS_RANDOM.STRING('A', 12) name,

5 DECODE(MOD(ROWNUM, 500), 0, 'Inactive', 'Active') status

6 FROM all_objects a,dba_objects b

7 WHERE ROWNUM <= 50000;

Table created.

SQL>create index t_ind on t(status);

Index created.

SQL>begin

2 dbms_stats.gather_table_stats(ownname =>'test',

3 tabname => 'test',

4 no_invalidate => FALSE,

5 estimate_percent => 100,

6 force => true,

7 degree => 5,

8 method_opt => 'for all columns size 1',

9 cascade => true);

10 end;

11 /

SQL>select status,count(*) from test group by status;

STATUS COUNT(*)

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

Active 49900

Inactive 100

上面的程式碼做了下面幾件事：

l 建立了一張測試表test,總記錄數50000。

l 表上的欄位status一共有2個唯一值：Active和Inactive，此欄位有資料傾斜。

l 列status上值為Active的值有49900個，佔了表裡絕大多數的記錄，為Inactive的記錄非常少，只有100個。

l status欄位上有索引，分析了表的統計資訊，但是status欄位沒有收集直方圖。

我們來對status為Inactive的值做查詢，由於status為Inactive的值非常少，因此走索引掃描效能更好，但是由於列上缺少直方圖，因此執行計劃會走全表掃描：

SQL>select count(name) from test where status='Inactive';

COUNT(NAME)

-----------

100

SQL>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID c37q7z5qjnwwf, child number 0

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

select count(name) from test where status='Inactive'

Plan hash value: 1950795681

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

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

| 0 | SELECT STATEMENT | | | | 51 (100)| |

| 1 | SORT AGGREGATE | | 1 | 21 | | |

|* 2 | TABLE ACCESS FULL| TEST | 25000 | 512K| 51 (2)| 00:00:01 |

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

上面執行計劃裡顯示的經過謂詞過濾後的全表掃描返回的基數為25000，因為缺少直方圖，因此最佳化器就簡單的透過基數=表的總記錄數/status欄位的唯一值數量=50000/2=25000來得出基數。我們來透過SQL Tuning Advisor分析一下這個SQL，看看最佳化器能不能識別到這是一個低效的執行計劃，能否給出我們一些建議：

SQL>var c varchar2(100)

SQL>exec :c := dbms_sqltune.CREATE_TUNING_TASK(SQL_ID=>'c37q7z5qjnwwf')

PL/SQL procedure successfully completed.

SQL>exec dbms_sqltune.execute_tuning_task(task_name => :c)

PL/SQL procedure successfully completed.

SQL>select dbms_sqltune.report_tuning_task(:c) from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK(:C)

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

GENERAL INFORMATION SECTION

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

Tuning Task Name : TASK_1112

Tuning Task Owner : TEST

Workload Type : Single SQL Statement

Scope : COMPREHENSIVE

Time Limit(seconds): 1800

Completion Status : COMPLETED

Started at : 08/01/2014 15:59:32

Completed at : 08/01/2014 15:59:33

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

Schema Name: TEST

SQL ID : c37q7z5qjnwwf

SQL Text : select count(name) from test where status='Inactive'

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

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: 51.46%)

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

- Consider accepting the recommended SQL Profile.

execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_1112',

task_owner => 'TEST', 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): .00212 .000221 89.57 %

CPU Time (s): .002099 .0002 90.47 %

User I/O Time (s): 0 0

Buffer Gets: 210 102 51.42 %

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: 1950795681

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

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

| 0 | SELECT STATEMENT | | 1 | 21 | 51 (2)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 21 | | |

|* 2 | TABLE ACCESS FULL| TEST | 100 | 2100 | 51 (2)| 00:00:01 |

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

2- Using SQL Profile

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

Plan hash value: 4130896540

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

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

| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 21 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 100 | 2100 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | T_IND | 100 | | 1 (0)| 00:00:01 |

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

Dbms_sqltune包被用來建立調優任務、執行調優任務、檢視調優結果，透過dbms_sqltune包的CREATE_TUNING_TASK函式來為SQL_ID為c37q7z5qjnwwf的SQL建立了一個調優任務。然後透過execute_tuning_task過程來執行這個調優任務，任務執行後，最佳化器會利用動態取樣等技術去驗證評估內容與實際內容的差異，並且根據差異去調整執行計劃。最後透過report_tuning_task來產生report檢視調優的結果。調優結果裡為我們提供了一個建議，建議我們採用一個SQL Profile，並且比對了採用SQL Profile後的效能提升，report的後面EXPLAIN PLANS SECTION部分展示了採用SQL Profile後，執行計劃變為了索引掃描，而且基數的評估非常準確，從25000已經變為了100。

n Note：當你執行SQL Tuning Advisor後，建議你接受一個SQL Profile，如果你想在接受SQL Profile前知道它到底為你提供了些什麼，可以執行以下查詢獲得：

sys@DLSP>select

2 -- b.ATTR1 -- 10g 列

3 b.ATTR5 -- 11g 列

4 from

5 wri$_adv_tasks a,

6 wri$_adv_rationale b

7 where

8 a.name = 'TASK_1112'

9 and b.task_id = a.id

10 order by

11 b.rec_id, b.id

12 ;

ATTR5

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

OPT_ESTIMATE(@"SEL$1", TABLE, "TEST"@"SEL$1", SCALE_ROWS=0.004)

OPT_ESTIMATE(@"SEL$1", INDEX_SCAN, "TEST"@"SEL$1", "T_IND", SCALE_ROWS=0.004)

OPTIMIZER_FEATURES_ENABLE(default)

wri$_adv_tasks的name欄位為任務名，在我們上面的例子裡，可以透過print c在SQLPLUS環境下獲得任務名，也可以在dbms_sqltune.report_tuning_task(:c)的輸出裡找到任務名。

我們接受這個SQL Profile來看看再次查詢是否能用到剛建立的SQL Profile：

SQL>execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_1112',-

> task_owner => 'TEST', replace => TRUE);

PL/SQL procedure successfully completed.

SQL>select count(name) from test where status='Inactive';

COUNT(NAME)

-----------

100

SQL>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID c37q7z5qjnwwf, child number 0

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

select count(name) from test where status='Inactive'

Plan hash value: 4130896540

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 21 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 100 | 2100 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | T_IND | 100 | | 1 (0)| 00:00:01 |

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

Note

-----

- SQL Profile SYS_SQLPROF_01479094feeb0003 used for this statement

接受SQL Tuning Advisor提供的SQL Profile後，執行計劃Note部分：- SQL Profile SYS_SQLPROF_01479094feeb0003 used for this statement，顯示已經使用到了SQL Profile，注意由SQL Tuning Advisor產生的SQL Profile名稱都是SYS_SQLPROF作為字首，使用到SQL Profile後執行計劃也已經從全表掃描變為了索引掃描。dbms_sqltune的accept_sql_profile過程有多個引數可用，task_name、task_owner指建立SQL調優任務的任務名和所屬使用者，引數name和DESCRIPTION指SQL Profile的名字和對SQL Profile的描述，引數CATEGORY來指定建立的SQL Profile所屬的類，預設的類為default。引數replace代表是否取代已有的SQL Profile，由於一個SQL只能有一個SQL Profile，不像Baseline，一個SQL可用有多個Baseline，因此如果一個SQL已經存在了SQL Profile，那麼重新建立時，必須指定replace引數，設定為true，引數force_match指明瞭文字標準化的方式，預設為false。一旦接受SQL Profile，就可以透過檢視dba_sql_profiles檢視來檢視SQL Profile的相關資訊。因為SQL Profile並不屬於某個使用者，因此all_sql_profiles和user_sql_profiles檢視都不可用。

如果一個SQL使用了SQL Profile，那麼這個SQL的v$sql的sql_profile欄位會顯示使用到的SQL Profile的名字。下面的查詢顯示了系統中存在的SQL Profile和當前共享池中正在使用的SQL Profile的SQL。

SQL>select name, category, status, substr(sql_text,1,25) sql_text, force_matching

2 from dba_sql_profiles

3 where sql_text like nvl('&sql_text','%')

4 and name like nvl('&name',name)

5 order by last_modified

6 ;

Enter value for sql_text:

Enter value for name:

NAME CATEGORY STATUS SQL_TEXT FORCE_

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

profile_c99yw1xkb4f1u_dwrose DEFAULT ENABLED select * from test NO

profile_bhm28h5575bjy_dwrose DEFAULT ENABLED select test2.object_name, NO

profile_51k1ug4rwah2c_dwrose DEFAULT ENABLED select distinct substr(ma NO

profile_cm6stbx539mcz_dwrose DEFAULT ENABLED select count(*) from tt NO

profile_c37q7z5qjnwwf_dwrose DEFAULT ENABLED select count(name) from t NO

SQL>select sql_id,

2 child_number cn,

3 plan_hash_value plan_hash,

4 sql_profile,

5 executions execs,

6 buffer_gets / decode(nvl(executions, 0), 0, 1, executions) avg_lio

7 from v$sql s

8 where upper(sql_text) like upper(nvl('&sql_text', sql_text))

9 and sql_text not like '%from v$sql where sql_text like nvl(%'

10 and sql_id like nvl('&sql_id', sql_id)

11 and sql_profile is not null

12 order by 1, 2, 3 ;

Enter value for sql_text:

SQL_ID CN PLAN_HASH SQL_PROFILE EXECS AVG_LIO

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

c37q7z5qjnwwf 0 4130896540 profile_c37q7z5qjnwwf_dwrose 1 108

c37q7z5qjnwwf 1 4130896540 profile_c37q7z5qjnwwf_dwrose 2 105

我們根據SQL_PROFILE的命名知道，這些SQL Profile都不是SQL Tuning Advisor建立的，是我們手工建立的，因為SQL Tuning Advisor建立的SQL Profile都是以SYS_SQLPROF作為字首的。

SQL Profile（第二篇）

透過SQL Tuning Advisor使用SQL profile

相關文章