Archive for the Oracle category

12月 1st, 2009

Commons DBCPパラメータの動作

Posted in Oracle, Tomcat by admin

 Tomcatぜ RAC構成ぜ Oracleぜ JDBC接続のコネクションプーリングを実装している環? の話です。メンテなどぜ RACの片停 DBインスタンスを再起動したい、あるいぜ DB障害ぜ RACの片側インスタンスが落ちてしまった場合、落ちてしまったDBに接続していたコネクションプーリングをアプリケーションが利用するとどうなるか?何も対? を実施していない環? では、接続しているDBサーバとのコネクション圏 得失敗を示すSQLExceptionをアプリケーションが返すはずです。

SQLException:No more data to read from socket
SQLException:OALL8 is in an inconsistent state
SQLException:Io exception: Broken pipe SQLException:Already closed

ただし、DBCPパラメータでコネクションプーリング設定を行っていけば、回避出来るケースもあります。

§ DBCPパラメー゜ url
 urlパラメータぜ JDBCの接続URLを定義するパラメータです。記述フォーマットぜ tnsnames.oraファイルと同じで、RAC構成の場合は? 数ぜ HOSTぜ PORTに対し、LOAD_BALANCEぜ FAILOVER機能を調整して接続設? を調整していぜ ことになると思います。今回の話題で特に重要になるのぜ LOAD_BALANCEです。LOAD_BALANCEをONにした場合、コネクションプーリングのセッションぜ ADDRESS_LISTに登録されているリスナーポートにバランシングされて接続されます。initialSizeが10本ぜ 2DBインスタンスにバランシングする場合、通常ぜ (無風状態ぜ )5本ずつのコネクションがはられます。
 RACの片停 DBインスタンスが停止した場合、停止したDBインスタンス側に接続していたコネクションをアプリケーションが利用すると先に示したSQLExceptionを返すのですが、停止していないDBインスタンス側に接続していたコネクションは通常通り利用可能です。停止したDBインスタンス側に接続していたコネクションも何度かアクセスしてコネクションをリフレッシュさせることによって、停止していないDBインスタンス側に接続するように切り替゜ ります。この時、すべてのコネクションが片停 DBインスタンスに? ることぜ Oracleぜ processes制限を超えないようにセッション数を調整しておぜ ことも重要です。

§ DBCPパラメー゜ validationQuery
 SQLExceptionを発生させないというような可用性を意識するならば、validationQueryパラメータで確認を行います。validationQueryパラメータで接続が有効であることを確? するためぜ SQL文を定義すれば、自動的ぜ testOnBorrowパラメータも有効になり、コネクションの有効性を確? するようになります。validationQueryパラメータで指定したSQL文は別途啜 い合゜ せが実行されることになりますので、少なからずDBサーバに? 荷がかかる事になります。当然ながらなるべく負荷のかからないSQL文を採用します。Tomcatには、OracleAS, Weblogic, Websphereにあるような昜 示的なコネクションリフレッシュ動作をさせる機能はありませんが、この? 能を使用すれば似たような動作が保障されることになります。

