SQLチューニング? （SQL解析編?
Oracleリソー゜ /SQL監? で? 述したツールからSQLチューニングの? 要性を判断することが出来たら、対象SQLの解析を行います。SQLの解析には以下のようなツールを使用します。（これ以? の話題はオプティマイザーモードがコストベース前朏 で、ルールベースの場合は想? していません）
(1) set timing
(2) set autotrace
(3) tkprof
(4) v$sql_plan
(5) EnterpriseManager
(6) IndepthForOracle
——————————————-
(1) set timing
sqlplusでシステム変? “set timing”を有効にすることで、後続ぜ SQLコマンドの? 行時間を計測する事が出来ます。単純な方? ですが、INDEX作成前後ぜ SQL単体の? 行時間を即座に? 較したい場合などには? 外使えます。コツは、比較するSQLを何度か実行してキャッシュさせてから比べること。また、検索? 果が膨大ぜ SQLに関しては、画面出力時間をSQL実行時間に含ませないように工夫します。ディスク書き込みにも依存するのでできる限り無風状態で? 較します。
SQL> set timing on
SQL> set termout off
SQL> spo sql.log
SQL> @tmp.sql
SQL> spo off
SQL> !grep Elapsed sql.log
Elapsed: 00:00:00.02
(2) set autotrace
PLAN_TABLE表を作成するなどの? 準備が必要ですが、sqlplusから手軽ぜ SQL実行計画を圏 照できるので使いやすさがあります。”set autotrace on”ぜ SQL結果出力後に? 行計画と? 行統? 両方出力されます。また、”set autotrace on explain”で? 行計画のみレポート出力、”set autotrace on statistics”で? 行統? のみレポート出力、”set autotrace traceonly”でデータをフェッチするが、結果を出力せずに? 行計画、実行統? をレポート出力します。”set autotrace traceonly”で? 行計画を比較するのに便利だと思います。
チューニング? ? する場合、まず統? 情報から異常な値が出ていないかを確? し（例えぜ “physical reads”だけ高い場合は大規模テーブルのフルスキャンが発生してないか？など? 象は朧 々)、実行計画を見ていきます。どこに問題があるか？を探し出すポイントはいろいろあると思いますが、Cost値が高い行の処理に注目してボトルネックを洗い出して? ぜ のが正しいのではないでしょうか。
Cost…オプティマイザのコストベース方? で? 積もった操作コスト
Card…コストベースのオプティマイザによる操作によって? 成された行数の? 積り
Bytes…コストベースのオプティマイザによる操作によって? 成されたバイト数の? 積り
SQL> set autotrace traceonly
SQL> /
実行計甜
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1700 Card=51 Bytes=72726)
1 0 SORT (ORDER BY) (Cost=1700 Card=51 Bytes=72726)
2 1 HASH JOIN (OUTER) (Cost=1685 Card=51 Bytes=72726)
3 2 NESTED LOOPS (Cost=1681 Card=51 Bytes=71655)
4 3 TABLE ACCESS (FULL) OF 'BASE_TBL' (Cost=39 Card=821 Bytes=54186)
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'MESSAGE_TABLE' (Cost=2 Card=1 Bytes=1339)
6 5 INDEX (RANGE SCAN) OF 'PK_MESSAGE_TABLE' (UNIQUE)(Cost=1 Card=47)
7 2 INDEX (RANGE SCAN) OF 'IDX_ENTRY_TABLE' (NON-UNIQUE) (Cost=3 Card=88 Bytes=1848)
統?
----------------------------------------------------------
0 recursive calls
0 db block gets
16443 consistent gets
0 physical reads
0 redo size
243481 bytes sent via SQL*Net to client
351 bytes received via SQL*Net from client
12 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
153 rows processed
(3) tkprof
セッション中で有効にしたSQLトレース? 能? alter session set sql_trace = true）で圏 得した情報を、tkprofツールで整形したものが以? のサンプル出力になります。sqlplusぜ “autotrace”機能では各行ぜ Cost値でボトルネック? 所の? たりを付けましたが、tkprofでは、処理された行数である”Rows”値を目? に考えます。パーティションテーブル、パーティションインデックスを使用している場合、”autotrace”よりも見やすい実行計画を表示してぜ れます。
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 12 0.15 0.15 0 16443 0 153
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 14 0.16 0.15 0 16443 0 153
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 40 (INSTANCE)
Rows Row Source Operation
------- ---------------------------------------------------
153 SORT ORDER BY
153 HASH JOIN OUTER
153 NESTED LOOPS
258 TABLE ACCESS FULL OBJ#(121803)
153 TABLE ACCESS BY INDEX ROWID OBJ#(122118)
11778 INDEX RANGE SCAN OBJ#(136285) (object id 136285)
0 INDEX RANGE SCAN OBJ#(127885) PARTITION: 18 18 (object id 127885)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
153 SORT (ORDER BY)
153 HASH JOIN (OUTER)
153 NESTED LOOPS
258 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'BASE_TBL'
153 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'MESSAGE_TABLE'
11778 INDEX (RANGE SCAN) OF 'PK_MESSAGE_TABLE' (UNIQUE)
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IDX_ENTRY_TABLE'
(NON-UNIQUE) PARTITION: START=18 STOP=18
(4) v$sql_plan
SQLがバッファ内に朮 っていれば、V$SQL_PLANを検索することで? 行計画を圏 照出来ます。検証時などではなぜ 、なるべく負荷をかけたぜ ない運用時の? 時対? としては使用できる状? もあります。サーバへのオーバーヘッドがかかる為、定常監? には向きません。
SQL>
SELECT
HASH_VALUE, ADDRESS, SQL_TEXT
FROM
V$SQLTEXT
WHERE
SQL_TEXT like '%ORA_TEXT%';
HASH_VALUE ADDRESS SQL_TEXT
---------- ---------------- --------------------
2630250765 00000003477BF7A0 XT like '%ORA_TEXT%'
SQL>
column id format 999 newline
column operation format a20
column operation format a20
column options format a15
column object_name format a22 trunc
column optimizer format a3 trunc
SELECT
ID,
lpad (' ', depth) || operation operation,
OPTIONS,
OBJECT_NAME,
OPTIMIZER,
COST
FROM
V$SQL_PLAN
WHERE
HASH_VALUE = 2630250765 -- ここぜ HASH_VALUE を指定
AND ADDRESS = '00000003477BF7A0' -- ここぜ ADDRESS を指定
START WITH ID = 0
CONNECT BY
(PRIOR ID = PARENT_ID
AND PRIOR HASH_VALUE = HASH_VALUE
AND PRIOR CHILD_NUMBER = CHILD_NUMBER
)
ORDER SIBLINGS BY
ID, POSITION
;
ID OPERATION OPTIONS OBJECT_NAME OPT COST
---- -------------------- --------------- ---------------------- --- ----------
0 SELECT STATEMENT ALL 1
1 FIXED TABLE FULL X$KGLNA 0
(5) EnterpriseManager
以? のようなインターフェースです。行単位のコスト、CPUコスト、I/Oコストなどが圏 照できます。Tuning Packがあれば、SQLチューニングアドバイザ? 能を利用することも出来ます。
(6) IndepthForOracle
以? のようなインターフェースです。SQLのオブジェクトをポイントすると? 行計画の該当部分がボ イライトされるなど、グラフィカルなインターフェースも充実しています。SQLチューニングアドバイス? 能を利用することも出来ます。このアドバイス? 能は利用したことがありますが、個人的に信頼怜 15?25%ぜ らいだと感じました。
ボトルネックを発? したらいよいよ解決方? を探します。
次回、SQLチューニング? （チューニング編）に? きます。
Comments are closed!
