首页手记 ORACLE...

ORACLE _small_table_threshold与event 10949与direct path read测试

标签：

Oracle

下面的测试环境:OS:WIN7 DB：11.2.0.3

当表所占的BLOCK数大于_small_table_threshold时，表就认为是大表，FTS大表操作结果是放到LRU末端，只CACHE一次，对小表的操作是放到MRU的末端。FTS大表时，是通过direct path read的方式读取数据。我们可以通过配置10949事件，使用进程通过db file scattered read的方式来读取数据。

_small_table_threshold的值在数据库启动的时候自动配置成_db_block_buffer*0.02。

1，查看相当参数的值

SQL> @parameter_hide

SQL> set echo off

+------------------------------------------------------------------------+

| display hide parameter value |

+------------------------------------------------------------------------+

Enter Search Parameter (i.e. max|all) : db_block_buffer

PARAMETER SESSION_VALUE INSTANCE_VALUE DESCRIPTION

---------------------------------------- -------------------- -------------------- ------------------------------------------------------------

_db_block_buffers 63680 63680 Number of database blocks cached in memory: hidden parameter

SQL> @parameter_hide

SQL> set echo off

+------------------------------------------------------------------------+

| display hide parameter value |

+------------------------------------------------------------------------+

Enter Search Parameter (i.e. max|all) : small

PARAMETER SESSION_VALUE INSTANCE_VALUE DESCRIPTION

---------------------------------------- -------------------- -------------------- ------------------------------------------------------------

_small_table_threshold 1273 1273 lower threshold level of table size for direct reads

2，创建一个表TEST1,块的个数大小small_table_threshold

SQL> exec show_space(p_segname=>'TEST1',P_OWNER=>'SCOTT',P_TYPE=>'TABLE');

Unformatted Blocks ..................... 62

FS1 Blocks (0-25) ..................... 1

FS2 Blocks (25-50) ..................... 0

FS3 Blocks (50-75) ..................... 0

FS4 Blocks (75-100)..................... 52

Full Blocks ..................... 1,137

Total Blocks............................ 1,280

Total Bytes............................. 10,485,760

Total MBytes............................ 10

Unused Blocks........................... 0

Unused Bytes............................ 0

Last Used Ext FileId.................... 5

Last Used Ext BlockId................... 411,008

Last Used Block......................... 128

PL/SQL procedure successfully completed.

3,创建一个小表

SQL> create table scott.test2 as select * from scott.test1 where rownum<75000;

Table created.

SQL> exec show_space(p_segname=>'TEST2',P_OWNER=>'SCOTT',P_TYPE=>'TABLE');

Unformatted Blocks ..................... 0

FS1 Blocks (0-25) ..................... 0

FS2 Blocks (25-50) ..................... 0

FS3 Blocks (50-75) ..................... 0

FS4 Blocks (75-100)..................... 0

Full Blocks ..................... 1,067

Total Blocks............................ 1,152

Total Bytes............................. 9,437,184

Total MBytes............................ 9

Unused Blocks........................... 59

Unused Bytes............................ 483,328

Last Used Ext FileId.................... 5

Last Used Ext BlockId................... 413,440

Last Used Block......................... 69

PL/SQL procedure successfully completed.

4，对大表进行多次访问

我们可以看到物理读都是一样，并没有减小，说明BLOCK没有CACHE到BUFFER CACHE中

SQL> set autotrace traceonly;

SQL> select count(*) from scott.test1;

Execution Plan

----------------------------------------------------------

Plan hash value: 3896847026

--------------------------------------------------------------------

--------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 349 (1)| 00:00:05 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST1 | 66578 | 349 (1)| 00:00:05 |

--------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

0 recursive calls

1 db block gets

1200 consistent gets

1190 physical reads

0 redo size

425 bytes sent via SQL*Net to client

415 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> select count(*) from scott.test1;

Execution Plan

----------------------------------------------------------

Plan hash value: 3896847026

--------------------------------------------------------------------

--------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 349 (1)| 00:00:05 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST1 | 66578 | 349 (1)| 00:00:05 |

--------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

0 recursive calls

1 db block gets

1200 consistent gets

1190 physical reads

0 redo size

425 bytes sent via SQL*Net to client

415 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

5，下面是trace进程

查看进程是以那种方式访问数据块的

SQL> oradebug setmypid

Statement processed.

SQL> oradebug event 10046 trace name context forever,level 12;

Statement processed.

SQL> select count(*) from scott.test1;

SQL> oradebug tracefile_name

E:\APP\LUOPING\diag\rdbms\winorcl\winorcl\trace\winorcl_ora_8624.trc

PARSING IN CURSOR #262715460 len=32 dep=0 uid=0 oct=3 lid=0 tim=13695518487 hv=3636002668 ad='b88bc3dc' sqlid='gtwt4m7cbj