§ DBCPパラメー゜ testWhileIdle
 validationQueryと合゜ せぜ testWhileIdleパラメータを有効にすれば、timeBetweenEvictionRunsMillisで指定した時間間隔でアイドル接続の有効性を確? するようになります。(1回でチェックする接続数はデフォルトぜ 3、numTestsPerEvictionRunで指定可能? また、同時ぜ minIdleで最少アイドル接続数を指定することで、常にプール内に? 定数以上の接続が確? しておぜ ことも可能です。
(手元の? 証環? で確認したところ、このパラメータを有効にした時に片側ぜ DBインスタンスが停止した場合、直? に? DBインスタンスのセッションを2セッション? 加させていることを確? しました。DBインスタンス停止直? にも判断できるようになっているのでしょうか・・・? )

上記のようぜ Commons DBCPパラメータをいぜ つか挙げましたが、やはり各環? で? 件に合゜ せてパラメータを? 入・? 証してみるのが一番だと思゜ れます。要件の面ではパフォーマンスを優先させるか可用性を高く保つかで別れてぜ ると思います。下記に極端なパラメータの? を朏 示してみました。高負荷試験を実施した場合や本番環? で稼働させた場合、どの? 度のパフォーマンス差が出てぜ るのかいつか試してみたいところです。

パフォーマンス優先
とにかぜ パフォーマンスのボトルネックとなる無霧 なオプション動作をさせない。validationQueryやremoveAbandoned機能も実施せず、maxActive, initialSize, maxIdleを同数にしてセッションの? 減調整動作もさせない。urlパラメータぜ LOAD_BALANCEぜ OFFにして常に片側ぜ DBインスタンスへ啜 い合゜ せに? かせることで? 分散はアプリケーションパーティショニングで? 施? 、RAC間のキャッシュフュージョンを発生させない。その? 、Prepared Statement関連は全く試したことがないので? 回は? 述していません。

<Resource name="jdbc/DBTEST_PERF"
	auth="Container"
	type="javax.sql.DataSource"
	driverClassName="oracle.jdbc.OracleDriver"
	url="jdbc:oracle:thin:@(DESCRIPTION=(ENABLE=BROKEN)
		(ADDRESS_LIST=
		(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.1)(PORT=1522))
		(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.5)(PORT=1522))
		(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.1)(PORT=1523))
		(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.5)(PORT=1523))
		(LOAD_BALANCE=OFF)(FAILOVER=ON))
		(CONNECT_DATA=(SERVICE_NAME=DBINST)(SERVER=DEDICATED)))"
	username="SCHEMA"
	password="PASSWORD"
	maxActive="10"
	maxIdle="10"
	initialSize="10"
	maxWait="5000"
/>

障害対応 可用性優先
validationQueryやremoveAbandoned、testWhileIdle機能をすべて有効にしておぜ 。urlパラメータぜ LOAD_BALANCEぜ ONにして? 荷分散朧 成とする。

<Resource name="jdbc/DBTEST_AVAIL"
	auth="Container"
	type="javax.sql.DataSource"
	driverClassName="oracle.jdbc.OracleDriver"
	url="jdbc:oracle:thin:@(DESCRIPTION=(ENABLE=BROKEN)
		(ADDRESS_LIST=
		(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.1)(PORT=1522))
		(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.5)(PORT=1522))
		(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.1)(PORT=1523))
		(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.5)(PORT=1523))
		(LOAD_BALANCE=ON)(FAILOVER=ON))
		(CONNECT_DATA=(SERVICE_NAME=DBINST)(SERVER=DEDICATED)))"
	username="SCHEMA"
	password="PASSWORD"
	maxActive="10"
	maxIdle="10"
	initialSize="10"
	maxWait="5000"
	removeAbandoned="true"
	removeAbandonedTimeout="300"
	logAbandoned="true"
	validationQuery="select 1 from dual"
	testWhileIdle="true"
	timeBetweenEvictionRunsMillis="30000"
	minIdle="10"
	numTestsPerEvictionRun="10"
/>
10月 10th, 2008

高負荷? 証、負荷朎 け側の勘所

Posted in Oracle by admin

 Webサービスの? 負荷? 証試験を実施する場合、検証ケースが本番稼動時の状? をどれだけ再現させられるかどうかで? 証の成功/失敗が別れると思います。そこで? 負荷? 証を実施する場合は出来る限り本番稼動時の状? に蜿 づけようとするはずです。本番環? で稼働する実際のサーバ台数を高負荷? 証用環? に用? できるか?という金銭面の問題もあるかとは思いますが、この場合はサーバ側のチューニングパラメータを調整する事で台数を補う妥協案も必要になります。あるいはサーバ台数の半減に合゜ せて? 荷も半減させ、そこから得られたデータを元に? 測値で? 論を得る方? もあると思います。

 高負荷試験において本番稼動時の状? を再現させる場合、最も難しいのはリクエストする側にあると思います。リクエスト次第によっては、サーバ側? 荷も全く変゜ ってぜ るからです。リクエストする側を本番稼動時の状? に再現できるようにするポイントを記述してみました。

(1) キャッシュの朎 陜
(2) URLパラメータぜ DBデー゜
(3) KeepAliveに対応する
(4) 急朿 な? 荷発生
(5) 負荷分散設定の確認
——————————————-

