チューニングガイド

J2X1-7496-01Z0(00)

2010

年

1

月

Windows/Solaris/Linux

Symfoware Server V10.0.0

まえがき

本書の目的

本書は、アプリケーションの性能チューニングについて説明しています。

本書の読者

本書は、アプリケーションの性能チューニングを行う方に読んでいただくように書かれています。 本書を読むためには、以下の知識が必要です。

・

コンピュータについての一般的な知識

・

リレーショナルデータベースについての知識

・

Symfowareデータベースについての知識

・

SQLに関する知識

・

データベースシステムの性能チューニングに関する知識

・

業務に関する知識

本書の構成

本書の構成と内容は以下のとおりです。 第1章チューニングの概要 アプリケーションのチューニングの手順、チューニングの例を説明しています。 第2章パフォーマンスモニタ パフォーマンスモニタの使用方法を説明しています。 第3章原因の特定 性能が悪くなっている原因の特定方法を説明しています。 第4章チューニング 性能を改善するためのチューニング方法を説明しています。 付録A 性能チューニングのためのパラメタ一覧 性能チューニングのためのパラメタを説明しています。

コマンド操作例について

本書で説明しているコマンド操作例は、UNIX系システムを前提として記載しています。Windows(R)で操作する場合は、 以下の表に従って読み替えてください。 UNIX系での操作の場合 Windows(R)での操作の場合 /tmp/out_data E:\TMP\OUT_DATA /tmp/sqlfile.txt E:\TMP\SQLFILE.TXT /tmp/plan E:\TMP\PLAN /tmp/trace.log E:\TMP\TRACE.LOG /tmp/sql.snp E:\TMP\SQL.SNP /tmp/tbl.log E:\TMP\TBL.LOG /tmp/exec.log E:\TMP\EXEC.LOG /tmp/index.log E:\TMP\INDEX.LOG /tmp/statistics.log E:\TMP\STATISTICS.LOG

目　次

第1章チューニングの概要...1 1.1 チューニングの手順...1 1.2 原因の調査とチューニングの例...1 1.2.1 表の全件検索となっている場合...1 1.2.2 ASSIST指定が無効となっている場合...3 1.2.3 資源の占有待ちが多く発生している場合...4 1.2.4 入出力待ちが多く発生している場合...6 第2章パフォーマンスモニタ...8 2.1 パフォーマンスモニタの概要...8 2.2 パフォーマンスモニタの使用方法...8 2.2.1 処理時間が長いSQL文を特定する...9 2.2.2 SQL文のアクセスプランと実行状態の内訳を表示する...10 2.3 動作環境の設定変更...14 2.3.1 処理に時間がかかっていると判断する時間の変更...14 2.3.2 情報の保存期間の変更...15 2.3.3 システム情報の採取間隔の変更...15 2.4 動作環境の設定内容の確認...15 第3章原因の特定...17 3.1 アクセスプランを確認する...17 3.2 サンプリングした実行状態の内訳...19 第4章チューニング...21 4.1 アクセス方式のチューニング...21 4.2 ジョイン方法のチューニング...24 4.3 ジョイン順のチューニング...25 4.4 アドバイスに応じたチューニング...27 4.5 ソートサイズ、ワークサイズのチューニング...27 4.6 処理が待ち状態となっていた場合のチューニング...27 4.7 その他のチューニング...29 4.7.1 CASTを使用したSQL文のチューニング...29 4.7.2 トランザクション占有のチューニング...31 付録A 性能チューニングのためのパラメタ一覧...33 A.1 パラメタ一覧...33 索引...40

第

1

章

チューニングの概要

本章では、チューニングの手順を説明します。

1.1

チューニングの手順

アプリケーションの処理が遅いなど、アプリケーションの性能に問題が発生した場合には、動作環境やSQL文をチューニ ングする必要があります。 問題となっている事象の原因特定には、パフォーマンスモニタを使用します。 チューニングの手順を説明します。

1.2

原因の調査とチューニングの例

パフォーマンスモニタを利用した原因調査と、その結果によるチューニング実施の概要を、例を挙げて説明します。 なお、パフォーマンスモニタの詳細については、“第2章 パフォーマンスモニタ”を参照してください。

1.2.1

表の全件検索となっている場合

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: SQL Name: APL001/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 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文に記述されている検索条件やジョインの条件を見て、適切なインデックスを付けることで、このような問題は解決す ることができます。

1.2.2 ASSIST

指定が無効となっている場合

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: SQL Name: APL001/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 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”カラムにイ ンデックスを付ければ、表に対する全データの読出し処理ではなく、インデックスを用いた高速な検索処理が動作するよ うになるため、性能は大幅に改善されます。

1.2.3

資源の占有待ちが多く発生している場合

適切なインデックスを使った検索を行っているにもかかわらず、処理に時間がかかっている例および対策を以下に示し ます。

表示例

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: SQL Name: APL001/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

