[Oracle]データベースバッファキャッシュを明示的に破棄してみる

データベースバッファキャッシュは下のSQLで明示的に破棄できる。

alter system flush buffer_cache;

破棄されたことを確認する

破棄前の統計情報

SQL> select * from person where id = 1;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1167617095

--------------------------------------------------------------------------------------------
| Id  | Operation      | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |     |  1 | 17 |  3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| PERSON    |  1 | 17 |  3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN     | SYS_C0013811 |  1 |    |  2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1)


Statistics
----------------------------------------------------------
   0  recursive calls
   0  db block gets
   4  consistent gets
   0  physical reads
   0  redo size
 469  bytes sent via SQL*Net to client
 540  bytes received via SQL*Net from client
   1  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
   1  rows processed

データベースバッファキャッシュを破棄する。

SQL> alter system flush buffer_cache;

System altered.

同じ問い合わせを実行するとphysical readsが増えているので、データファイルからブロックが読み取られていることが確認できる。

SQL> select * from person where id=1;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1167617095

--------------------------------------------------------------------------------------------
| Id  | Operation      | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |     |  1 | 17 |  3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| PERSON    |  1 | 17 |  3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN     | SYS_C0013811 |  1 |    |  2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1)


Statistics
----------------------------------------------------------
   1  recursive calls
   0  db block gets
   4  consistent gets
  24  physical reads
   0  redo size
 469  bytes sent via SQL*Net to client
 540  bytes received via SQL*Net from client
   1  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
   1  rows processed