Understanding AWR

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