sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle测试OPTIMIZER_DYNAMIC_SAMPLING参数对数据库影响

时间:2016-12-03 12:45   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库OPTIMIZER_DYNAMIC_SAMPLING参数测试笔记,当OPTIMIZER_DYNAMIC_SAMPLING参数值不同时对SQl查询带来不同影响测试。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 深入解析参数 OPTIMIZER_DYNAMIC_SAMPLING

下午看同事的博客提到了参数 OPTIMIZER_DYNAMIC_SAMPLING,

没有提到关于该参数的详细说明,下面我这里就在研究一下,跟大家分享。

10g 的官方文档有如下的简单描述:
If OPTIMIZER_FEATURES_ENABLE is set to 10.0.0 or higher, then 2
If OPTIMIZER_FEATURES_ENABLE is set to 9.2.0, then 1
If OPTIMIZER_FEATURES_ENABLE is set to 9.0.1 or lower, then 0

该参数的取值范围是0~10.

我们这里通过自己的实验来探究该参数的奥秘。

-- Create test table

SQL> CREATE TABLE t_stats AS SELECT * FROM dba_objects;
TABLE created.

SQL> SELECT COUNT(*) FROM t_stats;

  COUNT(*)
----------
     50936

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'ROGER', tabname=> 'T_STATS', estimate_percent=>100, degree=>2);
PL/SQL PROCEDURE successfully completed.

SQL> SET LINES 150

SQL> SELECT COUNT(*) FROM t_stats WHERE object_id+data_object_id >1000;

  COUNT(*)
----------
      3841

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'allstats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  3n8ukg0rs29p8, child NUMBER 0   SELECT COUNT(*) FROM t_stats WHERE object_id+data_object_id >1000

Plan hash VALUE: 3162492167

-----------------------------------------------
| Id  | Operation          | Name    | E-ROWS |
-----------------------------------------------
|   0 | SELECT STATEMENT   |         |        |
|   1 |  SORT AGGREGATE    |         |      1 |
|*  2 |   TABLE ACCESS FULL| T_STATS |   2547 |
-----------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - FILTER("OBJECT_ID"+"DATA_OBJECT_ID">1000)

Note
-----
   - Warning: basic plan statistics NOT available. These are ONLY collected WHEN:

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
       * hint 'gather_plan_statistics' IS used FOR the statement OR
       * parameter 'statistics_level' IS SET TO 'ALL', at SESSION OR system level

25 ROWS selected.

SQL> ALTER SESSION SET statistics_level=ALL;

SESSION altered.

SQL>  SELECT COUNT(*) FROM t_stats WHERE object_id+data_object_id >1000;

  COUNT(*)
----------
      3841

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'allstats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
SQL_ID  cb4f1rba8yn42, child NUMBER 0  SELECT COUNT(*) FROM t_stats WHERE object_id+data_object_id >1000

Plan hash VALUE: 3162492167

----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-ROWS | A-ROWS |   A-TIME   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |      1 |00:00:00.03 |     705 |
|   1 |  SORT AGGREGATE    |         |      1 |      1 |      1 |00:00:00.03 |     705 |
|*  2 |   TABLE ACCESS FULL| T_STATS |      1 |   2547 |   3841 |00:00:00.02 |     705 |
----------------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - FILTER("OBJECT_ID"+"DATA_OBJECT_ID">1000)

19 ROWS selected.

SQL> ALTER system FLUSH shared_pool;
System altered.

SQL> ALTER SESSION SET statistics_level=typical;
SESSION altered.

SQL> SELECT /*+ gather_plan_statistics */ COUNT(*) FROM t_stats WHERE object_id+data_object_id >1000;

  COUNT(*)
----------
      3841

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID  f0t9aqq1jrgsv, child NUMBER 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM t_stats WHERE
object_id+data_object_id >1000

Plan hash VALUE: 3162492167

