pctfree 0/10/90に変更してデータ入れて、where条件無しのselect/update/deleteを実行したときに速度差が出るか見る。
環境
- Oracle Database Express Edition 11g Release 2をWindows上で動作
- Oracle SQL Developer 3.2
- SQL*Plus: Release 11.2.0.2.0 Production
- jdk1.7.0_21
- Eclipse 4.2.0 SR2
- ojdbc6.jar
準備
データ入れるテーブルを作る。なおこの環境ではpctfreeのデフォは10。
drop table customer purge; create table customer ( customer_id integer , customer_name varchar2(10 byte) );
pctfree変更してテーブル作るときはこんな感じ。0と90に変えてdrop&createする。
create table CUSTOMER ( CUSTOMER_ID integer , CUSTOMER_NAME varchar2(10 byte) ) pctfree 0;
適当なデータを100万件入れる。
insert into customer select rownum, DBMS_RANDOM.STRING('X', 10) from (select rownum from all_catalog where rownum <= 1000), (select rownum from all_catalog where rownum <= 1000); commit;
データ挿入後にテーブルの使用ブロック数などを確認する。
select segment_name, bytes, blocks, extents from user_segments where segment_name = 'CUSTOMER';
pctfreeの値それぞれでの、上記SQLの実行結果。
pctfree | BYTES | BLOCKS | EXTENTS |
---|---|---|---|
0 | 22020096 | 2688 | 36 |
10 | 24117248 | 2944 | 38 |
90 | 218103808 | 26624 | 97 |
計測対象のSQLを実行するまえに、キャッシュをクリアする。あと、実行時間の計測はSQL*Plusでやる。
alter system flush buffer_cache; set timing on;
計測対象のSQL、select/update/deleteはこんな感じ。
select * from customer;
update customer set customer_name = DBMS_RANDOM.STRING('X', 10);
delete from customer;
実行結果
update/deleteはcommitに要した時間も含む。
select * from customer;
pctfree | 1 | 2 | 3 |
---|---|---|---|
0 | 01:43.32 | 01:42.32 | 01:42.50 |
10 | 01:31.96 | 01:31.87 | 01:31.90 |
90 | 01:35.00 | 01:33.43 | 01:34.32 |
update customer set customer_name = DBMS_RANDOM.STRING('X', 10);
pctfree | 1 | 2 | 3 |
---|---|---|---|
0 | 01:52.81 | 01:58.43 | 01:52.23 |
10 | 01:57.59 | 01:52.90 | 01:53.37 |
90 | 02:04.67 | 02:03.15 | 02:02.43 |
delete from customer;
pctfree | 1 | 2 | 3 |
---|---|---|---|
0 | 00:34.59 | 00:34.32 | 00:35.23 |
10 | 00:36.28 | 00:36.51 | 00:35.01 |
90 | 00:51.15 | 00:48.75 | 00:48.23 |
selectのautotrace
pctfree=0
0 recursive calls 0 db block gets 69290 consistent gets 2638 physical reads 0 redo size 29646927 bytes sent via SQL*Net to client 733745 bytes received via SQL*Net from client 66668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1000000 rows processed
pctfree=10
0 recursive calls 0 db block gets 71538 consistent gets 2892 physical reads 0 redo size 29646927 bytes sent via SQL*Net to client 733745 bytes received via SQL*Net from client 66668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1000000 rows processed
pctfree=90
0 recursive calls 0 db block gets 117553 consistent gets 26417 physical reads 0 redo size 29646927 bytes sent via SQL*Net to client 733745 bytes received via SQL*Net from client 66668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1000000 rows processed
感想とか
全表走査のselectはpctfreeの違いによる速度の差は見られなかった。読み込むブロック数は(pctfree0と10はともかく90はかなり)差があるのに、実行時間には差が出てこない。むしろpctfree0の方が10秒とか遅かったりしている*1。これは何故なのか。
autotraceを取ってみると、physical readsは差がある。しかし、クライアントに返されたバイト数は差が無い。てことは、実行時間に占める割合のうち、ブロックを読み出すよりもバイト列をクライアントに返すためにかかった時間のほうが圧倒的に多い、のだと思う。なので、ブロック読み出しにかかる時間で差は出てはいるものの、その差はクライアントにバイト列を返すためにかかる時間に比べると、無視できるほど小さい差しか無いのだろう。
てか良く考えてみたらインデックス範囲スキャンとフルテーブルスキャンの損益分岐点 - kagamihogeのblogとほぼ同条件下なので、すべてのブロックリードにかかる時間は1秒かそこら、pctfree90でも数秒に留まると思われる。ブロックサイズ8k * 2600 = 20.8Mくらいだし、まぁそんなもんか。ブロック数2600と26000と10倍で差が出なかったんで、千倍一万倍にならないとハッキリとした差は出てこないんだろうなぁ。ディスク性能によりけりだろうけど。
ただし、update/deleteは、ブロック数の差が実行時間の差となって出てる。読み込みだけでなく、ロールバックセグメントへの書き込みも発生する。なので、テーブルの全データ更新・削除するような場合は、ブロック数の差が速度にそのまんま出てくる。つってもまぁ、条件なしupdateやdeleteをやるようなことはまず無いんだけども。
*1:何度かやると30秒台になってたんで誤差の範囲内とは思うのだけど