Oracle 11g怎么收集多列統計信息

144次閱讀

共計 3188 個字符，預計需要花費 8 分鐘才能閱讀完成。

這篇文章主要介紹“Oracle 11g 怎么收集多列統計信息”，在日常操作中，相信很多人在 Oracle 11g 怎么收集多列統計信息問題上存在疑惑，丸趣 TV 小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”Oracle 11g 怎么收集多列統計信息”的疑惑有所幫助！接下來，請跟著丸趣 TV 小編一起來學習吧！

通常，當我們將 SQL 語句提交給 Oracle 數據庫時，Oracle 會選擇一種最優方式來執行，這是通過查詢優化器 Query Optimizer 來實現的。CBO（Cost-Based Optimizer）是 Oracle 默認使用的查詢優化器模式。在 CBO 中，SQL 執行計劃的生成，是以一種尋找成本（Cost）最優為目標導向的執行計劃探索過程。所謂成本（Cost）就是將 CPU 和 IO 消耗整合起來的量化指標，每一個執行計劃的成本就是經過優化器內部公式估算出的數字值。

我們在寫 SQL 語句的時候，經常會碰到 where 子句后面有多個條件的情況，也就是根據多列的條件篩選得到數據。默認情況下，oracle 會把多列的選擇率（selectivity）相乘從而得到 where 語句的選擇率，這樣有可能造成選擇率（selectivity）不準確，從而導致優化器做出錯誤的判斷。為了能夠讓優化器做出準確的判斷，從而生成準確的執行計劃，oracle 在 11g 數據庫中引入了收集多列統計信息。本文通過對測試表的多條件查詢，介紹收集多列統計信息的重要性。

1. 環境準備

我們在 Oracle 11g 中進行試驗。

此處)折疊或打開

SQL

SQL explain plan for select * from hoegh where employee_id=110 and email=\ JCHEN\

Explained.

SQL

SQL select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

——————————————————————————–

Plan hash value: 774871165

—————————————————————————

—————————————————————————

| 0 | SELECT STATEMENT | | 1 | 69 | 8 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| HOEGH | 1 | 69 | 8 (0)| 00:00:01 |

—————————————————————————

Predicate Information (identified by operation id):

—————————————————

PLAN_TABLE_OUTPUT

——————————————————————————–

1 – filter(\ EMPLOYEE_ID\ =110 AND \ EMAIL\ =\ JCHEN\)

13 rows selected.

SQL

從執行計劃可以看出返回了 1 行記錄，而事實又是什么樣的呢？我們執行一下這條 sql 語句。

點擊 (此處) 折疊或打開

SQL select count(*) from hoegh where employee_id=110 and email=\ JCHEN\

COUNT(*)

———-

16

SQL

由此看出，測試表 hoegh 符合查詢條件的數據有 16 行，而執行計劃提示的只有 1 行，出錯了。這是怎么回事呢，也就是我們在開篇提到的選擇率（selectivity）出了問題。
在這個多列條件查詢語句中，選擇率=1/107＊1/107，返回行數=1/107＊1/107*1712=16/107 1；由于表中存在符合條件的記錄，并且返回行數不可能小于 1，所以 Oracle 返回了 1。

5. 收集多列統計信息，再次查看兩個條件的 where 語句的執行計劃

點擊 (此處) 折疊或打開

SQL

SQL exec dbms_stats.gather_table_stats(\ HR\ ,\ HOEGH\ ,method_opt= \ for columns(employee_id,email)\

PL/SQL procedure successfully completed.

SQL

SQL explain plan for select * from hoegh where employee_id=110 and email=\ JCHEN\

Explained.

SQL select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

——————————————————————————–

Plan hash value: 774871165

—————————————————————————

—————————————————————————

| 0 | SELECT STATEMENT | | 16 | 1152 | 8 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| HOEGH | 16 | 1152 | 8 (0)| 00:00:01 |

—————————————————————————

Predicate Information (identified by operation id):

—————————————————

PLAN_TABLE_OUTPUT

——————————————————————————–

1 – filter(\ EMPLOYEE_ID\ =110 AND \ EMAIL\ =\ JCHEN\)

13 rows selected.

SQL

從執行計劃的結果來看，同樣的一條 sql 查詢語句，在收集多列統計信息后，Oracle 的選擇率（selectivity）由錯變對，這是由于 sql 語句中的兩個條件是有關聯的，即 employee_id 和 email 在 employees 表中都是唯一的，都可以唯一標識一行記錄；而在收集多列統計信息之前，Oracle 并不知道這兩個查詢條件有關聯，所以在計算選擇率（selectivity）時，只是簡單地采取了相乘的方法。

到此，關于“Oracle 11g 怎么收集多列統計信息”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注丸趣 TV 網站，丸趣 TV 小編會繼續努力為大家帶來更多實用的文章！

正文完