AWR : Instance Efficiency Percentages, Shared Pool

Continuing from the Load Profile, we now analyse instance parametes.

This will be in the Instance Efficiency Percentages and Shared Pool Statistics

Buffer NOWAIT and Buffer Hit Percentages

If we were to see the Buffer NOWAIT and Buffer Hit Percentages are low,typically say less than 95
percentage, we can review the sizing of the Data block buffers. The init.ora parameter for this 
is the DB_CACHE_SIZE.

Library Hit Percentage

If the library hit percentage is low we will need to investigate and find the reason for this low
percentage. If query parsing and bind variable usage is not optimum this can be analyzed. We can consider
increasing the shared pool allocation,if the values are consistently low and the exact reason for this
cannot be pin pointed. The init.ora parameter for this is SHARED_POOL_SIZE. 

The Redo NoWait %:

This parameter tells how efficiently the redo logs are being utilized. It the percentages are low we
need to look at tuning the redo Log buffer and the redo log files. If processes are waiting for redo, 
maybe the sizing needs to be re looked at , or something may be preventing the optimum usage of the redo
logs. For instance,  if the number of redo log files is less or the archiver writer processes are less,
the system may have to wait for the archive log process to copy the logs to the archive location. Only 
after successful writing of logs to the archive, can the redo log be taken up for writing again, thus
decreasing the NOWAIT percentage.

The In Memory Sort %

This parameter tells us if PGA_AGGREGATE_TARGET is sized correctly or in case of manual settings
SORT_AREA_SIZE, HASH_AREA_SIZE values need to be re examined. Numbers less than 100% indicate that sorts
are going to disk, which can cause significant performance issues.

Soft Parse %

This parameter tells us how often the SQL statement which is submitted is found in the memory cache. If
this value is not close to 100% it is indicative of improper bind variable usage or high amount of ad-hoc
SQL generation. This will result in more hard parses which are comparatively more costlier.

Latch Hit %

Latches protect memory buffers from concurrent usage. Latch hit percentage is the ratio of total latch
misses to latch gets. It tells us how often we are not waiting on latches. If this value is low we need to
look for CPU bound processes or other issues in latching.

Non parse CPU %

This is the time spent not parsing. A high value for this parameter indicates that the CPU is spending
more time on executing the queries rather than parsing them. If this value is low we also need to look at
the parse-related percentages because they tool will be low. 

Shared pool statistics

Memory Usage Percentage

One of the purposes of the shared pool is to provide a pool of pre parsed SQL statements that can quickly
be reused. If the memory usage is 70% to 75% or higher it indicates a good reuse occuring in the database.
A value less than 70% is an indication that the application should be reviewed for proper SQL reuse like
PL/SQL encapsulation and proper bind variable usage.

From Oracle documentation I could get the following information for the below awr parameters. 

%SQL with executionns>1:

From the universe of shared cursors, which % has been executed more than once?

%Memory for SQL w/exec >1:

If both % are low, then it indicates shared pool is small and needs resizing and/or improper use of bind
variables which will need a relook at application level. Both these issues can be looked at to improve
these percentage values in the shared pool.

References