しばちょう先生の試して納得！DBAへの道 indexページ▶▶
みなさん、こんにちは。毎月気が付くと、この連載記事の締め切りがやってきている感じがする、“しばちょう”こと柴田長（しばた つかさ）です。こんなときは大抵、休日の朝からファミレスで優雅（?）に執筆する時間を楽しんでいます。
さて、まだまだ連続でオプティマイザ統計についてご紹介していきます。これまではコストベース・オプティマイザが適切な実行計画を選択する為には、精度の高いオプティマイザ統計情報が必要不可欠です。とお伝えしてきましたが、今回はその基本的なオプティマイザ統計だけでは不十分なケースがあり得る状況を体験して頂き、「ヒストグラム」と呼ばれる追加で取得する統計情報の必要性を理解して頂きたいと思います。そのようなケースを体験しておくことで、実際の性能トラブルに遭遇した際に発揮されるDBAの底力を養うことの手助けになればと思っています。また、オプティマイザ統計を収集する際にDBAが気付かないうちに、これらも追加で自動的に取得されることがあります。その自動判断に使用される情報とその格納場所についても、少しだけご紹介したいと思います。
以下の演習をこれまでの演習で使用したOracle Database 11g Release 2 Enterprise Editionのデータベースで試してみてください。
■0. 次のSQLで今回の演習で使用するTBL11表をTRYスキーマ内に作成して下さい。
sqlplus TRY/TRY
SQL>
-- TBL11表の作成
create table TBL11 (COL1 number NOT NULL, COL2 number, COL3 char(100)) ;
-- TBL11表へレコード100万件を挿入
declare
intNum_COL1 number := 0 ;
intInterval number := 10000 ;
begin
for i in 1..100 loop
insert into TBL11 select LEVEL + intNum_COL1, i , rpad('A',100,i)
from DUAL connect by LEVEL <= intInterval ;
commit ;
intNum_COL1 := intNum_COL1 + intInterval ;
end loop ;
end ;
/
-- TBL11表のCOL1列にユニーク索引を作成
create unique index IDX_TBL11_COL1 on TBL11(COL1) ;
-- TBL11表に主キー（COL1列）を定義。その際、COL1列のユニーク索引を利用
alter table TBL11 add primary key (COL1) using index ;
-- TBL11表のCOL2列にBツリー索引を作成
create index IDX_TBL11_COL2 on TBL11(COL2);
-- TBL11表のオプティマイザ統計情報を収集
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TRY', -
tabname => 'TBL11', -
cascade => FALSE, -
method_opt => 'FOR ALL COLUMNS SIZE 1');
いつもの演習通り、今回の体験で使用して頂くベースの表を作成して頂きます。ここでは2点補足説明させて頂きますね。
1点目はTBL11表へレコード100万件を挿入するPL/SQL文のforループ処理です。一般的に他人が書いたPL/SQLは意図が汲み取れないので理解が難しいものです。
ステップに分けて解説すると、まずはループの条件。ここでは変数「i」に対して初期値「1」から最大値「100」まで増分「1」で合計「100」回ループすることが理解できます。
次に、TBL11表に実際にレコードを挿入しているINSERT文に注目します。INSERT文全体をみると複雑ですが、これまでもご紹介してきたINSERT + SELECT文ですね。SELECT文ではLEVEL疑似列を使用した連続値を変数「intInterval」の数「10000」だけ作成しています。
つまり、この時点で一回のINSERTで1万件のレコードが挿入され、そのINSERTがforループにより100回繰り返されるので、最終的にTBL11表に挿入されるレコード数は100万件と理解できます。
最後に各カラムに格納される値を見てみます。COL1列にはLEVEL疑似列（1～10000）と変数「intNum_COL1」を加算した値がセットされています。変数「intNum_COL1」はforループに入る直前に「0」で初期化され、forループ内の最後で（次の繰り返しの前に）変数「intInterval(=10000)」が加算されます。つまり、COL1列には1回目のループでは「1~10000」、2回目のループでは「10001~20000」、3回目のループでは「20001~30000」の値がセットされることになります。ちなみに、COL2列は、変数「i」なのでforループを繰り返した回数がセットされますね。
実際にPL/SQL処理でレコードを挿入したTBL11表を次のSELECT文で確認してみると、COL1列が1~100万の一意の値、COL2列が1~100へ1万レコード毎に1つずつ増加していっていることが確認できます。ちなみに、COL3列はオマケですが、このような検証作業では意外と重要だったりします。
SQL> set pagesize 10003 linesize 130 select * from TBL11 order by 1 ;
ちょっと解説が長くなってしまいましたが、もう少しお付き合い下さい。2点目の補足説明はGAGHER_TABLESTATSのパラメータです。初めて使用するパラメータ「method_opt」が設定されています。これは今回の演習を通して体験して頂く「ヒストグラム」の取得を制御するパラメータであり、上記の設定「method_opt => ‘FOR ALL COLUMNS SIZE 1’」はヒストグラムを明示的に取得しないように指定しています。デフォルトの設定は「method_opt => ‘FOR ALL COLUMNS SIZE AUTO’」であり、この場合はOracle Databaseがヒストグラムを取得するか否かを自動判断する仕組みが適用されます。
さて、準備が整いましたね。では、いよいよ演習の本番に入っていきましょう。
■ 1. TBL11表において、COL2列が100以下のレコード数をカウントして下さい。
sqlplus / as sysdba
SQL> -- バッファ・キャッシュと共有プールにキャッシュされている情報をクリア（フラッシュ）
alter system flush buffer_cache ;
alter system flush shared_pool ;
-- SELECTの実行
connect TRY/TRY
set timing on
select count(COL1),sum(mod(ora_hash(COL3),2)) from TBL11 where COL2 <= 100 ;
COUNT(COL1) SUM(MOD(ORA_HASH(COL3),2))
----------- --------------------------
1000000 530000
経過: 00:00:07.36
-- SELECT文の実行計画を確認
select * from table(dbms_xplan.display_cursor()) ;
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID 72fq5cqt9753g, child number 0
-------------------------------------
select count(COL1),sum(mod(ora_hash(COL3),2)) from TBL11 where COL2 <= 100
Plan hash value: 694736540
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4414 (100)| |
| 1 | SORT AGGREGATE | | 1 | 104 | | |
|* 2 | TABLE ACCESS FULL| TBL11 | 1000K| 99M| 4414 (1)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL2"<=100)
演習問題を読んで「おや？」と疑問に持たれた方は、TBL11表に格納されている「レコードの特徴」が頭の中に入っている方で、私のPL/SQLを完全にご理解されているでしょう。何故かと言えば、SELECT文の条件に指定された「COL2列が100以下」とは、結局、全てのレコードにヒットする条件ですよね。よって、全レコードにアクセスすることになるので、実行計画を確認してみるとTBL11表へアクセスするOperationとしては「TABLE ACCESS FULL」が選択されています。
しかし、この部分で索引「IDX_TBL11_COL2」に対する「INDEX FAST FULL SCAN」が選択された方もいらっしゃるかと思います。それが一番正しいです。と言うのは、COL2列には索引「IDX_TBL11_COL2」が作成されているので、この索引だけを読み取ることで条件「COL2列が100以下」と演習の目的である「カウント」は済んでしまうからですね。今回の場合、3つのレコードが格納されているTBL11表にアクセスするよりも、TBL11表のCOL2列だけの値が格納されている索引「IDX_TBL11_COL2」へアクセスする方が、CPU使用量もI/O量も少なくて済みますから、いくらSELECT文で「COUNT(*)」つまり「全列」=「*」を指定しても、コストベース・オプティマイザは不必要な処理を省く賢い挙動をする為、実際は「COUNT(*)」を「COUNT(COL2)」に内部的に置き換えて実行計画を選択するのです。ちょっと難しかったかも知れませんが、体験して理解しておくことで次に遭遇した際には「当たり前」として扱うことができると思います。
なので、私の回答例では、敢えて「TABLE ACCESS FULL」が選択されるようにCOL3列使用する「sum(mod(ora_hash(COL3),2))」をSELECT文に含めています。これは、文字列であるCOL3列をora_hash関数で数字に変換し、その値をmod関数を使用して2で割り算した際の余りをsumで集計する処理です。
ちなみに、上記の回答例の「TABLE ACCESS FULL」でアクセスする行数「Rows」の値が「1000K」と表示されていますが、これは表の行数の100万件と一致しますが、どの値を元にどのように計算された値なのでしょうか？
sqlplus TRY/TRY SQL> select TABLE_NAME, NUM_ROWS from USER_TAB_STATISTICS where TABLE_NAME = 'TBL11' ; TABLE_NAME NUM_ROWS ------------------------------ ---------- TBL11 1000000 select TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, HISTOGRAM from USER_TAB_COL_STATISTICS where TABLE_NAME = 'TBL11' and COLUMN_NAME = 'COL2' ; TABLE_NAME COLUMN_NAME NUM_DISTINCT HISTOGRAM ------------------------------ ------------------------------ ------------ --------------- TBL11 COL2 100 NONE
答えは、上記の統計情報が元になっています。コストベース・オプティマイザは、SELECT文の条件によってヒットする件数をこの値を元に見積もります。例えば、TBL11表のレコード数（NUM_ROWS）は100万件で、条件に指定したCOL2列の個別値（NUM_DISTINCT）は1~100の100種類です。そして、SELECT文の条件「COL2 <= 100」により100種類の値が指定されているので、全レコード数× (アクセスする個別値の割合) = 100万 × (100 / 100) = 100万 = 1000Kと計算されています。ここでは条件が理解し辛いものとなってしまっていますが、条件を変更しながら実行計画のRowsの値を参照して頂けると、その通りになっていることがご理解いただけると思います。
■ 2. TBL11表に、COL1=0、COL2=1000000のレコードを1件挿入して下さい。再度、オプティマイザ統計情報を収集し、演習1のSELECT文を実行することで実行計画を確認して下さい。
sqlplus TRY/TRY
SQL> -- 1レコードをTBL11表へ挿入
insert into TBL11 values(0, 1000000, 'Adding Record') ;
commit ;
-- TBL11表の統計情報を取得し直す
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TRY', -
tabname => 'TBL11', -
cascade => TRUE, -
method_opt => 'FOR ALL COLUMNS SIZE 1');
connect / as sysdba
alter system flush buffer_cache ;
alter system flush shared_pool ;
connect TRY/TRY
select count(COL1),sum(mod(ora_hash(COL3),2)) from TBL11 where COL2 <= 100 ;
COUNT(COL1) SUM(MOD(ORA_HASH(COL3),2))
----------- --------------------------
1000000 530000
経過: 00:00:27.77
いかがでしょうか。100万レコードが格納されているTBL11表に1レコードを追加しただけで、同じSELECT文のレスポンスタイム（実行時間）が大幅に劣化してしまったではないですか!! まさに、これを体験して頂きたかったのです。たった1レコードの挿入なので、CPU使用量やI/O量が大幅に増加することはありませんし、何よりも追加で挿入したレコードのCOL2列の値は「1000000」でありSELECT文の条件「COL2 <= 100」にはヒットしないので、SELECT文の結果に変化が無いことは確認できていると思います。
では何が理由で、SELECT文のレスポンスタイムが大幅に劣化したのか。その答えは実行計画にあるのですね。はい、シナリオ通りで申し訳ないですが。。。
select * from table(dbms_xplan.display_cursor()) ; PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------- SQL_ID 72fq5cqt9753g, child number 0 ------------------------------------- select count(COL1),sum(mod(ora_hash(COL3),2)) from TBL11 where COL2 <= 100 Plan hash value: 2476470710 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 182 (100)| | | 1 | SORT AGGREGATE | | 1 | 104 | | | | 2 | TABLE ACCESS BY INDEX ROWID| TBL11 | 10000 | 1015K| 182 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IDX_TBL11_COL2 | 10000 | | 22 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("COL2"<=100)
実行計画を改めて確認してみると、TBL11表へアクセスするOperationが「TABLE ACCESS BY INDEX ROWID」に変化していることが確認できます。これは、その下に表示されているIDX_TBL11_COL2索引を「INDEX RANGE SCAN」した結果得られるROWIDを元に、TBL11表へアクセスしていることを意味しています。コストベース・オプティマイザはTBL11表を全件スキャン（TABLE ACCESS FULL）するよりも、索引を使用してアクセスした方が効率的と判断したことになります。しかし、SELECT文は1000001件の内、1000000件にアクセスすることになるので、演習1と同様にTABLE ACCESS FULLの方が効率的なのは明らかなのに。。。
と言う現実が、コストベース・オプティマイザとDBAが知り得る情報の違いによって発生しているのです。これは実行計画の選択時に見積もられたTBL11表でヒットするRowsの値から理解するこができます。1レコードを挿入する前のRowsの値は「1000K」でしたが、挿入後は「10000」に変化しています。つまり、TBL11表の全レコードである1000001件の内、10000件にだけヒットすると見積もられたので、TBL11表を全件スキャン（TABLE ACCESS FULL）するよりも、索引「IDX_TBL11_COL2」で絞り込んだ後にTBL11表へアクセスする方式をコストベース・オプティマイザが選択してしまったのです。
つまり、このRows「10000」を見積もってしまった部分に根本の問題があるのですね。では、この値はどのように計算されたのでしょうか。それはINSERTしたレコードのCOL2列の値にヒントがあります。その値は「1000000」であり、元々COL2列に格納されていた値の範囲「1~100」から大きく外れる値です。この「1000000」が格納された後にオプティマイザ統計情報を収集し直しているので、COL2列に含まれる値の範囲を「1~1000000」と認識できるようになっていますが、実は101~999999の値が入っていないという情報は持っていない為、オプティマイザは1~1000000が全レコード1000001件に『均等に分散している』として見積もることしかできないのです。つまりは、全1000001件（約100万件）のうち条件「COL2 <= 100」にヒットする件数は、100万件 × (100 / 100万) = 10000と見積もっているのです。
DBAは『均等に分散していない』という情報を知っているので、「TABLE ACCESS FULL」が効率的だと一瞬で判断できますが、データの偏りが存在するという情報を知らないコストベース・オプティマイザは適切な実行計画を選択できないのです。なので、このデータの偏りによる分散具合の情報「ヒストグラム統計」をオプティマイザにも追加で教えてあげる必要が出てくるのです。
■ 3. TBL11表のCOL2列のヒストグラム統計を追加で取得して下さい。
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TRY', -
tabname => 'TBL11', -
cascade => FALSE, -
method_opt => 'FOR COLUMNS COL2 SIZE AUTO');
select TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
from USER_TAB_COL_STATISTICS
where TABLE_NAME = 'TBL11' and COLUMN_NAME = 'COL2' ;
TABLE_NAME COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------------------------ ------------------------------ ------------ ---------------
TBL11 COL2 101 FREQUENCY
col COLUMN_NAME for a5
select TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE
from USER_TAB_HISTOGRAMS
where TABLE_NAME = 'TBL11' and COLUMN_NAME = 'COL2' ;
TABLE_NAME COLUM ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ----- --------------- --------------
TBL11 COL2 55 1
TBL11 COL2 113 2
TBL11 COL2 171 3
TBL11 COL2 237 4
TBL11 COL2 293 5
TBL11 COL2 334 6
TBL11 COL2 394 7
TBL11 COL2 450 8
TBL11 COL2 506 9
TBL11 COL2 576 10
TBL11 COL2 627 11
..... .... .... ..
TBL11 COL2 5337 96
TBL11 COL2 5402 97
TBL11 COL2 5452 98
TBL11 COL2 5510 99
TBL11 COL2 5570 100
100行が選択されました。
COL2列のヒストグラムを明示的に取得する方法は、統計収集取得時に「method_opt => ‘FOR COLUMNS COL2 SIZE AUTO’」を指定します。
対象の列にヒストグラムが存在するか否かの確認は、USER_TAB_COL_STATISTICSディクショナリ・ビューの「HISTOGRAM」列で確認することができます。存在しない場合には、「NONE」と表示されます。また、実際にどのようなヒストグラムが格納されているのかは、USER_TAB_HISTOGRAMSディクショナリ・ビューで確認することができます。
では、このヒストグラムによって、コストベース・オプティマイザが最適な実行計画を選択できるようになるのでしょうか、確認してみましょう。
■ 4. 演習1のSELECT文を実行し、実行計画からヒストグラムの効果を確認して下さい。
sqlplus / as sysdba
SQL>
alter system flush buffer_cache ;
alter system flush shared_pool ;
connect TRY/TRY
set timing on
select count(COL1),sum(mod(ora_hash(COL3),2)) from TBL11 where COL2 <= 100 ;
COUNT(COL1) SUM(MOD(ORA_HASH(COL3),2))
----------- --------------------------
1000000 530000
経過: 00:00:07.93
select * from table(dbms_xplan.display_cursor()) ;
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID 72fq5cqt9753g, child number 0
-------------------------------------
select count(COL1),sum(mod(ora_hash(COL3),2)) from TBL11 where COL2 <= 100
Plan hash value: 694736540
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4414 (100)| |
| 1 | SORT AGGREGATE | | 1 | 104 | | |
|* 2 | TABLE ACCESS FULL| TBL11 | 1000K| 99M| 4414 (1)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL2"<=100)
さあ、いかがですか？COL2列のヒストグラムを取得することで、期待通りコストベース・オプティマイザが正しく最適な実行計画を選択できるようになったので、TABLE ACCESS FULLが選択されるように元に戻ったと思います。実行時間も演習1とほとんど同じ程度に戻っていますよね。
今回は疑似的に発生させた表での性能トラブルを扱いましたが、現実のシステムにおいても格納されているデータの偏りが発生していることによって、同じような問題が発生することがあり得る可能性を体感して頂けたかと思います。よって、DBAはデータの偏りを把握してヒストグラム統計を取得するか否かを判断する必要があります。とは言え、何百という表が存在するようなデータベースで、何千種類ものSQL文が実行されるような環境では手に負えませんよね。そこで、Oracle Databaseではデータの偏りの存在可能性と実際のワークロード（SQL）の実績によってヒストグラム統計を取得するか否かを自動的に判断する仕組みを提供しています。それが先にもご紹介した「method_opt => ‘FOR ALL COLUMNS SIZE AUTO’」であり、DBMS_STATS.GATHER_XX_STATSプロシージャのデフォルトで設定（明示的に指定する必要無し）されています。今回は最後に、ワークロードの実績の確認方法をご紹介しておきます。
■ 5. DBMS_STATSパッケージのREPORT_COL_USAGEファンクションを用いて、TBL11表のCOL2列の使用実績（Where句でどのように使用されたのか）を確認せよ。
sqlpus TRY/TRY SQL> set trimspool on trim on pages 0 linesize 1000 long 1000000 longchunksize 1000000 select DBMS_STATS.REPORT_COL_USAGE(ownname => 'TRY', tabname => 'TBL11') from DUAL; LEGEND: ....... EQ : Used in single table EQuality predicate RANGE : Used in single table RANGE predicate LIKE : Used in single table LIKE predicate NULL : Used in single table is (not) NULL predicate EQ_JOIN : Used in EQuality JOIN predicate NONEQ_JOIN : Used in NON EQuality JOIN predicate FILTER : Used in single table FILTER predicate JOIN : Used in JOIN predicate GROUP_BY : Used in GROUP BY expression ............................................................................... ############################################################################### COLUMN USAGE REPORT FOR TRY.TBL11 ................................. 1. COL1 : EQ 2. COL2 : RANGE ###############################################################################
上記回答例では、DBMS_STATS.REPORT_COL_USAGEファンクションをTBL11表に対して実行することで、TBL11表の各列がどのようなWhere条件で使用されたのかという実績を確認することが可能であり、COL1列が「EQ」=「イコール条件」で、COL2列が「RANGE」=「範囲条件」で使用されたことを示しています。基本的にはOracle Databaseがヒストグラムを取得するか否かを自動判断する為の情報ですが、DBAにとっても、どの表の列がどのような条件に指定されているのかを把握できる便利な情報なので、是非とも覚えておいて頂きたいファンクションですね。ちなみに、これらの情報は一定周期でディクショナリへ書き込まれますが、そのタイミングを待たずに直ぐに反映させたい場合には「DBMS_STATS.FLUSH_DATABASE_MONITORING_INFOプロシージャ」をコールします。
DBMS_STATS.REPORT_COL_USAGEファンクションは新しいファンクションであり、マニュアル「PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス 11gリリース2(11.2)」には見当たりませんが、オプティマイザの開発チームのOracle Optimizerブログにも記載されているので、こちらでご紹介させて頂きました。
さて、今回はヒストグラムの必要性について体験して頂きましたがいかがでしたでしょうか。
ヒストグラムはより詳細なオプティマイザ統計情報の位置づけであり、より適切な実行計画を選択させる為には基本的には取得していて悪い情報ではありませんし、実はDBAが気付かない内にOracle Databaseでは自動的にヒストグラムが取得されていて、大きな問題の発生を回避してくれているかも知れませんね。
ただし、今回の演習では触れることができませんでしたが、このヒストグラムをトラブル無く完璧に使いこなすにはバインド変数と実行計画の関係について理解しておくことが必要不可欠です。これに関しては（変更するかもしれませんが）もう少し後の回でAdaptive Cursor Sharing機能と併せてご紹介できればと考えておりますので、ご期待下さい。
今回もありがとうございました。次回も頑張りますので、よろしくお願いします。