溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
在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 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 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 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 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 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 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 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 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_bhm28h6575bjy_dwrose DEFAULT ENABLED select test2.object_name, NO profile_51k1ug4rwah3c_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作為前綴的。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
