淺談Oracle執行計劃

要對sql語句進行分析，首先就是要去看sql語句的執行計劃是否存在問題，oracle在10g之後，預設采用CBO基于代價的優化器，sql語句的執行計劃根據統計資訊分析來決定，如果統計資訊未收集，則采取動态采樣的方式來決定最優的執行計劃！

一: 擷取sql語句的執行計劃，在使用執行計劃前，先以sys使用者運作腳本$ORACLE_HOME/sqlplus/admin/plustrce.sql，該腳本建立了plustrace角色，并給該角色查詢v$sessstat,v$statname,v$mystat三個動态性能視圖的權限；最後将plustrace角色授予普通使用者

1：建立測試表，填充資料，建立索引

SQL> create table t as select 1 id,object_name from dba_objects;

Table created.

SQL> select count(*) from t;

COUNT(*)

----------

77262

SQL> update t set id=99 where rownum=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select id,count(*) from t group by id;

ID COUNT(*)

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

1 77261

99 1

SQL> create index i_t_id on t(id);

Index created.

2：擷取sql語句的執行計劃，‘dynamic sampling used for this statement (level=2)’表示采取級别2的動态采樣；執行計劃的步驟為靠右靠上先執行，而不是第一列的id順序，在本例中先執行縮進最靠右的I_T_ID索引範圍掃描，然後根據索引掃描出來的結果定位到T表相應行的rowid，謂詞中的"2 - access("ID"=99)"表示where後條件id=99會對id為2的INDEX RANGE SCAN造成決定行的影響，這個也很好了解，在本例中如果where語句後面為id=1，則必然選擇全表掃描才是最優的執行計劃；rows則會傳回的結果集行數，統計資訊中對應select語句主要看實體讀和一緻性讀的個數

SQL> set autot traceonly

SQL> select * from t where id=99;

Execution Plan

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

Plan hash value: 4153437776

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

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

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 79 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | I_T_ID | 1 | | 1 (0)| 00:00:01 |

Predicate Information (identified by operation id):

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

2 - access("ID"=99)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

10 recursive calls

0 db block gets

67 consistent gets

1 physical reads

0 redo size

491 bytes sent via SQL*Net to client

420 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

3：使用dbms_stats包對表進行收集統計資訊，在本例中可以看出進行分析過的表，将不會繼續使用動态采樣的方式收集資訊

