— 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…
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;…
To use automatic shared memory management, the value of statistics_level must be set to “typical” or “all”. The parameter can be changed without restarting the database. The following views shows information on SGA resize operations: V$SGA_RESIZE_OPS – the view contains information about the last 400 completed SGA resize operations. V$SGA_DYNAMIC_COMPONENTS – Shows current size, minimum…