----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-ROWS | A-ROWS |   A-TIME   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |      1 |00:00:00.01 |     705 |
|   1 |  SORT AGGREGATE    |         |      1 |      1 |      1 |00:00:00.01 |     705 |
|*  2 |   TABLE ACCESS FULL| T_STATS |      1 |   2547 |   3841 |00:00:00.02 |     705 |
----------------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - FILTER("OBJECT_ID"+"DATA_OBJECT_ID">1000)


20 ROWS selected.

---- Test optimizer_dynamic_sampling

SQL> ALTER system FLUSH shared_pool;
System altered.

SQL> ALTER SESSION SET optimizer_dynamic_sampling=2;
SESSION altered.

SQL> SELECT /*+ gather_plan_statistics */ COUNT(*) FROM t_stats WHERE object_id+data_object_id >1000;

  COUNT(*)
----------
      3841

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID  f0t9aqq1jrgsv, child NUMBER 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM t_stats WHERE
object_id+data_object_id >1000

Plan hash VALUE: 3162492167

----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-ROWS | A-ROWS |   A-TIME   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |      1 |00:00:00.01 |     705 |
|   1 |  SORT AGGREGATE    |         |      1 |      1 |      1 |00:00:00.01 |     705 |
|*  2 |   TABLE ACCESS FULL| T_STATS |      1 |   2547 |   3841 |00:00:00.02 |     705 |
----------------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - FILTER("OBJECT_ID"+"DATA_OBJECT_ID">1000)


20 ROWS selected.

SQL> ALTER system FLUSH shared_pool;
System Oracleоaltered.

SQL> ALTER SESSION SET optimizer_dynamic_sampling=3;
SESSION altered.

SQL> SELECT /*+ gather_plan_statistics */ COUNT(*) FROM t_stats WHERE object_id+data_object_id >1000;

  COUNT(*)
----------
      3841

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID  f0t9aqq1jrgsv, child NUMBER 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM t_stats WHERE
object_id+data_object_id >1000

Plan hash VALUE: 3162492167

----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-ROWS | A-ROWS |   A-TIME   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |      1 |00:00:00.01 |     705 |
|   1 |  SORT AGGREGATE    |         |      1 |      1 |      1 |00:00:00.01 |     705 |
|*  2 |   TABLE ACCESS FULL| T_STATS |      1 |   1490 |   3841 |00:00:00.02 |     705 |
----------------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - FILTER("OBJECT_ID"+"DATA_OBJECT_ID">1000)

Note
-----
   - dynamic sampling used FOR this statement


24 ROWS selected.

SQL> ALTER system FLUSH shared_pool;
System altered.

SQL>  ALTER SESSION SET optimizer_dynamic_sampling=4;
SESSION altered.

SQL>  SELECT /*+ gather_plan_statistics */ COUNT(*) FROM t_stats WHERE object_id+data_object_id >1000;

  COUNT(*)
----------
      3841

SQL>  SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'allstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID  885p45bwprw2y, child NUMBER 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM t_stats WHERE
object_id+data_object_id >1000

Plan hash VALUE: 3162492167

----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-ROWS | A-ROWS |   A-TIME   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |      1 |00:00:00.01 |     705 |
|   1 |  SORT AGGREGATE    |         |      1 |      1 |      1 |00:00:00.01 |     705 |
|*  2 |   TABLE ACCESS FULL| T_STATS |      1 |   1490 |   3841 |00:00:00.02 |     705 |
----------------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - FILTER("OBJECT_ID"+"DATA_OBJECT_ID">1000)

Note
-----
   - dynamic sampling used FOR this statement


24 ROWS selected.

SQL> ALTER system FLUSH shared_pool;
System altered.

SQL> ALTER SESSION SET optimizer_dynamic_sampling=5;
SESSION altered.

SQL> SELECT /*+ gather_plan_statistics */ COUNT(*) FROM t_stats WHERE object_id+data_object_id >1000;

  COUNT(*)
