oracle 延迟块清楚,Oracle 11g中直接路径读取对于延迟块清除的影响

Oracle 11g中直接路径读取对于延迟块清除的影响

[日期：2012-01-17]

来源：Linux社区

作者：liu_maclean

[字体：大 中 小]

SQL> select count(*) from tv;

COUNT(*)

----------

300000

SQL> select vm.sid, vs.name, vm.value

2    from v$mystat vm, v$sysstat vs

3   where vm.statistic# = vs.statistic#

4     and vs.name in ('cleanouts only - consistent read gets',

5                     'session logical reads',

6                     'physical reads',

7                     'physical reads direct','redo size');

SID NAME                                                                  VALUE

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

25 session logical reads                                                109104

25 physical reads                                                        54546

25 physical reads direct                                                 54546

25 redo size                                                                 0

25 cleanouts only - consistent read gets                                 54546

再次查询仍采用直接路径读取,产生了相同数目的延迟块清除操作，并没有产生redo;可见direct path read的清除操作仅是针对从磁盘上读取到PGA内存中的镜像，而不对实际的块做任何修改，因而也没有任何redo;

下面我们使用普通串行全表扫描方式，设置event 10949可以避免采用直接路径读取方式.关于该事件可以参见这里.

SQL> ALTER SESSION SET EVENTS '10949 TRACE NAME CONTEXT FOREVER';

会话已更改。

SQL> select count(*) from tv;

COUNT(*)

----------

300000

SQL> select vm.sid, vs.name, vm.value

2    from v$mystat vm, v$sysstat vs

3   where vm.statistic# = vs.statistic#

4     and vs.name in ('cleanouts only - consistent read gets',

5                     'session logical reads',

6                     'physical reads',

7                     'physical reads direct','redo size');

SID NAME                                                                  VALUE

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

25 session logical reads                                                163662

25 physical reads                                                        81819

25 physical reads direct                                                 54546

25 redo size                                                           1966560

25 cleanouts only - consistent read gets                                 81819

SQL> select count(*) from tv;

COUNT(*)

----------

300000

SQL> select vm.sid, vs.name, vm.value

2    from v$mystat vm, v$sysstat vs

3   where vm.statistic# = vs.statistic#

4     and vs.name in ('cleanouts only - consistent read gets',

5                     'session logical reads',

6                     'physical reads',

7                     'physical reads direct','redo size');

SID NAME                                                                  VALUE

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

25 session logical reads                                                190947

25 physical reads                                                        95673

25 physical reads direct                                                 54546

25 redo size                                                           1966560

25 cleanouts only - consistent read gets                                 81819

第一次采用普通全表扫描方式时产生了与direct path read时相同量的延迟块清除操作，并因此产生了大量的redo,这种模式回归到了最经典的延迟块清除情景中；之后的一次读取则不再需要清除块和产生重做了，我们在读取一个“干净”的表段。

从以上测试我们可以了解到，11g中使用更为广泛的direct path read方式对有需要延迟块清除操作的段所可能产生的影响，因为实际没有一个“修改块”的操作，所以虽然延迟块清除操作在该种模式下每次都必须产生，却实际没有产生脏块，因而也就不会有“写块”的必要，故而也没有redo的产生。所产生的负载可能更多的体现在cpu time的使用上。