SQL> exec dbms_stats.gather_table_stats('HR','T',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

| 0 | SELECT STATEMENT | | 14 | 378 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 14 | 378 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | I_T_ID | 14 | | 1 (0)| 00:00:01 |

0 recursive calls

3 consistent gets

0 physical reads

4：對表分析後的表進行update操作，在未重新進行分析前，将産生錯誤的執行計劃，重新分析後正常;因而在生産環境中，經常有DML操作的表應當根據實際情況進行分析，否則将産生類似的問題

SQL> set autot off

SQL> update t set id=99 where id=1;

77261 rows updated.

SQL> update t set id=1 where rownum=1;

1 1

99 77261

77261 rows selected.

11017 consistent gets

13840 redo size

3170997 bytes sent via SQL*Net to client

57070 bytes received via SQL*Net from client

5152 SQL*Net roundtrips to/from client

77261 rows processed

SQL> select /*+ full(t) */ * from t where id=99;

Plan hash value: 1601196873

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

| 0 | SELECT STATEMENT | | 14 | 378 | 101 (1)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T | 14 | 378 | 101 (1)| 00:00:02 |

1 - filter("ID"=99)

1 recursive calls

5477 consistent gets

2357600 bytes sent via SQL*Net to client

SQL> exec dbms_stats.gather_table_stats('HEROSTART_CN','T',CASCADE=>TRUE);

| 0 | SELECT STATEMENT | | 77241 | 2036K| 101 (1)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T | 77241 | 2036K| 101 (1)| 00:00:02 |

10845 consistent gets

77261 rows processed

5：執行計劃的另一種檢視方法，使用explain，同set autot on方式比(set autot traceonly隻産生執行計劃和統計資訊，不執行實際的sql語句，因而廣泛用于生産環境中)，不産生"Statistics"資訊

SQL> explain plan for select * from t where id=1;

Explained.

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

PLAN_TABLE_OUTPUT

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

2 - access("ID"=1)

14 rows selected.

二：直方圖(histogram），指資料在列上的值分布情況；傾斜表場合，應當做對索引列做直方圖,否則同樣會産生錯誤的執行計劃

DBMS_STATS包對表進行分析主要包含

1:表本身的分析，包括表的行數，資料塊數，每一行的長度等資訊

2:列的分析，包括列的重複數，列的空值數，列的值分布情況等

3:索引的分析，包括索引的塊數，索引的深度(blevel),索引的聚合因子等

SQL> create table t1 as select 1 id,object_name from dba_objects;

SQL> update t1 set id=99 where rownum=1;

SQL> create index i_t1_id on t1(id);

Index created.

SQL> select table_name,column_name,endpoint_number,endpoint_value from user_hist

ograms where table_name='T1';

no rows selected

SQL> exec dbms_stats.gather_table_stats('HEROSTART_CN','T1',CASCADE=>TRUE);

TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE

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

T1 ID 0 1

T1 OBJECT_NAME 0 2.4504E+35

T1 ID 1 99

T1 OBJECT_NAME 1 6.2963E+35

SQL> select count(*),id from t1 group by id;

COUNT(*) ID

77267 1

1 99

SQL> select * from t1 where id=99;

Plan hash value: 1111474805

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

| 0 | SELECT STATEMENT | | 14 | 378 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 14 | 378 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | I_T1_ID | 14 | | 1 (0)| 00:00:01 |

351 consistent gets

487 bytes sent via SQL*Net to client

1 rows processed

SQL> select * from t1 where id=1;

77267 rows selected.

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

Plan hash value: 3617692013

| 0 | SELECT STATEMENT | | 77254 | 2036K| 101 (1)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T1 | 77254 | 2036K| 101 (1)| 00:00:02 |

1 - filter("ID"=1)

8 recursive calls

5489 consistent gets

2357825 bytes sent via SQL*Net to client

57081 bytes received via SQL*Net from client

5153 SQL*Net roundtrips to/from client

3 sorts (memory)

77267 rows processed

SQL> exec dbms_stats.delete_column_stats('HR','T1','ID');

| 0 | SELECT STATEMENT | | 773 | 20871 | 77 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 773 | 20871 | 77 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | I_T1_ID | 309 | | 75 (0)| 00:00:01 |

10781 consistent gets

3171208 bytes sent via SQL*Net to client

SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE);

T1 ID 5527 99

T1 ID 5526 1

T1 OBJECT_NAME 1 6.2963E+35

三：動态采樣，動态采樣分0-10，11個級别，級别越高，采樣的結果越精确，需要消耗的資料庫成本也越高

level0:不進行動态采樣

level1:對沒有進行分析的表進行動态采樣，要求同時滿足下列4個條件；

sql語句中至少有一個未分析的表

未分析的表出現在關聯查詢或者子查詢中

未分析的表沒有索引

未分析的表占用的資料塊大于動态采樣預設的資料塊(32個)

level2:對所有未分析的表做分析，動态采樣的資料塊是預設資料塊的2倍

level3:采樣的表包含滿足level2定義的所有表，同時包括，謂詞中包含的潛在的需要動态采樣的表

level4:采樣的表滿足level3定義的所有表，同時還包括一些表，他們包含一個單表的謂詞會引用另外的2個列或者更多的列

level5,6,7,8,9:采樣的表包含滿足level4定義的所有表，同時分别使用預設資料庫的2,4,8,32,128倍的數量做動态采樣

level10:采樣的表滿足level9定義的所有表,同時對表的所有資料進行動态采樣

1：建立基表，未收集統計資訊前，user_tables視圖中的相關資訊未填充，11g版本中，user_indexes視圖中的資訊會被填充

SQL> create table t2 as select object_id,object_name from dba_objects;

SQL> create index i_t2_id on t2 (object_id);

SQL> select num_rows,avg_row_len,blocks,last_analyzed from user_tables where tab

le_name='T2';

NUM_ROWS AVG_ROW_LEN BLOCKS LAST_ANALYZED

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

SQL> select blevel,leaf_blocks,distinct_keys,num_rows,last_analyzed from user_in

dexes where table_name='T2';

BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS LAST_ANALYZED

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

1 171 77265 77265 2012-06-07:13:55:04

2：執行查詢，毫無疑問的采用動态采樣，也能擷取正确的執行計劃，‘1 - filter("OBJECT_ID">30)’表示對結果進行過濾

SQL> select * from t2 where object_id > 30;

77236 rows selected.

Plan hash value: 1513984157

| 0 | SELECT STATEMENT | | 58208 | 4490K| 105 (0)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T2 | 58208 | 4490K| 105 (0)| 00:00:02 |

1 - filter("OBJECT_ID">30)

24 recursive calls

5586 consistent gets

6 physical reads

3005346 bytes sent via SQL*Net to client

57059 bytes received via SQL*Net from client

5151 SQL*Net roundtrips to/from client

6 sorts (memory)

77236 rows processed

3：使用hint提示符，禁用動态采樣，将産生錯誤的執行計劃

SQL> select /*+ dynamic_sampling(t2 0) */ * from t2 where object_id > 30;

Plan hash value: 3661687773

| 0 | SELECT STATEMENT | | 1556 | 120K| 8 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1556 | 120K| 8 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | I_T2_ID | 280 | | 3 (0)| 00:00:01 |

2 - access("OBJECT_ID">30)

10897 consistent gets

165 physical reads

3314234 bytes sent via SQL*Net to client

3：收集統計資訊後，user_tables視圖相應的内容會被填充，禁用動态采樣也能擷取正确的執行計劃

SQL> exec dbms_stats.gather_table_stats('HR','T2',CASCADE=>TRUE);

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

77266 29 381 2012-06-07:14:05:52

1 171 77265 77265 2012-06-07:14:05:52

| 0 | SELECT STATEMENT | | 77242 | 2187K| 106 (1)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T2 | 77242 | 2187K| 106 (1)| 00:00:02 |

12 recursive calls

5516 consistent gets

5 sorts (memory)

本文以《讓oracle跑的更快》為指導，如有雷同，不勝榮幸！

本文轉自斬月部落格51CTO部落格，原文連結http://blog.51cto.com/ylw6006/892178如需轉載請自行聯系原作者

ylw6006

淺談Oracle執行計劃

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

Oracle 批量查詢傳入List 傳回List