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

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

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


How to flush shared pool?

One can flush the shared pool using the following DDL.


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;
v_result := DBMS_SQL.EXECUTE(v_cur);
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.

Automatic Shared Memory Management (ASMM)

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 size, maximum size, last operation (LAST_OPER_TYPE value: GROW, SHRINK, STATIC), OPER_COUNT (# of times SGA operations performed, at startup the value is 0) during the instance started of SGA components.
V$SGA_DYNAMIC_FREE_MEMORY – Shows free memory available for future SGA memory resize.
V$SGA_CURRENT_RESIZE_OPS – Currently SGA resize operations in progress.
V$SGAINFO – has usage and free size informaton on SGA size of different components.
V$SGASTAT – detail information on usage of SGA
V$SGA_DYNAMIC_COMPONENTS – information on dynamic components of SGA
V$SGA_TARGET_ADVICE – gives information on tuning of SGA_TARGET

Automatic memory management has it’s own background process (mman). It monitors the instance to find the best memory allocation for SGA.

If the database is iin automatic shared memory enabled the value of statistics_level cannot be changed to basic. If the value of sga_target is not set or if the value is 0 automatic shared memory management, statistics_level can be set to basic which means automatic memory management is turned off.

SQL> alter system set statistics_level=basic;
alter system set statistics_level=basic
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00830: cannot set statistics_level to BASIC with auto-tune SGA enabled

If SGA_MAX_SIZE is not set or it is less than SGA_TARGET then Oracle will SGA_MAX_SIZE to be the same value as SGA_TARGET. If one tries to increase SGA_TARGET to more than SGA_MAX_SIZE then you will get ORA-00823 so you may want to set SGA_MAX_SIZE which will allow one to increase SGA with restart of the instance.

SQL> show parameter sga;

———————————— ———– ——————————
sga_max_size big integer 160M
sga_target big integer 160M

SQL> alter system set sga_target=170M;
alter system set sga_target=170M
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size