KNOWN

１．データベース バッファ キャッシュは　HIT率を９０％以上を維持する。

     下記スクリプトでヒット率を確認する。
　　ヒット率が90%以上でなければ初期化パラメータファイル（init.ora）の
　　データベース・バッファ・キャッシュのサイズを変更する必要がある。

  -------------------------------------------------------  
  -- ヒット率の確認用スクリプト
  -------------------------------------------------------
  SET SERVEROUTPUT ON;
 
  DECLARE
      d_gets  NUMBER;
      c_gets  NUMBER;
      p_reads NUMBER;
      result  NUMBER;
  BEGIN
      SELECT VALUE INTO d_gets  FROM V$SYSSTAT WHERE NAME = 'db block gets';
      SELECT VALUE INTO c_gets  FROM V$SYSSTAT WHERE NAME = 'consistent gets';
      SELECT VALUE INTO p_reads FROM V$SYSSTAT WHERE NAME = 'physical reads';
      result := ROUND((1 - (p_reads / (c_gets + d_gets))),3) * 100;
      DBMS_OUTPUT.PUT_LINE( '  db block gets -> ' || d_gets  );
      DBMS_OUTPUT.PUT_LINE( 'consistent gets -> ' || c_gets  );
      DBMS_OUTPUT.PUT_LINE( ' physical reads -> ' || p_reads );
      DBMS_OUTPUT.PUT_LINE( 'データベース・バッファキャッシュヒット率 = ROUND((1 - (p_reads / (c_gets + d_gets))),3) * 100' );
      DBMS_OUTPUT.PUT_LINE( 'データベース・バッファキャッシュヒット率->' || result || '%');
  END;
  /
 
  ---------------------------------------------------
  -- 結果例
  ---------------------------------------------------
  db block gets   -> 21357627
  consistent gets -> 2734980151
  physical reads  -> 29680776
  DB CACHE HIT RATE = ROUND((1 - (p_reads / (c_gets + d_gets))),3) * 100
  データベース・バッファキャッシュヒット率->98.9%
 
  PL/SQLプロシージャが正常に完了しました。
 


２．データベース・バッファ・キャッシュの見積もり