----------
      3841

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID  f0t9aqq1jrgsv, child NUMBER 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM t_stats WHERE
object_id+data_object_id >1000

Plan hash VALUE: 3162492167

----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-ROWS | A-ROWS |   A-TIME   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |      1 |00:00:00.01 |     705 |
|   1 |  SORT AGGREGATE    |         |      1 |      1 |      1 |00:00:00.01 |     705 |
|*  2 |   TABLE ACCESS FULL| T_STATS |      1 |   3281 |   3841 |00:00:00.05 |     705 |
----------------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - FILTER("OBJECT_ID"+"DATA_OBJECT_ID">1000)

Note
-----
   - dynamic sampling used FOR this statement


24 ROWS selected.

SQL> ALTER system FLUSH shared_pool;
System altered.

SQL> ALTER SESSION SET optimizer_dynamic_sampling=6;
SESSION altered.

SQL> SELECT /*+ gather_plan_statistics */ COUNT(*) FROM t_stats WHERE object_id+data_object_id >1000;

  COUNT(*)
----------
      3841

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID  885p45bwprw2y, child NUMBER 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM t_stats WHERE
object_id+data_object_id >1000

Plan hash VALUE: 3162492167

----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-ROWS | A-ROWS |   A-TIME   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |      1 |00:00:00.01 |     705 |
|   1 |  SORT AGGREGATE    |         |      1 |      1 |      1 |00:00:00.01 |     705 |
|*  2 |   TABLE ACCESS FULL| T_STATS |      1 |   3328 |   3841 |00:00:00.04 |     705 |
----------------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - FILTER("OBJECT_ID"+"DATA_OBJECT_ID">1000)

Note
-----
   - dynamic sampling used FOR this statement


24 ROWS selected.

SQL> ALTER SESSION SET optimizer_dynamic_sampling=8;

SESSION altered.

SQL>  ALTER system FLUSH shared_pool;

System altered.

SQL> ALTER SESSION SET optimizer_dynamic_sampling=8;

SESSION altered.

SQL>  SELECT /*+ gather_plan_statistics */ COUNT(*) FROM t_stats WHERE object_id+data_object_id >1000;

  COUNT(*)
----------
      3841

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'allstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID  885p45bwprw2y, child NUMBER 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM t_stats WHERE
object_id+data_object_id >1000

Plan hash VALUE: 3162492167

----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-ROWS | A-ROWS |   A-TIME   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |      1 |00:00:00.01 |     705 |
|   1 |  SORT AGGREGATE    |         |      1 |      1 |      1 |00:00:00.01 |     705 |
|*  2 |   TABLE ACCESS FULL| T_STATS |      1 |   3841 |   3841 |00:00:00.02 |     705 |
----------------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - FILTER("OBJECT_ID"+"DATA_OBJECT_ID">1000)

Note
-----
   - dynamic sampling used FOR this statement


24 ROWS selected.

SQL> ALTER system FLUSH shared_pool;
System altered.

SQL> ALTER SESSION SET optimizer_dynamic_sampling=9;
SESSION altered.

SQL> SELECT /*+ gather_plan_statistics */ COUNT(*) FROM t_stats WHERE object_id+data_object_id >1000;

  COUNT(*)
----------
      3841

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID  f0t9aqq1jrgsv, child NUMBER 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM t_stats WHERE
object_id+data_object_id >1000

Plan hash VALUE: 3162492167

----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-ROWS | A-ROWS |   A-TIME   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |      1 |00:00:00.01 |     705 |
|   1 |  SORT AGGREGATE    |         |      1 |      1 |      1 |00:00:00.01 |     705 |
|*  2 |   TABLE ACCESS FULL| T_STATS |      1 |   3841 |   3841 |00:00:00.02 |     705 |
----------------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - FILTER("OBJECT_ID"+"DATA_OBJECT_ID">1000)

