Analyaze
AWR Report
Performance
report in database
for
this we need to check the following section of the AWR Report
1)
Session Information:-
we can find out the session information from the top of
the awr report
Check
the Begin snap and End snap time if there is high number of seesions
then we need to check the shadow process of the session or check
whether shadow process is being created. The most likely cause is in
application start-up issue, which is spawning all those sessions.
2)
Load profile:-
It
shows per-second and per-transaction statistics . And for database
load like hard parse and number of transactions
while
looking to the Load profile first important look into is “DB CPU”
per second . But we need to understand what is is “DB CPU”
suppose we have 8 core into system. So we have a 8 seconds to work
on the CPU. If in report if DB CPU per second is greater than the
cores in host configuration means our environment is CPU bound issue
so we need to add the CPU on our environment or check for further
for it's happening all the time or just fraction of them.
Next
we need to look into the Parses and Hard parses . If the hard parses
to parses is high, it means database is performing the more hard
parse. In that case we need to check on the parameter like
cursor_sharing and the bind variables that are available on the
application level.
3)
Instance Efficiency percentages:-
This
section show several hit ratio as well as Execute to Parse percentage
and latch hit percentage. The execute to Parse percentage should be
very high in good running instances.
If
we have Low value of the % SQL with executions>1: statistics means
instance is not re-using shared sql statements because SQL not using
bind variables.
Now
we will check for the meaning of the particular hit ratio:-
a)
Buffer Nowait:- show the percent of times when data buffers were
accessed directly without any wait time.
b)
Buffer Hit Ratio:- It calculate the percentage on the basic of how
many times required block is found in the memory rather than having
to execute an expensive read operation on disk to get the block.
c)
Library Hit:- This shows that how many times the SQL statement and
PL/SQL package is found in the shared pool memory cache.
d)
Execute to Parse:- how frequently SQL statements are reused without
re-parse.
e)
Parse CPU to Parse Elapsd %:- Give the ratio of CPU time spent to
parse sql statement
f)
Redo NoWait:- shows whether the redo log buffer has sufficient size.
g)
In-memory Sort :- shows the percentage of sorting performed in
memory instead of using temporary tablespace.
h)
Soft parse % :- It shows that whenever any session has issued any SQL
statement that is already in shared pool and how it use an existing
statement.
I)
Latche Hit % :- shows how often latches were acquired without
having to wait.
J) %
Non-Parse CPU:- It show percentage of how much of CPU resources were
spent on the actual SQL execution.
Int
this statistics we need to look at the
soft
parse % :- 100% shows that the SQL statements are actively re-used.
%
Non-Parse CPU:- 100% means CPU resources are used into operation
other than parsing . Means this is good for the database.
Parse
CPU to Parse Elapsed:- it is happen very low. This represent that
that oracle is waiting for resources during parsing for SQL
statements. If it's then there is need to be investigate further.
3)
TOP FIVE FOREGROUND EVENT
This section describe the
events that were responsible for the most wait during the entire
snapshot details. This report is very critical because this events
are responsible for the system bottleneck this wait events are
arranged by total wait events.
a)
DB File Sequential Read:-
This
event signifies that the user process is reading a buffer into the
SGA buffer cache and is waiting for a physical I/O call to return. A
sequential read is a single-block read.
Single block I/Os
are usually the result of using indexes. Rarely, full table scan
calls could get truncated to a single block call due to extent
boundaries, or buffers already present in the buffer cache. These
waits would also show up as 'db file sequential read'.
b)
DB CPU:-
DB
CPU is Oracle server/foreground/shadow process CPU consumption.Each
Oracle server process gathers its own CPU consumption using the time
and/or getrusage C function system call. So unless there is a major
screw-up by either the operating system or the Oracle kernel
developers, the time will be good... very good. The name DB CPU is
taken from the actual statistic name, which is found in both
v$sess_time_model and v$sys_time_model.
If you look at any AWR or Statspack report in the
"Time Model" section, you will see DB CPU.
c)
DB FILE SCATTERED READ :-
This
event signifies that the user process is reading buffers into the SGA
buffer cache and is waiting for a physical I/O call to return. A db
file scattered read issues a scattered read to read the data into
multiple discontinuous memory locations of buffer cache.A scattered
read is usually a multi block read. It can occur for a fast full scan
(of an index) in addition to a full table scan.
The db
file scattered read wait event identifies that a full scan is
occurring. When performing a full scan into the buffer cache, the
blocks read are read into memory locations that are not physically
adjacent to each other. Such reads are called scattered read calls,
because the blocks are scattered throughout memory. This is why the
corresponding wait event is called 'db file scattered read'.
multiblock (up to DB_FILE_MULTIBLOCK_READ_COUNT blocks) reads due to
full scans into the buffer cache show up as waits for 'db file
scattered read'
d)
Direct Path Read:-
The session is waiting
for a direct read to complete. A direct read is a physical I/O from a
data file that bypasses the buffer cache and reads the data block
directly into process-private memory.
If asynchronous I/O
is supported (and in use), then Oracle can submit I/O requests and
continue processing. Oracle can then pick up the results of the I/O
request later and wait on "direct path read" until the
required I/O completes.
If asynchronous I/O is not being
used, then the I/O requests block until completed but these do not
show as waits at the time the I/O is issued. The session returns
later to pick up the completed I/O data but can then show a wait on
"direct path read" even though this wait will return
immediately. Hence this wait event is very misleading because: •
The total number of waits does not reflect the number of I/O requests
• The total time spent in "direct path read" does not
always reflect the true
e) Control file
Sequential Read:-
The control file
sequential read oracle metric indicates indicates the process is
waiting for block to be read from a control file.
This happens in many
cases. For example:-
I) Making a backup of
the controlfile.
ii) Sharing
information (between instances) from the control file.
iii) Reading other
block from the controlfile.
iV) Reading the header
block
control file sequential
read waits occur when control file are placed on the same physical
disk causing read-write head contention at the disk level.
4) TIME MODEL
STATISTICS
This
sectio give the detail about how the database is spend the time. The
most processing time on actual SQL execution but not
on parsing.
5) Operating system
statistics:-
This shows the
operating system load status
REF:-
Oracle12c-Performance-Tuning-Recipes
