今天发现系统上线的有个sql语句用到了sample,也就是对表段的数据进行抽量采样。
SQL> create table t_samp01 as select * from dba_objects;
表已创建。
SQL> set autotrace traceonly
SQL> select * from t_samp01;
已选择72073行。
执行计划
----------------------------------------------------------
Plan hash value: 997462556
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 77447 | 15M| 289 (1)| 00:0
| 1 | TABLE ACCESS FULL| T_SAMP01 | 77447 | 15M| 289 (1)| 00:0
------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
328 recursive calls
0 db block gets
5873 consistent gets
1026 physical reads
0 redo size
7994136 bytes sent via SQL*Net to client
53259 bytes received via SQL*Net from client
4806 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72073 rows processed
SQL> select * from t_samp01 sample(10);
已选择7092行。
执行计划
----------------------------------------------------------
Plan hash value: 2892340515
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 774 | 165K| 287 (0)| 00
| 1 | TABLE ACCESS SAMPLE| T_SAMP01 | 774 | 165K| 287 (0)| 00
------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
1513 consistent gets
0 physical reads
0 redo size
788018 bytes sent via SQL*Net to client
5607 bytes received via SQL*Net from client
474 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7092 rows processed
SQL> select * from t_samp01 sample block(10);
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 2892340515
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 774 | 165K| 30 (0)| 00:00:01 |
| 1 | TABLE ACCESS SAMPLE| T_SAMP01 | 774 | 165K| 30 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
76 consistent gets
0 physical reads
0 redo size
1184 bytes sent via SQL*Net to client
404 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
可以看出sample的对执行计划的cost消耗并没有改变,是对采取后的数据进行采样区分的,而sample block是先对block进行采样然后检索。
SQL> select count(*) from t_samp01 sample(10);
COUNT(*)
----------
7095
SQL> select count(*) from t_samp01 sample(10);
COUNT(*)
----------
7215
SQL> select count(*) from t_samp01 sample(10) seed(1);
COUNT(*)
----------
7248
SQL> select count(*) from t_samp01 sample(10) seed(1);
COUNT(*)
----------
7248
通过第一次执行计划我们看见了row和实际返回的数据还是有较大的区别的,通过统计信息的sample返回的并不一定是正确的值,而且通过block的方式对于执行计划中只会对block进行抽样,这样还是会减少一定的IO消耗的了。注意上面写上seed后,扫描的结果不会变化。
也可以利用index来进行抽样。
SQL> explain plan for select * from t_obj01 sample(10) where owner='XIAOYU' ;
Explained
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1086466707
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1095 | 4 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| T_OBJ01 | 5 | 1095 | 4 (0)|
|* 2 | INDEX RANGE SCAN | INDEX_YY02 | 5 | | 2 (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='XIAOYU')
filter(ORA_HASH(ROWID,0,1843020923,'SYS_SAMPLE',0)<429496730)
Note
-----
- dynamic sampling used for this statement (level=2)
19 rows selected
可以看出这个抽样在index第一步就进行了抽样检索了,然后再根据index range scan来返回数据行。
SQL> explain plan for select * from t_obj01 where owner='XIAOYU' ;
Explained
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1086466707
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 45 | 9315 | 3 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| T_OBJ01 | 45 | 9315 | 3 (0)|
|* 2 | INDEX RANGE SCAN | INDEX_YY02 | 45 | | 1 (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='XIAOYU')
Note
-----
- dynamic sampling used for this statement (level=2)
18 rows selected
SQL> select * from t_obj01 sample block(10) where owner='XIAOYU' ;
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY NAMESPACE EDITION_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------------------------
XIAOYU SYS_LOB0000073644C00004$$ 73648 73648 LOB 2012/5/17 9 2012/5/18 15: 2012-05-18:15:06:19 VALID N Y N 8
XIAOYU ORD_DETAILS 73916 73916 TABLE 2012/5/21 2 2012/5/21 22: 2012-05-21:22:57:22 VALID N N N 1
XIAOYU T_OBJ01 74510 74510 TABLE 2012/6/19 1 2012/6/19 10: 2012-06-19:10:40:11 VALID N N N 1
XIAOYU TT13 74105 74105 TABLE 2012/5/31 1 2012/5/31 11: 2012-05-31:11:27:53 VALID N N N 1
XIAOYU PARTITION_TEST01 P4
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID c4xak3jnwbdbt, child number 0
-------------------------------------
select * from t_obj01 sample block(10) where owner='XIAOYU'
Plan hash value: 1086466707
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | | | 4 (100)|
|
| 1 | TABLE ACCESS BY INDEX ROWID| T_OBJ01 | 5 | 1095 | 4 (0)|
00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX_YY02 | 5 | | 2 (0)|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
00:00:01 |
--------------------------------------------------------------------------------
----------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='XIAOYU')
filter(ORA_HASH(ROWID,0,370515066,'SYS_SAMPLE_BLOCK',0)<429496730)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
已选择24行。
看出在sample block同样也是先对索引进行block的抽样然后index range scan,其实抽样都是源于执行计划来进行的,而且仅仅只能针对单表,多表抽样是无法在oracle中实现的!
[@more@]来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25362835/viewspace-1058583/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25362835/viewspace-1058583/