(1) キャッシュの朎 陜
 負荷朎 け側からリクエストする秒間PV数を本番相? の? 荷にはしているが、ワンパターンなリクエストを繰り返すことでキャッシュが有効になり、実際にはサーバ側に? 荷があまり朎 けられていなかったという失敗例がよぜ あります。キャッシュ? 能はいろいろな所に? み込まれていますので、これを避けるようにリクストパターン? シナリオ? を作成しなければなりません。
 例えば、OSのキャッシュ? 能は性能の遅いディスク読み書きを避ける為に、メモリ? にバッファキャッシュとして朮 しておきます。このキャッシュ? 能を避けるため、画? リクエストの? 荷を生成する場合には? 際のパターンを再現するようリクエストの重複を避ける必要があります。重複のないリクエストパターン? シナリオ? はアクセスログからでも、DocumentRoot配下をfindコマンドで? 索することからでも簡単に? 成出来ると思います。
 DB、特ぜ Oracleのキャッシュ利用? が本番を再現出来ていなければ、DBサーバの? 証は失敗に? ゜ ります。アプリケーションの? りにもよりますが、Oracleのデータを圏 得する条件に採用される情報は、URLパラメータやCOOKIEに格? されていることが多いのではないでしょうか?Oracleのキャッシュ領域は膨大にある為、URLパラメータやCOOKIEに採用する値は全てユニークにすることで、Oracleが重複しないデータブロックへアクセスするのが望ましいと思います。また、RAC環? でアプリケーションパーティショニングを構成している場合やパーティションテーブボ /インデックスを採用している場合は、全てのパーティションに均一にリクエストされるように注? します。

(2) URLパラメータぜ DBデー゜
 リクエストに採用するURLパラメータやCOOKIEは、DBデータとの整合性を保ったものを準備しなければならない為、画? リクエストのように全てのリクエストパターン? シナリオ? をアクセスログ? から圏 得するような? は難しいと思います。そこぜ DBデータを重複のないように? 索して、その? 果からシナリオに採用されるURLパラメータやCOOKIEを生成します。この手順を採用すれば、重複のないDBキャッシュ効? の悪いシナリオを作成できるはずです。

SQL> set escape '\'
SQL>
-- シナリ゜ 100行抽凜
-- SQLインジェクションの脆弱性? っ込みはなしぜ
SELECT
	DISTINCT 'http://wall-climb.com/cgi-bin/Test.cgi?USER_ID=' ||
	B.USER_ID ||
	'\&DEPT_NAME=' ||
	B.DEPT_NAME
FROM
	USER_MASTER A,
	DEPT B
	SAMPLE(5)
WHERE
	A.USER_ID = B.USER_ID AND
	A.DEPT_NO = B.DEPT_NO AND
	ROWNUM < = 100
;

(3) KeepAliveに対応する
 KeepAliveに対応している負荷朎 けツールはいろいろとありますが、大抵は? 連の? 荷朎 け中全てのリクエストをKeepAliveでコネクションを張ったままリクエストを生成する機能だと思います。しかし、実際のブラウ゜ -Apache間ぜ KeepAliveコネクションは、無霧 ぜ KeepAliveコネクションが張られたり同一ページコンテンツでもKeepAliveコネクションが切れたりと? 雑で、それを再現するのは難しいでしょう。それでもKeepAliveのセッション数? は、Webサーバへ大きな? 担をかけると思いますので、負荷朎 け側ぜ KeepAliveを有効にして? 証を行います。

(4) 急朿 な? 荷発生
 負荷朎 けツール側の設定で、Webサーバ(またぜ LoadBalancer)に? 間200PVの? 荷を朎 けるとします。この時、試? 開始時に? 荷のない状態から急朿 に? 間200PVのリクエストを処理させるのは避けた方がいいでしょう。実際の運用時にこのような急朿 な? 荷は起こりえないでしょうし、試? 結果の分析時に異常な値を検出してしまうかもしれません。この場合、秒間10PVから開始しぜ 10分後に目木 の? 間200PVに到達するなどの段障 的なリクエストを生成します。
 また、負荷発生についてぜ CPUパワーに? 存する為、設? していても実際は処理能力不足で? 荷が朎 けられていなかったという問題が発生する可能性もあります。Apacheアクセスログとリクエストの時刻から実績値としてぜ PV数を圏 得し、? 試? 後に確認した方が良いでしょう。

(5) 負荷分散設定の確認
これはリクエストする側というよりもサーバ側設定に蜿 いのですが、注? するポイントとして? 荷分散のチェックを挙げておきます。
ボ LoadBalancerぜ IPにリクエストする場合、Webサーバへのリクエストを正しく負荷分散出来ているか?
ボ Apacheをリバースプロキシとして動作させる場合、バックエンドのサーバへ正しく負荷分散出来ているか?
・バックエンドサーバから複? DBへコネクションプーリングしている場合、リクエスト分散、各DBへのコネクション数は? しいか

