下午看同事的博客提到了参数 OPTIMIZER_DYNAMIC_SAMPLING,
原帖地址:
http://www.muzijiang.cn/index.php/2012/04/gather_columns_group_stats/
没有提到关于该参数的详细说明，下面我这里就在研究一下，跟大家分享。
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 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中).
发表回复