如何獲得正確的基數估計值

原文：http://www.oracle.com/technetwork/issue-archive/2013/13-jan/o13asktom⑴886639.html


I have a question about joining with collections and cardinality estimation. The optimizer is always estimating that 8,168 rows are coming back from my collection, and because of that, it is using inefficient plans. The estimate of 8,168 is more than two orders of magnitude more than the real cardinality. How can I solve this problem?
我有1個關于集合和基數估計。優化器針對我的集合基數估計總是8168，就由于這，它老師使用低效力計劃。8168是實際基數的2個數量級！
我該如何解決這個問題？


This is a long-running issue with pipelined functions and collections during optimization. The optimizer in general doesn’t have any information about the cardinality (the number of rows) being returned by the collection. It has to guess―and that guess is based on the block size (default statistics are block-size-driven). So, for a database with an 8 K block size, the guess is about 8,000. And because it is unlikely that your collection has about 8,000 elements (probably more like 8 or 80 in most cases), you can definitely arrive at a suboptimal plan.
這的確是個長時間提出的問題：關于管道函數和集合的優化。優化器通常對集合的基數信息毫無所知。它必須去猜！怎樣猜？是基于塊大小。
所以，對8k大小數據塊的數據庫，猜出來的基數就是8000左右！但是通常集合的基數其實不是8000，其更多是8或80。


下面，我提供4種方法來取得正確的集合基數：
1）The cardinality hint (undocumented) 基數提示（無文件證明）


2）The OPT_ESTIMATE hint (undocumented) OPT_ESTIMATE提示（無文件證明）


3）Dynamic sampling (Oracle Database 11g Release 1 and later) 動態采樣（Oracle 11g初版或更高）


4）Oracle Database’s Cardinality Feedback feature (Oracle Database 11g Release 2 and later) Oracle基數反饋特性（Oracle 11g第2版或更高）






-⑴）用于字符串分割的管道函數
SQL> create or replace type str2tblType as table of varchar2(30)
/
Type created. 


SQL> create or replace
function str2tbl( p_str in varchar2, p_delim in varchar2 default ',' )
return str2tblType
PIPELINED
as
    l_str      long default p_str || p_delim;
    l_n        number;
begin
    loop
        l_n := instr( l_str, p_delim );
        exit when (nvl(l_n,0) = 0);
        pipe row( ltrim(rtrim(substr(l_str,1,l_n⑴))) );
        l_str := substr( l_str, l_n+1 );
    end loop;
end;
/
Function created.


SQL> variable x varchar2(15)


SQL> exec :x := '1,2,3,a,b,c'


PL/SQL procedure successfully completed.


SQL> select * from table(str2tbl(:x));


COLUMN_VALUE
――――――――――――――――――――――――――――――――――――――
1
2
3
a
b
c


6 rows selected.


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


PLAN_TABLE_OUTPUT
――――――――――――――――――――――――――――――――――――――――――――――――――――――――
SQL_ID  ddk1tv9s5pzq5, child number 0
――――――――――――――――――――――――――――――――――――――――――――――――――――――――
select * from table(str2tbl(:x))


Plan hash value: 2407808827


―――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――
|Id|Operation                      |Name   |Rows|Bytes|Cost (%CPU)|Time    |
―――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――
| 0|SELECT STATEMENT               |       |    |     |  29  (100)|        |
| 1| COLLECTION ITERATOR PICKLER...|STR2TBL|8168|16336|  29    (0)|00:00:01|


--使用CARDINALITY 提示
SQL> select * from table(dbms_xplan.display_cursor);


PLAN_TABLE_OUTPUT
――――――――――――――――――――――――――――――――――――――――――――――――――――――――
SQL_ID  bd2f8rh30z3ww, child number 0
――――――――――――――――――――――――――――――――――――――――――――――――――――――――
select /*+ cardinality(sq 10) */ * from table(str2tbl(:x)) sq


Plan hash value: 2407808827


―――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――
|Id|Operation                      |Name   |Rows|Bytes|Cost (%CPU)|Time    |
―――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――
| 0|SELECT STATEMENT               |       |    |     |  29  (100)|        |
| 1| COLLECTION ITERATOR PICKLER...|STR2TBL|  10|   20|  29    (0)|00:00:01|


