Archive for 9月, 2008

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チューニング? (チューニング編)に? きます。

9月 1st, 2008

Oracleリソー゜ /SQL監?

Posted in Oracle by admin

  運用時や負荷? 証時ぜ DBサーバ関連で起きる問題として、例えば、DBサーバぜ CPU利用? が異常に? い、バッチ処理時間やCGIのレスポンス時間が長い・・・? 々が起こりえます。問題を解決する為の情報として、以? のように順を追ってボトルネックを検出します。

【問題発生】DBサーバCPU負荷、DBサーバディスク? 荷、バッチ処理時間の遅延、CGIレスポンスの劣化
      ↓
【問題解析】OSリソース監? (sar, vmstat, iostat ...)、アプリケーション解析(callgrind, collect ...)
      ↓
【? 因解析】Oracleリソース監? 、SQL監?
      ↓
【解決策検? 】 OS/Oracleパラメータチューニング、SQLチューニング、バッチ計画? 直し、リソース? ?

  問題を解決する為の情報として、OSリソースの圏 得と監? は? 常時から実施しておぜ べきです。DBサーバぜ CPU利用? やディスク? 荷が見圏 けられる時の次の調査段障 として、Oracleリソースを圏 照することになると思います。また、バッチ処理時間やCGIのレスポンス時間の遅延に対しては、アプリケーションの解析後、SQL処理時間に問題があると判断された後に問題のあるSQLを探し出す事になります。定常時からOracleリソースやSQL監? までが出来ていれば良いのでですが、そうでない場合は、以? のようなツールの地 入、監? を検? します。

(1) statspack
(2) 動的パフォーマンスビュボ
(3) EnterpriseManager/AWR、IndepthForOracle
——————————————-

(1) statspack
  ある時間からある時間の時間帯ぜ Oracleリソース統計情報をテキストベースでレポート出力します。ボトルネックをピンポイントで? 出するというよりは、全? のリソース傾向を掴むのに適しています。テキストベースなので直観性はありませんが、慣れればここから素早ぜ 、多ぜ の情報を得ることも可能だと思います。Oracle標準のパフォーマンスレポートツールのため、EnterpriseEditionでもStandardEditionでも利用することができ、追加のライセンスやオプションも必要としません。statspackぜ Oracle 8.1.6以降、AWRぜ 10.1.0以降のバージョンに対応しています。

§ Oracleリソース監?
  情報釜 ぜ SNAP SHOTレベルで調整できますが、レベボ 7以? でかなり細かいレベルまでリソース情報を表示してぜ れます。ただし、この情報圏 得レベルを大きぜ しすぜ るとパフォーマンスに影響するかもしれませんので、リソースに? 裕がないDBサーバではレベボ 5以? が目? になります。特に目? となる注目情報は? 機イベントTOP5とキャッシュヒット? でしょうか。下記に地 しだけポイントを抜粋しています。


Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
db file scattered read               63,079         967     15   27.5   User I/O
CPU time                                            907          25.8
db file parallel read                43,785         819     19   23.3   User I/O
db file sequential read             125,279         712      6   20.2   User I/O
gc cr multi block request           430,241         144      0    4.1    Cluster
          -------------------------------------------------------------

“ “CPU time”が100%になる事が最も望ましい待機イベントTOP5の? 果です。イベント”CPU time”は唯? 待機している時間ではなく稼働しているイベントです。その? の? 機イベントをなぜ し、”CPU time”の眷 時間を減らすことがチューニングの目木 になります。Wait Class “User I/O”の? 機イベント(db file sequential read、db file scattered read)が多い場合、SQLチューニングの? 要性が高い場合が多いです。


Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:   99.99       Redo NoWait %:    100.00
            Buffer  Hit   %:   95.35    In-memory Sort %:    100.00
            Library Hit   %:   99.84        Soft Parse %:     99.64
         Execute to Parse %:   26.59         Latch Hit %:     99.97
