9月 20th, 2008

SQLチューニング? (SQL解析編?

Posted in Oracle by admin

 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チューニングアドバイザ? 能を利用することも出来ます。
Em_plan

(6) IndepthForOracle
 以? のようなインターフェースです。SQLのオブジェクトをポイントすると? 行計画の該当部分がボ イライトされるなど、グラフィカルなインターフェースも充実しています。SQLチューニングアドバイス? 能を利用することも出来ます。このアドバイス? 能は利用したことがありますが、個人的に信頼怜 15~25%ぜ らいだと感じました。
Indepth_plan

ボトルネックを発? したらいよいよ解決方? を探します。
次回、SQLチューニング? (チューニング編)に? きます。

Both comments and pings are currently closed. RSS 2.0

Comments are closed!