老熊的三分地-Oracle、UNIX、数据恢复

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(*)
----------
4000

Statistics
----------------------------------------------------------
          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 processed

SQL> /

COUNT(*)
----------
4000

Statistics
----------------------------------------------------------
          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

SQL> /

COUNT(*)
----------
4000

Statistics
----------------------------------------------------------
          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中，这将显著减少物理读的数量。