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

Trackback

4 comments untill now

  1. 不错!学习了

  2. 老熊你好!
    我目前遇到一个情况和您文章的情形有些类似,在数据仓库环境中,很多sql 并发同时访问一个表,而且是全表扫描,但是等待事件是 单块读。
    将表的并行特性打开,想用直接 读入pga, 避免单块读的等待,但是依然走单块读
    你在工作的过程中有没有遇到这种情况?

    老熊 Reply:

    全表扫描单块读通常会有两种情况:
    1. 表中很多的块已经在buffer cache中,个别块在磁盘上;
    2. Oracle的BUG。
    当然其他一些情况有单块读,比如extent的边界什么的,但那个量很少,可以忽略。

    并行打开后,但是SQL仍然没有并行,那需要看看是不是并行进程已经用完了。

  3. 无风不起浪 @ 2013-10-03 15:45

    不知道为什么我做的结果和博主的结果不一样的?我的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>

Add your comment now