[ 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 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

1.2.4

入出力待ちが多く発生している場合

“1.2.3 資源の占有待ちが多く発生している場合”とは異なる理由で、適切なインデックスを使った検索を行っているにも

かかわらず、処理に時間がかかっている例および対策を以下に示します。

表示例

Symfoware Server Performance Monitor / SQL detailed information Start time: 2008/10/16 14:32:18.934 End time: 2008/10/16 14:32:23.166 Running time: 4.232 Connection ID: 2008101015375000000458 Connection information: Uid: I4874 Pid: 12521 Sid: Type: SQL Name: APL001/CONNECT1 Client information: Client: u=UserID,i=RequestID,h=HostName Module: IJServer01 Action: ---Termination status: Status: normal Message Number: 2001 SQL statement:

SELECT SUM(C2)FROM USR1.TBL1 WHERE C1 BETWEEN 20 AND 30 Access plan:

Convert SQL statement:

SELECT SUM(TBL1.C2) FROM USR1.TBL1 WHERE TBL1.C1 BETWEEN ? AND ?

=============================================================================== 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 2 [ 2] INSERT ELEMENT

table name SORT0001 insert record length 12

2 : SCAN [SORT0001 ][TBL1DSO ][ ] GROUP [ ][ ][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] GROUPING ELEMENT condition evaluation No [ 4] OUTPUT ELEMENT

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 SS_RATE : 0.200000 0.250000 0.500000 0.400000 0.000100 Sampling status: ACTIVE: 1 WAITING: 3 DB_READ: 3

対策

アクセスプランを見ると、アクセス方式は“INDEX KEY SCAN”となっており、適切にインデックスを使用した検索となって いることが確認でき、その後の処理も問題ないことがわかります。 サンプリングした実行状態の内訳を見ると、処理中断の状態(WAITING)を3回検出しており、3回ともデータベースから のページの読込み待ち(DB_READ)が原因であることがわかります。また、rdbpmreportコマンドでデータベーススペース の性能に関する情報を見ることで、データベースに関する入出力の動作状態を確認することができます。 対策として、以下が考えられます。

・

データベースバッファの設計を見直し、頻繁にアクセスされる資源に対するバッファの割当てを増やす

・

データベースのディスク配置を見直し、入出力の負荷を複数のディスクに分散させる

第

2

章

パフォーマンスモニタ

本章では、パフォーマンスモニタの使用方法を説明します。

2.1

パフォーマンスモニタの概要

パフォーマンスモニタは、SQL文の処理時間の監視を行い、処理に時間がかかっているSQL文を検出すると、そのSQL 文に関する情報を収集し、パフォーマンスデータ格納ファイルに蓄積します。処理に時間がかかっているSQL文に関す る情報以外にも、アプリケーションの動作状況、システム資源の使用情報、アプリケーションによって獲得される資源の占 有情報の定常的な採取を行い、同様にパフォーマンスデータ格納ファイルに蓄積します。 パフォーマンスデータ格納ファイルに蓄積されているデータは、rdbpmsqllistコマンドおよびrdbpmreportコマンドを用いて 取り出すことができます。 rdbpmsqllistコマンドでは、処理に時間がかかっているSQL文の一覧を表示し、さらに、そのSQL文を依頼したクライアン トの情報、アクセスプランの情報を調べることができます。 rdbpmreportコマンドでは、アプリケーションの動作状況、システム資源の使用情報、アプリケーションによって獲得される 資源の占有情報に関して、情報が保存されている期間の任意の時間の情報を取り出して表示することができます。 各コマンドを使用して、パフォーマンスデータ格納ファイルに蓄積されているデータを取り出すイメージを以下に示しま す。 これらの情報を活用することで、処理が遅くなっているSQL文を特定することができ、アクセスプランやシステム資源の状 態を用いて、処理が遅くなっている原因の調査と、対策の検討を行うことができます。

2.2

パフォーマンスモニタの使用方法

パフォーマンスモニタの使用方法を説明します。 以下の手順で使用します。

1.

処理時間が長いSQL文を特定する

2.

SQL文のアクセスプランと実行状態の内訳を表示する

2.2.1

処理時間が長い

SQL

文を特定する

rdbpmsqllistコマンドを使用し、処理時間が長いSQL文から順番に一覧を表示します。 コマンドの実行例と表示内容を以下に示します。 $ rdbpmsqllist

Symfoware Server Performance Monitor / SQL list information

Period of analysis: 2008/10/15 08:00:00 - 2008/10/15 11:48:33 (1) (2) (3) (4) (5) (6) time connection-id running-time status SQL

2008/10/15-09:18:19.598 2008101015375000000315 13.856 normal SELECT COUNT(*)FROM USR1.TBL1 2008/10/15-11:33:18.311 2008101015375000000118 1.438 normal UPDATE USR1.TBL8 SET COL1=COL1+1 2008/10/15-10:08:02.819 2008101015375000000290 1.192 normal COMMIT (1) SQL文の処理時間の解析を行った対象の期間 (処理時間が長いSQL文の調査を行う対象とした期間です。 遅い処理が発生した時間帯が判っている場合には、対象となる期間を指定することで、 情報を絞り込むことができます。 対象となる期間を指定するには、rdbpmsqllistコマンドのpオプションを使用します。) (2) SQL文の開始時刻 (SQL文の実行を開始した時刻です。表示形式は、“西暦/月/日-時:分:秒.ミリ秒”です。) (3) SQL文を実行したコネクションのコネクションID (コネクションIDはSymfoware/RDBが自動的に採番して割り当てます。 コネクションIDおよびSQL文の開始時刻をrdbpmsqllistコマンドのcオプションおよびtオプションに 指定して実行すると、コネクションおよびSQL文に関する詳細な情報を表示することができます。) (4) SQL文の処理時間 (Symfoware/RDBがSQL文を処理していた時間です。単位は秒.ミリ秒です。 一覧はこの時間が長い順番にソートされて表示されています。) (5) SQL文の終了ステータス normal : 正常に終了した error : エラーで終了した canceled : タイムアウトやrdbtermコマンドにより処理が中断した (6) SQL文 (実行したSQL文です。 先頭から50文字以内を表示します。 英小文字は英大文字で表示します。 SQL文の前後の空白は削除して表示します。 SQL文中の冗長な空白は削除して表示します。) この例では、2008年10月15日の9時18分19秒に実行した単一行SELECT文で約14秒の時間がかかっていることがわか ります。 このSQL文の処理が長かった原因を調査するための詳細な情報を取得する方法については、“2.2.2 SQL文のアクセス プランと実行状態の内訳を表示する”を参照してください。

注意

・

処理が完了していないSQL文に関する情報は表示されません。動作中のSQL文に関する情報を参照するには、その SQL文の処理の完了を待ってから、情報の表示を行ってください。

・

採取対象となるSQL文は、以下のとおりです。

－

CALL文

－

COMMIT文

－

DELETE文:位置づけ

－

DELETE文:探索

－

INSERT文

－

OPEN文

－

ROLLBACK文

－

単一行SELECT文

－

UPDATE文:位置づけ

－

UPDATE文:探索

－

問合せ式(Java連携、ODBC連携または.NET Framework連携のアプリケーションの場合)

・

採取対象のSQL文がOPEN文の場合、カーソルが閉じられるまでにSymfoware Serverがそのカーソルを処理するの にかかった時間が、処理時間となります。

2.2.2 SQL

文のアクセスプランと実行状態の内訳を表示する

処理時間が長いSQL文に対して、原因を調査するため、SQL文のアクセスプランと実行状態の内訳を表示します。“2.2.1 処理時間が長いSQL文を特定する”で表示されたコネクションIDおよびSQLの開始時刻をrdbpmsqllistコマンドのcオプ ションおよびtオプションに指定して実行します。

アクセスプラン

アクセスプランは、SQL文を実行するときのデータベースへのアクセス手順を表現したものです。データベースの同じ資 源に対してアクセスを行うときでも、アクセス手順には複数の方法があり、それが適切に選択されていない場合には、処 理時間が大幅に長くなることがあります。

参照

アクセス手順にどのようなものがあり、それらがアクセスプランでどのように表現されるかの詳細については、“アプリケー ション開発ガイド（共通編）”の“アプリケーションの性能”を参照してください。

実行状態の内訳

サンプリングした実行状態の内訳は、SQL文が実際に動作した状況をサンプリングしたものです。 一定時間間隔で処理状態を確認し、集計した結果です。どのような処理に時間がとられていたのかということを判断する ためのヒントになります。 SQL文のアクセスプランと実行状態の内訳の表示例を説明します。 $ rdbpmsqllist -c 2008102015375000000315 -t 2008/10/15-09:18:19.598 Symfoware Server Performance Monitor / SQL detailed information

Start time: 2008/10/15 09:18:19.598 (1) End time: 2008/10/15 09:18:33.454 (2) Running time: 13.856 (3) Connection ID: 2008101015375000000315 (4) Connection information: Uid: I4874 (5) Pid: 23045 (6) Sid: --- (7) Type: SQL (8) Name: APL001/CONNECT1 (9) Client information: Client: u=UserID,i=RequestID,h=HostName (10)

Module: IJServer01 (11) Action: --- (12) Termination status: Status: normal (13) Message Number: 2001 (14) SQL statement: (15) SELECT COUNT(*)FROM USR1.TBL1

Access plan: (16) Convert SQL statement:

SELECT COUNT(*) FROM USR1.TBL1 Advice to an SQL statement: JYP2401I 表の全件検索を行います． =============================================================================== Main query =sno===sectname=====input1==============input2==============output/update====== 1 : SCAN [TBL1DSO ][ ][ ] GROUP [ ][ ][APPL ] [ 1] SCAN ELEMENT

table name USR1.TBL1 scan type TABLE ALL SCAN

dso name TBL1DSO [NONE/NONE] condition evaluation No

scan record number 10 [ 2] GROUPING ELEMENT condition evaluation No [ 3] OUTPUT ELEMENT record length 8 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 SS_RATE : 0.200000 0.250000 0.500000 0.400000 0.000100 Sampling status: (17) ACTIVE: 2 WAITING: 11 LOCK_TEMPORARY: 1 DB_READ: 10 (1) SQL文の開始時刻 (SQL文の実行を開始した時刻です。表示形式は、“西暦/月/日 時:分:秒.ミリ秒”です。) (2) SQL文の終了時刻 (SQL文の実行が終了した時刻です。表示形式は、“西暦/月/日 時:分:秒.ミリ秒”です。) (3) SQL文の処理時間 (Symfoware/RDBがSQL文を処理していた時間です。単位は秒.ミリ秒です。 途中で処理を中断し、別のSQL文の処理を行ったあとで、再開することもあるため、 処理時間はSQL文の終了時刻とSQL文の開始時刻の差よりも小さくなることがあります。) (4) SQL文を実行したコネクションのコネクションID (コネクションIDはSymfoware/RDBが自動的に採番して割り当てます。)

(5) ログイン名 (6) プロセスID (コネクションの接続を行っているアプリケーションプロセスのプロセスIDです。 トランザクションモニタ配下のアプリケーションの場合は、このSQL文の実行を依頼した クライアントプロセスのプロセスIDになります。) (7) セションID (マルチスレッド環境で動作しているアプリケーションのセションIDです。 マルチスレッド環境で動作していない場合には、“---”となります。) Solaris/Windowsの場合 (8) クライアントプロセス種別 SQL : システム内通信のアプリケーション SQL-XA : トランザクションモニタ配下のアプリケーション TCP/IP : RDB2_TCP接続のアプリケーション RDA-SV : RDA-SV接続のアプリケーション ODOS : ODOS連携のODBCアプリケーション (9) モジュール名 システム内通信のアプリケーション : アプリケーションロードモジュール名/コネクション名 (アプリケーションロードモジュール名が15バイトを超える場合は、 先頭の15バイトのみが出力されます。) トランザクションモニタ配下のアプリケーション : データベース名/コネクション名 (トランザクションモニタ配下のアプリケーションのデータベース名/ コネクション名は、OPEN-INFO文字列に指定された値が表示されます。) RDB2_TCP接続のアプリケーション : 接続端末のIPアドレス/コネクション名 RDA-SV接続のアプリケーション : 接続端末のIPアドレス/コネクション名 ODOS連携のODBCアプリケーション : 接続端末のIPアドレス/データソース名または アプリケーションのロードモジュール名/データソース名 Linuxの場合 (8) クライアントプロセス種別 SQL : システム内通信のアプリケーション SQL-XA : トランザクションモニタ配下のアプリケーション TCP/IP : RDB2_TCP接続のアプリケーション ODOS : ODOS連携のODBCアプリケーション (9) モジュール名 システム内通信のアプリケーション : アプリケーションロードモジュール名/コネクション名 (アプリケーションロードモジュール名が15バイトを超える場合は、 先頭の15バイトのみが出力されます。) トランザクションモニタ配下のアプリケーション : データベース名/コネクション名 (トランザクションモニタ配下のアプリケーションのデータベース名/ コネクション名は、OPEN-INFO文字列に指定された値が表示されます。) RDB2_TCP接続のアプリケーション : 接続端末のIPアドレス/コネクション名 ODOS連携のODBCアプリケーション : 接続端末のIPアドレス/データソース名または アプリケーションのロードモジュール名/データソース名

(10) クライアント情報

(Interstage Application Serverと連携している場合、Webサーバ接続情報が設定されます。 (設定される情報の形式は、Interstage Application Serverのマニュアルを参照してください。))

(Interstage Application Serverと連携していない場合、“---”が表示されます。)

(11) モジュール情報

(Interstage Application Serverと連携している場合、IJServer名が設定されます。

(設定される情報の形式は、Interstage Application Serverのマニュアルを参照してください。))

(Interstage Application Serverと連携していない場合、“---”が表示されます。)

(12) アクション情報 (常に“---”が表示されます。) (13) SQL文の終了ステータス normal: 正常に終了した error: エラーで終了した canceled: タイムアウトやrdbtermコマンドにより処理が中断した (14) メッセージ変数に設定されるメッセージの番号 (表示されるのは数字部分の4桁になります。) (rdbprtmsgコマンドのjypオプションにより、メッセージに関する情報を表示できます。) SQL文の終了ステータスが“canceled”の場合には、“---”となります。 (15) SQL文 (実行したSQL文です。 先頭から50文字以内を表示します。 英小文字は英大文字で表示します。 SQL文の前後の空白は削除して表示します。 SQL文中の冗長な空白は削除して表示します。) (16) アクセスプラン SQLのアクセスプランです。 表示項目の詳細に関しては、“SQLTOOLユーザーズガイド”を参照してください。 (17) サンプリングした実行状態の内訳 SQL文が動作中のときにサンプリングした、処理状態の内訳です。 一定時間間隔で処理状態を確認し集計した結果です。 ACTIVE: 動作中の状態を検出した回数 WAITING: 入出力処理待ち、資源の占有待ちなどにより処理が一時的に中断中の状態を検出した回数 処理状態にWAITINGがある場合には、さらに待ち状態となった原因に関する内訳が その後に続きます。 LOCK : トランザクション占有待ちを検出した回数 LOCK_TEMPORARY : トランザクション一時占有待ちを検出した回数 DB_BUFFER : 共用バッファの空き待ちを検出した回数 DB_READ : ページ読込み待ちを検出した回数 DB_WRITE : ページ書込み待ちを検出した回数 DB_WRITE_TRNEND : トランザクション終了時のページ書込み待ちを検出した回数 DB_SEND : メモリ常駐ページの待機系システムへの送信待ちを検出した回数 LOG_BI_BUFFER : BIログバッファの空き待ちを検出した回数 LOG_AI_BUFFER : AIログバッファの空き待ちを検出した回数 LOG_RCP : リカバリログ量の減少待ちを検出した回数 LOG_BI_READ : BIログ読込み待ちを検出した回数 LOG_BI_WRITE : BIログ書込み待ちを検出した回数 LOG_AI_WRITE : AIログ書込み待ちを検出した回数 LOG_ARC_FULL：アーカイブログファイルの満杯状態の解消待ちを検出した回数 COM_BUFFER : RDBシステム間通信の通信バッファの空き待ちを検出した回数 COM_SEND : RDBシステム間通信の送信待ちを検出した回数 COM_RECV : RDBシステム間通信の受信待ちを検出した回数 OTHER : 上記以外の待ちを検出した回数 アクセスプランの内容を参照することで、どのデータベース資源に対して、どのようなアクセスを行っているかを見ることが できます。

この例では、表の全件検索が行われていることがわかります。 表の全件検索では、表に格納されているすべてのデータを参照するため、表に格納されているデータ量に応じて処理 時間がかかります。また、表に格納されているすべてのデータがメモリ上に読み込まれていることも少ないため、データの 読込み処理が多く発生し、さらに時間がかかることになります。このことは、サンプリングした実行状態の内訳に記載され ている、13回中10回がデータベースからのデータ読込み待ちで止まっているという状況からも見てとれます。 このように、アクセスプランの情報や、サンプリングした実行状態の内訳を見ることで、どのような点に問題があるのかを判 断することができます。

注意

以下のSQL文ではアクセスプランは表示されません。

・

CALL文

・

COMMIT文

・

ROLLBACK文

2.3

動作環境の設定変更

パフォーマンスモニタの動作環境の変更を説明します。

2.3.1

処理に時間がかかっていると判断する時間の変更

標準の設定では、1秒以上の処理時間がかかっているSQL文を処理に時間がかかっているSQL文であると判断します。 しかし、全体的にアクセスデータ量が多く、SQL文の処理時間が通常の状態でも1秒を超えることがあるようなシステムも 考えられます。そのようなシステムでは、性能的になんら問題はないのに、アクセスプランの情報が採取されてしまい、パ フォーマンスデータ格納ファイルを格納する領域として、必要以上に多くのディスクを使用してしまうことになります。 そのような場合には、処理に時間がかかっていると判断する時間を1秒より大きな値に変更することで、ディスク領域の無 駄を減らすことができます。 処理に時間がかかっていると判断する時間を変更するには、まず、性能異常と判断する処理時間を決定してください。 次に、Symfoware/RDBが停止している状態で、rdbpmsetupコマンドのtオプションにより決定した時間に変更してくださ い。Symfoware/RDBが動作しているときには、変更できません。 例1 処理に時間がかかっていると判断する時間を3秒に変更する場合 $ rdbstop $ rdbpmsetup -t 3000 逆に、軽い処理ばかりのシステムの場合、1秒未満でも処理に時間がかかりすぎているということもあります。そのような場 合には、処理に時間がかかっていると判断する時間を1秒よりも小さい値に設定することもできます。その場合には、実行 状態の内訳をサンプリングする間隔も合わせて小さい値に変更してください。実行状態の内訳をサンプリングする間隔は rdbpmsetupコマンドのsオプションにより変更します。 例2 処理に時間がかかっていると判断する時間を800ミリ秒に、実行状態をサンプリングする間隔を200ミリ秒に変更する 場合 $ rdbstop $ rdbpmsetup -t 800 -s 200

注意

実行状態をサンプリングする間隔を短くすると、パフォーマンスデータ格納ファイルを配置するディスクに必要な容量 が多くなります。“セットアップガイド”の“資源の見積り式”を参照して、ディスクに必要な容量を算出し、十分な容量の ディスクを用意してください。

2.3.2

情報の保存期間の変更

パフォーマンスモニタは、収集した情報をパフォーマンスデータ格納ファイル内のデータとして、ディスク上に保存しま す。データの保存期間は、標準の設定では24時間となります。アプリケーションのレスポンス異常を検出してから24時間 以内に調査を行うことができない場合には、情報の保存期間を変更することで、調査に必要な情報を保存している期間 を延ばすことができます。

注意

情報の保存期間を変更すると、パフォーマンスデータ格納ファイルを配置するディスクに必要な容量も変化します。“セッ トアップガイド”の“資源の見積り式”を参照して、ディスクに必要な容量を算出し、十分な容量のディスクを用意してくださ い。 情報の保存期間を変更するには、Symfoware/RDBが停止している状態で、rdbpmsetupコマンドのrオプションで行いま す。 例 情報の保存期間を48時間に変更する場合 $ rdbstop $ rdbpmsetup -r 48

2.3.3

システム情報の採取間隔の変更

標準の設定では、30秒間隔でアプリケーションの動作状況、システム資源の使用情報、アプリケーションによって獲得さ れる資源の占有情報の採取を行います。採取間隔は、通常変更する必要はありません。 なんらかの理由により変更する必要が生じた場合には、Symfoware/RDBが停止している状態で、rdbpmsetupコマンドのi オプションにより変更してください。 例 システム情報の採取間隔を60秒に変更する場合 $ rdbstop $ rdbpmsetup -i 60

2.4

動作環境の設定内容の確認

パフォーマンスモニタが現在どのような設定内容で動作しているかを確認する方法は、rdbpmsetupコマンドのqオプション により行います。 例 パフォーマンスモニタの動作環境の状態表示 $ rdbpmsetup -q Threshold time : 1000 (1) Status sampling interval : 1000 (2) Retention period : 24 (3)

System information collecting interval : 30 (4) (1) SQL文のアクセスプラン情報を収集する基準時間(単位はミリ秒) (2) SQL文の処理状態をサンプリングする時間間隔(単位はミリ秒) (3) 採取した情報の保存期間(単位は時間) (4) アプリケーションの動作状況、システム資源の使用情報、およびアプリケーションによって 獲得される資源の占有情報を採取する時間間隔(単位は秒)

第

3

章

原因の特定

パフォーマンスモニタで取得したアクセスプランおよびサンプリングした実行状態の内訳から、処理が長くなっている原 因を特定します。 本章では、原因を特定する方法を説明します。

3.1

アクセスプランを確認する

アクセスプランの確認ポイントとしては、以下があります。

・

アクセス方式を確認する 処理に時間がかかるアクセス方式が選択されていないか、インデックスが適切に使用されているかを確認します。

・

ジョイン方法を確認する 適切なジョイン系アクセスモデルが選択されているかを確認します

・

アドバイスを確認する SQL文に対するアドバイスを確認します。また、ASSIST指定が有効となっているかを確認します。

・

ソート作業域、ワーク作業域を確認する ソート作業域、ワーク作業域が不足していないかを確認します。

参照

アクセスプランの出力形式や出力内容は、“SQLTOOLユーザーズガイド”を参照してください。

アクセス方式を確認する

アクセスプランのSCANエレメントのscan typeでアクセス方式を確認します。 scan typeの表示内容 アクセス方式

TABLE KEY SCAN 表をキー値で検索

TABLE ALL SCAN 表の全文検索 TABLE PARALLEL

SCAN

表の全件を並列に検索

INDEX KEY SCAN(n) インデックス検索

INDEX ALL SCAN インデックスの全件を順検索 INDEX TABLE SCAN(n) インデックスをキー範囲検索しながら表 のレコードも取り出す 処理に時間がかかるアクセス方式が選択されていないか、インデックスが適切に使用されているかを確認します。 適切なアクセス方式でない場合には、アクセス方式をチューニングします。 アクセス方式のチューニング方法については、“4.1 アクセス方式のチューニング”を参照してください。

ジョイン方法を確認する

アクセスプランのエレメント情報で、ジョイン方法を確認します。

エレメント名の表 示内容 意味 特長 MERGE JOIN マージジョインを行う 複数の表をジョインキー(ジョインで使用する列)の データの昇順に読み込み、ジョインキーの等しい 行を結合します。 FETCH JOIN フェッチジョインを行う 一方の表のレコードごとに、ジョインキーで他方の 表へのアクセスを繰り返すため、大量データの検 索を行う場合に十分な共用バッファがないと、結合 される側の表のI/O効率が低下します。一般的に は、大量データのジョインには向きません。反面、 少量データのジョインには向いています NESTED LOOP JOIN ネスティドループジョイ ンを行う ジョインする表のすべてのレコードを組み合わせて 結合を行うため、ジョイン結果を求めるのに時間が かかります。 ジョイン方法の特長を考慮し、適切なジョイン方法が選択されているかを確認します。ジョイン方法の選択が適切でない 場合には、ジョイン方法をチューニングします。

参照

ジョイン方法についての詳細は、“アプリケーション開発ガイド(共通編)”を参照してください。 また、チューニング方法については、“4.2 ジョイン方法のチューニング”、“4.3 ジョイン順のチューニング”を参照してくだ さい。

アドバイスを確認する

アクセスプランの“Advice to an SQL statement”で、アドバイスを確認します。 アドバイスは、以下の場合に出力されます。

・

ASSIST指定が無効となっている場合

・

SQL文に対するアドバイスがある場合 アドバイスが出力されている場合には、出力された内容に応じて、チューニングを実施します。 チューニング方法については、“4.4 アドバイスに応じたチューニング”を参照してください。

ソート作業域、ワーク作業域を確認する

アクセスプランのセクション情報で、出力先の資源名を確認します。 出力先の資源名として、以下が表示されている場合は、ソート作業域、ワーク作業域が使用されています。

・

SORTxxxx

・

WORKxxxx 次に、SQL性能情報の実行エレメント情報に出力される以下の内容を確認します。 実行エレメント名 詳細情報 出力内容 SORT SCAN/ SORT INSERT SCAN file size ソート処理時の二次記憶使用量 run merge マージした回数 WORK SCAN/ WORK INSERT SCAN file size ソート処理時の二次記憶使用量

参照

SQL性能情報については、“SQLTOOLユーザーズガイド”を参照してください。 ソート処理などを行う場合に、二次記憶を大量に使用していることで性能が劣化している可能性があります。また、ソート 処理時に、2回以上のマージ処理が行われていることで、メモリが不足し、性能が悪くなっている可能性があります。これ らの場合には、ソートサイズ、ワークサイズのチューニングを実施します。 チューニング方法については、“4.5 ソートサイズ、ワークサイズのチューニング”を参照してください。

3.2

サンプリングした実行状態の内訳

データベースのデータを参照するには、ディスク上にあるデータをメモリに読み込む必要があります。ディスクからデータ を読み込むためには、それなりの時間がかかるため、その読込み処理が完了するまではデータの参照を行うことができ ません。他にも、参照しようとしているデータを他のトランザクションが更新している可能性があります。適切に整合性のと れたデータを参照するためには、他のトランザクションの更新処理が完了するのを待つ必要があります。このように、デー タベースのデータを参照したり、更新したりするためには、様々な状態に対応して、処理を一時的に中断して待ち状態に なることがあります。サンプリングした実行状態の内訳を見ることで、発生した状況や、どのような理由で処理が待ち状態 になっていたのかを推測することができます。 サンプリングした実行状態の内訳で、待ち状態(WAITING)となった回数と待ち状態となった原因を確認します。 待ち状態(WAITING)の場合の表示内容と発生した状況を以下に示します。 表示内容 待ち状態となった原因 状況 LOCK トランザクションの占有 待ち 検索対象となっているレコードへのアクセスが、他 のトランザクションのアクセスと競合したため、待ち 状態になったことを示します。rdbpmreportコマンド で、SQL文が実行されていたときの資源の占有待 ちに関する情報を確認することができます。 LOCK_TEMPO RARY トランザクションの一時 占有待ち ページ長が大きくかつレコード長が短い、というよ うな1つのページの中に存在するレコードの数が多 い場合に発生しやすくなります。 DB_BUFFER 共用バッファの空き待 ち 共用バッファプール、あるいはデフォルトバッファ プールに未使用のページがなくなったときに発生 します。 DB_READ ページ読込み待ち データベースからのページの読み込み待ちが発 生しています。rdbpmreportコマンドで、データベー スに関する入出力の動作状況を確認できます。 DB_WRITE ページ書込み待ち 共用バッファ上でページを変更しようとする際に、 当該ページがデータベーススペースへ書き込み中 であったため、書き込みが完了するまで待ちになっ たことを示します。 DB_WRITE_T RNEND トランザクション終了時 のページ書込み待ち トランザクションのロールバック、またはリカバリなし の利用規定を設定しているDSIのデータ変更を行っ たトランザクションのコミットで発生する場合があり ます。 LOG_BI_BUFF ER BIログバッファの空き 待ち テンポラリログファイルのBIログバッファの空き待ち が発生したことを示します。 LOG_AI_BUFF ER AIログバッファの空き 待ち テンポラリログファイルのAIログバッファの空き待ち が発生したことを示します。 LOG_RCP リカバリログ量の減少 待ち テンポラリログファイルのAIログ量がリカバリログ量 を超過したため、リカバリログ量が減少するまで待 ちとなったことを示します。

表示内容 待ち状態となった原因 状況

LOG_BI_READ BIログ読込み待ち テンポラリログファイルのBIログ域への読込み待ち が発生したことを示します。 LOG_BI_WRIT E BIログ書込み待ち テンポラリログファイルのBIログ域への書込み待ち が発生したことを示します。 LOG_AI_WRIT E AIログ書込み待ち テンポラリログファイルのAIログ域への書込み待ち が発生したことを示します。 LOG_ARC_FU LL アーカイブログファイル の満杯状態の解消待 ち 動作環境ファイルにARC_FULL=WAITを指定し ている場合に、アーカイブログファイルが満杯に なったため、アーカイブログファイルのバックアップ または破棄が行われるまで待ち状態となったことを 示します。 処理が待ち状態となっていた場合のチューニング方法については、“4.6 処理が待ち状態となっていた場合のチューニ ング”を参照してください。

参照

以下の項目についての詳細は、“クラスタ導入運用ガイド”を参照してください。

・

DB_SEND

・

COM_BUFFER

・

COM_SEND

・

COM_RECV

第

4

章

チューニング

処理が長くなっている原因を特定後、性能を改善するためのチューニングを実施します。 本章では、特定した原因に応じたチューニング方法を説明します。

4.1

アクセス方式のチューニング

表の全件検索が行われている場合

アクセスプランに表示される以下を参考にして、インデックスを使用した検索処理に変更します。

・

CONVERT SQL Statement

・

Advice to an SQL statement

インデックスのチューニング

Symfoware/RDBは、検索に利用できるインデックスが複数存在する場合に、検索範囲の最も小さいインデックスを採用 します。検索範囲の大きさは、WHERE探索条件に指定した条件と、インデックスに設定している最適化情報の異なる キー値数から計算しています。 すでに、インデックス検索を実施しているが、使用するインデックスを変更したい場合のチューニング方法を説明します。 比較述語に比較演算子“=”を指定している場合 インデックスの検索範囲は、インデックスのデータ部の“1/異なるキー値数”の範囲を検索すると計算します。したがっ て、異なるキー値数の大きいインデックスの方が検索範囲が狭いと考え、優先的に選択されます。 例1 インデックスAの検索範囲の大きさは1/10で全体の10%となります。一方、インデックスBの検索範囲の大きさは1/5 で全体の20%となるので、インデックスAが選択されます。もし、インデックスBを優先的に選択させたい場合は、イ ンデックスBの異なるキー値数を10より大きくすることで、インデックスAよりも優先して使用されるようになります。 例2 マルチカラムインデックスの場合

マルチカラムインデックスの場合は、比較述語の比較演算子“=”条件が続く部分までの1/異なるキー値数が検索 範囲の大きさとなります。上記例では、インデックスAの検索範囲の大きさは1/10で10%となります。なお、インデッ クスの途中の列に条件を指定しない場合は、それ以降の列に条件を指定しても検索範囲の大きさは変わりませ ん。 IN述語を指定している場合の検索範囲は、(1/異なるキー値数)×IN値リスト数となります。同じ列に対して、比較 述語“=”をORで複数指定した場合も同じです。 比較述語“=”以外を指定している場合 BETWEEN述語や比較述語“>(=)”や“<(=)”、LIKE述語を指定している場合は、それぞれの述語に応じた固定的な 検索範囲の割合(選択率といいます)で検索範囲の大きさを決定します。各述語ごとの選択率は、デフォルトで次のよ うになっています。 述語の種類 選択率 BETWEEN 0.2 (20%) >(=)と<(=)の両方をANDで指定 0.25 (25%) LIKE述語を指定 0.4 (40%) >(=)または<(=)のみを指定 0.5 (50%) これらの値は、最適化パラメタ“SS_RATE”で変更することができます。

参照

“SS_RATE”の詳細については、“SQLTOOLユーザーズガイド”の“CHANGE ENV文”を参照してください。 インデックスAの検索範囲の大きさは全体の20%となります。一方、インデックスBの検索範囲の大きさは全体の40% となるので、インデックスAが選択されます。実際には、LIKE述語の方が絞り込める確率が高い場合は、最適化パラ メタ“SS_RATE”でLIKE述語の選択率を20%より小さくすると、インデックスBが優先して使用されるようになります。

同じ述語を指定した場合は、異なるキー値数の大きいインデックスが優先されます。この場合は、インデックスAが選 択されます。インデックスBを選択したい場合は、インデックスBの異なるキー値数を100より大きい値に変更するか、 または、次のようにSQL文を書き換えてください。 SELECT * FROM SCM1.TBL1 WHERE C1 >=1 AND C1 <=30 AND C2 BETWEEN 1 AND 20

上記のようにSQL文を変更すると、インデックスAの検索範囲は25%となり、インデックスBが優先して選択されます。 マルチカラムインデックスの場合は、比較述語の比較演算子“=”条件が続く部分までの1/異なるキー値数に、比較述 語“=”以外を指定した述語の選択率を掛けたものが検索範囲の大きさとなります。この場合は、1/10×0.2で2%の大 きさとなります。 ASSIST指定によるチューニング 最適化情報や最適化パラメタを変更すると、他のアプリケーションやSQL文に悪影響がでる場合があります。このよう な場合、ASSIST指定の“USE_INDEX”を利用すると、最適化情報や最適化パラメタを変更することなく、検索で使用 するインデックスを固定化することができます。 最適化パラメタについては、“付録A 性能チューニングのためのパラメタ一覧”を参照してください。

参照

ASSIST指定の使用方法については、“アプリケーション開発ガイド(共通編)”を参照してください。

ポイント

検索に使用できるインデックスが複数ある場合は、次の優先順位で使用するインデックスを決定しています。

a.

検索範囲が最も小さいインデックスを優先する

b.

(a)で同じものが複数あれば、インデックスで判定できる条件の多いものを優先する。

c.

(b)で同じものが複数あれば、検索範囲に使用した構成列までの異なるキー値数の大きいものを優先する。

4.2

ジョイン方法のチューニング

ジョインには、マージジョイン、フェッチジョイン、ネスティドループジョインの3種類のジョイン方法があります。ネスティド ループジョインは、異なる表間の列を比較述語“=”で指定していない場合に動作します。マージジョインは大量のデータ をジョインする場合に有効な手法です。フェッチジョインは、OLTP業務での少量データ検索に有効なジョイン手法です。 適切なジョイン方法を選択する ジョインの方法は、Symfoware/RDBが自動的に選択しますが、最適化パラメタ“JOIN_RULE”で、マージジョインを優 先して選択するか、フェッチジョインを優先して選択するかを指定することができます。 ただし、ASSIST指定のジョインASSISTによるチューニングを行わない場合、フェッチジョインを有効にするためには、 表の結合列にインデックスを定義しているか、表の結合列がクラスタキーになっていなければなりません。インデック スやクラスタキーになっていない場合は、“JOIN_RULE”でフェッチジョインを優先するように指定しても、マージジョ インが選択されるので注意が必要です。

参照

ASSIST指定のジョインASSISTによるチューニングの詳細については“アプリケーション開発ガイド(共通編)”を参照 してください。 フェッチジョインにおけるジョインする方向 フェッチジョインでは、ジョインする方向により、性能値が左右されます。フェッチジョインの軸となる表は、なるべくレ コード件数が絞り込まれている表である方が性能は良くなります。 上記の例で、TBL1側が“TBL1.C2 BETWEEN 10 AND 20”で十分に絞り込まれるのであれば、TBL1→TBL2の順 でジョインを行った方が性能が良くなります。Symfowareが選択するアクセスモデルの精度を上げるには、IX-2を BETWEEN述語で検索するときに、何パーセントぐらいのデータを検索するのかがなるべく正確である方が良いです 。 Symfowareがアクセスプランを選択したときの読み込むレコード件数の見積もり結果はアクセスプランに出力されるの で、性能情報で取得される実際の検索結果件数と大きく異なる場合は、インデックスの異なるキー値数を変更した り、“SS_RATE”による選択率を変更して、実際に検索する件数に近くなるようにチューニングすると、Symfowareのア クセスプランの選択精度が向上します。上記の例で、もしTBL2→TBL1の順でジョインしているなら、BETWEENの選 択率を“SS_RATE”で小さくすることにより、TBL1→TBL2でジョインするようになります。 また、フェッチジョインは、ジョインする表のジョインキーにインデックスが定義されていないと選択されないことを利用 して、IX-1を削除することにより、TBL1→TBL2へのフェッチジョインしか行うことができなくなります。定型的にジョイン 方向が決まるような場合(絞り込める表が必ず決まっているような場合)は、その表に対するジョインキーにあえてイン デックスを定義しないのも1つのチューニング手法です。 ASSIST指定によるチューニング 最適化情報や最適化パラメタを変更すると、他のアプリケーションやSQL文に悪影響がでる場合があります。このよう な場合、ASSIST指定を利用すると、最適化情報や最適化パラメタを変更することなく、ジョイン方法やフェッチジョイ ンする方向を固定化することができます。ジョイン方法を固定化したい場合は“JOIN_RULE”を、フェッチジョインする 方向などジョイン順を固定化したい場合は“LEADING_TABLE”を利用します。

上記のSQL文例で、TBL1→TBL2の順にジョインしたい場合は、以下のようにLEADING_TABLEを指定します。

SELECT /* ASSIST LEADING_TABLE(TBL1,TBL2) */ * FROM SCM1.TBL1,SCM2.TBL2

WHERE TBL1.C1=TBL2.C1 AND TBL1.C2=10 AND TBL2.C2=1

ジョインする方向に加えて、ジョイン方法をフェッチジョインにしたい場合は、以下のようにJOIN_RULE指定します。

SELECT /* ASSIST LEADING_TABLE(TBL1,TBL2) JOIN_RULE(FETCH) */ * FROM SCM1.TBL1,SCM2.TBL2 WHERE TBL1.C1=TBL2.C1 AND TBL1.C2=10 AND TBL2.C2=1 また、ジョインに利用するインデックスを指定したい場合は、“JOIN_RULE”、“LEADING_TABLE”、“USE_INDEX” を同時に指定します。

SELECT /* ASSIST LEADING_TABLE(TBL1,TBL2) JOIN_RULE(FETCH) USE_INDEX(TBL2(IX_4)) */ * FROM SCM1.TBL1,SCM2.TBL2 WHERE TBL1.C1=TBL2.C1 AND TBL1.C2=10 AND TBL2.C2=1

参照

“JOIN_RULE”、“LEADING_TABLE”および“USE_INDEX”の詳細については、“アプリケーション開発ガイド(共通 編)”を参照してください。 共用バッファのチューニング フェッチジョインで表を結合するときは、結合する表のクラスタキーやインデックスにランダムにアクセスするため、結 果件数が多いときに、結合される側の表とインデックスに共用バッファを十分に用意しておかないと、ページの再読 み込みが多く発生して性能が悪くなることがあります。DSIの読み込みページ数は、rdbpsコマンドで知ることができま す。読み込みが多く発生しているようなら、結合される側のインデックスと表に十分な共用バッファを与えるようにして ください。

4.3

ジョイン順のチューニング

FROM句に表と結合表を指定したとき、表と結合表(結合型がLEFTの場合)の左側の表を先にジョインし、その後、右側 の表とジョインしたい場合があります。また、結合表(結合型がLEFTの場合)を複数指定したとき、左側の表どうしを先に ジョインし、その後、右側の表とジョインしたい場合があります。このような場合、最適化パラメタ“JOIN_ORDER”で、FROM 句全体のジョイン順を最適化することによって、意図したジョイン順を選択できます。

TBL2が大量件数でTBL1.C2=10の検索範囲が小さい場合 TBL2が大量件数でTBL1.C1=10の検索範囲が小さい場合、TBL1→TBL2→TBL3の順にジョインした方が性能がよ くなります。このような場合は、最適化パラメタ“JOIN_ORDER”にAUTOまたはOUTSIDEを指定します。 TBL2が少量件数でTBL1.C2=10の検索範囲が大きい場合 TBL2が少量件数でTBL1.C1=10の検索範囲が大きい場合、TBL2→TBL3→TBL1の順にジョインした方が性能がよ くなります。このような場合は、最適化パラメタ“JOIN_ORDER”にAUTOまたはINSIDEを指定します。 適切なジョイン順を選択する 一般的に、最適化パラメタ“JOIN_ORDER”にはAUTOと指定しておくことをお奨めします。その上で、最適化情報を 設定または変更しても意図したジョイン順にならない場合は、OUTSIDEまたはINSIDEを指定して、ジョイン順をチュー ニングしてください。 ASSIST指定によるチューニング 最適化パラメタ“JOIN_ORDER”を変更すると、他のアプリケーションやSQL文に悪影響がでる場合があります。この ような場合、ASSIST指定の“LEADING_TABLE”を利用すると、最適化パラメタを変更することなく、ジョイン順を固 定化することができます。

参照

ASSIST指定の使用方法については、“アプリケーション開発ガイド(共通編)”を参照してください。

4.4

アドバイスに応じたチューニング

アドバイスに、ASSIST指定のメッセージが出力された場合、以下の状態になるまでチューニングを行います。

・

ASSIST指定が有効になる

・

意図したアクセスプランになる たとえば、以下のように、TBL1に以下のインデックスDSOが定義されている場合、定義されていないIX3をUSE_INDEX に指定すると、インデックスが定義されていない旨のメッセージが出力されます。このような場合には、適切なインデック スを使用することで、ASSIST指定が有効となります。 [TBL1] インデックスのDSO名 構成列名 IX1 C1 IX2 C2 [アドバイスの出力例] : SQL statement:

SELECT /* ASSIST USE_INDEX(TBL1(IX3)) */ C2 FROM SCM1.TBL1 WHERE TBL1.C2 = 1 Access plan: ： Advice to an SQL statement: JYP2410I ASSIST要素“USE_INDEX”に指定されたインデックス“IX3”が定義されてい ません．

4.5

ソートサイズ、ワークサイズのチューニング

性能情報を取得してソートやワークでファイルを使用している場合、ソートメモリやワークメモリを増やすことで、ソートや ワークで二次記憶を使用しないようにして性能を改善することができます。ソートメモリやワークメモリを増やすには、動作 環境ファイルの実行パラメタ“SORT_MEM_SIZE”および“WORK_MEM_SIZE”で行うことができます。作業域の大きさ は、次のように見積もることができます。 また、アプリケーション単位で使用するソートまたはワークの最大量は、rdbpsコマンド(sオプション指定)で知ることもでき ます。

4.6

処理が待ち状態となっていた場合のチューニング

サンプリングした実行状態の内訳に、待ち状態(WAITING)が表示されていた場合、資源の占有待ち、入出力待ちなど により、一時的に処理が中断していたことを示します。このような場合には、事象に応じて、以下の対処を実施します。

事象 対処 備考 トランザクションの 占有待ち (LOCK) 排他の単位を行に変更 アクセスプラン情報の“R_LOCK”で確認できま す。 トランザクションの独立性水準 を変更 アクセスプラン情報の“transaction isolation level”で確認できます。 処理の論理上問題ないかを確認して、独立性 水準を“READ UNCOMMITTED”に変更する などの対処を実施します。 トランザクションの 一時占有待ち (LOCK_TEMPOR ARY) ページ長の変更 ページ長を小さくすると、1つのページに格納さ れるレコードの件数が少なくなり、1つのページ へのアクセスの競合が少なくなります。ただし、 ページ長を小さくすると必要となるデータベー ススペースの容量が多くなります。ページ長の 変更は、十分なデータベーススペースの容量を 確保した上で行ってください。 格納構造の変更 RANDOM構造に変更した場合、データは割り 付けを行った領域全体に分散されて格納され ます。そのため、1つのページに格納されるレ コード数は少なくなるため、1つのページへのア クセスの競合が少なくなります。 共用バッファの空き 待ち (DB_BUFFER) バッファプールのページ数の 増量 メモリに余裕のある範囲でバッファプールのペー ジ数を増やしてください。 ページ読込み待 ち (DB_READ) データベースバッファの再設 計 頻繁にアクセスされる資源に対するバッファの 割当てを増やしてください。 ディスク配置の変更 データベースのディスク配置を見直し、入出力 の負荷を複数のディスクに分散させてください。 ページ書込み待 ち (DB_WRITE) テンポラリログファイルのリカ バリログ量の変更 一般的にリカバリログ量が多いほど、データベー ススペースへのページの書き込み頻度は少な くなる反面、ダウンリカバリ時間が長くなります。 このためダウンリカバリ時間に問題がない程度 までリカバリログ量を多くとるようにしてください。 トランザクション終 了時のページ書込 み待ち (DB_WRITE_TR NEND) トランザクション当たりの変更 データ量の削減（ロールバッ クで多く発生している場合） トランザクション当たりの変更データ量が多いほ ど、ロールバックに時間がかかります。 DSIのリカバリなしの利用規定 の解除（コミットで多く発生し ている場合) DSIのリカバリなしの利用規定を設定している場 合、当該DSIのデータ変更はトランザクションの コミットと同期してデータベーススペースへ反映 します。これに対してリカバリなしの利用規定を 設定していない場合は、データベーススペース への反映はバックグラウンドで行います。 BIログバッファの空 き待ち (LOG_BI_BUFFE R) ディスク配置の変更 テンポラリログファイルのBIログ域について、ディ スク配置の見直しを行い、I/O効率の向上を図 ります。テンポラリログファイルのBIログ域と同じ 物理ディスク上に、頻繁にアクセスが行われる 他のファイルを配置している場合は、それらの ファイルを別の物理ディスクへ配置します。 BIログバッファ数の変更 ディスク配置に問題がないと判断した場合、メ モリに余裕のある範囲でBIログバッファ数を増 やしてください。 AIログバッファの空 き待ち ディスク配置の変更 テンポラリログファイルのAIログ域およびアーカ イブログファイルについて、ディスク配置の見直