データベースバッファの検証

データベースバッファに関することで、前から気になってたことがあったので検証してみた。

１０万件のテーブルから１件のレコードをテーブルフルスキャンによって取得した際に、データベースバッファ上に載る対象となるのは、
１０万件か、１件のみなのか？

フルスキャンで１０万件を取得した場合

データベースバッファの状態を確認（freeが空き領域）

SQL> select status,count(*) from v$bh group by status ;

STATUS       COUNT(*)
---------- ----------
cr                 13
free             1431
xcur             1556

１０万件のレコードを取得

SQL> select * from test;

100000行が選択されました。

統計
----------------------------------------------------------
        335  recursive calls
          0  db block gets
       7292  consistent gets
        626  physical reads
          0  redo size
    4860720  bytes sent via SQL*Net to client
      73698  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
     100000  rows processed

データベースバッファ確認

SQL> select status,count(*) from v$bh group by status ;

STATUS       COUNT(*)
---------- ----------
cr                 15
free              771
xcur             2214

約７００程度使っていることに。

１０万件のテーブルからフルスキャンで１件のみを取得した場合

まず、データベースバッファをクリア。
Oracle10gなら「alter system flush buffer_cache」でクリアすることが可能だが、
Oracle9iでの検証のため、インスタンスを再起動することに。。
( 「alter system flush shared_pool」ならあるが、これは共有プールをクリアするもので
データベースバッファはクリアされない）
再起動後、データベースバッファ確認。

SQL>  select status,count(*) from v$bh group by status ;

STATUS       COUNT(*)
---------- ----------
cr                 13
free             1434
xcur             1553

１０万件のテーブルからフルスキャンで１件のみ取得

SQL> select * from test where test_num = 10000;

統計
----------------------------------------------------------
        335  recursive calls
          0  db block gets
        669  consistent gets
        626  physical reads
          0  redo size
        721  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed

データベースバッファ確認。

SQL> select status,count(*) from v$bh group by status ;

STATUS       COUNT(*)
---------- ----------
cr                 14
free              776
xcur             2210

最初と同様に700程度使っている。よって、1件のみ取得した場合でも
10万件が対象となっていることがわかる。
念のためINDEXを利用した場合についても検証してみる。

INDEXを利用して、10万件から1件を取得した場合

INDEX作成。

SQL> create unique index test_num_idx on test(test_num);

索引が作成されました。

再起動後、データベースバッファ確認。

SQL> select status,count(*) from v$bh group by status ;

STATUS       COUNT(*)
---------- ----------
cr                 13
free             1115
xcur             1872

INDEXを利用して１件取得。

SQL> select * from test where test_num = 10000;

統計
----------------------------------------------------------
        364  recursive calls
          0  db block gets
         53  consistent gets
          5  physical reads
          0  redo size
        721  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

データベースバッファ確認。

SQL> select status,count(*) from v$bh group by status ;

STATUS       COUNT(*)
---------- ----------
cr                 13
free             1110
xcur             1877

5しか使っていない。
ってことは、INDEXをうまく利用すれば、物理アクセスも減らせるし、
バッファ上に無駄なデータも置かなくてもよく、メリット多いってことか。