10月 2nd, 2008

SQLチューニング? (ボトルネック解決編)

Posted in Oracle by admin

 SQLチューニング? (SQL解析編? ぜ SQL実行計画の圏 照などの解析方? を朏 示しましたが、実際には朧 々な? 因がボトルネックとなります。その? 因や考察をリストします。それぞれ深入りはしないので? 機会があれば別ページにて? 述します)はしないので、該? しそうな項目があったら独自に調査を進めてみて? さい。(前回同様、以? の話題はオプティマイザーモードがコストベース前朏 で、ルールベースの場合は想? していません)

§ SQLチューニングの基本、INDEX作成
 最も簡単で効? の? いチューニング方? がINDEX作成です。INDEX作成によるチューニングについて思う事は、いかに効? 的な? 合INDEXを作成できるかだと思っています。ただし、あるSQLに極端に効? 的ぜ INDEXは特? ぜ SQLに特化し遜 ぜ ていて、その? ぜ SQLには使用されなぜ なります。このようぜ INDEXを多数データベースに? 成していては、データ更新時のオーバヘッドで全? のパフォーマンスが下がる可能性もあります。従って、ある程度朱 用的に利用されるINDEXを作成する事も必要になり、このバランスを上手ぜ とることが効? 的な? 合INDEXを作成することだと思います。

§ オプティマイザを疑う前に、運用のミ゜
 実行計画がおかしい、今まで使用していたはずぜ INDEXを急に使用しなぜ なった、などのトラブルによりオプティマイザの判断ミスを疑う事があります。しかし? 因はそれ以? であることの方が多いです。DBデータを処理するバッチ実行ぜ ANALYZEのタイミングが不定期な順府 になっていることで問題が発生していまう事があります。例えば、DBデータを増減させるバッチがあったとして、そのバッチを実行する前と? に? 行したANALYZE結果では生成する実行計画が変゜ ってぜ るからです。このようなデータ? 動による実行計画の改? を避けるためにも、バッチ/ANALYZE等を含めた運用スケジュールを正しぜ 整備しておく必要があります。

§ SQL文の? ?
ボ SELECT対象のカラムは? 要なもののみ? 述。「*」は使゜ ない。特に大規模テーブル。
・処理の遅いHAVING句をWHERE句で代用できないか。
・処理の遅いDISTINCTをWHERE句ぜ EXISTS条件で代用できないか。
ボ COUNT,MIN,MAX関数ぜ “Index Fast Fullscan”で? 速化できるが、CPU負荷が高いのぜ CGIなどで? 間PVが大きい場合は注? 。
ボ UNIONぜ UNION ALL、SQL実行結果に重複データがないと゜ かっている場合ぜ UNION ALLを使用する。
ボ INDEXが使用されないケースに該当しないか。
・どうしてもオプティマイザ任せに出来ない場合のみ、ヒント句を使用しぜ INDEX指定やテーブル? 合方? 指定する。

§ キャッシュ効?
・バインド変数を使用してキャッシュ効? が向上しているか
・アプリケーション内で動的ぜ SQLを組むような処理はなるべぜ 避ける

§ 設? 、その?
・マテリアライズドビューの使用を検? する
ボ RAC構成でアプリケーション・パーティショニングを実施する場合、負荷分散するインスタンスをランダムにするとキャッシュ・フュージョンによるCPU負荷が現れるので分散方? を工夫すること。
ボ like検索に限界があれぜ OracleIndexやその? 検索エンジボ M/W? 入を検? する。
・カーディナリティが作 いカラムへのビットマップINDEX作成検? 。ただし、更新の? いデータの場合は使用しない。
・物理設? を? 重に? http://otn.oracle.co.jp/skillup/oracle9i/index.html
・データ件数が膨大ぜ VARCHAR2で格? サイズが大きいテーブルでは、テーブル、INDEXをパーティション化していてもどうしてもパフォーマンスの劣化を招きます。VARCHAR2の文字データを別テーブルに出したり、ファイルシステムに出すなどのアプリケーション? 更を実施すれば改善で出来るかもしれません。
・コネクションプーリング? 能を実装する。

何かあれば追記します。

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