收集多列統(tǒng)計信息后，Oracle的選擇率恢復(fù)正常了？

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

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

1、查看版本信息

SQL> select * from v$version;

2、創(chuàng)建基礎(chǔ)數(shù)據(jù)

在scott用戶下創(chuàng)建測試表t1，重復(fù)插入數(shù)據(jù)，數(shù)據(jù)量相當(dāng)于128個emp表(總行數(shù)1152=9*128)。

SQL> drop table t1;

Table dropped.

SQL> create table t1 as select * from emp;

Table created.

SQL> select count(*) from t1;

COUNT(*)

----------

9

SQL> insert into t1 select * from t1;

9 rows created.

SQL> /

18 rows created.

SQL> /

36 rows created.

SQL> /

72 rows created.

SQL> /

144 rows created.

SQL> /

288 rows created.

SQL> /

576 rows created.

exec dbms_stats.gather_table_stats('SCOTT','T1');

explain plan for select * from t1 where empno=7499;

select * from table(dbms_xplan.display);

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

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

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

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

explain plan for select * from t1 where empno=7499 and ename='ALLEN';

select * from table(dbms_xplan.display);

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

select count(*) from t1 where empno=7499 and ename='ALLEN';

由此看出，測試表t1符合查詢條件的數(shù)據(jù)有128行，而執(zhí)行計劃提示的只有14行，出錯了。這是怎么回事呢，也就是我們前面提到的選擇率（selectivity）出了問題。

在這個多列條件查詢語句中，選擇率＝1/9＊1/9，返回行數(shù)＝1/9＊1/9 *1152=1152/81=14.22，所以O(shè)racle返回了14行。

淮安數(shù)據(jù)恢復(fù)

exec dbms_stats.gather_table_stats('SCOTT','T1',method_opt=>'for columns(empno,ename)');

explain plan for select * from t1 where empno=7499 and ename='ALLEN';

select * from table(dbms_xplan.display);

是不是又變回了128呢...

南通數(shù)據(jù)恢復(fù)

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

看完是不是又學(xué)會了一招呢？所以要相信Oracle收集統(tǒng)計信息未必也是正確的，具體情況還是要具體分析。

后面會分享更多devops和DBA方面的內(nèi)容，感興趣的朋友可以關(guān)注一下~