! ~Oracle Database を監視しよう ~ Session by Shinnosuke Akita 2014.02.00
Self Introduction Shinnosuke Akita Oracle DBA をやっています 今の現場は DB 設計もやっています 入社 2 年目 休日はランニングと家族サービス たまに小説も書いたり 勉強会にでかけたり 大衆酒場めぐりがマイブーム
Today s Agenda 悪玉 SQLを探し出せ! 突然データベースが遅くなったら? 本当にSQLが悪いのか? 被害者と加害者 SQLチューニングの基礎 Tips 紹介
データベースが突然遅くなった! エンドユーザに早く報告しなければならないだが 糸口が掴めない いったいどうすれば
いったい何が原因になり得るのか? SQL のプランが悪くなった? OS の問題? ディスク IO の問題? ネットワークの問題 (RAC の場合 )
アラートログにでも出てくれればいいが 正常に動いてはいるのなら 何も出してくれない 様々な要素をどう効率よく確認するかが解決を早くする!
Jonathan Lewis 氏に聞いてみた この問題は難しく 必ずしも同じパターンでいつも対応できるものではない だが 一般論としてお話はできる Oracle Ace トラブルシューティングや SQL オプティマイザが専門
一般的な確認順 OS CPU の確認 SQL の確認 スナップショットの確認 Top コマンドなどで OS の CPU を確認する 現在動いている SQL を確認する 現状のスナップショットを取得し いつもの状態と比較する
SQL を探してみる まずは 現状動いている SQL を見てみましょう select SID,USERNAME,PROGRAM,COMMAND,SQL_ID, STATUS,EVENT,P1,P2,P3,WAIT_TIME,SECONDS_IN_WAIT,last_call_et from v$session where status = 'ACTIVE' and USERNAME IS NOT NULL; 何度か叩いてみましょう 続けて出てくる SQL_ID があったら そいつが怪しいです
V$SESSION SID セッションのID USERNAME PROGRAM COMMAND SQL_ID STATUS EVENT SQLを実行したユーザの名前 SQLを実行したプログラム SQLを実行したコマンド SQLに振られたID アクティブかインアクティブか 今回はアクティブしか見ない待機をしている場合 何が理由で待機をしているか P1,P2,P3 EVENT の補足情報 (EVENT によって内容が変わる ) WAIT_TIME SECONDS_IN_WAIT LAST_CALL_ET 待機時間 -1 は 1/100 秒未満 現在の待機が開始されるまでの時間 セッションがアクティブになってからの時間
SQL_ID を探したら SQL をみてみましょう select sql_text from v$sqltext Where sql_id = XXXXXXX order by piece ; SQL_ID から SQL テキストを導き出します
実行計画を見てみる SQL がどのように実行されているかを見てみます まずは どんな実行計画があるか見てみましょう select sql_id, plan_hash_value, executions, optimizer_cost, application_wait_time,concurrency_wait_time,cluster_wait_time,user_io_w ait_time,plsql_exec_time,java_exec_time,buffer_gets,physical_read_bytes from v$sql Where sql_id = XXXXXXX ;
V$SQL SID_ID PLAN_HASH_VALUE EXECUTIONS SQL に振られた ID 実行計画の値 実行回数 OPTIMIZER_COST オプティマイザが計算したコスト ( 実行負荷 ) XXXX_WAIT_TIME XXXX による待機時間 (5 つの総計が処理時間 ) BUFFER_GETS PHYSICAL_READ_BYTES 論理読み込み数 物理読み込み数
本当に SQL が悪いのか?
遅い SQL が悪いとも限らない バッチ処理で時間がかかる SQL そもそも処理量が多い ( 分析系 ) ハードパース処理 普段の状態との比較をする必要がある
普段の状態をどう調べるか 普段の状態 現在の状態 Statspack AWR スナップショット間のデータベースの状態を確認できる デフォルトで 1 時間に 1 回取得する スナップショット間のデータベースの状態を確認できる 期間比較やアドバイス機能あり ( 有償 ) v$session ASH (Active Session History) Enterprise Manager 現在データベースにあるセッション情報を確認できる データベースにあるセッション情報の履歴を確認できる ( 有償 ) パフォーマンスタブで現在のデータベースの状態を確認できる ( 有償 )
被害者と加害者 注意すべきなのは 待機しているセッションは 被害者 であること 被害者を無くするのではなく 加害者 をどうするか考えるのが重要
待機イベント 待機イベントとは プロセスが SQL を処理するために (CPU を使わずに ) 待たなくてはいけない出来事のこと I/O やロック競合などの情報を確認できる 図 )buffer busy wait の例
待機イベント例 (1) db file sequencial read ファイル読み込み ( 単一ブロック ) による待機 サッポロラガーがあるかメニュー確認しているファイル読込 ) で店員さんを待たせている 待機させられている
待機イベント例 (2) free buffer waits バッファキャッシュに空きが無くて待機している 3 人で来たが 2 席しか空いていない
SQL チューニングの基礎 どうやってチューニングするのか? チューニングにはどのような方法があるのだろうか?
SQL チューニングの基礎 実行計画の取得 まずは事前準備 権限の取得 (sysdbaにて実行) GRANT PLUSTRACE to USERS; PLAN_TABLEの作成 ( なければ ) @?/rdbms/admin/utlxplan.sql
SQL チューニングの基礎 実行計画の取得 フォーマット設定 SQL> set lines 200 SQL> col plan_plus_exp format a200 SQL> set pages 0 SQL> set autotrace on; SQL 実行 select num from test2 where num = 1;
SQL チューニングの基礎 実行計画の中身 (1) 実行計画 ---------------------------------------------------------- Plan hash value: 2520579295 ------------------------------------------------------------------------------ Id Operation Name Rows Bytes Cost (%CPU) Time ------------------------------------------------------------------------------ 0 SELECT STATEMENT 1 4 1 (0) 00:00:01 * 1 INDEX RANGE SCAN TEST2_IX1 1 4 1 (0) 00:00:01 ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("num"=1)
SQL チューニングの基礎 実行計画の中身 (2) 統計 ---------------------------------------------------------- 1 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 283 bytes sent via SQL*Net to client 388 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
SQL チューニングの基礎 主な問題 インデックスが無い 使用されない 表の結合と絞り込みの問題 ライブラリキャッシュが有効に使われていない インデックスを貼る 外部表で条件がある場合等 SQL 記述ルールとバインド変数化
SQL チューニングの基礎 ケース 1) ID OPERATION OPTIONS OBJECT_NAME OPT COST ---- -------------------- --------------- ---------------------- --- ---------- 0 SELECT STATEMENT CHO 1 NESTED LOOPS 125 2 VIEW 116 3 SORT UNIQUE 116 4 TABLE ACCESS FULL ORDERS ANA 40 5 TABLE ACCESS BY INDEX ROWID EMPLOYEES ANA 1 6 INDEX UNIQUE SCAN EMP_EMP_ID_PK ANA < チェックポイント > インデックスが使用されていない データを絞り込んでから JOIN していない
SQL チューニングの基礎 ケース 2) ID OPERATION OPTIONS OBJECT_NAME OPT COST ---- -------------------- --------------- ---------------------- --- ---------- 0 SELECT STATEMENT CHO 2 1 FAST DUAL 2 < チェックポイント > dual は内部的に発行する SQL が大量に発行される
SQL チューニングの基礎 解決のヒント SQL を書きなおす ヒント句を使う SQL 実行計画管理 (SPM) を使う SQL チューニングアドバイザ ( 有償 )
ORA-3113