【sql調優之執行計劃】estimator i

Oracle的estimator（評估器）產生3種不同型別的評估量：

Selectivity選擇性

Cardinality集勢

Cost開銷

這些評估量互相之間是關聯的，estimator的目的是評估plan的整體cost，如果統計資訊是可用的，estimator將使用統計資訊來計算評估量。

Selectivity

選擇性是指從行集中返回的行的比例，行集可以是基本表，檢視，或者是由join或者group by等操作產生的結果集。Selectivity取決於查詢謂詞(predicate)或者查詢謂詞的組合。謂詞的選擇性表明了限定謂詞後返回多少行。選擇性的取值範圍是0到1，選擇性為0意味著沒有從行集中選擇行，為1則意味著選擇了所有行。

當統計資訊可用的時候estimator使用它來評估選擇性，比如一個相等謂詞(equality predicate)ename=’Smith’，選擇性就為1/distinct(ename)，如果ename上有可用的直方圖，那麼評估器使用直方圖來計算選擇性，而不是用distinct value。直方圖記錄了列上不同值的分佈，所以將較好的評價選擇性，這個很好理解。

Cardinality

集勢表示行集的行數，同樣這裡的行集可以是基本表，檢視，或者join，group by等操作的結果集。

Base cardinality是指基本表的行數，可以通過analyze table來獲得，如果表統計資訊不可用，estimator將使用table的extents數來評估base cardinality。

Effective cardinality是指從表中選擇的行，如果基礎表上沒有謂詞，那麼它就等於表的Base cardinality

例子：

SQL> explain plan for

2 select * from dual;

已解釋。

SQL> select * from dual;

D

-

X

執行計劃

----------------------------------------------------------

Plan hash value: 272002086

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |

--------------------------------------------------------------------------

SQL> select a.operation,a.cardinality,a.cost from plan_table a;

OPERATION CARDINALITY COST

------------------------------ ----------- ----------

SELECT STATEMENT 1 2

TABLE ACCESS 1 2

再看一個例子：

SQL> explain plan for

2 select a.empno,a.ename from scott.emp a where a.deptno < 20 ;

已解釋。

SQL> select a.empno,a.ename from scott.emp a where a.deptno < 20 ;

EMPNO ENAME

---------- ----------

7782 CLARK

7839 KING

7934 MILLER

執行計劃

----------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 4 | 52 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| EMP | 4 | 52 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("A"."DEPTNO"<20)

..

SQL> select a.operation,a.cardinality,a.cost from plan_table a;

OPERATION CARDINALITY COST

------------------------------ ----------- ----------

SELECT STATEMENT 4 3

TABLE ACCESS 4 3

可以看到這裡的cardinality並不是期待的3，如果返回的行為0（比如使用<10，rownum<1等條件），cardinality也是1，就這個例子而言，使用<的條件始終比行數要多1，而=,>=,<=則與返回的行數一致。

事實上這種情況（全表掃描）的cardinality可以這樣計算：num_rows*表的選擇性=15*(1/20+1/3)=5.75

其中對於非索引欄位，,>=,<=,<>,not in等的選擇性=（1/20+1/欄位唯一值數）

而=的選擇性=（1/100+1/欄位唯一值數）

對於索引欄位的選擇性，=的選擇性是1/欄位唯一值數

而其他的則是(1/欄位唯一值數+1/記錄數)

不一一累述。

Join cardinality是join後產生的行數，是兩個行集的cardinality的乘積，然後乘以連線謂詞的選擇性的結果。看個例子

笛卡爾乘積，很顯然笛卡爾乘積沒有連線條件，選擇性為1，選擇所有join後的結果

則：

SQL> explain plan for

2 select a.empno,a.ename,a.* from scott.emp a,scott.dept b ;

已解釋。

SQL> select a.operation,a.cardinality,a.cost from plan_table a;

OPERATION CARDINALITY COST

------------------------------ ----------- ----------

SELECT STATEMENT 60 7

MERGE JOIN 60 7

INDEX 4 1

BUFFER 15 6

TABLE ACCESS 15 2

Cardinality為兩者的cardinality的乘積4*15=60

修改一下上述查詢，增加條件：

SQL> explain plan for

2 select a.empno,a.ename,b.dname from scott.emp a,scott.dept b

3 where a.deptno = b.deptno

4 and a.sal < 2000

5 ;

已解釋。

SQL> select a.operation,a.cardinality,a.cost from plan_table a;

OPERATION CARDINALITY COST

------------------------------ ----------- ----------

SELECT STATEMENT 6 4

NESTED LOOPS 6 4

TABLE ACCESS 6 3

TABLE ACCESS 1 1

INDEX 1 0

增加了a.sal<2000以後，看看這時候的cardinality是如何計算的：

SQL> select count(*) from scott.emp a ;

COUNT(*)

----------

15

SQL> select count(*) from scott.dept a ;

COUNT(*)

----------

4

SQL> select count(*) from scott.emp a where a.sal < 2000 ;

COUNT(*)

----------

9

SQL> select count(*) from scott.dept a where a.deptno in

2 (select a.deptno from scott.emp a where a.sal < 2000 )

3 ;

COUNT(*)

----------

3

看看具體的執行計劃：

SQL> select a.empno,a.ename,b.dname from scott.emp a,scott.dept b

2 where a.deptno = b.deptno

3 and a.sal < 2000

4 ;

執行計劃

----------------------------------------------------------

Plan hash value: 351108634

--------------------------------------------------------------------------------

--------

me |

--------------------------------------------------------------------------------

--------

| 0 | SELECT STATEMENT | | 6 | 156 | 4 (0)| 00

:00:01 |

| 1 | NESTED LOOPS | | 6 | 156 | 4 (0)| 00

:00:01 |

|* 2 | TABLE ACCESS FULL | EMP | 6 | 78 | 3 (0)| 00

:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00

:00:01 |

|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00

:00:01 |

--------------------------------------------------------------------------------

--------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("A"."SAL"<2000)

4 - access("A"."DEPTNO"="B"."DEPTNO")

上面這些數值按照之前描述的演算法計算，兩個行集的cardinality乘積乘以join謂詞的選擇性：

對於nested loops join,hash join ,sort merge join來說選擇性可以有這樣的計算公式：

Join Selectivity =
((num_rows(t1) - num_nulls(t1.c1)) / num_rows(t1)) *
((num_rows(t2) - num_nulls(t2.c2)) / num_rows(t2)) /
greater(num_distinct(t1.c1), num_distinct(t2.c2))

即 9*3*((15-0)/15*(4-0)/4/greatest(3,4))

而cardinality= Join Selectivity *filtered cardinality(t1) * filtered cardinality(t2)

故而可以計算出cardinality：

SQL> Select 9*3*((15-0)/15*(4-0)/4/greatest(3,4)) from dual;

9*3*((15-0)/15*(4-0)/4/GREATEST(3,4))

-------------------------------------

6.75

Distinct cardinality

是行集中某一列的distinct值

Group cardinality

是一個行集在應用了group by操作後產生的行數

9i文件中描述如下：

group cardinality lies between max ( dist. card. colx , dist. card. coly )

and min ( (dist. card. colx * dist. card. coly) ,

num rows in row set )

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-671532/，如需轉載，請註明出處，否則將追究法律責任。

【sql調優之執行計劃】estimator i

相關文章