Oracle 11g怎么收集多列統(tǒng)計信息

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

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

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

1.環(huán)境準備

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

此處)折疊或打開

1. SQL>
   

2. SQL> explain plan for select * from hoegh where employee_id=110 and email=\'JCHEN\';
   

3. 
   

4. Explained.
   

5. 
   

6. SQL>
   

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

8. 
   

9. PLAN_TABLE_OUTPUT
   

10. --------------------------------------------------------------------------------
    

11. Plan hash value: 774871165
    

12. 
    

13. ---------------------------------------------------------------------------
    

14. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    

15. ---------------------------------------------------------------------------
    

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

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

18. ---------------------------------------------------------------------------
    

19. 
    

20. Predicate Information (identified by operation id):
    

21. ---------------------------------------------------
    

22. 
    

23. PLAN_TABLE_OUTPUT
    

24. --------------------------------------------------------------------------------
    

25. 
    

26.    1 - filter(\"EMPLOYEE_ID\"=110 AND \"EMAIL\"=\'JCHEN\')
    

27. 
    

28. 13 rows selected.
    

29. 
    

30. SQL>

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

點擊(此處)折疊或打開

1. SQL> select count(*) from hoegh where employee_id=110 and email=\'JCHEN\';
   

2. 
   

3.   COUNT(*)
   

4. ----------
   

5.         16
   

6. 
   

7. SQL>

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

5.收集多列統(tǒng)計信息，再次查看兩個條件的where語句的執(zhí)行計劃

點擊(此處)折疊或打開

1. SQL>
   

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

3. 
   

4. PL/SQL procedure successfully completed.
   

5. 
   

6. SQL>
   

7. SQL> explain plan for select * from hoegh where employee_id=110 and email=\'JCHEN\';
   

8. 
   

9. Explained.
   

10. 
    

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

12. 
    

13. PLAN_TABLE_OUTPUT
    

14. --------------------------------------------------------------------------------
    

15. Plan hash value: 774871165
    

16. 
    

17. ---------------------------------------------------------------------------
    

18. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    

19. ---------------------------------------------------------------------------
    

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

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

22. ---------------------------------------------------------------------------
    

23. 
    

24. Predicate Information (identified by operation id):
    

25. ---------------------------------------------------
    

26. 
    

27. PLAN_TABLE_OUTPUT
    

28. --------------------------------------------------------------------------------
    

29. 
    

30.    1 - filter(\"EMPLOYEE_ID\"=110 AND \"EMAIL\"=\'JCHEN\')
    

31. 
    

32. 13 rows selected.
    

33. 
    

34. SQL>

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

到此，關于“Oracle 11g怎么收集多列統(tǒng)計信息”的學習就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續(xù)學習更多相關知識，請繼續(xù)關注億速云網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>