前言
通常，當(dāng)我們將SQL語(yǔ)句提交給Oracle數(shù)據(jù)庫(kù)時(shí)，Oracle會(huì)選擇一種最優(yōu)方式來(lái)執(zhí)行，這是通過(guò)查詢(xún)優(yōu)化器Query Optimizer來(lái)實(shí)現(xiàn)的。CBO（Cost-Based Optimizer）是Oracle默認(rèn)使用的查詢(xún)優(yōu)化器模式。在CBO中，SQL執(zhí)行計(jì)劃的生成，是以一種尋找成本（Cost）最優(yōu)為目標(biāo)導(dǎo)向的執(zhí)行計(jì)劃探索過(guò)程。所謂成本（Cost）就是將CPU和IO消耗整合起來(lái)的量化指標(biāo)，每一個(gè)執(zhí)行計(jì)劃的成本就是經(jīng)過(guò)優(yōu)化器內(nèi)部公式估算出的數(shù)字值。
我們?cè)趯?xiě)SQL語(yǔ)句的時(shí)候，經(jīng)常會(huì)碰到where子句后面有多個(gè)條件的情況，也就是根據(jù)多列的條件篩選得到數(shù)據(jù)。默認(rèn)情況下，oracle會(huì)把多列的選擇率（selectivity）相乘從而得到where語(yǔ)句的選擇率，這樣有可能造成選擇率（selectivity）不準(zhǔn)確，從而導(dǎo)致優(yōu)化器做出錯(cuò)誤的判斷。為了能夠讓優(yōu)化器做出準(zhǔn)確的判斷，從而生成準(zhǔn)確的執(zhí)行計(jì)劃，oracle在11g數(shù)據(jù)庫(kù)中引入了收集多列統(tǒng)計(jì)信息。本文通過(guò)對(duì)測(cè)試表的多條件查詢(xún)，介紹收集多列統(tǒng)計(jì)信息的重要性。
一、環(huán)境準(zhǔn)備
我們?cè)贠racle 11g中進(jìn)行試驗(yàn)。
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用戶(hù)下創(chuàng)建測(cè)試表hoegh，重復(fù)插入數(shù)據(jù)，數(shù)據(jù)量相當(dāng)于16個(gè)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)計(jì)量信息；
SQL>
SQL> exec dbms_stats.gather_table_stats(\'HR',\'HOEGH');
PL/SQL procedure successfully completed.
SQL>
三、查看執(zhí)行單個(gè)條件的where語(yǔ)句的執(zhí)行計(jì)劃
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í)行計(jì)劃可以看出返回了16行記錄，結(jié)果沒(méi)有問(wèn)題。可是，這個(gè)16是哪兒來(lái)的呢，我們先要了解選擇率（selectivity）和返回行數(shù)是如何計(jì)算的：
選擇率（selectivity）＝在本例中是 1/唯一值
返回行數(shù)＝選擇率（selectivity）＊表記錄總數(shù)
也就是說(shuō)，在這個(gè)查詢(xún)語(yǔ)句中，選擇率＝1/107，返回行數(shù)＝1/107*1712=16
四、查看執(zhí)行兩個(gè)條件的where語(yǔ)句的執(zhí)行計(jì)劃
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í)行計(jì)劃可以看出返回了1行記錄，而事實(shí)又是什么樣的呢？我們執(zhí)行一下這條sql語(yǔ)句。
SQL> select count(*) from hoegh where employee_id=110 and email=\'JCHEN';
COUNT(*)
----------
16
SQL>
由此看出，測(cè)試表hoegh符合查詢(xún)條件的數(shù)據(jù)有16行，而執(zhí)行計(jì)劃提示的只有1行，出錯(cuò)了。這是怎么回事呢，也就是我們?cè)陂_(kāi)篇提到的選擇率（selectivity）出了問(wèn)題。
在這個(gè)多列條件查詢(xún)語(yǔ)句中，選擇率＝1/107＊1/107，返回行數(shù)＝1/107＊1/107*1712=16/1071；由于表中存在符合條件的記錄，并且返回行數(shù)不可能小于1，所以O(shè)racle返回了1。
五、收集多列統(tǒng)計(jì)信息，再次查看兩個(gè)條件的where語(yǔ)句的執(zhí)行計(jì)劃
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í)行計(jì)劃的結(jié)果來(lái)看，同樣的一條sql查詢(xún)語(yǔ)句，在收集多列統(tǒng)計(jì)信息后，Oracle的選擇率（selectivity）由錯(cuò)變對(duì)，這是由于sql語(yǔ)句中的兩個(gè)條件是有關(guān)聯(lián)的，即employee_id和email在employees表中都是唯一的，都可以唯一標(biāo)識(shí)一行記錄；而在收集多列統(tǒng)計(jì)信息之前，Oracle并不知道這兩個(gè)查詢(xún)條件有關(guān)聯(lián)，所以在計(jì)算選擇率（selectivity）時(shí)，只是簡(jiǎn)單地采取了相乘的方法。
總結(jié)
以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來(lái)一定的幫助，如果有疑問(wèn)大家可以留言交流，謝謝大家對(duì)腳本之家的支持。
您可能感興趣的文章:- Oracle查詢(xún)sql錯(cuò)誤信息的控制和定位
- Oracle cloud control 12c的啟動(dòng)、關(guān)閉及獲取安裝信息的方法
- Oracle 查詢(xún)表信息獲取表字段及字段注釋
- oracle獲取當(dāng)前用戶(hù)表、字段等詳細(xì)信息SQL
- Oracle 11g自動(dòng)診斷信息庫(kù)(Automatic Diagnostic Repository，ADR)概述
- ORACLE查看當(dāng)前賬號(hào)的相關(guān)信息