select count(*) from scott.test1

END OF STMT

PARSE #262715460:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3896847026,tim=13695518486

EXEC #262715460:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3896847026,tim=13695518602

WAIT #262715460: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=75700 tim=13695518629

WAIT #262715460: nam='direct path read' ela= 598 file number=5 first dba=409859 block cnt=13 obj#=75700 tim=13695519645

WAIT #262715460: nam='direct path read' ela= 367 file number=5 first dba=409889 block cnt=15 obj#=75700 tim=13695520603

WAIT #262715460: nam='direct path read' ela= 316 file number=5 first dba=409905 block cnt=15 obj#=75700 tim=13695521114

WAIT #262715460: nam='direct path read' ela= 136 file number=5 first dba=409921 block cnt=15 obj#=75700 tim=13695521445

WAIT #262715460: nam='direct path read' ela= 4731 file number=5 first dba=409937 block cnt=15 obj#=75700 tim=13695526371

WAIT #262715460: nam='direct path read' ela= 1603 file number=5 first dba=409986 block cnt=126 obj#=75700 tim=13695529850

WAIT #262715460: nam='direct path read' ela= 553 file number=5 first dba=410114 block cnt=126 obj#=75700 tim=13695531752

WAIT #262715460: nam='direct path read' ela= 734 file number=5 first dba=410242 block cnt=126 obj#=75700 tim=13695533842

WAIT #262715460: nam='direct path read' ela= 589 file number=5 first dba=410370 block cnt=126 obj#=75700 tim=13695535660

WAIT #262715460: nam='direct path read' ela= 1648 file number=5 first dba=410498 block cnt=126 obj#=75700 tim=13695538106

WAIT #262715460: nam='direct path read' ela= 374 file number=5 first dba=410626 block cnt=126 obj#=75700 tim=13695539715

WAIT #262715460: nam='direct path read' ela= 834 file number=5 first dba=410754 block cnt=126 obj#=75700 tim=13695541868

WAIT #262715460: nam='direct path read' ela= 634 file number=5 first dba=410882 block cnt=126 obj#=75700 tim=13695543782

这里可以看到是以direct path read的方式访问数据

7，刷新一下buffer_cache

SQL> alter system flush buffer_cache;

System altered.

8.访问小表

每一次访问小表有物理读，从2次开发物理读消失，说明BLOCK已经CACHE到BUFFER CACHE中了

SQL> select count(*) from scott.test2;

Execution Plan

----------------------------------------------------------

Plan hash value: 634289536

--------------------------------------------------------------------

--------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 299 (1)| 00:00:04 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST2 | 74999 | 299 (1)| 00:00:04 |

--------------------------------------------------------------------

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

1071 consistent gets

1068 physical reads

0 redo size

425 bytes sent via SQL*Net to client

415 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> select count(*) from scott.test2;

Execution Plan

----------------------------------------------------------

Plan hash value: 634289536

--------------------------------------------------------------------

--------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 299 (1)| 00:00:04 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST2 | 74999 | 299 (1)| 00:00:04 |

--------------------------------------------------------------------

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

1071 consistent gets

0 physical reads

0 redo size

425 bytes sent via SQL*Net to client

415 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

9，下面通过配置10949事件来改变块的访问方式

SQL> alter session set events '10949 trace name context forever, level 1';

Session altered.

SQL> select count(*) from scott.test1;

select count(*) from scott.test1

END OF STMT

PARSE #258512224:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3896847026,tim=13852556637

EXEC #258512224:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3896847026,tim=13852556706

WAIT #258512224: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=0 tim=13852556724

WAIT #258512224: nam='db file sequential read' ela= 551 file#=5 block#=409858 blocks=1 obj#=75700 tim=13852557314

WAIT #258512224: nam='db file scattered read' ela= 367 file#=5 block#=409859 blocks=5 obj#=75700 tim=13852557812

WAIT #258512224: nam='db file scattered read' ela= 424 file#=5 block#=409864 blocks=8 obj#=75700 tim=13852558366

WAIT #258512224: nam='db file scattered read' ela= 408 file#=5 block#=409873 blocks=7 obj#=75700 tim=13852558909

WAIT #258512224: nam='db file scattered read' ela= 521 file#=5 block#=409880 blocks=8 obj#=75700 tim=13852559557

WAIT #258512224: nam='db file scattered read' ela= 412 file#=5 block#=409889 blocks=7 obj#=75700 tim=13852560061

WAIT #258512224: nam='db file scattered read' ela= 427 file#=5 block#=409896 blocks=8 obj#=75700 tim=13852560716

WAIT #258512224: nam='db file scattered read' ela= 519 file#=5 block#=409905 blocks=7 obj#=75700 tim=13852561331

