Oracle11g收集多列統(tǒng)計信息詳解

前言

創(chuàng)新互聯(lián)建站是一家專注于成都做網(wǎng)站、成都網(wǎng)站建設與策劃設計,揭陽網(wǎng)站建設哪家好?創(chuàng)新互聯(lián)建站做網(wǎng)站,專注于網(wǎng)站建設十年,網(wǎng)設計領域的專業(yè)建站公司;建站業(yè)務涵蓋:揭陽等地區(qū)。揭陽做網(wǎng)站價格咨詢:18982081108

通常，當我們將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)化器內部公式估算出的數(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)計信息的重要性。

一、環(huán)境準備

我們在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用戶下創(chuàng)建測試表hoegh，重復插入數(shù)據(jù)，數(shù)據(jù)量相當于16個employees表（總行數(shù)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>

二、按照常規(guī)方法收集統(tǒng)計量信息；

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

PL/SQL procedure successfully completed.

SQL>

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

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>

從執(zhí)行計劃可以看出返回了16行記錄，結果沒有問題?？墒?，這個16是哪兒來的呢，我們先要了解選擇率（selectivity）和返回行數(shù)是如何計算的：

選擇率（selectivity）＝在本例中是 1/唯一值

返回行數(shù)＝選擇率（selectivity）＊表記錄總數(shù)

也就是說，在這個查詢語句中，選擇率＝1/107，返回行數(shù)＝1/107*1712=16

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

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>

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

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

 COUNT(*)
----------
  16

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。

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

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>

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

總結

以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作能帶來一定的幫助，如果有疑問大家可以留言交流，謝謝大家對創(chuàng)新互聯(lián)的支持。

名稱欄目：Oracle11g收集多列統(tǒng)計信息詳解
網(wǎng)頁URL：http://www.muchs.cn/article20/jsoeco.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供全網(wǎng)營銷推廣、網(wǎng)站設計公司、搜索引擎優(yōu)化、移動網(wǎng)站建設、自適應網(wǎng)站、微信小程序

廣告

聲明：本網(wǎng)站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經(jīng)允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)

h5響應式網(wǎng)站建設