　　データベース・バッファ・キャッシュとして割り当てられたメモリの比率が
　　0以下になってしまうと「バッファ・キャッシュ余裕なし」になってしまいます。
　　
　　X$BHからバッファ・キャッシュの空き比率を確認する。　
　　見積りをするために現在のバッファサイズを確認する。
　　空き比率が１以上になるようSGA_MAX_SIZEを確認してグラニュル単位

  ---------------------------------------------------------------------------------------------  
  --     バッファ・キャッシュの空き比率確認用スクリプト
  --     SQLPLUS SYS/******@接続文字列 AS SYSDBAにて接続が必要
  ---------------------------------------------------------------------------------------------  
  SET SERVEROUTPUT ON;
 
  DECLARE
      cnt_total NUMBER;
      cnt_free NUMBER;
      result NUMBER;
      message VARCHAR2(256);
  BEGIN
      SELECT COUNT(*) INTO cnt_total FROM X$BH;
      SELECT COUNT(*) INTO cnt_free  FROM X$BH  WHERE STATE = 0;
      result := ROUND((cnt_free / cnt_total), 3) * 100;
      SELECT DECODE(SIGN(result), 1,
            'バッファキャッシュ余裕有り',
            'バッファキャッシュ余裕なし') INTO message
            FROM DUAL;
      DBMS_OUTPUT.PUT_LINE( '  cnt_total -> ' || cnt_total  );
      DBMS_OUTPUT.PUT_LINE( '  cnt_free   -> ' || cnt_free   );
      DBMS_OUTPUT.PUT_LINE( 'バッファ空き比率 =ROUND((cnt_free / cnt_total), 3) * 100' );
      DBMS_OUTPUT.PUT_LINE('バッファ空き比率->' || result || '%');
      DBMS_OUTPUT.PUT_LINE(message);
  END;
  /
 
  ---------------------------------------------------
  -- 結果例
  ---------------------------------------------------
  cnt_total -> 994
  cnt_free   -> 0
  バッファ空き比率 =ROUND((cnt_free / cnt_total), 3) * 100
  バッファ空き比率->0%
  バッファキャッシュ余裕なし
 
  PL/SQLプロシージャが正常に完了しました。
 
 
  ---------------------------------------------------------------------------------------------  
  --　　Oracle9i以降
  --　　現状のデータベース・バッファ・キャッシュサイズ
  --                 ＝DB_CACHE_SIZE
  ---------------------------------------------------------------------------------------------  
  　　SQL> SHOW PARAMETERS DB_CACHE_SIZE
 
  ---------------------------------------------------------------------------------------------  
  --　　Oracle8i以前
  --　　現状のデータベース・バッファ・キャッシュサイズ
  --               ＝ DB_BLOCK_BUFFERS × DB_BLOCK_SIZE
  ---------------------------------------------------------------------------------------------  
      SQL> SHOW PARAMETERS DB_BLOCK_BUFFERS
      SQL> SHOW PARAMETERS DB_BLOCK_SIZE
  
 
 

　　　V$SGA_DYNAMIC_COMPONENTSの「GRANULE_SIZE」の項目でも確認可能。

      　取得したバッファ空き比率と現在設定されているバッファサイズの2項目を基に、
        最適なデータベース・バッファサイズを設定する。
　　　その際、SGA_MAX_SIZEの値により、その単位（グラニュル単位と呼ぶ）に合わせる必要がある。
　　　例えば、バッファサイズを１０MBと検討した場合
        SGA_MAX_SIZEが１２８MB未満であれば 12MBに、
        SGA_MAX_SIZEが１２８MB以上であれば 16MBにしたほうがよい。
  


３．共有プール全体のサイズの見直し

　現在の共有プールサイズを確認する。
   ---------------------------------------------------------------------------------------------   
　--  現在設定されている共有プールサイズ、
　--  最低限必要な共有プールサイズ、
　--  適正な共有プールのメモリサイズ確認用スクリプト
  ---------------------------------------------------------------------------------------------   
   SET SERVEROUTPUT ON;
    DECLARE
       sum_db_object_cache NUMBER;
       sum_s_sqlarea NUMBER;
       sum_u_sqlarea NUMBER;
       now_parameter NUMBER;
       result NUMBER;
    BEGIN
       SELECT SUM(SHARABLE_MEM) INTO sum_db_object_cache
         FROM V$DB_OBJECT_CACHE;
       SELECT SUM(SHARABLE_MEM) INTO sum_s_sqlarea
         FROM V$SQLAREA;
       SELECT SUM(250 * USERS_OPENING) INTO sum_u_sqlarea
         FROM V$SQLAREA;
       result :=
         sum_db_object_cache + sum_s_sqlarea + sum_u_sqlarea;
       SELECT value INTO now_parameter
         FROM V$PARAMETER WHERE NAME = 'shared_pool_size';
       DBMS_OUTPUT.PUT_LINE(
         'パラメータSHARED_POOL_SIZE->' || now_parameter);
       DBMS_OUTPUT.PUT_LINE(
         '現在最低限必要なSHARED_POOL_SIZE->' || result);
       DBMS_OUTPUT.PUT_LINE(
         '適正なSHARED_POOL_SIZE(×1.3)->' || result * 1.3);
    END;
    /

   ライブラリ・キャッシュ・ヒット率
  ライブラリ・キャッシュのヒット率を計算することで、
  SQLやパッケージが再利用されているかを確認できます。
   ヒット率は99％以上が望ましいとされています。
　チューニングする場合には下記2点を考慮する。
    １．バインド変数を使用する
    ２．PL/SQLストアド・プロシージャやストアド・ファンクションはできるだけパッケージ化する

   ---------------------------------------------------------------------------------------------   
　--  ライブラリ・キャッシュ・ヒット率確認用スクリプト
  ---------------------------------------------------------------------------------------------   
     SELECT SUM(PINS) AS キャッシュヒット合計,
            SUM(RELOADS) AS キャッシュミス合計,
            ROUND((1 - SUM(RELOADS) / SUM(PINS)) * 100, 2)
                  || '%' AS ライブラリキャッシュヒット率
     FROM V$LIBRARYCACHE;
     
  ---------------------------------------------------
  -- 結果例
  ---------------------------------------------------
    キャッシュヒット合計 キャッシュミス合計 ライブラリキャッシュヒット率
    -------------------- ------------------ -----------------------------------------
           293435291              57405 99.98%

     実行されるたびに解析が行われているSQLは、
    実行された回数と解析された回数が同じと考えられます。
    これはV$SQLAREAから確認可能
   ---------------------------------------------------------------------------------------------   
　--  実行されるたびに解析が行われているSQLの実行数、解析数確認用スクリプト
  ---------------------------------------------------------------------------------------------   
     SELECT SQL_TEXT AS SQL文,
            PARSE_CALLS AS 解析回数,
            EXECUTIONS AS 実行回数
     FROM V$SQLAREA
     WHERE PARSING_USER_ID != 0
     AND PARSE_CALLS = EXECUTIONS
     ORDER BY SQL_TEXT;



    ディクショナリ・キャッシュ・ヒット率の算出
    ディクショナリ・キャッシュのヒット率を計算することで、
    データベース・オブジェクト情報をメモリから取得できているか確認できます。
    ヒット率は95％以上が望ましいとされています。
   ---------------------------------------------------------------------------------------------   
　--  ディクショナリ・キャッシュ・ヒット率の確認用スクリプト
  ---------------------------------------------------------------------------------------------   
     SELECT SUM(GETS) AS キャッシュヒット合計,
            SUM(GETMISSES) AS キャッシュミス合計,
            ROUND((1 - SUM(GETMISSES) / SUM(GETS)) * 100, 2)
                  || '%' AS ディクショナリヒット率
     FROM V$ROWCACHE;
  ---------------------------------------------------
  -- 結果例
  ---------------------------------------------------
     キャッシュヒット合計 キャッシュミス合計 ディクショナリヒット率
    -------------------- ------------------ -----------------------------------------
            46037213             490311 98.93%


    初期化パラメータの変更
    実際に初期化パラメータファイルのパラメータを変更するか、
    「ALTER SYSTEM …」で各パラメータを変更します。
    なお、Oracle9i以降のバージョンでは、データベース・バッファ・キャッシュと共有プールは
    動的に変更可能です。

    SQL> ALTER SYSTEM SET パラメータ名 = 値;

    当然のことながら、今回取り上げたメモリ設定は、SGA_MAX_SIZEを超えるサイズは指定できません



引用：　SGAに起因するパフォーマンス障害を発見する　Page 1/3
　　　　　高橋 潤　2004/12/11
　　　　　http://www.atmarkit.co.jp/fdb/rensai/oraobstacle05/oraobstacle05_1.html