Report Header:
This is
a self-explanatory section which provides information about database
name,database id, instance name, instance number, startup time, release number,
RAC/NON-RAC system, Hostname, platform, number of cpus, cores, memory and snap
interval etc.
Load Profile:
It
gives the information about workload on the database during the snapshot
captured time / interval time.
DB Time: It is
the amount of time spent on executing user calls. It doesn’t include background
processes
CPU Time: It is
the amount of CPU time spent on executing user calls. It doesn’t inclu
background processes and time is in milliseconds.
Logical Reads:
Consistent GETS + DB blocks GETS = Logical Reads
Block Changes:
The number blocks modified during snapshot interval time
Physical Reads:
The number of blocks request causing an I/O operation
Physical Writes:
The number of physical writes writes
User calls:
Number of user queries generated
Parses: The total
number of parses including soft parses as well as hard parses
Hard parses: The
number of hard parses
Soft parses:
parses-hard parses
Sorts: Number of
sorts performed
Logons: Number of
logons during snapshot interval time
Executes: Number
of SQLs executed during snapshot interval time
Transactions:
Number of transactions per second
Compare the statistics with a base line snapshot report to
determine if the database activity is abnormal.
Instance
Efficiency Percentage:
This
section displays the collected information in the form of ratios for the
several buffers like database buffer cache hit percentage and library cache hit
percentage and shared pool memory usage statistics also available in this
section. Instance efficiency should be close to 100%.
Execute to parse %
and Parse CPU to Parse Elapsed %, having low values means that there could
be a parsing problem. We may need to look at shared pool sizing issue or bind
variables issue.
Redo NoWait%:
Usually this stats is 99% or greater
In-memory Sort%:
This deals about sort_area_size, hash_area_size and pga_aggregate_area, if any
of these are not enough in size to handle the database load then In-memory
Sort% will go down
Latch Hit%: Should
be close to 100%
Soft parse%: with 98.20 % for the soft parse meaning that
about 1.72 % (100 -soft parse) is happening for hard parsing. You might want to
look at you bind variables issues.
% Non-Parse CPU: Most
of our statements were already parsed so we weren't doing a lot of re parsing.
Re parsing is high on CPU and should be avoided.
Buffer
Nowait %:
|
99.99
|
Redo
NoWait %:
|
100.00
|
Buffer
Hit %:
|
93.06
|
In-memory
Sort %:
|
100.00
|
Library
Hit %:
|
98.67
|
Soft
Parse %:
|
98.20
|
Execute
to Parse %:
|
3.40
|
Latch
Hit %:
|
99.98
|
Parse
CPU to Parse Elapsd %:
|
0.01
|
%
Non-Parse CPU:
|
96.21
|
Shared pool
Statistics:
|
Begin
|
End
|
|
|
Memory Usage %:
|
73.86
|
75.42
|
|
% SQL with
executions>1:
|
92.61
|
93.44
|
|
% Memory for SQL
w/exec>1:
|
94.33
|
94.98
|
Memory usage % is the shared pool usage, SO here 73.86% of
our shared pool used and out of that almost 94% is being re-used.
If memory usage % is larger like 90% means that our shared
pool is too small and if it is at 50% then shared pool is too large
