liguofeng29’s blog

個人勉強用ブログだっす。

Oracle - メモリチューニング

-- キャッシュヒット率

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 * 100, 2)

|| '%' AS ディクショナリヒット率

FROM V$ROWCACHE;

-- 共有プール適切サイズ

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;

/

*1:1 - (p_reads / (c_gets + d_gets))),3) * 100;

DBMS_OUTPUT.PUT_LINE(

'データベース・バッファキャッシュヒット率->' ||

result || '%');

END;

/

-- ディクショナリヒット率

SELECT SUM(GETS) AS キャッシュヒット合計,

SUM(GETMISSES) AS キャッシュミス合計,

ROUND((1 - SUM(GETMISSES) / SUM(GETS