kagamihogeの日記

kagamihogeの日記です。

PCTFREE変えたときのテーブル全選択・全更新・全削除の速度

pctfree 0/10/90に変更してデータ入れて、where条件無しのselect/update/deleteを実行したときに速度差が出るか見る。

準備

データ入れるテーブルを作る。なおこの環境では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秒台になってたんで誤差の範囲内とは思うのだけど