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!