パフォーマンスモニタを利用した原因調査と、その結果によるチューニング実施の概要を、例を挙げて説明します。
なお、パフォーマンスモニタの詳細については、“5.2 パフォーマンスモニタ”を参照してください。
rdbpmsqllistコマンドを使用して、処理が長くなっているSQL文のアクセスプランと処理の内訳を表示した例および対策を以下に示します。
表示例
Symfoware Server Performance Monitor / SQL detailed information
Start time: 2008/10/16 11:21:03.332
End time: 2008/10/16 11:21:13.541
Running time: 10.209
Connection ID: 2008101015375000000458
Connection information:
Uid: I4874
Pid: 12521
Sid: -----
Type: TCP/IP
Name: 10.124.4.123/CONNECT1
Client information:
Client: u=UserID,i=RequestID,h=HostName
Module: IJServer01
Action: -----
Termination status:
Status: normal
Message Number: 2001
SQL statement:
SELECT C1 FROM USR1.TBL1 WHERE C2=100
Access plan:
Convert SQL statement:
SELECT TBL1.C1 FROM USR1.TBL1 WHERE TBL1.C2=?
Advice to an SQL statement:
JYP2401I 表の全件検索を行います．
===============================================================================
Main query
=sno===sectname=====input1==============input2==============output/update======
1 : SCAN [TBL1DSO ][ ][APPL ]
-------------------------------------------------------------------------------
[ 1] SCAN ELEMENT
table name USR1.TBL1
scan type TABLE ALL SCAN
dso name TBL1DSO [NONE/NONE]
condition evaluation Yes
scan record number 1
[ 2] OUTPUT ELEMENT
record length 23
Execution environment
-------------------------------------------------------------------------------
transaction access mode : READ WRITE
transaction isolation level : READ UNCOMMITTED
R_LOCK : YES
JOIN_RULE : AUTO
JOIN_ORDER : INSIDE
SCAN_KEY_ARITHMETIC_RANGE : YES
SCAN_KEY_CAST : YES
TID_SORT : YES
TID_UNION : YES
USQL_LOCK : SH
IGNORE_INDEX : NO
INACTIVE_INDEX_SCAN : YES
SAME_COST_JOIN_ORDER : ORDER
GROUP_COL_COND_MOVE : YES
CHOOSE_TID_UNION : NO
MAX_SCAN_RANGE : 1000
SS_RATE : 0.200000 0.250000 0.500000 0.400000 0.000100
Sampling status:
ACTIVE: 3
WAITING: 10
DB_READ: 10
対策
アクセスプランの情報を見ると、表“USR1.TBL1”からデータをとってくるだけの単純な検索処理です。SCANエレメントのアクセス方式を見ると“TABLE ALL SCAN”となっており、表から全レコードを取り出す処理が動作していることがわかります。表から全レコードを取り出す処理は、その表の最初のデータから最後のデータまでをくまなく参照することになるため、表に格納されているデータの量に依存して処理時間が長くなります。そのため、データ量の多い表に対しては避けるべきアクセス方式となります。
確認のためにサンプリングした実行状態の内訳を参照すると、“DB_READ”が10回となっており、多くのタイミングでデータベースからのデータ読込み待ちが発生していることがわかります。このことから、データ量の多い表からすべてのデータを読み込んで参照しているため時間が長くかかっていることが確認できます。
このようにデータ量の多い表に対して避けるべきものとしては、他にNESTED LOOP JOINエレメントがあります。
この問題は、適切なインデックスを付けることで解決します。SQL文のWHERE句を見てみると“C2”カラムでの検索条件がついています。しかし、この表には“C2”カラムにはインデックスを付けていませんでした。“C2”カラムにインデックスを付ければ、表に対する全データの読出し処理ではなく、インデックスを用いた高速な検索処理が動作するようになるため、性能は大幅に改善されます。
SQL文に記述されている検索条件やジョインの条件を見て、適切なインデックスを付けることで、このような問題は解決することができます。
ASSIST指定が無効となっているため、処理に時間がかかっている例および対策を以下に示します。以下の例では、アクセスプランにアドバイスが出力されています。
表示例
Symfoware Server Performance Monitor / SQL detailed information
Start time: 2008/10/16 11:21:03.332
End time: 2008/10/16 11:21:13.541
Running time: 10.209
Connection ID: 2008031015375000000458
Connection information:
Uid: I4874
Pid: 12521
Sid: -----
Type: TCP/IP
Name: 10.124.4.123/CONNECT1
Client information:
Client: u=UserID,i=RequestID,h=HostName
Module: IJServer01
Action: -----
Termination status:
Status: normal
Message Number: 2001
SQL statement:
SELECT /* ASSIST USE_INDEX(TBL1(TBL1IXDSO1)) */ C1 FROM USR1.TBL1 WHERE C1=80
Access plan:
Convert SQL statement:
SELECT /* ASSIST USE_INDEX(TBL1(TBL1IXDSO1)) */ TBL1.C1 FROM USR1.TBL1 WHERE
TBL1.C1=?
Advice to an SQL statement:
JYP2410I ASSIST要素“USE_INDEX”に指定されたインデックス“TBL1IXDSO1”が定義さ
れていません．
JYP2401I 表の全件検索を行います．
===============================================================================
Main query
=sno===sectname=====input1==============input2==============output/update======
1 : SCAN [TBL1DSO ][ ][APPL ]
-------------------------------------------------------------------------------
[ 1] SCAN ELEMENT
table name USR1.TBL1
scan type TABLE ALL SCAN
dso name TBL1DSO [NONE/NONE]
condition evaluation Yes
scan record number 1
[ 2] OUTPUT ELEMENT
record length 23
Execution environment
-------------------------------------------------------------------------------
transaction access mode : READ WRITE
transaction isolation level : READ UNCOMMITTED
R_LOCK : YES
JOIN_RULE : AUTO
JOIN_ORDER : INSIDE
SCAN_KEY_ARITHMETIC_RANGE : YES
SCAN_KEY_CAST : YES
TID_SORT : YES
TID_UNION : YES
USQL_LOCK : SH
IGNORE_INDEX : NO
INACTIVE_INDEX_SCAN : YES
SAME_COST_JOIN_ORDER : ORDER
GROUP_COL_COND_MOVE : YES
CHOOSE_TID_UNION : NO
MAX_SCAN_RANGE : 1000
SS_RATE : 0.200000 0.250000 0.500000 0.400000 0.000100
Sampling status:
ACTIVE: 3
WAITING: 10
DB_READ: 10
対策
アクセスプランのアドバイスをみると、ASSIST指定で指定したインデックスが未定義であることが分かります。可能性として、運用中に何らかの理由でインデックスが削除されたことが考えられます。
この問題は、適切なインデックスを付けることで解決します。SQL文のWHERE句を見てみると“C1”カラムでの検索条件がついています。しかし、この表にはアドバイスのとおり“C1”カラムにはインデックスは付いていません。“C1”カラムにインデックスを付ければ、表に対する全データの読出し処理ではなく、インデックスを用いた高速な検索処理が動作するようになるため、性能は大幅に改善されます。
適切なインデックスを使った検索を行っているにもかかわらず、処理に時間がかかっている例および対策を以下に示します。
表示例
Symfoware Server Performance Monitor / SQL detailed information
Start time: 2008/10/16 13:45:53.145
End time: 2008/10/16 13:45:59.627
Running time: 6.482
Connection ID: 2008101015375000000458
Connection information:
Uid: I4874
Pid: 12521
Sid: -----
Type: TCP/IP
Name: 10.124.4.123/CONNECT1
Client information:
Client: u=UserID,i=RequestID,h=HostName
Module: IJServer01
Action: -----
Termination status:
Status: normal
Message Number: 2001
SQL statement:
SELECT C2 FROM USR1.TBL1 WHERE C1=80
Access plan:
Convert SQL statement:
SELECT TBL1.C2 FROM USR1.TBL1 WHERE TBL1.C1=?
===============================================================================
Main query
=sno===sectname=====input1==============input2==============output/update======
1 : SCAN [TBL1IXDSO1 ][ ][SORT0001 ]
-------------------------------------------------------------------------------
[ 1] SCAN ELEMENT
table name USR1.TBL1
scan type INDEX KEY SCAN(1)
dso name TBL1IXDSO1 [REC/SH]
condition evaluation No
scan record number 1
[ 2] INSERT ELEMENT
table name SORT0001
insert record length 12
-------------------------------------------------------------------------------
2 : SCAN [SORT0001 ][TBL1DSO ][APPL ]
-------------------------------------------------------------------------------
[ 1] SCAN ELEMENT
table name SORT0001
scan type TABLE ALL SCAN
condition evaluation No
[ 2] SCAN ELEMENT
table name USR1.TBL1
scan type TABLE KEY SCAN
dso name TBL1DSO [REC/SH]
condition evaluation Yes
scan record number 1
[ 3] OUTPUT ELEMENT
record length 23
Execution environment
-------------------------------------------------------------------------------
transaction access mode : READ WRITE
transaction isolation level : REPEATABLE READ
R_LOCK : YES
JOIN_RULE : AUTO
JOIN_ORDER : INSIDE
SCAN_KEY_ARITHMETIC_RANGE : YES
SCAN_KEY_CAST : YES
TID_SORT : YES
TID_UNION : YES
USQL_LOCK : SH
IGNORE_INDEX : NO
INACTIVE_INDEX_SCAN : YES
SAME_COST_JOIN_ORDER : ORDER
GROUP_COL_COND_MOVE : YES
CHOOSE_TID_UNION : NO
MAX_SCAN_RANGE : 1000
SS_RATE : 0.200000 0.250000 0.500000 0.400000 0.000100
Sampling status:
ACTIVE: 1
WAITING: 5
LOCK: 5
対策
アクセスプランを見ると、アクセス方式は“INDEX KEY SCAN”となっており、適切にインデックスを使用した検索となっていることが確認できます。インデックスを検索したあとも、インデックスから取り出した情報をもとに“TABLE KEY SCAN”により正しく表へのアクセスが行われており、アクセスプラン自体には問題がないことがわかります。
遅くなっている原因を調べるために、サンプリングした実行状態の内訳を参照すると、処理中断の状態(WAITING)を5回検出しており、5回ともトランザクション占有待ち(LOCK)で待ちとなっていることがわかります。このことから、検索対象となっているレコードへのアクセスが、他のトランザクションのアクセスと競合したため、処理に時間がかかったことがわかります。rdbpmreportコマンドでこのSQL文が実行されていたときの資源の占有待ちに関する情報を確認することで、裏付けをとることができます。
対策として、以下が考えられます。
排他の単位が行になっていなければ行に変更する
トランザクションの独立性水準を変更する
排他の単位は、アクセスプラン情報の“R_LOCK”の項目で確認ができます。この例では“YES”となっているため、排他の単位は行になっており、問題ないことがわかります。
トランザクションの独立性水準は、アクセスプラン情報の“transaction isolation level”の項目でわかります。この例では“REPEATABLE READ”となっています。処理の論理上問題ないか否かを確認して、独立性水準を“READ UNCOMMITTED”に変更するということが対策として考えられます。