Note
-----
   - dynamic sampling used FOR this statement


24 ROWS selected.


########## 10053 trace event ##########

SQL> ALTER system FLUSH shared_pool;

System altered.

SQL> ALTER SESSION SET optimizer_dynamic_sampling=6;                    
SESSION altered.

SQL> ALTER SESSION SET events '10053 trace name context forever, level 1';
SESSION altered.

SQL> SELECT /*+ gather_plan_statistics */ COUNT(*) FROM t_stats WHERE object_id+data_object_id >1000;

  COUNT(*)
----------
      3841

SQL>  ALTER SESSION SET events '10053 trace name context off';

SESSION altered.

########## 10053  trace log ##########

****************
QUERY BLOCK TEXT
****************
select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
  fro(0): flg=0 objn=54933 hint_alias="T_STATS"@"SEL$1"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
  Using NOWORKLOAD Stats
  CPUSPEED: 2398 millions instruction/sec
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T_STATS  Alias: T_STATS
    #Rows: 50936  #Blks:  720  AvgRowLen:  93.00
***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
*** 2012-04-11 00:46:03.969
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning TRUE (level = 6).
*** 2012-04-11 00:46:03.970
** 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_STATS") FULL("T_STATS") NO_PARALLEL_INDEX("T_STATS") */ 1 AS C1, CASE WHEN "T_STATS"."OBJECT_ID"+"T_STATS"."DATA_OBJECT_ID">1000 THEN 1 ELSE 0 END AS C2 FROM "T_STATS" SAMPLE BLOCK (17.638889 , 1) SEED (1) "T_STATS") SAMPLESUB
*** 2012-04-11 00:46:03.975
** Executed dynamic sampling query:
    level : 6
    sample pct. : 17.638889                 <== (128-1)/720=0.176388889
    actual sample size : 8938
    filtered sample card. : 584
    orig. card. : 50936
    block cnt. table stat. : 720
    block cnt. for sampling: 720
    max. sample block cnt. : 128             <== 这里是最大是sample block数量,128个block。
    sample block cnt. : 127                  <== 这里我猜测是除去段头得到的,也就是说128个block里面肯定要包含段头。
    min. sel. est. : 0.05000000
** Using single table dynamic sel. est. : 0.06533900    <== 这里是什么来的呢?  584/8938=0.065339002
  Table: T_STATS  Alias: T_STATS    
    Card: Original: 50936  Rounded: 3328  Computed: 3328.11  Non Adjusted: 3328.11
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  196.76  Resp: 196.76  Degree: 0
      Cost_io: 196.00  Cost_cpu: 21936317
      Resp_io: 196.00  Resp_cpu: 21936317
  Best:: AccessPath: TableScan
         Cost: 196.76  Degree: 1  Resp: 196.76  Card: 3328.11  Bytes: 0   <== 这里card当然就是sel*table rownums了。
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:  T_STATS[T_STATS]#0
***********************
Best so far: Table#: 0  cost: 196.7622  card: 3328.1074  bytes: 23296
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations tried: 1
*********************************
Final - All Rows Plan:  Best join order: 1
  Cost: 196.7622  Degree: 1  Card: 3328.0000  Bytes: 23296
  Resc: 196.7622  Resc_io: 196.0000  Resc_cpu: 21936317
  Resp: 196.7622  Resp_io: 196.0000  Resc_cpu: 21936317