--這地方，我們給優化器提示1個10使其認為管道函數的結果基數為10


-⑵）使用OPT_ESTIMATE提示
--提供3個參數：對象類型；對象名；1個縮放因素，優化器將拿它認為的基數乘以這個因素
--所以，先算出這個大概的因素
SQL> select 10/8168 from dual;


    10/8168
――――――――――――――――
  .00122429


select /*+ opt_estimate(table, sq, scale_rows=0.00122429) */ * 
  from table(str2tbl(:x)) sq


Plan hash value: 2407808827


―――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――
|Id|Operation                      |Name   |Rows|Bytes|Cost (%CPU)|Time    |
―――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――
| 0|SELECT STATEMENT               |       |    |     |  29  (100)|        |
| 1|  OLLECTION ITERATOR PICKLER...|STR2TBL|  10|   20|  29    (0)|00:00:01|




-⑶）使用DYNAMIC SAMPLING 提示
select /*+ dynamic_sampling( sq, 2 ) */ * from table( str2tbl(:x,',') ) sq


Plan hash value: 2407808827


―――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――
|Id|Operation                      |Name   |Rows|Bytes|Cost (%CPU)|Time    |
―――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――
| 0|SELECT STATEMENT               |       |    |     |  11  (100)|        |
| 1| COLLECTION ITERATOR PICKLER...|STR2TBL|   6|   12|  11    (0)|00:00:01|
―――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――


Note
―――――――
dynamic sampling used for this statement (level=2)


-⑷）使用Cardinality Feedback
--這地方我們要將查詢變1下形：
with sq 
as (
select /*+ materialize */ *    
  from table( str2tbl( :x ) )
) 
select * 
  from sq


Plan hash value: 630596523


―――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――
|Id|Operation                        |Name   |Rows|Bytes|Cost (%CPU)|Time    |
―――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――
| 0|SELECT STATEMENT                 |       |    |     |  32  (100)|        |
| 1| TEMP TABLE TRANSFORMATION       |       |    |     |           |        |
| 2|  LOAD AS SELECT                 |       |    |     |           |        |
| 3|   COLLECTION ITERATOR PICKLER...|STR2TBL|8168|16336|   29   (0)|00:00:01|
| 4|  VIEW                           |       |8168| 135K|    3   (0)|00:00:01|
| 5|   TABLE ACCESS FULL             |SYS_...|8168|16336|    3   (0)|00:00:01|
―――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――


18 rows selected.


--whoop，第1次還沒生效呢，沒關系， one more try
with sq as (select /*+ materialize */ *    from table( str2tbl( :x ) )
) select * from sq


Plan hash value: 630596523


―――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――
|Id|Operation                        |Name   |Rows|Bytes|Cost (%CPU)|Time    |
―――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――
| 0|SELECT STATEMENT                         |    |     |  32  (100)|        |
| 1| TEMP TABLE TRANSFORMATION               |    |     |           |        |
| 2|  LOAD AS SELECT                         |    |     |           |        |
| 3|   COLLECTION ITERATOR PICKLER...|STR2TBL|8168|16336|  29    (0)|00:00:01|
| 4|  VIEW                                   |   6| 102 |   3    (0)|00:00:01|
| 5|   TABLE ACCESS FULL             |SYS_...|   6|   12|   3    (0)|00:00:01|
―――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――


Note
―――――――
   - cardinality feedback used for this statement


22 rows selected.




--這1次OK了
--揭曉1下基數反饋的原理，下面直接援用TOM的話：
Cardinality Feedback works by having the optimizer change its cardinality estimates after executing a query for the first time and observing that the actual cardinalities were very far off from the estimated cardinalities. That is, the optimizer starts to learn from its mistakes. If it executes a query and discovers that the real row counts are far off from the estimated counts, it will reoptimize the query, using the newly discovered values.

關于基數反饋請參考：http://www.oracle.com/technetwork/issue-archive/2010/10-sep/o50asktom⑴65477.html

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

Dylan    Presents.