Category Archives: Shared Pool

Buffer and Dictonary Hit ratio

Posted on by
Reply

— Buffer hit ratio
select (1-(sum(decode(name, ‘physical reads’, value, 0))/
(sum(decode(name, ‘db block gets’, value, 0)) +
(sum(decode(name, ‘consistent gets’, value, 0))))))*100 “Buffer Hit Ratio”
from v$sysstat;

Read Hit Ratio
————–
99.1751978

Buffer hit ratio, ideal value would be 98%-100%. If you are not using automatic shared memory changing db_cache_size could increase performance, if using automatic shared memory changing sga_target and sga_max_size.

— Dictionary hit ratio
SQL> select (1-(sum(getmisses)/(sum(gets)+sum(getmisses))))*100 “Dict Hit Ratio”
from v$rowcache;

Dict Hit Ratio
————–
91.8405306

Ideal percent recommended hit ratio is 95% or higher, increasing shared_pool_size could be increased to increase hit ratio.

Advertisements

How to flush shared pool?

Posted on by
Reply

One can flush the shared pool using the following DDL.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

Using the following one can implement the functionality in a store procedure.
SQL> CREATE OR REPLACE procedure flush_shared_pool IS
v_cur INTEGER;
v_result INTEGER;
BEGIN
v_cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cur, ‘ALTER SYSTEM FLUSH SHARED_POOL’, DBMS_SQL.NATIVE);
v_result := DBMS_SQL.EXECUTE(v_cur);
DBMS_SQL.CLOSE_CURSOR(v_cur);
END;
/
And in special circumstances one can grant execute access “grant execute on sys.flush_shared_pool to SCOTT;”
to this store procedure if there is a need in a development environment.