sample采样的一点记载

今天发现系统上线的有个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/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值