动态采样一例

对于有复杂的过滤条件的sql, 为了在执行计划中得到正确的cardinality, 统计信息未必有帮助, 包括extended statistics. 比如下面in和like的组合条件, 或者where条件中使用了自定义的函数.

status in (‘COM’, ‘ERR’)
and v1 like ‘10%’

这时候dynamic sampling可能是唯一的选择. 下面是一个例子, 采用level为6的采样之后, cardinality更为接近真实的数据.

create table t6 as
with v1 as (
select /*+ materialize */
rownum id from dual connect by level <= 1000
)
select
    rownum                      id,
    rpad(rownum, 10, '0')       v1,
    trunc((rownum - 1)/100)     n1,
    case
        when mod(rownum,100000) = 7                 then 'ERR'
        when rownum <= 9990000                      then 'COM'
        when mod(rownum,10) =0                      then 'NEW'
        when mod(rownum,10) between 1 and 5         then 'PRP'
        when mod(rownum,10) between 6 and 8         then 'FKC'
        when mod(rownum,10) = 9                     then 'LDD'
    end status,
    rpad(rownum, 100)           padding
from v1, v1
where rownum <= 1e6;

begin
    dbms_stats.gather_table_stats(user,'T6');
end;
/

  1  select
  2      count(*)
  3  from
  4      t6
  5  where
  6      status in ('COM', 'ERR')
  7* and v1 like '10%'
sid@OFA> /

  COUNT(*)
----------
     11113


Execution Plan
----------------------------------------------------------
Plan hash value: 4096694858

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |    15 |  4983   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE            |      |     1 |    15 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| T6   |   395 |  5925 |  4983   (1)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - storage("V1" LIKE '10%' AND ("STATUS"='COM' OR "STATUS"='ERR'))
       filter("V1" LIKE '10%' AND ("STATUS"='COM' OR "STATUS"='ERR'))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      18188  consistent gets
          0  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        547  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


sid@OFA> select /*+ OPT_PARAM('OPTIMIZER_DYNAMIC_SAMPLING', 6) */
  2      count(*)
  3  from
  4      t6
  5  where
  6      status in ('COM', 'ERR')
  7  and v1 like '10%';

  COUNT(*)
----------
     11113


Execution Plan
----------------------------------------------------------
Plan hash value: 4096694858

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |    15 |  4983   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE            |      |     1 |    15 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| T6   | 16595 |   243K|  4983   (1)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - storage("V1" LIKE '10%' AND ("STATUS"='COM' OR "STATUS"='ERR'))
       filter("V1" LIKE '10%' AND ("STATUS"='COM' OR "STATUS"='ERR'))

Note
-----
   - dynamic statistics used: dynamic sampling (level=6)

发表评论

电子邮件地址不会被公开。 必填项已用*标注

您可以使用这些HTML标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>