WAIT #258512224: nam='db file scattered read' ela= 528 file#=5 block#=409912 blocks=8 obj#=75700 tim=13852561948

WAIT #258512224: nam='db file scattered read' ela= 459 file#=5 block#=409921 blocks=7 obj#=75700 tim=13852562493

WAIT #258512224: nam='db file scattered read' ela= 477 file#=5 block#=409928 blocks=8 obj#=75700 tim=13852563103

WAIT #258512224: nam='db file scattered read' ela= 429 file#=5 block#=409937 blocks=7 obj#=75700 tim=13852563662

WAIT #258512224: nam='db file scattered read' ela= 470 file#=5 block#=409944 blocks=8 obj#=75700 tim=13852564261

WAIT #258512224: nam='db file scattered read' ela= 424 file#=5 block#=409953 blocks=7 obj#=75700 tim=13852564809

WAIT #258512224: nam='db file scattered read' ela= 472 file#=5 block#=409960 blocks=8 obj#=75700 tim=13852565401

WAIT #258512224: nam='db file scattered read' ela= 409 file#=5 block#=409969 blocks=7 obj#=75700 tim=13852565972

WAIT #258512224: nam='db file scattered read' ela= 426 file#=5 block#=409976 blocks=8 obj#=75700 tim=13852566498

WAIT #258512224: nam='db file scattered read' ela= 2543 file#=5 block#=409986 blocks=126 obj#=75700 tim=13852569237

WAIT #258512224: nam='db file scattered read' ela= 2434 file#=5 block#=410114 blocks=126 obj#=75700 tim=13852572799

WAIT #258512224: nam='db file scattered read' ela= 2535 file#=5 block#=410242 blocks=126 obj#=75700 tim=13852576312

WAIT #258512224: nam='db file scattered read' ela= 2641 file#=5 block#=410370 blocks=126 obj#=75700 tim=13852580912

WAIT #258512224: nam='db file sequential read' ela= 284 file#=5 block#=410881 blocks=1 obj#=75700 tim=13852582218

WAIT #258512224: nam='db file sequential read' ela= 292 file#=5 block#=409857 blocks=1 obj#=75700 tim=13852582591

WAIT #258512224: nam='db file scattered read' ela= 297 file#=5 block#=411008 blocks=2 obj#=75700 tim=13852582924

WAIT #258512224: nam='db file scattered read' ela= 2563 file#=5 block#=410498 blocks=126 obj#=75700 tim=13852585689

WAIT #258512224: nam='db file scattered read' ela= 2604 file#=5 block#=410626 blocks=126 obj#=75700 tim=13852589282

WAIT #258512224: nam='db file scattered read' ela= 2548 file#=5 block#=410754 blocks=126 obj#=75700 tim=13852592817

WAIT #258512224: nam='db file scattered read' ela= 1853 file#=5 block#=410882 blocks=78 obj#=75700 tim=13852595699

WAIT #258512224: nam='db file scattered read' ela= 1267 file#=5 block#=410960 blocks=48 obj#=75700 tim=13852597583

WAIT #258512224: nam='db file scattered read' ela= 1550 file#=5 block#=411072 blocks=64 obj#=75700 tim=13852599520

已经更改成db file scattered read了。

oracleORACLE 基础

点击查看更多内容

为 TA 点赞

若觉得本文不错，就分享一下吧！

评论

评论

共同学习，写下你的评论

评论加载中...

展开查看更多评论

作者其他优质文章

正在加载中

PIPIONE

手记
篇

粉丝

151

获赞与收藏

705

关注作者，订阅最新文章

阅读免费教程

后端通用面试教程

41个小节 32538 368

网络编程入门教程

20个小节 13472 255

Pandas 入门教程

25个小节 20119 382

推荐

评论

收藏

共同学习，写下你的评论



感谢您的支持，我会继续努力的～

扫码打赏，你说多少就多少

赞赏金额会直接到老师账户

支付方式

打开微信扫一扫，即可进行扫码打赏哦

今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与放弃机会

点击
抽奖

慕课手记新用户专享福利

恭喜你，你的运气太好了，居然抽中了 100个积分！

恭喜你，抽中了价值元的专栏！

太棒了，直接落到你账户里！

积分商城里的罗技鼠标、机械键盘、
Kindle 阅读器、小米平衡车
Apple iPad （10.2英寸）、大额优惠券
在等着你去兑换了噢

作者：

免费赠送

兑换码：1111222211 复制

优惠券可用于购买实战课、体系课
无门槛使用

先去看看，有什么好东西马上兑换我爱学习，选课去


热搜

最近搜索清空

ORACLE _small_table_threshold与event 10949与direct path read测试

阅读免费教程