Parse CPU to Parse Elapsd %:   86.74     % Non-Parse CPU:     98.53

※各キャッシュヒット? ぜ 100%に蜿 づけることを目木 にします。予算の? 裕があれば、物理メモリを増設しぜ Oracleぜ SGAサイズ? メモリ関連パラメータを増加・・・と、そうもいかないのがチューニングをする理由でしょうか。

§ SQL監?
  SQL実行時間順、CPU使用時間順、バッファーメモリからのデータ圏 得釜 が多い順、物理ディスクからのデータ圏 得釜 が多い順などにソートして? 荷の? いSQLをリストしてぜ れます。ただし、負荷の? いSQL一覧ぜ SQL文が先頭の? バイトかで省略されているので、改めぜ SQL実行させたい時にコピー? ペーストできないところが難点ではあります。また、SQL実行計画も出力されません。

(2) 動的パフォーマンスビュボ
  statspack/AWRがスナップショット間の時間帯の統計情報なのに対し、動的パフォーマンスビューで圏 得する情報は圏 得タイミングのリソースを得ることが可能です。時間軸を用いたリソースの推移を見ることが出来ます。

§ Oracleリソース監?
  statspack/AWRぜ “Load Profile”情報などは統計情報よりも時間推移で表示した方が傾向が掴みやすい場合も多々あります。スクリプトぜ V$SYSSTAT、V$SESSION、V$SESSION_WAITを監? しておけば、各Oracleリソースの時間推移を得ることが出来ます。スクリプトで監? する場合、圏 得? ぜ DB接続処理を行うのは? 計な? 荷がかかりますので、以? に示したスクリプトのように接続状態を保ったままにしておぜ のがおすすめです。

$ cat oracle_sysstat.sh
#!/bin/sh

#check
if [ $# -lt 5 ]; then
        exit 1
fi

pid=$$
echo ${pid} > logs/v_sysstat.pid
echo "${3}/${4}@${5}"
echo "set pages 0 lin 1024 echo off trimspool on feedback off ver off colsep '      '"
echo "spo logs/v_sysstat_`date +%Y%m%d%H%M%S`.log"

count=0
while [ $count -lt ${2} ]
do
        echo "SELECT TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI:SS') TIME FROM DUAL ;"
        echo "SELECT NAME,CLASS,VALUE FROM V$SYSSTAT ;"
        sleep ${1}
        count=`expr $count + 1`
done
echo "spo off"

rm -fr logs/v_sysstat.pid
exit 0

$ /bin/sh oracle_sysstat.sh 10 480 schema pass instance | sqlplus > /dev/null &

dpv_resource
? V$SYSSTAT情報をEXCELでグラフ化

§ SQL監?
  V$SQL_PLAN、V$SQLTEXTを監? すれば可能ですが、キャッシュに? 持されているSQLのみが対象になります。また、テーブルデータも大きく定常時から監? することでサーバへのオーバーヘッド発生の可能性もあります。

(3) EnterpriseManager/AWR、IndepthForOracle
  圏 得できる情報釜 ぜ (1)statspack(2)動的パフォーマンスビューを合゜ せた釜 ですが、ブラウザベースでグラフィカルなので直観性があります。しかし、Oracle Databaseではない別製品なので、当然? がかかります。(1)(2)にない機能として、診断? 能、アドバイザ? 能があります。

§ Oracleリソース監?
以? のようなインターフェースです。統? 、時間推移でもグラフィカルな形? で圏 照可能です。
Em_Resource2
? EnterpriseManager

Indepth_resource
? Indepth

§ SQL監?
以? のようなインターフェースです。SQL単位の使用リソース、平均実行時間、実行回数、実行計画などが圏 照できます。SQLチューニングアドバイザ? 能を利用することも出来ます。
Em_sql
? EnterpriseManager

Indepth_sql
? Indepth