程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> 關於Oracle數據庫 >> Oracle 11g收集多列統計信息詳解

Oracle 11g收集多列統計信息詳解

編輯:關於Oracle數據庫

前言

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

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

一、環境准備

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

SQL> 
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL>

在hr用戶下創建測試表hoegh,重復插入數據,數據量相當於16個employees表(總行數1712=107*16)。

SQL> 
SQL> conn hr/hr
Connected.
SQL> 
SQL> create table hoegh as select * from employees;

Table created.

SQL> select count(*) from hoegh;

 COUNT(*)
----------
  107

SQL> 
SQL> insert into hoegh select * from hoegh;

107 rows created.

SQL> /

214 rows created.

SQL> /

428 rows created.

SQL> /

856 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from hoegh;

 COUNT(*)
----------
  1712

SQL>

二、按照常規方法收集統計量信息;

SQL> 
SQL> exec dbms_stats.gather_table_stats(\'HR\',\'HOEGH\');

PL/SQL procedure successfully completed.

SQL>

三、查看執行單個條件的where語句的執行計劃

SQL> 
SQL> explain plan for select * from hoegh where employee_id=110;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 774871165

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 1104 | 8 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| HOEGH | 16 | 1104 | 8 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

 1 - filter(\"EMPLOYEE_ID\"=110)

13 rows selected.

SQL>

從執行計劃可以看出返回了16行記錄,結果沒有問題。可是,這個16是哪兒來的呢,我們先要了解選擇率(selectivity)和返回行數是如何計算的:

選擇率(selectivity)=在本例中是 1/唯一值

返回行數=選擇率(selectivity)*表記錄總數

也就是說,在這個查詢語句中,選擇率=1/107,返回行數=1/107*1712=16

四、查看執行兩個條件的where語句的執行計劃

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

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 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。

五、收集多列統計信息,再次查看兩個條件的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

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 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)時,只是簡單地采取了相乘的方法。

總結

以上就是這篇文章的全部內容了,希望本文的內容對大家的學習或者工作能帶來一定的幫助,如果有疑問大家可以留言交流,謝謝大家對的支持。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved