Oracle® 非公式 SQL, PL/SQL & DBA's ガイドサイト

分析・チューニング関連

チューニング( キャッシュヒット率、隠し初期化パラメータ、 DML とトランザクション数)

キャッシュヒット率の取得

• ライブラリ・キャッシュ

SELECT
      ROUND(100 * SUM(PINHITS) / SUM(PINS), 2) "LIBRARY HIT RATE(%)"
 FROM
       V$LIBRARYCACHE ;

• ディクショナリ・キャッシュ

SELECT
        ROUND(100 * SUM(GETS-GETMISSES) / SUM(GETS), 2) "DICTIONARY HIT RATE(%)"
 FROM
        V$ROWCACHE ;

関連 初期化パラメータ　SHARED_POOL_SIZE (共有プール)

• データベースバッファ・キャッシュ

SELECT
        ROUND(100- 100 * SUM(DECODE(NAME,'physical reads',VALUE,0)) /
	     (SUM(DECODE(NAME,'db block gets from cache',VALUE,0)) + 
              SUM(DECODE(NAME,'consistent gets from cache',VALUE,0))), 2)
	"DB CACHE HIT RATE(%)"
   FROM 
         V$SYSSTAT
   WHERE 
         NAME IN
         (
          'physical reads', 'db block gets from cache',
          'consistent gets from cache'
         );
 
 -- または
SELECT
        ROUND(100- 100 * SUM(DECODE(NAME,'physical reads',VALUE,0)) /
	     (SUM(DECODE(NAME,'db block gets',VALUE,0)) + 
              SUM(DECODE(NAME,'consistent gets',VALUE,0))), 2)
	"DB BUFFER HIT RATE(%)"
   FROM 
      V$SYSSTAT
   WHERE 
      NAME IN
         ('physical reads', 'db block gets', 'consistent gets');

関連 初期化パラメータ　DB_CACHE_SIZE / (DB_BUFFER_SIZE)

隠し初期化パラメータ

隠しパラメータ(要 SYS権限) / Kernel Service, Parameter

SELECT PI.KSPPINM NAME , SV.KSPPSTVL VALUE, PI.KSPPDESC DES
   FROM X$KSPPI PI, X$KSPPCV SV
  WHERE PI.INDX = SV.INDX AND PI.KSPPINM LIKE '\_%' ESCAPE '\'

テーブル毎の DML 実行回数の取得(目安)

前回の表の統計情報を収集してから変更されている表(パーティション)の情報を取得する。
INSERT/UPDATE/DELETEの各回数と TRUNCATE TABLE の有無がわかる。

SELECT * FROM DBA_TAB_MODIFICATIONS ; -- (DBAロールでは閲覧不可)
SELECT * FROM ALL_TAB_MODIFICATIONS ;
SELECT * FROM USER_TAB_MODIFICATIONS ;

注意 このデータディクショナリは、即時反映ではない。(約 3時間毎に更新される)
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO を使用すると手動で反映させることが可能

総トランザクション数の取得

インスタンス起動からのトランザクション数

SELECT SUM(VALUE) "USER TRANSACTIONS" FROM V$SYSSTAT
  WHERE NAME IN ( 'user commits', 'user rollbacks');

JOB スケジューラで定時実行し、そのスナップショットログを残すことで異常なトランザクションの増加(減少) を発見することができる。

チューニング ( バッファクリア、ソートメモリ、SQL トレース)

チューニングの検証前のメモリフラッシュ

• ダーティバッファの書き出し(更新系の処理の場合)

  ALTER SYSTEM CHECKPOINT ;

• 共有プール(SHARED POOL) の フラッシュ

  ALTER SYSTEM FLUSH SHARED_POOL ;

• データベース・バッファ・キャッシュのクリア (Oracle 9iでは不可能＝インスタンスの停止が必要)

  ALTER SYSTEM FLUSH BUFFER_CACHE ;

  注意 SGA上のバッファ・キャッシュがフラッシュされる
  同一インスタンス上で実行中の、すべてのSQLに影響が発生する。(結果レコードが返されないなど)
  周知されたタイミング以外の使用は他人に迷惑をかけることもある。(本番運用状態のインスタンスでは厳禁)

チューニング順序

チューニングの高速化にインパクトのある順序は

1. 設計
2. アプリケーション(SQL)
3. ミドルウェア(アプリケーションサーバーなど)
4. インスタンス
   1. メモリ(SGA,PGA,SORT_AREA)
   2. Disk IO
   3. 競合(ロック)
   4. 初期化パラメータ
5. オペレーティングシステム(カーネルパラメータ)
6. ハードウェア となる

(1),(6)は調達やスケジュールにより、早い段階で FIX させる必要があるため困難、痛み(費用、設計の巻き戻り)を伴う
現実レベルでは SQL のチューニング(＝プログラミング能力)によるところが大きい。

メモリ管理 ( SGA、PGA ) SORT_AREA_SIZE

• SORT_AREA_SIZE 初期化パラメータ

  ディスクソートが多い場合には、初期化パラメータの変更を検討
  局所的なSQLだけには、 ALTER SESSION SET SORT_AREA_SIZE=〜 を使用を検討する。

  SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE 'sorts%'

  NAME	VALUE
  sorts (memory)	15175
  sorts (disk)	1
  sorts (rows)	4460599

  ※ 専用サーバー接続では PGA に、共有サーバー接続では、共有プール or ラージプール上(SGA)に確保される。
  ただし、専用サーバーにおいて PGA_AGGREGATE_TARGET が使用できる環境では推奨されないパラメータである。

• 関連コマンド

  ipcs (UNIX:IPC リソース表示 )
  SHOW SGA (SQL*Plus : SGA のサイズ表示)

トレース SQL-TRACE

インスタンスレベルでのトレース(初期パラメータの設定)

ALTER SYSTEM SET SQL_TRACE=TRUE ;
ALTER SYSTEM SET TIMED_STATISTICS=TRUE ;

セッション単位でのトレース

ALTER SESSION SET SQL_TRACE=TRUE ;
(PL/SQL パッケージ DBMS_SESSION.SET_SQL_TRACE(TRUE) )

トレースファイルの出力

USER_DUMP_DEST 初期パラメータディレクトリに出力
--
SELECT
      value
 FROM v$parameter
WHERE
      value = 'user_dump_dest';
--

抽出＆フォーマット

tkprof filename1 filename2 [waits=yes|no] [sort=option] [print=n]
      [aggregate=yes|no] [insert=filename3] [sys=yes|no] [table=schema.table]
      [explain=user/password] [record=filename4] [width=n]