Buffer and Dictonary Hit ratio

— 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…

How to flush shared pool?

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