kkoipt: Query block SEL$1 (#0)
******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "ROGER"."T_STATS" "T_STATS" WHERE "T_STATS"."OBJECT_ID"+"T_STATS"."DATA_OBJECT_ID">1000
kkoqbc-subheap (delete addr=0xb72ebe3c, in-use=10232, alloc=11076)
kkoqbc-end
          : call(in-use=15100, alloc=32736), compile(in-use=32720, alloc=33876)
apadrv-end: call(in-use=15100, alloc=32736), compile(in-use=33316, alloc=33876)

sql_id=f0t9aqq1jrgsv.
Current SQL statement for this session:
select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000

============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |         |       |       |   197 |           |
| 1   |  SORT AGGREGATE     |         |     1 |     7 |       |           |
| 2   |   TABLE ACCESS FULL | T_STATS |  3328 |   23K |   197 |  00:00:03 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter("OBJECT_ID"+"DATA_OBJECT_ID">1000)

Content of other_xml column
===========================
  db_version     : 10.2.0.5
  parse_schema   : ROGER
  dynamic_sampling: yes
  plan_hash      : 3162492167
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
      OPT_PARAM('optimizer_dynamic_sampling' 6)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T_STATS"@"SEL$1")
    END_OUTLINE_DATA
  */

下面来自官方文档关于在10g中,该参数 level的详细描述:
Level 0: Do not use dynamic

Level 1: Sample all tables twing criteria are met:
      (1) there is at least
      (2) this unanalyzed tars in a subquery or non-mergeable view;
      (3) this unanalyzed ta
      (4) this unanalyzed tablocks that would be used for dynamic sampling of this table.
        The number of blocksic sampling blocks (32).

Level 2: Apply dynamic sampl
     The number of blocks saf dynamic sampling blocks.

Level 3: Apply dynamic samplteria, plus all tables for which
      standard selectivity ete that is a potential dynamic sampling predicate.
      The number of blocks s sampling blocks. For unanalyzed tables,
      the number of blocks sof dynamic sampling blocks.

Level 4: Apply dynamic samplteria, plus all tables that have
      single-table predicates thatr of blocks sampled is the default
      number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times
      the default number of dynamic sampling blocks.

Levels 5, 6, 7, 8, and 9: Apply dynamic sampling to all tables that meet the previous level criteria
      using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively.

Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.
下面是一个换算结果图:
level    Blocks
-------- --------
1        32
2        32
3        32
4        64
5        64
6        128
7        256
8        1024
9        4096
10       ALL blocks

下面我们来研究下默认的情况下sample 比例是多少呢?其实就是5%,也就是32个block。
########## 10053 trace event ON ##########

SQL> ALTER system FLUSH shared_pool;

System altered.

SQL> ALTER SESSION SET optimizer_dynamic_sampling=2;

SESSION altered.

SQL> ALTER SESSION SET events '10053 trace name context forever, level 1';

SESSION altered.

SQL> SELECT /*+ gather_plan_statistics */ COUNT(*) FROM t_stats WHERE object_id+data_object_id >1000;

  COUNT(*)
----------
      3841

SQL> ALTER SESSION SET events '10053 trace name context off';

SESSION altered.

########## 10053 trace file format ##########

****************
QUERY BLOCK TEXT
****************
select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
  fro(0): flg=0 objn=54933 hint_alias="T_STATS"@"SEL$1"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
  Using NOWORKLOAD Stats
  CPUSPEED: 2398 millions instruction/sec
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T_STATS  Alias: T_STATS
    #Rows: 50936  #Blks:  720  AvgRowLen:  93.00
***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Table: T_STATS  Alias: T_STATS    
    Card: Original: 50936  Rounded: 2547  Computed: 2546.80  Non Adjusted: 2546.80
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  196.76  Resp: 196.76  Degree: 0
      Cost_io: 196.00  Cost_cpu: 21936317
      Resp_io: 196.00  Resp_cpu: 21936317
  Best:: AccessPath: TableScan
         Cost: 196.76  Degree: 1  Resp: 196.76  Card: 2546.80  Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:  T_STATS[T_STATS]#0
***********************
Best so far: Table#: 0  cost: 196.7622  card: 2546.8000  bytes: 17829
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations tried: 1
*********************************
Final - All Rows Plan:  Best join order: 1
  Cost: 196.7622  Degree: 1  Card: 2547.0000  Bytes: 17829
  Resc: 196.7622  Resc_io: 196.0000  Resc_cpu: 21936317
  Resp: 196.7622  Resp_io: 196.0000  Resc_cpu: 21936317
kkoipt: Query block SEL$1 (#0)
******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "ROGER"."T_STATS" "T_STATS" WHERE "T_STATS"."OBJECT_ID"+"T_STATS"."DATA_OBJECT_ID">1000
kkoqbc-subheap (delete addr=0xb72ebe3c, in-use=9720, alloc=11076)
kkoqbc-end
          : call(in-use=12348, alloc=32736), compile(in-use=32484, alloc=33876)
apadrv-end: call(in-use=12348, alloc=32736), compile(in-use=33020, alloc=33876)

sql_id=f0t9aqq1jrgsv.
Current SQL statement for this session:
select /*+ gather_plan_statistics */ count(*) from t_stats where object_id+data_object_id >1000

============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |         |       |       |   197 |           |
| 1   |  SORT AGGREGATE     |         |     1 |     7 |       |           |
| 2   |   TABLE ACCESS FULL | T_STATS |  2547 |   17K |   197 |  00:00:03 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter("OBJECT_ID"+"DATA_OBJECT_ID">1000)

Content of other_xml column
===========================
  db_version     : 10.2.0.5
  parse_schema   : ROGER
  plan_hash      : 3162492167
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T_STATS"@"SEL$1")
    END_OUTLINE_DATA
  */
SQL> SELECT 50936*0.05 FROM dual;

50936*0.05
----------
    2546.8  <== 进行四舍五入以后即是我们看到的card值。

所以准确的讲,只有参数optimizer_dynamic_sampling 越大,得到的数据才是最准确的,当然这里也存在弊端,对于比较大的表来说,level越高的话,对于系统资源的消耗越大。

我们也可以发现oracle的CBO非常强悍了,在缺少统计信息的情况下,我们使用hint即可得到正常的执行计划,只是说对于默认情况下该参数level较低,数据不太准确而已。

其实对于非常小的表,默认的level应该就是准确的了。

下面我复制一下这个测试表,数据量稍微小点,用来验证我的观点:

SQL> CREATE TABLE t_stats_2 AS SELECT * FROM t_stats WHERE rownum < 2000;

TABLE created.

SQL> EXEC dbms_stats.gather_table_stats(ownname=> 'ROGER', tabname=> 'T_STATS_2', estimate_percent=>100, degree=>2);

PL/SQL PROCEDURE successfully completed.

SQL> ALTER system FLUSH shared_pool;

System altered.

SQL> SELECT /*+ gather_plan_statistics */ COUNT(*) FROM t_stats_2 WHERE object_id+data_object_id >1000;

  COUNT(*)
----------
       290

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'allstats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  8zv1brsz19pvj, child NUMBER 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM t_stats_2 WHERE
object_id+data_object_id >1000

Plan hash VALUE: 3465718877

------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-ROWS | A-ROWS |   A-TIME   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |      1 |00:00:00.01 |      28 |
|   1 |  SORT AGGREGATE    |           |      1 |      1 |      1 |00:00:00.01 |      28 |
|*  2 |   TABLE ACCESS FULL| T_STATS_2 |      1 |    290 |    290 |00:00:00.01 |      28 |
------------------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - FILTER("OBJECT_ID"+"DATA_OBJECT_ID">1000)

Note
-----
   - dynamic sampling used FOR this statement


24 ROWS selected.
得到验证了,所以这或许也就是为什么oracle将这个参数默认设置为2的原因吧(10g中).

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle测试OPTIMIZER_DYNAMIC_SAMPLING参数对数据库影响

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1360.html

Oracle研究中心

关键词:

深入研究OPTIMIZER_DYNAMIC_SAMPLING参数

Oracle OPTIMIZER_DYNAMIC_SAMPLING对SQL查询带来的影响