• The first part

Database profile information

DB Name         DB Id    Instance     Inst Num Release     Cluster Host
---------- ----------- ------------ --------   -----------  ------------
GLOB         188430914   glob              1  9.2.0.4.0   NO    b02

• The second part

During the sampling period of database, this part records the sampling time of database and the number of sampling points. This part of information is very important for report.

Any statistical data needs to be measured by time latitude. Without time, any data is meaningless.

We often see people posting Top 5 in forums waiting for events to be analyzed. Our answer is:

No analysis, no latitude and time!

            Snap Id     Snap Time      Sessions Curs/Sess Comment
            ------- ------------------ -------- --------- -------------------
Begin Snap:   508    10-Nov-03 15:27:29       76      39.4
End Snap:     511    10-Nov-03 15:57:42       66      35.4
   Elapsed:                               30.22 (mins)

• The third part

Description of main performance indicators:

• Execute to Parse% Execute Analysis Ratio

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %: 100.00
            Buffer  Hit   %:   99.81    In-memory Sort %: 100.00
            Library Hit   %:   98.75        Soft Parse %:  97.05
         Execute to Parse %:   44.21         Latch Hit %:  94.79
Parse CPU to Parse Elapsd %:   11.74     % Non-Parse CPU:  96.08

The formula for calculating the execution analysis ratio is as follows:

100 * (1 - Parses/Executions) = Execute to Parse

So if the system Parses > Executions, it may occur that the ratio is less than 0.

The parameters are calculated from the following parts:

Instance Activity Stats for DB: ORA9  Instance: ora91  Snaps: 30 -32

Statistic                              Total     per Second    per Trans
--------------------------------- ------------------ -------------- ------------
exchange deadlocks                       481            0.2          0.0
execute count                      4,873,158        1,968.2         94.4

……………

parse count (failures)                   542            0.2          0.0
parse count (hard)                    80,281           32.4          1.6
parse count (total)                2,718,643        1,098.0         52.6
parse time cpu                        44,009           17.8          0.9
parse time elapsed                   374,902          151.4          7.3

…………………….

Through the formula and the above two numerical values:

100 * (1 - Parses/Executions) = Execute to Parse
100 * (1 - 2,718,643/4,873,158) = 0.44211884777797067117462 * 100 = 44.21

This value < 0 usually indicates problems with shared pool settings or efficiency.

Causing repeated parsing, reparse may be more serious, or it may be related to snapshot

If the value is negative or extremely low, it usually indicates a problem with database performance.

• Parse CPU to Parse Elapsd %

From parse time cpu and parse time elapsed

100*(parse time cpu / parse time elapsed)= Parse CPU to Parse Elapsd %

100*(44,009 / 374,902)= 11.7388010733471680599196590% = 11.74%

• Rollback per transaction average transaction rollback rate

  % Blocks changed per Read:    0.37    Recursive Call %:    1.14
   Rollback per transaction %:   38.22       Rows per Sort:   11.83


If the rollback rate is too high, it may indicate that your database has experienced too many invalid operations.
Too many rollbacks may also lead to competition from Undo Block

The formula for calculating this parameter is as follows:


     Round(User rollbacks / (user commits + user rollbacks) ,4)* 100%

…………….
user commits                             31,910           12.9          0.6
user rollbacks                           19,740            8.0          0.4
…………….

For this example:

     Round(19740 / (31910 + 19740),4) = .3822

The content of this part has not been finished yet and is continuing.