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;
/