AWR Report

Instance Efficiency Percentage

The Instance Efficiency Percentage report section contains ratios or calculations that may provide information regarding different structures and operations in the Oracle instance. Database tuning must never be driven by hit ratios. Hit ratios only provide additional information to help the DBA understand how the instance is operating.

For example, in the Decision Support System (DSS), a low cache hit ratio may be acceptable due to the amount of recycling needed due to the large volume of data accessed. If the size of the buffer cache is increased based on this number, the corrective action may not take affect and expensive RAM memory resources may be wasted. The following is a sample Instance Efficiency Percentage section of an AWR report:

Instance Efficiency Percentages (Target 100%)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

            Buffer Nowait %:   96.11       Redo NoWait %:   99.98

            Buffer  Hit   %:   66.52    In-memory Sort %:  100.00

            Library Hit   %:   98.42        Soft Parse %:   95.70

         Execute to Parse %:   80.96         Latch Hit %:  100.00

Parse CPU to Parse Elapsd %:    3.13     % Non-Parse CPU:   97.75

 Shared Pool Statistics        Begin    End

                              ------  ------

             Memory Usage %:   92.70   92.49

    % SQL with executions>1:   86.73   84.20

  % Memory for SQL w/exec>1:   84.12   71.86

The following list includes the meanings of particular hit ratios:

Buffer Hit Ratio: Measures how many times a required block was found in memory rather than having to execute an expensive read operation on disk to get the block.

Buffer Nowait %: Shows the percentage of times when data buffers were accessed directly without any wait time.

Library Hit %: Shows the percentage of times when SQL statements and PL/SQL packages were found in the shared pool.

Execute to Parse %: Shows how often parsed SQL statements are reused without reparsing.

Parse CPU to Parse Elapsd %: Gives the ratio of CPU time spent to parse SQL statements.

Redo NoWait %: Shows whether the redo log buffer has sufficient size.

In-memory Sort %: Shows the percentage of times when sorts are performed in memory instead of using temporary tablespaces.

Soft Parse %: Shows how often sessions issued a SQL statement that is already in the shared pool and how it can use an existing version of that statement.

Latch Hit %: Shows how often latches were acquired without having to wait.

% Non-Parse CPU: Shows the percentage of how much CPU resources were spent on the actual SQL execution.

In the above list of statistics, special attention should be paid to parse-related statistics. The Instance Efficiency Percentage report provided previously shows that about 95 percent of the parses are soft as indicated by the Soft Parse %. This is good enough, indicating that the SQL statements are actively reused by Oracle.

The next interesting item to review is the Parse CPU to Parse Elapsd % statistic. In this case, it is about three percent, which is very low. This fact reveals that Oracle waits for some resources during parsing of SQL statements. This should be investigated further to find the cause.

In this case, % Non-Parse CPU statistic is about 97 percent, which is quite high.  This indicates Oracle utilizes the CPU mostly for statement execution but not for parsing.

As a rule of thumb, one should always minimize the number of hard parses in your production database. This reduction yields the benefit of minimizing CPU overhead spent performing costly parse work.

This following sample report section shows shared pool related statistics:

Shared Pool Statistics        Begin    End

                              ------  ------

             Memory Usage %:   92.70   92.49

    % SQL with executions>1:   86.73   84.20

  % Memory for SQL w/exec>1:   84.12   71.86

In this example, the Memory Usage % statistic shows that almost all, approximately 92 percent, of the shared pool memory is consumed. This could indicate that the system experiences some overhead while aging out old shared memory structures like cursors, PL/SQL programs, and so on. This places additional overhead on the CPU to perform reparsing aging-out. The size of the shared pool should be increased appropriately to eliminate such overhead. In general, this statistic should be near 70 percent after the database has been running a long time. If it is quite low, memory is being wasted.

The % SQL with executions>1 statistic indicates how many SQL statements are executed more than one time. This measures how well production applications are tuned and how well they make use of bind variables.

SEE CODE DEPOT FOR FULL SCRIPTS

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm