ホーム » DB関連 » SQLチューニングに役に立つポイント

SQLチューニングに役に立つポイント

   作者:ビズコネクトポータル   发布时间:2013-09-02   

SQLチューニングに役に立つポイント

1. 問題のあるSQL文の特定

パフォーマンスに悪影響を与えている原因が、特定のSQL文であることが
ありますが、まずはじめにstatspackまたはAWR(10g以降のリリース)を使用して、
データベース全体に関する問題を解決することが有効です。

データベースが適当なレベルまでチューニングされた後、多くのリソースを消費
しているSQL文をStatspackやAWRレポートの次のセクションで特定します。

SQL ordered by Buffer Gets
SQL ordered by Physical Reads
SQL ordered by Executions
SQL ordered by Parse Calls (not in 8i)
SQL ordered by CPU (AWR only)

次のような方法でリソースを多く消費するSQL文を特定することもできます(同様の
結果は、Enterprise ManagerのTunning Packでも取得可能)。

SELECT address, SUBSTR(sql_text,1,20) Text, buffer_gets, executions,
buffer_gets/executions AVG
FROM v$sqlarea
WHERE executions > 0
AND buffer_gets/executions > 100000
ORDER BY 5;

buffer_gets/executions に対する条件値(ここでは、>100000)は、個々のシステム
によって適切な値が異なります。システムによっては、上記の条件に該当するSQLが
ひとつもないことも、多くのSQLが該当することもあるので、チューニング対象の
SQL文が適切な数に制限される値を選択してください。
上記の問合せで返されたaddress列の値を使用して、問題のあるSQL文のテキストを
確認することができます。

SELECT sql_text FROM v$sqltext WHERE address = '...' ORDER BY piece;

SQL文が特定できたら、リソース使用量を低減するためのチューニングを行います。

問題がCPUに関係する場合は、各セッションでのCPUの使用状況を確認します。
次の方法で、CPUを多く使用するセッションとSQL文を確認することができます。

a. 'CPU used by this session'の統計を参照するSTATISTIC#を確認します。
STATISTIC#はリリースによって異なるので、使用リリースで確認してください。

SELECT name ,statistic#
FROM v$statname
WHERE name LIKE 'CPU%session';

NAME STATISTIC#
----------------------------------- ----------
CPU used by this session 12

b. CPUを多く使用しているセッションを確認します。

SELECT * FROM v$sesstat WHERE statistic# = 12; <- a.で確認した値 SID STATISTIC# VALUE ---------- ---------- ---------- 1 12 0 2 12 0 3 12 0 4 12 0 5 12 0 6 12 0 7 12 0 8 12 0 9 12 0 10 12 0 11 12 0 12 12 0 16 12 1930 <- CPUを多く使用している c. このセッションの詳細を確認します。 SELECT address ,SUBSTR(sql_text,1,20) Text, buffer_gets, executions, buffer_gets/executions AVG FROM v$sqlarea a, v$session s WHERE sid = 16 <- b.で特定したSID AND s.sql_address = a.address AND executions > 0
ORDER BY 5;

d. v$sqltextを使用して、addressからSQL文のテキストを確認します。

e. dの問合せから得たSQL文に対してautotraceなどを使用し、実行計画を確認します。

2. ディスク・パフォーマンス/ディスク競合の解決

この問題の解決には、statspackやAWRレポートの"Tablespace IO Statistics"の
セクションや、オペレーティング・システムのI/Oレポートが有効です。他の処理を
排除した状態で1つのSQL文の直前と直後のレポートを実行することで、そのSQL文が
必要としたI/Oの情報を取得することができます。

10g以降のリリースでは、次のようにDBMS_MONITORパッケージでwaits=trueの
トレースを取得する状態にしてSQL文を実行することも有効です。

exec dbms_monitor.session_trace_enable(waits=>true);

出力されたトレースに次のようなディスクからの読み込みを待機する待機イベント
の情報が出力されます。
'db file sequential read' : シングル・ブロック読み込み(索引アクセスなど)
'db file scattered read' : マルチ・ブロック読み込み(全表走査など)
'direct path read' : バッファ・キャッシュを経由しない読み込み

3. 不要なソート処理の回避

不要なソート処理を行なっていないかを確認し、行なっている場合はそのソート処理
を回避してください。
ソート処理が必要な場合は、十分なメモリを確保しディスクI/Oが発生しないように
してください。ソートは、CPUやディクスI/Oを多く必要とする可能性のあるコスト
のかかる処理です。できる限り実行計画の最終段階でソートを行い、ソートの対象と
なる行を少なくしてください。
索引を使用するアクセス・パスを選択することで、ソートが不要になる場合もあります。

4. 早い段階での行の絞り込み

最終的に排除される行は実行計画の早い段階で絞り込むことで、問合せのパフォー
マンスを向上させることができる可能性があります。

5. 省略可能なParse処理

SQL文は大文字/小文字、スペースや改行等を含めて完全にテキストとして一致して
いないとカーソルが共有されません。同じ意味を持つSQL文の表記を統一して
カーソルを共有することで、Hard Parseの処理を省略することができます。
WHERE句の条件値のみを変えて複数回実行されるようなSQLの場合、条件値の部分に
バインド変数を使用したり、CURSOR_SHARINGパラメータを設定することも有効です。

6. 適切な索引の使用

通常、適切な索引が使用されることで問合せのパフォーマンスは向上します。
意図する索引が使用されない場合は、以下のことを考慮してチューニングを
おこなってください。

ルールベース・オプティマイザ(RBO)を使用している場合:
・複数の索引が同じランクの場合、ディクショナリ・キャッシュに現れた順番で
選択されます。

コストベース・オプティマイザ(CBO)を使用している場合:
・サンプル・サイズを増やして、統計情報を再収集してください。
・列データの値が均一に分布していない場合は、ヒストグラムを収集してください。
・意図する索引を使用するように、ヒントを使用してください。

結合で索引が使用されるかどうかは、結合方法にも依存します。

7. 適切な実行計画と結合順序の選択

適切でない実行計画が選択されてしまった場合は、それを修正する必要があります。
適切な結合順序を選択するためには、結合する表の関係を図に描いてみることも
有効です。そのことで欠落している結合条件が見つかることもあります。

8. データ移行時の統計情報

export/importやData Pumpによるデータ移行を行う場合、オプションの指定によって
移行前のオブジェクトの統計情報をデータと共に移行するかどうかを指定できます。
データの移行によって格納状況が変わる可能性があるので、移行先で統計を再収集
した場合は移行元とは異なる実行計画が選択される可能性があります。

9. 統計収集時の十分なサンプル・サイズの使用

CBOに正確な統計情報を提供することで、CBOは最適な実行計画が選択することが
できます。5%程度のサンプル・サイズで十分な場合もありますが、より大きな
サンプル・サイズでの収集が必要な場合もあります。

10. 列データが均一に分布していない場合

列データの分布が均一ではない場合、列のヒストグラム統計を収集してください。
ヒストグラムは、列の値が均一に分布している場合や、問合せの述語にバインド変数
が使われている場合には役に立ちません。

※ ただし、Oracle9i以降のリリースでバインド変数の先読み機能(bind peek)が
有効な場合は、バインド変数を使用しているSQL文でもヒストグラムが取得されて
いれば、使用されます。

11. CBOの使用を強制する新機能

RBOでは実装されていないデータ構造や構文があります。問合せの中にそのような
要素を含む場合は、パラメータの設定などにかかわらずCBOが使用されます。

CBOが強制されるデータ構造 :
- 並列度が1より大きく設定された表
- 索引構成表(IOT)
- パーティション表

CBOが強制される構文 :
- MERGE
- SAMPLE / SAMPLE BLOCK
- LEFT/RIGHT/FULL OUTER JOIN

12. ITLの競合

各ブロックのトランザクション・リストに十分な領域がない場合、ITLの競合が
起こることがあります。export/importなどによってデータが整理され、ブロック
内の空き領域が少なくなるとこの現象が発生しやすくなります。
同時トランザクション数が多い表や索引にはあらかじめINITRANSの値を大きく
設定して下さい。

此评论不代表本站观点大家说

《SQLチューニングに役に立つポイント》等您坐沙发呢!

发表评论

亲,不支持纯字母、符号评论哦~