◇便利な新機能
ORACLE10gから追加された新機能で非常に便利なコマンドを発見したので紹介したい。
主にデバッグ用にしか使用できないが・・・。
コマンドを見るとある程度、どのような内容か推測できるかもしれない。
ALTER SYSTEM FLUSH BUFFER_CACHE;
9iまではSHARED_POOLのFLUSHしか実行できなかった。
しかし、10gからはBUFFER_CACHEのFLUSHも実行できるようになった。
これで、性能検証の際、バッファに乗ったBLOCKを追い出す為にDBの再起動をする必要がなくなった。
実はVol.19 ORACLE10g基礎性能検証～追加検証（解答編）～では、このコマンドを多用している。
このコマンドのおかげで性能試験は本当に楽になったと言える。
◇BUFFER_CACHEって？
ORACLE Silver Fellow（ORACLE入門）の内容だが簡単に説明を・・・。
ORACLEのインスタンスはORACLEのバックグラウンドプロセスとSGAから構成されている。
SGAとはORACLEの使用するメモリ空間のことでSHARED_POOLとBUFFER_CACHEから構成されている。
BUFFER_CACHEは実データを読み込む為のメモリ空間。
ORACLEに対する、検索、更新は全てこのBUFFER_CACHEを介して行なう事になる。
◇検証方法
検証環境
RDBMS:ORACLE10.1.0
OS:Windows2000
Optimizer mode: ALL_ROWS
今回の検証は「ALTER SYSTEM FLUSH BUFFER_CACHE;」コマンドによりデータがキャッシュから追い出されていることを確認する。
手順は以下の通り。
/* sysにてログイン */
/* DBの再起動 */
shutdown;
startup;
/* 以下、作業用ユーザにてログイン */
/* SQL Traceの取得 */
alter session set sql_trace = true ;
/* SQL1 */
SELECT SUM(COLUMN3) FROM TEST_2;
/* SQL2 */
SELECT SUM(COLUMN3) FROM TEST_2;
/* sysにて実行 */
/* FLUSH BUFFER_CACHE */
ALTER SYSTEM FLUSH BUFFER_CACHE;
/* 作業用ユーザにて実行 */
/* SQL3 */
SELECT SUM(COLUMN3) FROM TEST_2;
/* SQL Traceの終了 */
alter session set sql_trace = false ;
SQL1、SQL2、SQL3は同じSQLに見えて微妙に違う。
SELECT句とSUM関数の間のスペースの数が、それぞれ
SQL1 ・・・ 1個
SQL2 ・・・ 2個
SQL3 ・・・ 3個
と、なっている。
これは、3つのSQLを異なるSQLであるとORACLEに認識させる為。
なぜ異なるSQLだと認識させる必要があるかと言うと、そのほうがTraceFileが見やすくて便利だからである。
SQL1は再起動直後の為、DiskからBLOCKを読み込む。
SQL2はSQL1を実行した際にBLOCKがキャッシュに乗っている為、Diskから読み込むBLOCKの数が減っているはず。
（必要なBLOCKが全てキャッシュに乗っていればDiskは0となる。）
SQL3はBUFFER_CACHEのFLUSHを行なった後に実行する為、SQL1と同等のDiskを読み込むはず。
◇検証結果
以下が取得したTraceFileの中身。
********************************************************************************
SELECT SUM(COLUMN3) FROM TEST_2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.04 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.04 0.14 497 500 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.06 0.19 497 500 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=500 pr=497 pw=0 time=145739 us)
100000 TABLE ACCESS FULL TEST_2 (cr=500 pr=497 pw=0 time=214712 us)
********************************************************************************
SELECT SUM(COLUMN3) FROM TEST_2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.03 0.02 0 500 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.02 0 500 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=500 pr=0 pw=0 time=28258 us)
100000 TABLE ACCESS FULL TEST_2 (cr=500 pr=0 pw=0 time=200057 us)
********************************************************************************
SELECT SUM(COLUMN3) FROM TEST_2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.04 0.13 497 500 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.04 0.13 497 500 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=500 pr=497 pw=0 time=132281 us)
100000 TABLE ACCESS FULL TEST_2 (cr=500 pr=497 pw=0 time=308852 us)
********************************************************************************
わかりにくいのでtotalだけ注目して見てみよう。
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
SQL1 total 4 0.06 0.19 497 500 0 1
SQL2 total 4 0.03 0.02 0 500 0 1
SQL3 total 4 0.04 0.13 497 500 0 1
SQL1ではインスタンス起動後、初めての検索なのでDiskから497Block読み込んでいる。
SQL2では検索に必要となるDataBlockが全てBUFFER_CACHEに載っているので、Diskから読み込みを行なう必要が無い。
よってDiskが0になっている。
SQL3では直前に「ALTER SYSTEM FLUSH BUFFER_CACHE;」コマンドが実行されている為、再度DiksからDataBlockを読み込む必要がある。
このためSQL1と同じくDiskの値が497となる。
予想通りの結果。
上記の結果から「ALTER SYSTEM FLUSH BUFFER_CACHE;」コマンドを実行することによりBUFFER_CACHE上のDataBlockが追い出されている事を確認できた。
ORACLE10gから追加された新機能で非常に便利なコマンドを発見したので紹介したい。
主にデバッグ用にしか使用できないが・・・。
コマンドを見るとある程度、どのような内容か推測できるかもしれない。
ALTER SYSTEM FLUSH BUFFER_CACHE;
9iまではSHARED_POOLのFLUSHしか実行できなかった。
しかし、10gからはBUFFER_CACHEのFLUSHも実行できるようになった。
これで、性能検証の際、バッファに乗ったBLOCKを追い出す為にDBの再起動をする必要がなくなった。
実はVol.19 ORACLE10g基礎性能検証～追加検証（解答編）～では、このコマンドを多用している。
このコマンドのおかげで性能試験は本当に楽になったと言える。
◇BUFFER_CACHEって？
ORACLE Silver Fellow（ORACLE入門）の内容だが簡単に説明を・・・。
ORACLEのインスタンスはORACLEのバックグラウンドプロセスとSGAから構成されている。
SGAとはORACLEの使用するメモリ空間のことでSHARED_POOLとBUFFER_CACHEから構成されている。
BUFFER_CACHEは実データを読み込む為のメモリ空間。
ORACLEに対する、検索、更新は全てこのBUFFER_CACHEを介して行なう事になる。
◇検証方法
検証環境
RDBMS:ORACLE10.1.0
OS:Windows2000
Optimizer mode: ALL_ROWS
今回の検証は「ALTER SYSTEM FLUSH BUFFER_CACHE;」コマンドによりデータがキャッシュから追い出されていることを確認する。
手順は以下の通り。
/* sysにてログイン */
/* DBの再起動 */
shutdown;
startup;
/* 以下、作業用ユーザにてログイン */
/* SQL Traceの取得 */
alter session set sql_trace = true ;
/* SQL1 */
SELECT SUM(COLUMN3) FROM TEST_2;
/* SQL2 */
SELECT SUM(COLUMN3) FROM TEST_2;
/* sysにて実行 */
/* FLUSH BUFFER_CACHE */
ALTER SYSTEM FLUSH BUFFER_CACHE;
/* 作業用ユーザにて実行 */
/* SQL3 */
SELECT SUM(COLUMN3) FROM TEST_2;
/* SQL Traceの終了 */
alter session set sql_trace = false ;
SQL1、SQL2、SQL3は同じSQLに見えて微妙に違う。
SELECT句とSUM関数の間のスペースの数が、それぞれ
SQL1 ・・・ 1個
SQL2 ・・・ 2個
SQL3 ・・・ 3個
と、なっている。
これは、3つのSQLを異なるSQLであるとORACLEに認識させる為。
なぜ異なるSQLだと認識させる必要があるかと言うと、そのほうがTraceFileが見やすくて便利だからである。
SQL1は再起動直後の為、DiskからBLOCKを読み込む。
SQL2はSQL1を実行した際にBLOCKがキャッシュに乗っている為、Diskから読み込むBLOCKの数が減っているはず。
（必要なBLOCKが全てキャッシュに乗っていればDiskは0となる。）
SQL3はBUFFER_CACHEのFLUSHを行なった後に実行する為、SQL1と同等のDiskを読み込むはず。
◇検証結果
以下が取得したTraceFileの中身。
********************************************************************************
SELECT SUM(COLUMN3) FROM TEST_2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.04 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.04 0.14 497 500 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.06 0.19 497 500 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=500 pr=497 pw=0 time=145739 us)
100000 TABLE ACCESS FULL TEST_2 (cr=500 pr=497 pw=0 time=214712 us)
********************************************************************************
SELECT SUM(COLUMN3) FROM TEST_2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.03 0.02 0 500 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.02 0 500 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=500 pr=0 pw=0 time=28258 us)
100000 TABLE ACCESS FULL TEST_2 (cr=500 pr=0 pw=0 time=200057 us)
********************************************************************************
SELECT SUM(COLUMN3) FROM TEST_2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.04 0.13 497 500 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.04 0.13 497 500 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=500 pr=497 pw=0 time=132281 us)
100000 TABLE ACCESS FULL TEST_2 (cr=500 pr=497 pw=0 time=308852 us)
********************************************************************************
わかりにくいのでtotalだけ注目して見てみよう。
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
SQL1 total 4 0.06 0.19 497 500 0 1
SQL2 total 4 0.03 0.02 0 500 0 1
SQL3 total 4 0.04 0.13 497 500 0 1
SQL1ではインスタンス起動後、初めての検索なのでDiskから497Block読み込んでいる。
SQL2では検索に必要となるDataBlockが全てBUFFER_CACHEに載っているので、Diskから読み込みを行なう必要が無い。
よってDiskが0になっている。
SQL3では直前に「ALTER SYSTEM FLUSH BUFFER_CACHE;」コマンドが実行されている為、再度DiksからDataBlockを読み込む必要がある。
このためSQL1と同じくDiskの値が497となる。
予想通りの結果。
上記の結果から「ALTER SYSTEM FLUSH BUFFER_CACHE;」コマンドを実行することによりBUFFER_CACHE上のDataBlockが追い出されている事を確認できた。