Analyaze AWR Report

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