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 '\'

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

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

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

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

チューニング ( バッファクリア、ソートメモリ、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]

---

移転済

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

移転先 ⇒ USER_TAB_MODIFICATIONS（詳細）

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

移行先 ⇒ ALTER SYSTEM FLUSH SHARED_POOL、ALTER SYSTEM FLUSH BUFFER_CACHE