优化器革命之-Dynamic Sampling（三）

表上存在过时的统计信息，采用动态采样情况下，也会表现出一些异常情况：
drop table t purge;

create table t
as
select
        rownum as id
      , mod(rownum, 10) + 1 as attr1
      , rpad('x', 100) as filler
from
        dual
connect by
        level <= 10
;

begin
  dbms_stats.gather_table_stats(ownname          =>'test',
                                tabname          => 't',
                                no_invalidate    => FALSE,
                                estimate_percent => 100,
                                force            => true,
                                degree         => 5,
                                method_opt       => 'for  all  columns size 1',
                                cascade          => false);
end;
/
插入了10条记录，并对表做了统计信息收集。

insert /*+ append */ into t (id, attr1, filler)
select
        rownum as id
      , mod(rownum, 10) + 1 as attr1
      , rpad('x', 100) as filler
from
        dual
connect by
        level <= 1000000 - 10
;

commit;

往表里灌入了大量的数据，没有做统计信息的收集，也就是说，现在数据字典里的统计信息过期了。

test@DLSP>@tabstat
Please enter Name of Table Owner: test
Please enter Table Name : t

**********************************************************
Table Level
**********************************************************

Table                                  Number                        Empty    Chain Average Global         Sample Date
Name                                  of Rows          Blocks       Blocks    Count Row Len Stats            Size MM-DD-YYYY
------------------------------ -------------- --------------- ------------ -------- ------- ------ -------------- ----------
T                                          10            0,04            0        0     107 YES                10 07-17-2014


Column                             Distinct              Number       Number         Sample Date
Name                                 Values     Density Buckets        Nulls           Size MM-DD-YYYY
------------------------------ ------------ ----------- ------- ------------ -------------- ----------
ID                                       10   .10000000       1            0             10 07-17-2014
ATTR1                                    10   .10000000       1            0             10 07-17-2014
FILLER                                    1  1.00000000       1            0             10 07-17-2014

我们来看下，在表里统计信息过期的情况下，动态采样会发生哪些异常。

alter session set events '10053 trace name context forever, level 1';
select  /*+ dynamic_sampling(5) */
        count(*) as cnt
from
        t
where
        attr1  = 1
and     id > 0
;


       CNT
----------
    100000
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     1 |     6 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=5)

alter session set events '10053 trace name context off';

执行计划显示出的基数为1，而不是与100000接近的数字！！WHY？
我们来看看trace文件的输出：
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ 1 AS C1, CASE WHEN "T"."ATTR1"=1 AND "T"."ID">0 THEN 1 ELSE 0 END AS C2 FROM "TEST"."T" SAMPLE BLOCK (0.392793 , 1) SEED (1) "T") SAMPLESUB


*** 2014-07-17 12:00:59.073
** Executed dynamic sampling query:
    level : 5
    sample pct. : 0.392793
    actual sample size : 3405
    filtered sample card. : 341
    orig. card. : 10
    block cnt. table stat. : 4
    block cnt. for sampling: 16039
    max. sample block cnt. : 64
    sample block cnt. : 63
    min. sel. est. : 0.10000000
** Using single table dynamic sel. est. : 0.10014684
  Table: T  Alias: T
    Card: Original: 10.000000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00

我们发现优化器虽然采用了动态采样获得的选择率，但是却没有采用动态选择的基数，而是选用了表的统计信息里记录的基数：10（最后一行）
如何解决这个问题？我们可以通过HINT dynamic_sampling_est_cdn来解决：
alter session set events '10053 trace name context forever, level 1';
test@DLSP>   select  /*+ dynamic_sampling(5) dynamic_sampling_est_cdn(t) */
  2          count(*) as cnt
  3  from
  4          t
  5  where
  6          attr1  = 1
  7  and     id > 0
  8  ;


       CNT
----------
    100000
alter session set events '10053 trace name context off';
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     6 |  3519   (1)| 00:00:43 |
|   1 |  SORT AGGREGATE    |      |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 86814 |   508K|  3519   (1)| 00:00:43 |
---------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - filter("ATTR1"=1 AND "ID">0)


Note
-----
   - dynamic sampling used for this statement (level=5)
我们看到使用了HINT dynamic_sampling_est_cdn后，优化器已经能使用动态采样提供的基数信息。
** Generated dynamic sampling query:
    query text :
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ 1 AS C1, CASE WHEN "T"."ATTR1"=1 AND "T"."ID">0 THEN 1 ELSE 0 END AS C2 FROM "TEST"."T" SAMPLE BLOCK (0.392793 , 1) SEED (1) "T") SAMPLESUB


*** 2014-07-17 15:24:14.159
** Executed dynamic sampling query:
    level : 5
    sample pct. : 0.392793
    actual sample size : 3843
    filtered sample card. : 388
    orig. card. : 10
    block cnt. table stat. : 16039
    block cnt. for sampling: 16039
    max. sample block cnt. : 64
    sample block cnt. : 63
    min. sel. est. : 0.10000000
** Using dynamic sampling card. : 978379
** Using single table dynamic sel. est. : 0.10096279
  Table: T  Alias: T
    Card: Original: 978379.000000  Rounded: 98780  Computed: 98779.87  Non Adjusted: 98779.87
trace文件的输出Card: Original: 978379.000000也说明使用了动态采样的基数，而非表的统计信息里记录的基数10.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-1221196/，如需转载，请注明出处，否则将追究法律责任。