ORACLE查詢最佳化及gather_plan_statistics hint，oracledbmsgather
查詢最佳化手段和gather_plan_statistics hint：
在10g以後我們可以通過利用gather_plan_statistics提示來瞭解更多的SQL執行統計資訊，具體使用方法如下：
如果在statistics_level參數設定為ALL，或者執行的sql使用了gather_plan_statistics hint，則在sql執行後，會在v$sql_plan_statistics_all視圖中查到SQL的執行統計資訊，例如邏輯讀，物理讀，基數等等。這些資料對於效能診斷有著非常大的協助。同時v$sql_plan中的執行計畫，與通過EXPLAINPLAN得到的執行計畫相比，前者是oracle執行sql時真正使用的執行計畫，而後者則可能不是真正的執行計畫；同時有的時候，執行過的sql使用了綁定變數，而oracle在解析sql時通常會進行綁定變數窺探，這個時候我們不能使用EXPLAIN PLAN來得到那個sql的執行計畫，就算得到的跟那個sql的真實的執行計畫是不一樣的，所以有時我們更願意直接從v$sql_plan中得到執行計畫。
下面結合tom的文章執行個體講一下這個hint得用法：原文：http://www.oracle.com/technetwork/issue-archive/2014/14-nov/o64asktom-2298498.html
---------------------------------------------------------------------------------
一個查詢調優的例子：
SQL> create table t
as
select case when mod(rownum,200000) = 0 then 5
else mod(rownum,4)
end X,
rpad( 'x', 100, 'x' ) data
from dual
connect by level <= 1000000
/
--此處為了迷惑最佳化器而製造了分布不均的資料（skew data）
SQL> create index t_idx on t(x);
Index created.
SQL> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
SQL> select x, count(*)
from t
group by x
order by x
/
X COUNT(*)
——————————— ———————————
0 249995
1 250000
2 250000
3 250000
5 5
SQL> select /*+ gather_plan_statistics */
count(data)
from t
where x = 5;
/
--查看上面查詢執行計畫
SQL> select *
from table(
dbms_xplan.display_cursor( format=> 'allstats last' )
)
/
PLAN_TABLE_OUTPUT
——————————————————————————————————
SQL_ID cdwn5mqb0cpg1, child number 0
——————————————————————————————————
select /*+ gather_plan_statistics */
count(data)
from t
where x = 5
Plan hash value: 2966233522
———————————————————————————————————————————————————————————————————————————
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
———————————————————————————————————————————————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.08 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.08 |
|* 2 | TABLE ACCESS FULL| T | 1 | 200K| 5 |00:00:00.08 |
———————————————————————————————————————————————————————————————————————————
Predicate Information (identified by operation id):
———————————————————————————————————————————————————————————————————————————
2 - filter("X"=5)
20 rows selected.
--可以看到執行計畫走了全表掃描並且E-Rows，即預估基數為200K而A-rows，即實際返回基數為5差距相當大（a huge difference）。原因就是最佳化器對X列上分布不均的資料沒有察覺。
--此時，我們需要提供X列上的長條圖（histogram）讓最佳化器掌控全面的基數資訊。
SQL> select histogram
from user_tab_columns
where table_name = 'T'
and column_name = 'X';
HISTOGRAM
---------------
NONE
SQL> exec dbms_stats.gather_table_stats( user, 'T', no_invalidate=>false );
SQL> select histogram
from user_tab_columns
where table_name = 'T'
and column_name = 'X';
HISTOGRAM
---------------
FREQUENCY
----此處是用NO_INVALIDATE參數使之前的依賴遊標失效，保證下次運行該查詢執行硬解析（hard parse）
--再次執行前面的查詢操作，查看運行時計劃資訊
PLAN_TABLE_OUTPUT
——————————————————————————————————
SQL_ID cdwn5mqb0cpg1, child number 0
——————————————————————————————————
select /*+ gather_plan_statistics */
count(data)
from t
where x = 5
Plan hash value: 1789076273
————————————————————————————————————————————————————————————————————————
| Id | Operation | Name | Starts | E-Rows | A-Rows |
————————————————————————————————————————————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 182 | 5 |
|* 3 | INDEX RANGE SCAN | T_IDX | 1 | 182 | 5 |
————————————————————————————————————————————————————————————————————————
--------------
| A-Time |
--------------
|00:00:00.01 |
|00:00:00.01 |
|00:00:00.01 |
|00:00:00.01 |
Predicate Information (identified by operation id):
————————————————————————————————————————————————————————————————————————
3 - access("X"=5)
21 rows selected.
--此時執行計畫走的是索引範圍掃描且E-Rows已經很接近A-Rows，執行時間也大大縮減。
--這裡你可能會犯嘀咕：咋第一次收集表資訊時沒有產生長條圖？
看下tom的原話:
This change is likely due to the default value of the METHOD_OPT parameter used with DBMS_STATS. In Oracle Database 10g, the METHOD_OPT parameter defaults to a SIZE AUTO. After you ran a query, the database remembered the predicates and updated a dictionary table, SYS.COL_USAGE$. Then, the next time you ran DBMS_STATS to gather statistics on these tables, DBMS_STATS queried that table to find out what columns should have histograms collected automatically, based on past query workload. It looked at your predicates and said, "Hmm, these columns are candidates for histograms based on the queries the end users have been running."
聽起來很神奇吧？事實卻是如此！
△補充1：關於管道函數dbms_xplan.display_cursor( format=> 'allstats last' )要解釋下幾個參數的取值含義：
--- function display_cursor(sql_id varchar2 default null,
--- cursor_child_no integer default 0,
--- format varchar2 default 'TYPICAL')
---
--- - sql_id:
指定SQL_ID取值為V$SQL.SQL_ID, V$SESSION.SQL_ID,
或者V$SESSION.PREV_SQL_ID，如果不指定則預設為最後執行語句SQL_ID
--- - cursor_child_no:
指定sql遊標的子號，取值為V$SQL.CHILD_NUMBER 或者 in V$SESSION.SQL_CHILD_NUMBER,V$SESSION.PREV_CHILD_NUMBER，
此參數只有指定sql_id情況下才有效。如果不指定，則指定sql_id下所有子遊標都顯示。
--- - format:
--- 指定輸出資料行，值取自視圖：v$sql_plan_statistics_all.
---
--- IOSTATS:
假設基本計劃統計在SQL執行時已收集（使用gather_plan_statistics提示或設定statistics_level參數為ALL）
--- 此格式將展示所有遊標執行的（或僅最後執行遊標）IO統計。
--- MEMSTATS:
--- 假設PGA記憶體管理開啟（例如：pga_aggregate_target參數設定為非0值），此格式允許展示記憶體管理統計
（例如：操作執行模式，記憶體使用量，溢出到磁碟位元組數）
--- ALLSTATS: 指定'IOSTATS MEMSTATS'的快捷命令
---
--- LAST:
--- 此格式就是指定僅顯示最後執行sql的統計
---
--- Also, the following two formats are still supported for backward
--- compatibility: 另外以下2個參數為了向後相容而保留
---
--- 'RUNSTATS_TOT': Same as 'IOSTATS', i.e. displays IO statistics
--- for all executions of the specified cursor.
--- 'RUNSTATS_LAST': Same as 'IOSTATS LAST', i.e. displays the runtime
--- statistics for the last execution of the cursor.
--- 許可權要求:
必須具有：SELECT ON V$SQL_PLAN_STATISTICS_ALL,V$SQL, 和 V$SQL_PLAN許可權。
△補充2：還可以使用另一路徑查詢SQL運行時執行計畫：
HR@orcl> select /*+ gather_plan_statistics emp_plan*/count(1) from employees;
COUNT(1)
----------
107
HR@orcl> SELECT SQL_ID,CHILD_NUMBER FROM V$SQL WHERE SQL_TEXT LIKE '%emp_plan%' and SQL_TEXT NOT LIKE '%V$SQL%';
SQL_ID CHILD_NUMBER
------------- ------------
2c5q2d8489ybt 0
HR@orcl> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('2c5q2d8489ybt',0,'ALL IOSTATS LAST'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2c5q2d8489ybt, child number 0
-------------------------------------
select /*+ gather_plan_statistics emp_plan*/count(1) from employees
Plan hash value: 3580537945
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 (100)| | 1 |00:00:00.02 | 1 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.02 | 1 | 1 |
| 2 | INDEX FULL SCAN| EMP_EMAIL_UK | 1 | 107 | 1 (0)| 00:00:01 | 107 |00:00:00.02 | 1 | 1 |
----------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / EMPLOYEES@SEL$1
Column Projection Information (identified by operation id):
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
已選擇25行。
---------------------------------------
Dylan Presents.
