QQ群里有位兄弟提出一个很有意思的问题，在一个db_cache_size为1.6G，几乎没有什么活动的数据库（版本为9.2.0.1)里面，一个简单的查询SELECT * FROM T，反复执行，这个查询的物理读始终很高。被查询的表不大，完全可以容纳在cache里面。根据直觉，频繁被读取的块，是应该cache在内存中的，不应该会有物理读，并且当时数据库没有其他的活动，cache中的块也不会被挤出去。
这个现象是违反直觉的，但注意，直觉的东西不一定不是正确的东西。那么为什么那个简单的SQL，在反复执行的情况下，怎么会有那么高的物理读呢？
这还得从Oracle的buffer cache管理说起。一个oracle进程在做全表扫描时，buffer会放置到LRU-AUX链表的尾端。如果在读块时，如果已经没有free buffer，那么进程就会查找可以被age out的块，这个查找过程是从LRU-AUX链表的尾端开始的。也就是说LRU-AUX链表尾端的buffer，总是第一个被替换的。
基于Oracle的这种LRU算法，我们假设，现在系统中已经没有free buffer，也就是buffer cache已经用完。实际上这种情况是最常见的，必竟现在的库相对于db_cache_size来说，总是大很多倍，buffer cache很快就会被填满。那么，在做全表扫描时，第1次多块读(multiblock read)的buffer会放置到LRU-AUX链表的尾端（不管这个buffer是从哪里得到的），第2次多块读时，需要buffer时，oracle也会从LRU-AUX链表的的尾端开始进行查找可以被替换的buffer，而刚好LRU-AUX链表的尾端的buffer正是上一次多块读时的buffer。也就是说前一次读到的块，很快就会被后面读的块替换掉了。
因此，不难理解，为什么在数据库活动很少时，反复扫描同一个表（这里不是同时扫描），其物理读仍然很高。并不是我们直觉的那样，这个表已经很“热”，应该全部在内存中命中。
下面我们通过一个测试来验证我们的推断。
测试环境：
Oracle 10.2.0.3 32Bit for Linux
我们设置如下的参数，以便让buffer cache的大小在我们控制的范围内：
SQL> alter system set shared_pool_size=100m;
System altered.
SQL> alter system set db_cache_size=30m;
System altered.
SQL> alter system set sga_target=200m;
再建两个测试表T1，T2，使每一个表的块只能放置1行数据。
SQL> create table t1 ( a int,b char(2000),c char(2000),d char(1000));
Table created.
SQL> create table t2 ( a int,b char(2000),c char(2000),d char(1000));
Table created.
SQL> insert into t1 values (1,'a','b','c');
1 row created.
SQL> commit;
Commit complete.
SQL> alter table t1 minimize records_per_block;
Table altered.
SQL> insert into t2 values (1,'a','b','c');
1 row created.
SQL> commit;
Commit complete.
SQL> alter table t2 minimize records_per_block;
Table altered.
向这两个表中插入3999行数据，与上面插入的1行一起分别共计4000行。
SQL> insert into t1 select rownum+1,'a','b','c' from dba_objects where rownum< =3999; 3999 rows created. SQL> insert into t2 select rownum+1,'a','b','c' from dba_objects where rownum< =3999; 3999 rows created. SQL> commit;
Commit complete.
SQL> col segment_name for a20
SQL> select segment_name,blocks from dba_segments where owner=user and segment_name in ('T1','T2');SEGMENT_NAME BLOCKS
-------------------- ----------
T2 4096
T1 4096
可以看到这两个表都有4096个块，每个表32MB。
刷新buffer_cache，保证buffer cache中没有任何这两个表的块。
SQL> select object_name,data_object_id from dba_objects where owner=user and object_name in ('T1','T2');
OBJECT_NAME DATA_OBJECT_ID
-------------------- --------------
T1 52416
T2 52417
SQL> alter system flush buffer_cache;System altered.
SQL> select count(*) from x$bh where obj in (52416,52417) and state<>0;
COUNT(*)
----------
0
查询T1表，让T1表中的所有块填充到buffer cache中，并统计X$BH中各状态的合计：
SQL> select count(*) from t1;
COUNT(*)
----------
4000
SQL> select set_ds,
2 decode(state,0,'free',1,'xcur',2,'scur',3,'cr',4,'read',5,'mrec',
3 6,'irec',7,'write',8,'pi') state,
4 count(*) cnt
5 from x$bh
6 group by set_ds, state
7 order by 1, 2;SET_DS STATE CNT
-------- ----- ----------
2C5430B4 cr 5
2C5430B4 free 72
2C5430B4 xcur 6872
用TEST用户连接产生另一个会话（以下称之为会话2），在会话2中查询T2表：
SQL> connect test/test
Connected.
SQL> set autot on statistics
SQL> select count(*) from sys.t2;COUNT(*)
----------
4000
Statistics
----------------------------------------------------------
154 recursive calls
0 db block gets
4026 consistent gets
4003 physical reads
0 redo size
411 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
在会话2中反复多次查询T2表，发现T2表的物理读数几乎不再变化：
SQL> /
COUNT(*)
----------
4000Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4009 consistent gets
3682 physical reads
0 redo size
411 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedSQL> /
COUNT(*)
----------
4000Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4009 consistent gets
3712 physical reads
0 redo size
411 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedSQL> /
COUNT(*)
----------
4000Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4009 consistent gets
3712 physical reads
0 redo size
411 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
在会话1中，对x$bh按state进行状态，其结果也趋于稳定：
SET_DS STATE CNT
-------- ----- ----------
2B9430B4 cr 6
2B9430B4 free 72
2B9430B4 xcur 10401
如果查询T1表，会发现，T1表不再产生逻辑读，这是因为T1表开始已经全部装入到buffer cache中。
因此，这里可以得到一个结论，除非buffer cache有足够的剩余空间，使一个全表扫描所读到的所有块全部装入到buffer cache，否则全表扫描产生的物理读仍然是相当的高，不管这个表被扫描到多少次。
虽然提出此问题的网友使用的Oracle为9.2.0.1，在这个版本下的测试，可以得到同样的结果。
即使是多个进程并发扫描同一个表，除非一个多块读操作读进buffer cache中的块，在age out之前被另一个多块读操作读到，那么多个进程对同一个表进行全表扫描，产生的物理读仍然是非常高的。
而如果一个多块读操作读进buffer cache中的块，age out之前被另一个单块读操作读到，那结果也许更糟糕，这导致一个块被移到LRU-MAIN链表中，被age out的机会相对减少，但是正是由于一个块没有被age out，下一次相同范围的多块读，将不得不分成两次多块读。
对于一个频繁全表扫描的表，如果SQL不能优化，则应该尽量放置到KEEP BUFFER中，这将显著减少物理读的数量。
不错！学习了
[回复]
老熊你好！
我目前遇到一个情况和您文章的情形有些类似，在数据仓库环境中，很多sql 并发同时访问一个表，而且是全表扫描，但是等待事件是 单块读。
将表的并行特性打开，想用直接 读入pga， 避免单块读的等待，但是依然走单块读
你在工作的过程中有没有遇到这种情况？
[回复]
老熊 回复:
3月 29th, 2012 at 5:10 下午
全表扫描单块读通常会有两种情况：
1. 表中很多的块已经在buffer cache中，个别块在磁盘上；
2. Oracle的BUG。
当然其他一些情况有单块读，比如extent的边界什么的，但那个量很少，可以忽略。
并行打开后，但是SQL仍然没有并行，那需要看看是不是并行进程已经用完了。
[回复]
不知道为什么我做的结果和博主的结果不一样的？我的oracle版本是oracle 11g v2，实验过程如下：
15:35:10 SQL> select object_name,data_object_id from dba_objects where owner=user and object_name in (’T1′,’T2′);
15:35:53 SQL> select object_name,data_object_id
15:35:54 2 from dba_objects
15:35:54 3 where owner=user and object_name in (‘T1′,’T2′);
OBJECT_NAME DATA_OBJECT_ID
——————– ————–
T1 74896
T2 74897
15:35:56 SQL> alter system flush buffer_cache;
System altered.
15:36:08 SQL> select count(*) from x$bh where obj in (74896,74897) and state0;
COUNT(*)
———-
0
15:36:29 SQL> select count(*) from t1;
COUNT(*)
———-
4000
15:37:36 SQL> select set_ds,
decode(state,0,’free’,1,’xcur’,2,’scur’,3,’cr’,4,’read’,5,’mrec’,
15:37:40 3 6,’irec’,7,’write’,8,’pi’) state,
15:37:40 4 count(*) cnt
15:37:40 5 from x$bh
15:37:40 6 group by set_ds, state
15:37:40 7 order by 1, 2;
SET_DS STATE CNT
——– ———- ———-
5E8605C8 free 32197
5E8605C8 xcur 4011
15:37:41 SQL> conn hr/hr123456
Connected.
15:37:53 SQL> set autot on stat
15:38:04 SQL> select count(*) from sys.t2;
COUNT(*)
———-
4000
Statistics
———————————————————-
4 recursive calls
0 db block gets
4069 consistent gets
4003 physical reads
0 redo size
416 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
15:38:58 SQL> select count(*) from sys.t2;
COUNT(*)
———-
4000
Statistics
———————————————————-
0 recursive calls
0 db block gets
4009 consistent gets
0 physical reads
0 redo size
416 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
15:39:04 SQL> select count(*) from sys.t2;
COUNT(*)
———-
4000
Statistics
———————————————————-
0 recursive calls
0 db block gets
4009 consistent gets
0 physical reads
0 redo size
416 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
15:39:05 SQL> select count(*) from sys.t2;
COUNT(*)
———-
4000
Statistics
———————————————————-
0 recursive calls
0 db block gets
4009 consistent gets
0 physical reads
0 redo size
416 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
15:39:06 SQL> select count(*) from sys.t2;
COUNT(*)
———-
4000
Statistics
———————————————————-
0 recursive calls
0 db block gets
4009 consistent gets
0 physical reads
0 redo size
416 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
15:39:07 SQL> select count(*) from sys.t2;
COUNT(*)
———-
4000
Statistics
———————————————————-
0 recursive calls
0 db block gets
4009 consistent gets
0 physical reads
0 redo size
416 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
15:39:24 SQL> select count(*) from sys.t1;
COUNT(*)
———-
4000
Statistics
———————————————————-
4 recursive calls
0 db block gets
4081 consistent gets
0 physical reads
0 redo size
416 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
15:39:39 SQL> select count(*) from sys.t1;
COUNT(*)
———-
4000
Statistics
———————————————————-
0 recursive calls
0 db block gets
4009 consistent gets
0 physical reads
0 redo size
416 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
15:39:41 SQL> select count(*) from sys.t1;
COUNT(*)
———-
4000
Statistics
———————————————————-
0 recursive calls
0 db block gets
4009 consistent gets
0 physical reads
0 redo size
416 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
15:39:42 SQL> select count(*) from sys.t1;
COUNT(*)
———-
4000
Statistics
———————————————————-
0 recursive calls
0 db block gets
4009 consistent gets
0 physical reads
0 redo size
416 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
15:39:43 SQL> select count(*) from sys.t1;
COUNT(*)
———-
4000
Statistics
———————————————————-
0 recursive calls
0 db block gets
4009 consistent gets
0 physical reads
0 redo size
416 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
15:39:43 SQL>
[回复]