·
AWR Report And Its Analysis
Oracle have provided many performance gathering and
reporting tools over the years. Originally the UTLBSTAT/UTLESTAT scripts were
used to monitor performance metrics. Oracle8i introduced the Statspack
functionality which Oracle9i extended. In Oracle 10g statspack has evolved into
the Automatic Workload Repository (AWR).
AWR
Features
The AWR is used to collect performance
statistics including:
· Wait events used to identify performance problems.
· Time model statistics indicating the amount of DB time
associated with a process
from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
· Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
· Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
· Object usage statistics.
·
Resource intensive SQL
statements.
Workload Repository Views
The following workload repository views
are available:
·
V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every
second.
·
V$METRIC - Displays metric information.
·
V$METRICNAME - Displays the metrics associated with each metric group.
·
V$METRIC_HISTORY - Displays historical metrics.
·
V$METRICGROUP - Displays all metrics groups.
·
DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session
history.
·
DBA_HIST_BASELINE - Displays baseline information.
·
DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
·
DBA_HIST_SNAPSHOT - Displays snapshot information.
·
DBA_HIST_SQL_PLAN - Displays SQL execution plans.
· DBA_HIST_WR_CONTROL - Displays AWR settings.
Workload Repository Reports
Oracle provide two scripts to produce
workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in
format to the statspack reports and give the option of HTML or plain text
formats. The two reports give essential the same output but the awrrpti.sql
allows you to select a single instance. The reports can be generated as
follows.
AWR report can be generated upon
setting the parameter is called statistics_level=typical or all, If set to
basic it will be disabled.
We can get the value like below.
SQL> show parameter statistics_l
NAME TYPE
VALUE
------------------------------------
----------- ------------------------------
statistics_level string TYPICAL
SQL>
- Snapshots
Snapshots
are sets of historical data for specific time periods that are used for
performance comparisons by ADDM. By default, Oracle Database automatically
generates snapshots of the performance data once every hour and retains the
statistics in the workload repository for 8 days. You can also manually create
snapshots, but this is usually not necessary. The data in the snapshot interval
is then analyzed by the Automatic Database Diagnostic Monitor (ADDM)
- Managing
Snapshots:-
By default, Oracle Database generates
snapshots once every hour, and retains the statistics in the workload
repository for 8 days. When necessary, you can use DBMS_WORKLOAD_REPOSITORY procedures to
manually create, drop, and modify the snapshots. To invoke these procedures, a
user must be granted the DBA role.
2.1.
Creating
Snapshots :-
You can manually create snapshots with the CREATE_SNAPSHOT procedure to capture
statistics at times different than those of the automatically generated
snapshots. For example:
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/
2.2.
Dropping
Snapshots:- You can drop a range of snapshots using the DROP_SNAPSHOT_RANGE
procedure. To view a list of the snapshot IDs along with database IDs, check
the DBA_HIST_SNAPSHOT view. For example, you can drop the following range of
snapshots:
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE
(low_snap_id => ,
high_snap_id => , dbid =>);
END;
/
2.3.
Modifying
Snapshot Settings:- You can adjust the
interval, retention, and captured Top SQL of snapshot generation for a
specified database ID, but note that this can affect the precision of the
Oracle Database diagnostic tools. The INTERVAL setting affects how often the
database automatically generates snapshots. The RETENTION setting affects how
long the database stores snapshots in the workload repository. The TOPNSQL
setting affects the number of Top SQL to flush for each SQL criteria (Elapsed
Time, CPU Time, Parse Calls, sharable Memory, and Version Count). The value for
this setting is not affected by the statistics/flush level and will override
the system default behavior for the AWR SQL collection. It is possible to set
the value for this setting to MAXIMUM to capture the complete set of SQL in the
shared SQL area, though by doing so (or by setting the value to a very high
number) may lead to possible space and performance issues because there will
more data to collect and store. To adjust the settings, use the
MODIFY_SNAPSHOT_SETTINGS procedure. For example:
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
retention => 43200,
interval
=> 30, topnsql => 100, dbid => 3310949047);
END;
/
- Generate
AWR report:-
The awrrpt.sql SQL script
generates an HTML or text report that displays statistics for a range of
snapshot IDs.
Below is the example.
Generating an Oracle RAC AWR
Report:-
The
awrgrpt.sql SQL script generates an HTML or text report that displays statistics
for a range of snapshot IDs using the current database identifier and all
available database instances in an Oracle Real Application Clusters (Oracle
RAC) environment.
Generate
a report for specific Instance.
The
awrrpti.sql SQL script generates an HTML or text report that displays
statistics for a range of snapshot IDs using a specific database and instance.
This script enables you to specify a database identifier and instance for which
the AWR report will be generated.
Details AWR
Analysis Concept
The
AWR report contains a significant amount of information which helps to focus on
certain areas to get started.
AWR
sample information at particular times. The AWR default sample is every 60
minutes.
By
default, Oracle Database automatically generates snapshots once every hour.
Checklist
1)
Review Overall picture from AWR header information
2)
Check Host and Instance CPU to determine the proportion of CPU usage by this
instance
3)
Check the Load profile to use later in the context of the top waits
4
)Examine Top 5 Timed Events for highest resource users
Checklist
Detail
This
checklist steps through the recommended areas to investigate when presented
with an AWR report. It does not assume
that
you have any information other than the database is performing slowly and what
is contained in the AWR output.
• Review Overall picture from AWR
header information
The
header section contains useful information that can help set the context of the
report you are looking at.
For
example, the report contains a number of sections that quote specific counts of
various statistics.
Without a timescale, these numbers are
meaningless.
o Release
Depending
on the problem, the database version may be important. If the version is old or
is not the latest patchset release then the most up to date fixes may not be
applied which has the potential to open the database up to issues.
o RAC
If
the database is running in a Real Application Cluster configuration then you
may need to look at information from the other instances to get a full picture
of the database performance
o Platform
There
may be platform specific issues that have a bearing on the system
o CPUs/Cores
In
a multi-processor environment, the "wall clock" time is not
necessarily a good indicator of how much work the database can do since
multiple operations can be pursued simultaneously. You can use cores for an
indication of how much CPU work can likely be done at once.
o Snap Time
The
Snap time shows the times for the starting and ending snapshots for the report
period. Does this cover the time of problem that is being encountered?
o Elapsed time
The
elapsed time indicates the duration of the report between the 2 selected
snapshots. Any other duration figures can be compared back to this. When
looking at this figure, is the duration reasonable? If the duration is too
short then important information may be missed. If it is too long then findings
may be diluted. A 30-60 minute reporting period is usually recommended. In
terms of AWR snapshots, as much as possible snapshots should be minimum 10
minutes, maximum 30 minutes.
o DB time
The
DB Time is the time spent in the database for the period of the report. If this
is significantly higher than the Elapsed time then this is a good indicator of
a heavily loaded system. Remember that on a multi-processor system, you might
expect the DB Time to be able to exceed the elapsed time. Additionally, the db
time includes the time waiting for the CPU to become available, so this number
can be higher than the Elapsed time X Cores.
In
the example above, the numbers say that the database worked for 2193 minutes in
15 minutes of elapsed time. Whether that is an indication of a problem depends
on the capacity and concurrency capabilities of the system. Looking at the
numbers, 2193:15 is a ratio of 146:1, so, in this case, if they had
significantly less than 146 cpus it is likely that there is some overloading
issues. Remember that the user
perception is also a significant factor in whether there is a "performance
issue" - if the system delivers what the users want then there might not
be a problem!
o Sessions
You
can use the sessions information along with the DB time to give an average
amount of DB time per session. Are there a large number or a small number of
connections?
• Check Host and Instance CPU to determine the proportion of CPU usage by
this instance
Another
important area to look at before going to the detail of the top wait events is
the Host and Instance CPU sections.
These
provide information regarding how much load there is on the underlying
operating system and also how much of it is attributable to the instance in the
AWR report. If the system is heavily loaded, then the performance of the
database itself may be affected by the external contention. In these cases,
look to see how much of the total CPU usage is being caused by this instance.
In this case, 92.4% of the Total CPU can be attributed to the instance, which
would tend to indicate that improving the instance performance is likely to
improve the overall performance. If the instance was only responsible for a
small proportion of the overall CPU, it may be that the problem lies elsewhere.
• Check the Load profile to use later in the context of the top waits
The
load profile section can provide you with a more detailed impression of where
the database is loaded. Information is provided "Per Second" and
"Per Transaction" for most statistics and also "Per Exec"
and "Per Call" for DB Time and CPU.
Suggested
interpretations:
o DB CPU(s)
The
DB CPU(s) figure shows the amount of the CPU being used by the database. You
can use this alongside the actual number of cores to give you an idea of how
much of the total available machine CPU is actually being used by this
instance.
o DB Time(s) Here the "Per Second" information gives you another
version of the total DB time used, just in this case expressed as every second
as opposed to the full elapsed period.
Other
statistics should be looked at within the context of the overall elapsed time
and also in the context of the top waits, once you have looked at these later.
For example:
o Top events indicate library cache or cursor contention
In
this case it would be sensible to look at the load in terms of Parse and Hard
Parse statistics. The number of parses per execution could also be a relevant
indicator
o Top events are related to reading of blocks
In
this case, do we see mainly physical or logical reads? If it is physical then
are the explain plans for top queries such as to encourage more logical reads?
At
this point you may also want to look at the Instance Efficiency Percentages to
see if these bear out the findings from the above:
Looking
at these in the context of a specific wait is far more beneficial than
attempting to reach 100%. If the bottleneck is elsewhere, attempting to change individual
statistics will have little or no impact on the overall system. For example, in
the Instance Efficiency Percentages above, the "Buffer Hit %" is
99.88%. If there is no contention for buffers and no waits for buffers, then
what is the benefit in making changes to try to improve this number?
You
should also look at the numbers in the context of the other numbers. For
example, in the case above, let us say that there is a performance issue and
the top timed events showed that CPU usage was a significant resource. Looking
at the "Parse CPU to Parse Elapsd %" alone, this says that 26.87% of
the total parse time is CPU and maybe you would prefer a lower percentage
(although 26% seems quite reasonable).
Since the: "% Non-Parse
CPU" is 98.07% this means that only 1.03% of the total CPU usage is
parsing, so even if you reduced that 26.87% to the impossible value of zero
then you would only gain 1% extra CPU overall. It is likely that you would need
to look elsewhere for the cause of your CPU resource issue.
• Examine Top 5 Timed Events for highest resource users
Once
you have looked at the background information, the Top 5 Timed Events section
is the place to start in order to tell what is taking up the largest proportion
of the database time. Based upon the general feeling for the system, the top
resource users are put in context and can be investigated to determine a root
cause. This topic is covered in more detail in the following article:
Interpretation
This
document provides guidance on some background information to bear in mind when
examining the detail of this section that can help by framing the context of
the problem.Since we are looking at a performance issue, our primary concern is
what the database is waiting for.When processes wait, they are being prevented
from doing an activity because of some other factor. High waits provide the
highest benefit when wait times are reduced and as such are a good focus.
The
Top Wait information provides such information and allows us to focus on the
main problem areas without wasting time investigating areas that are not
causing significant delay.
• Top 5 Timed Events
As
mentioned, the Top waits section is the most important single section in the
whole report being as it quantifies and allows comparison of the primary
diagnostic: what each session is waiting for. An example output is provided
below:
Top
5 Timed Events Avg
%Total
~~~~~~~~~~~~~~~~~~
wait Call
Event Waits Time (s)
(ms) Time Wait Class
------------------------------
------------ ----------- ------ ------ ----------
db
file scattered read
10,152,564 81,327 8
29.6 User I/O
db
file sequential read 10,327,231 75,878 7
27.6 User I/O
CPU
time
56,207 20.5
read
by other session
4,397,330 33,455 8
12.2 User I/O
PX
Deq Credit: send blkd
31,398 26,576 846
9.7 Other
-------------------------------------------------------------
The
Top 5 Waits section reports on a number of useful topics related to Events. It
records the number of waits encountered in the period and the total time spent
waiting together with the average time waited for each event. The section is
ordered by the %age of the total call time that each Event is responsible for.Dependent
on what is seen in this section, other report sections may need to be
referenced in order to quantify or check the findings. For example, the wait
count for a particular event needs to be assessed based upon the duration of
the reporting period and also the number of users on the database at the time;
10 Million waits in 10 minutes is far more significant than 10 Million in 10
hours, or if shared among 10 users as opposed to 10,000.
In
this example report, almost 60% of the time is spent waiting for I/O related
reads.
o Event 'db file scattered read ' is typically used when fetching blocks for
a full table scan index fast full scan and performs multi-block IO.
o Event 'db file sequential read' is
a single block read and is typically engaged for any activity where multi-block IO is unavailable (for example
index reads).
Another
20% of the time is spent waiting for or using CPU time. High CPU usage is often
a symptom of poorly tuned SQL (or at least SQL which has potential to take less
resource) of which excessive I/O can also be a symptom. More on CPU usage
follows later.
Based
on this, we would investigate whether these waits indicate a problem or not. If
so, resolve the problem, if not, move on to the next wait to determine if that
is a potential cause.
There
are 2 main reasons why I/O related waits are going to be top of the waits:
o The database is doing lots of reads
o The individual reads are slow
The
Top 5 events show us information that helps us here :
o Is the database doing lots of
reads?:
The
section shows > 10 Million reads for each of these events in the period.
Whether
this is a lot depends on whether the report duration is 1 hour or 1 minute.
Check
the report duration to asses this.
If
the reads do seem excessive, then why would the database do a lot of reads?
The
database only reads data because the execution of SQL statements has instructed
it to do so.
To investigate further refer to the SQL
Statistics Section.
o Are the individual reads slow?
The
section shows waits of <=8 ms for the 2 I/O related events.
Whether
this is fast or slow is dependent on the hardware underlying the I/O subsystem,
but typically anything under 20 ms is acceptable.
If
the I/O was slow, then you can get further information from the 'Tablespace IO Stats ' section:
o Tablespace IO Stats DB/Inst:
VMWREP/VMWREP Snaps: 1-15
o -> ordered by IOs (Reads + Writes) desc
o Tablespace
o ------------------------------
o Av Av
Av Av Buffer Av Buf
Reads
Reads/s Rd(ms) Blks/Rd Writes
Writes/s Waits Wt(ms)
o -------------- ------- ------
------- ------------ -------- ---------- ------
o TS_TX_DATA
o 14,246,367 283
7.6 4.6 145,263,880
2,883 3,844,161 8.3
o USER
o 204,834 4
10.7 1.0 17,849,021 354
15,249 9.8
o UNDOTS1
o 19,725 0
3.0 1.0 10,064,086 200
1,964 4.9
o AE_TS
o 4,287,567 85
5.4 6.7 932 0
465,793 3.7
o TEMP
o 2,022,883 40
0.0 5.8 878,049 17 0
0.0
o UNDOTS3
o 1,310,493 26
4.6 1.0 941,675 19
43 0.0
o TS_TX_IDX
o 1,884,478 37
7.3 1.0 23,695 0
73,703 8.3
o SYSAUX
o 346,094 7
5.6 3.9 112,744 2 0
0.0
o SYSTEM
101,771 2
7.9 3.5 25,098 0
653 2.7
Specifically,
look for the timing under Rd(ms). If it
is higher than 20 milliseconds per read and reads are high, then you may want
to start investigating a potential I/O bottleneck from the os.
NOTE:
You should ignore relatively idle tablespaces/files as you can get high values
due to disk spin-up etc. which are not relevant. If you have an issue with 10
million reads being slow it is unlikely that a tablespace/file with 10 reads
has caused the problem!
Although
high waits for 'db file scattered read' and 'db file sequential read' can be
I/O related, it is actually more common to find that these waits are relatively
'normal' based on the SQL that the database is being asked to run. In fact, on
a well tuned database, you would want these events to be top of the waits,
since that would mean that no 'problem' events were there instead!
The
trick is being able to assess whether the high waits is indicative of some SQL
statements are not using optimal paths (as mentioned earlier) or
otherwise. If there are high waits for
'db file scattered read', then SQL may not be using optimal access paths and so
are tending to do Full Table Scans as opposed to indexes (or there may be missing indexes or
not optimal indexes). Furthermore, high
waits for 'db file sequential read' may indicate SQL statements are using
unselective indexes and there for reading more index blocks than necessary or
using the wrong indexes. So these waits
may point to poor execution plans for SQL(s).
In
either case, the next step would be to check the top resource consuming SQL(s)
from the AWR report to determine whether these look excessive or whether
improvements can be made.
As
mentioned, 20% of the time is spent waiting for or using CPU time. This should
also be looked at when looking at the SQL Statistics.
Remember
that the next step to take following the Top 5 Waits is dependent upon the
findings within that section. In the example above, 3 of the waits point
towards potentially Sub-optimal SQL so that should be the section investigated
next.
Equally,
if you do not see any latch waits, then latches are not causing a significant
problem on your instance and so you do not need to investigate latch waits
further.
Generally,
if the database is slow, and the Top 5 timed events include "CPU" and
"db file sequential read" and "db file scattered read" in
any order, then it is usually worth jumping to the Top SQL (by logical and
physical reads) section of an AWR report and calling the SQL Tuning Advisor on
them (or tune them manually) just to make sure that they are running
efficiently.
• SQL Statistics
AWR
Reports show a number of different SQL statistics:
The
different SQL statistic sub sections should be examined based upon the Top Wait
events seen in the Top 5 Section.
In
our example, we saw top waits as 'db file scattered read' , 'db file sequential
read' and CPU. For these, we are most interested in SQL ordered by CPU Time, Gets and Reads. These sections actually duplicate some
information adding other specifics as appropriate to the topic.
Often
looking at 'SQL ordered by gets' is a convenient stating point as statements
with high buffer gets are usually good candidates for tuning :
SQL
ordered by Gets
->
Resources reported for PL/SQL code includes the resources used by all SQL
statements
called by the code.
->
Total Buffer Gets: 4,745,943,815
->
Captured SQL account for 122.2% of
Total
Gets CPU Elapsed
Buffer
Gets Executions per Exec
%Total Time (s) Time (s) SQL Id
--------------
------------ ------------ ------ -------- --------- -------------
1,228,753,877 168
7,314,011.2 25.9 8022.46
8404.73 5t1y1nvmwp2
SELECT
ADDRESSID",CURRENT$."ADDRESSTYPEID",CURRENT$URRENT$."ADDRESS3",
CURRENT$."CITY",CURRENT$."ZIP",CURRENT$."STATE",CURRENT$."PHONECOUNTRYCODE",
CURRENT$."PHONENUMBER",CURRENT$."PHONEEXTENSION",CURRENT$."FAXCOU
1,039,875,759 62,959,363 16.5
21.9 5320.27 5618.96 grr4mg7ms81
Module:
DBMS_SCHEDULER
INSERT
INTO "ADDRESS_RDONLY" ("ADDRESSID","ADDRESSTYPEID","CUSTOMERID","
ADDRESS1","ADDRESS2","ADDRESS3","CITY","ZIP","STATE","PHONECOUNTRYCODE","PHONENU
854,035,223 168
5,083,543.0 18.0 5713.50
7458.95 4at7cbx8hnz
SELECT
"CUSTOMERID",CURRENT$."ISACTIVE",CURRENT$."FIRSTNAME",CURRENT$."LASTNAME",CU<
RRENT$."ORGANIZATION",CURRENT$."DATEREGISTERED",CURRENT$."CUSTOMERSTATUSID",CURR
ENT$."LASTMODIFIEDDATE",CURRENT$."SOURCE",CURRENT$."EMPLOYEEDEPT",CURRENT$.
Tuning
can either be performed either manually or by calling the SQL Tuning Advisor on
them:
Analysis:
o -> Total Buffer Gets: 4,745,943,815
On
the assumption that this is an hour long report, this is a significant number
of gets and as such this confirms that it is worth investigating the top SQL
statements to make sure they are taking optimal paths.
o Individual Buffer Gets
The
buffer gets for the individual statements shown are very high with the lowest
being 850 Million. These 3 statements actually point towards 2 different
reasons for the large number of buffers:
Excessive Buffer
Gets/Execution
SQL_IDs
'5t1y1nvmwp2' and '4at7cbx8hnz' are only executed 168 times, but each execution
reads over 5 Million buffers. This SQL statement is a prime candidate for
tuning since the number of buffers read in each execution is so high.
Excessive
Executions
On
the other hand SQL_ID 'grr4mg7ms81' only reads 16 buffers for each execution.
Tuning the individual statement may not be able to reduce that significantly.
However, the issue with this statement is caused by the number of times it is
executed - 65 Million.
Changing
the way in which the statement is called is likely to have the largest impact
here - it is likely that the statement is called in a loop, once per record, if
it could be called so as to process multiple records at once then there is
potential for significant economies of scale.
Remember
that these numbers may be 'normal' for this environment (since some are very
busy). By comparing this report against
a baseline, you can see whether these SQL statements also read this much data
when the database performs well. If they do then they are not the cause of the
issue and can be ignored (although there may be benefit generally in improving
them).
Other SQL
Statistic Sections
As
mentioned previously, there are a number of different report sections that help
for specific causes. If you do not have the particular cause, then there is
likely to be little benefit in looking at these. The following section outlines
some potential causes and uses:
• Load Profile
Dependent
on the waits, the load profile section either provides useful general
background information or specific details related to potential issues.
Load
Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo
size: 4,585,414.80 3,165,883.14
Logical
reads: 94,185.63 65,028.07
Block
changes: 40,028.57 27,636.71
Physical
reads: 2,206.12 1,523.16
Physical
writes: 3,939.97 2,720.25
User
calls: 50.08 34.58
Parses: 26.96 18.61
Hard
parses: 1.49 1.03
Sorts: 18.36 12.68
Logons: 0.13 0.09
Executes: 4,925.89 3,400.96
Transactions: 1.45
%
Blocks changed per Read: 42.50 Recursive Call %: 99.19
Rollback
per transaction %: 59.69 Rows per Sort: 1922.64
In
the example, the waits section shows potential for issues with the execution of
SQL so the load profile can be checked for details in this area, although it is
not the primary source of such information.
If
you were looking at the AWR report for general tuning, you might pick up that
the load section shows relatively high redo activity with high physical writes.
There are more writes than reads on this load with 42% block changes.
Furthermore,
there is less hard parsing compared the soft parses.
If
there was a mutex wait as top wait such as 'library cache: mutex X', then
statistics such as the overall parse rate would be more relevant.
Again,
comparing to a baseline will provide the best information, for example,
checking to see if the load has changed by comparing redo size, users calls,
and parsing.
• Instance Efficiency
Again,
instance efficiency stats are more use for general tuning as opposed to
addressing specific issues (unless waits point at these).
Instance
Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer
Nowait %: 99.91 Redo NoWait %: 100.00
Buffer Hit
%: 98.14 In-memory Sort %: 99.98
Library
Hit %:
99.91 Soft Parse %: 94.48
Execute
to Parse %: 99.45 Latch Hit %: 99.97
Parse
CPU to Parse Elapsd %: 71.23 % Non-Parse CPU: 99.00
The
most important Statistic presented here from the point of view of our example
is the '% Non-Parse CPU' because this indicates that almost all the CPU time that we see in the Top Waits section is
attributable to Execution and not parse, which means that tuning SQL may help
to improve this.
If
we were tuning, then 94.48% soft parse rate would show a small proportion of
hard parsing which is desirable. The
high execute to parse % indicates good usage of cursors. Generally, we want the statistics here close
to 100%, but remember that a few percent may not be relevant dependent on the
application. For example, in a data
warehouse environment, hard parsing may be higher due to usage of materialized
views and, or histograms. So again
comparing to baseline report when performance was good is important.
• Latch Activity
In
the example, we are not seeing significant waits for latches so this section
could be ignored.
However,
if latch waits were significant, then we would be looking for high latch sleeps
under Latch Sleep Breakdown for latch free waits:
Latch
Sleep Breakdown
*
ordered by misses desc
Latch
Name
----------------------------------------
Get
Requests Misses Sleeps
Spin Gets Sleep1 Sleep2
Sleep3
--------------
----------- ----------- ---------- -------- -------- --------
cache
buffers chains
2,881,936,948
3,070,271
41,336 3,031,456 0
0 0
row
cache objects
941,375,571 1,215,395 852
1,214,606 0 0
0
object
queue header operation
763,607,977 949,376 30,484
919,782 0 0
0
cache
buffers lru chain
376,874,990 705,162 3,192
702,090 0
0 0
Here
the top latch is cache buffers chains. Cache Buffers Chains latches protect the
buffers in the buffer cache that hold data that we have retrieved from disk.
This is a perfectly normal latch to see when data is being read. When this
becomes stressed, the sleeps figure tends to rise as sessions start to wait to
get the buffers they require. Contention can be caused by poorly tuned SQL
reading the same buffers.
In
our example, although the gets are high at 2.8 billion buffer gets, the sleeps
at 41,336 is low. Average number of
sleeps per miss ratio (Avg Slps/Miss) is low. The reason for this is that the
server is able to deal with this volume of data and so there is no significant
contention on Cache Buffers Chains latches at this point.
For
other latch free waits, review the following Document to identify what type of
latches to investigate:
Notable
timed and wait events:
• CPU time events
Just
because CPU comes as top timed event in AWR may not indicate a problem. However, if performance is slow with high CPU
usage, then start investigating the wait.
First, check to see if a sql is taking most CPU under SQL ordered by CPU
Time in AWR:
SQL
ordered by CPU Time
->
Resources reported for PL/SQL code includes the resources used by all SQL
statements
called by the code.
->
% Total is the CPU Time divided into the Total CPU Time times 100
->
Total CPU Time (s): 56,207
->
Captured SQL account for 114.6% of
Total
CPU Elapsed CPU per % Total
Time
(s) Time (s) Executions
Exec (s) % Total DB Time SQL Id
----------
---------- ------------ ----------- ------- ------- -------------
20,349 24,884 168 121.12
36.2 9.1 7bbhgqykv3cm9
Module:
DBMS_SCHEDULER
DECLARE
job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :myda
te;
broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname
VARCHAR2(30)
:= :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start
TIMESTAMP
WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME
Analysis:
o -> Total CPU Time (s): 56,207
This
represents 15 minutes of CPU time in total. Whether this is significant depends
on the report duration.
o The top CPU using SQL uses 20,349
second (around 5 minutes),
o Total DB of time this represents is
9.1%.
o Executions is 168 - being as this
execution count is the same as 2 of the 3 SQLs identified earlier, these may be
related and this task may well be the scheduling job that runs the SQLs.
Actions:
Once
you have identified the SQL statements that are using the highest CPU,
investigate the reason for this usage.
o Look at the number of executions
and see whether that is appropriate for this statement. Excessive executions
might indicate that the statement is being called too frequently and it might
be possible to execute it for a group of rows rather than row by row (i.e.
execute it in a batch).
o Is the amount of CPU per execution
excessive - this might indicate that the statement itself is inefficient.
o Additionally, look at the other SQL
Statistics in the AWR report to see if the SQLID(s) in question show excessive
values for any of those, then deal with the statement appropriately.
• Details ADDM Analysis Concept
Real
–Time Analysis
Important
which need to address from AWR report.
1)
Load Profile
2)
Instance Efficiency Target (100%)
3)
Top 5/10 Events
4)
Time Mode Statistics
The first section displayed on the report shows a
summary of the snapshot window for your report as well as a brief look at the
elapsed time, which represents the snapshot window, and the DB time, which
represents activity on your database. If the DB time exceeds the elapsed
time, it denotes a busy database. If it is a lot higher than the elapsed
time, it may mean that some sessions are waiting for resources.
The instance efficiency section gives you a very
quick view to determine if things are running adequately on your database.
Generally, most percentages within this section should be above 90%. The Parse
CPU to Parse Elapsd metric shows how much time the CPU is spending parsing SQL
statements.
The third place to get a quick glance at your
database performance is the Top 5 Timed Events
section. This section gives you a quick look at
exactly where the highest amount of resources are being consumed within your
database for the snapshot period. Based on these results, it may show you that
there is an inordinate amount of time spent
performing full-table scans, or getting data across a network database link.
Below
is one example which shows main point related to of awr report which are
checked and recommend to application team.
IN Top Wait Event is coming enq:TX - row lock contention its totally based on one
Query which was being fired from App side.
It is also forcing us to go for the SQL
Ordered part as sql execute to elapsed
time is high close to 99% of DB time was being spent on it.
Let go on the Drill Down part now based on
the above observation:-
DB file parallel write wait event is totally
related to I/O Problem , I will show the artifacts of the same below as
well.
Common Causes and Actions
The db file parallel write latency is
normally a symptom of a slow I/O subsystem or poor I/O configurations. This
includes poor layout of database files, bad mount point to I/O controller
ratio, wrong stripe size and/or RAID level, and not enough disks (i.e. there
are a few high capacity disks versus many lower capacity disks).
The DBA needs to look at the average I/O
time.
Please find the Query which is Creating a
problem in the DB as its running with FOR Update clause also its coming high on
the Elapsed Time as well as on the CPU
Time.
So request you please check this Query and
its logic with App Team.
Here is the Artifacts of I/O Problem , Please check the Av RD(ms) column .
In a good I/O system it should not go above
the 20 but in our case it is going much beyond that request you to take care
this part asap..
Files Snapshot :-
The Table used in the above Highlighted Query
i.e Network_Stocks table is the main object creating the Row Locks Waits as
well.
Backup was also running during the
Problematic time :-
Also when I checked the System I observed
that All Application Session were going on the Node 2 only but today its
looking good.
Real –Time
AWR Analysis
Operating System Statistics
Statistic Total
AVG_BUSY_TIME 127,823
AVG_IDLE_TIME 360,597
AVG_IOWAIT_TIME 50,342
AVG_SYS_TIME 77,536 <---
AVG_USER_TIME 50,133
BUSY_TIME 1,279,619
IDLE_TIME 3,607,361
IOWAIT_TIME 504,842
SYS_TIME 776,815 <-- CPU
usage in sys mode is higer than user mode cpu
USER_TIME 502,804
Service Wait Class Stats
Service Name User I/O Total Wts User I/O Wt Time
Concurcy Total Wts Concurcy Wt Time Admin Total Wts Admin Wt Time Network Total
Wts Network Wt Time
APE 374814 2507 6258 10 0 0 7446511 10
SQL ordered by Reads
Physical Reads Executions Reads per Exec %Total
CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
154,993 1 154,993.00 14.84 13.46 78.82
0fzw3xs5kftff brconnect@hpx238 (TNS V1-V3) BEGIN DBMS_STATS.GATHER_TABLE_...
Buffer Pool Advisory
Only rows with estimated physical reads >0 are
displayed ordered by Block Size, Buffers For Estimate
P Size for Est (M) Size Factor Buffers for
Estimate Est Phys Read Factor Estimated Physical Reads
D 208 0.09 25,727 1.56 2,863,218
D 416 0.19 51,454 1.31 2,398,979
D 624 0.28 77,181 1.22 2,241,790
D 832 0.37 102,908 1.16 2,125,522
D 1,040 0.47 128,635 1.11 2,040,142
D 1,248 0.56 154,362 1.08 1,985,035
D 1,456 0.65 180,089 1.06 1,940,507
D 1,664 0.75 205,816 1.04 1,899,354
D 1,872 0.84 231,543 1.02 1,866,894
D 2,080 0.94 257,270 1.01 1,844,630
D 2,224 1.00 275,081 1.00
1,831,660 <-- current value
D 2,288 1.03 282,997 1.00 1,824,321
D 2,496 1.12 308,724 0.99 1,809,369
D 2,704 1.22 334,451 0.98 1,791,970
D 2,912 1.31 360,178 0.96 1,758,676
D 3,120 1.40 385,905 0.94 1,726,093
D 3,328 1.50 411,632 0.93 1,710,225
D 3,536 1.59 437,359 0.93 1,699,210
D 3,744 1.68 463,086 0.92
1,676,303 <--- Can be seen benefit ,if increased
D 3,952 1.78 488,813 0.91 1,669,838
D 4,160 1.87 514,540 0.91 1,663,223
Observations
================
++ log switches are occuring 12
per hour. Please resize redo logs and add redo log more groups ,in order to
ensure that log switch to happen at around 30 min interval.
++ Seeing large "Buffer
Busy Waits" on object DBTABLOG~0,can be rebuild with large initrans(50)
and pctfree (50)
++ Seeing zero free memory with buffer cache and
shared pool. Please ensure to resize this pools
to get overall performance improvements.
++ "Operating System Statistics"says that sys space cpu usage is higher compared to
user space cpu usage.
Please have OS admin to check
any issue with OS functionality.In ideal practice user space cpu usage should
be greater than or equal to sys space cpu usage
++ From "Service Wait Class Stats" ,the
APE Service is showing large network waits.
Please check if there is any network latencies .
++ Seeing dbms_stats gathering activity was in
use by brconnect@hpx238 module.Please gather statistics
gathering activity during non-peak hours.
ASH Report
ASH Report(Oracle Active Session History)- it need to generate when performance analysis on sessions that run too frequently or are too short to be available on AWR snapshots. it can show more real-time or near real-time session information to assist in doing performance analysis on your database. The ASH default sample is 1 second, but later is stored in 10 second intervals on disk.
it is useful to collect ASH reports in a
situations where you need to narrow down which selects are responsible for a
particular wait you want to know when a particular wait occurred within the
snapshot period to tie up with performance spikes or intermittent hang
Using ASH, Yoy will get following
·
Top SQL
·
Top Sessions
·
Top Waits
·
Blocking Sessions
·
Top Objects
·
Waits by time during sample intervals
SQL>script
for getting ASH Report on RAC database:
SQL>@$ORACLE_HOME/rdbms/admin/ashrpti.sql
SQL script
for getting ASH Report for single Instance:
SQL>@$ORACLE_HOME/rdbms/admin/ashrpt.sql
ADDM Report
The Automatic Database Diagnostic Monitor (ADDM) analyzes
data in the Automatic Workload Repository (AWR) to identify potential
performance bottlenecks. For each of the identified issues it locates the root
cause and provides recommendations for correcting the problem. An ADDM analysis
task is performed and its findings and recommendations stored in the database
every time an AWR snapshot is taken provided the STATISTICS_LEVEL
parameter is set to TYPICAL or ALL. The ADDM analysis includes the following.
· CPU load
· Memory usage
· I/O usage
· Resource intensive SQL
· Resource intensive PL/SQL and Java
· RAC issues
· Application issues
· Database configuration issues
· Concurrency issues
·
Object contention
addmrpt.sql
Script
The addmrpt.sql
script can be used to create an ADDM report from SQL*Plus. The script is called
as follows.
-- UNIX
@/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/addmrpt.sql
-- Windows
@d:\oracle\product\10.1.0\db_1\rdbms\admin\addmrpt.sql
SQL> @?/rdbms/admin/addmrpt.sql
Below is one test case for addm:-
I found row
lock wait was too high so down the line found problematic query as well and
shared that query with application team to check from their and it was
rectified by them.
addmrpt_1_22542_22543.txt
ADDM Report
for Task 'TASK_33939'
---------------------------------
Analysis
Period
---------------
AWR snapshot
range from 22542 to 22543.
Time period
starts at 06-JUN-16 04.00.06 PM
Time period
ends at 06-JUN-16 05.00.12 PM
Analysis
Target
---------------
Database
'DBNAME' with DB ID 577252600.
Database
version 11.2.0.3.0.
ADDM
performed an analysis of instance PACSMG, numbered 1 and hosted at
XXXXXXXX.
Activity
During the Analysis Period
-----------------------------------
Total
database time was 36014 seconds.
The average
number of active sessions was 9.99.
Summary of
Findings
-------------------
Description Active
Sessions Recommendation
s
Percent of
Activity
---------------------------------------- ------------------- --------------
-
1 Top SQL Statements 6.59 | 66.02 6
2 Row Lock Waits 4.17 | 41.7 1
3 Undersized SGA .64 | 6.4 1
4 Top Segments by "User I/O" and
"Cluster" .19 | 1.91 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Findings and
Recommendations
----------------------------
Finding 1:
Top SQL Statements
Impact is
6.59 active sessions, 66.02% of total activity.
---------------------------------------------------------
SQL
statements consuming significant database time were found. These
statements
offer a good opportunity for performance improvement.
Recommendation
1: SQL Tuning
Estimated
benefit is 4.46 active sessions, 44.68% of total activity.
--------------------------------------------------------------------
Action
Investigate
the SELECT statement with SQL_ID "8wfzx3nyvma8m" for
possible
performance improvements. You can supplement the information
given here
with an ASH report for this SQL_ID.
Related
Object
SQL
statement with SQL_ID 8wfzx3nyvma8m.
SELECT
TRIM(MDL_PARAMETER_VALUE) FROM FDS_M_MODULE_PARAM WHERE
MOD_1_MOD_ID
='PROVS' AND MDL_PARAMETER_ID =:B1 FOR UPDATE OF
FDS_M_MODULE_PARAM.MDL_PARAMETER_VALUE
Rationale
The SQL
spent only 0% of its database time on CPU, I/O and Cluster
waits.
Therefore, the SQL Tuning Advisor is not applicable in this case.
Look at
performance data for the SQL to find potential improvements.
Rationale
Database
time for this SQL was divided as follows: 100% for SQL
execution,
0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL
statement with SQL_ID "8wfzx3nyvma8m" was executed 412 times and had
an average
elapsed time of 36 seconds.
Rationale
Waiting for
event "enq: TX - row lock contention" in wait class
"Application"
accounted for 100% of the database time spent in
processing
the SQL statement with SQL_ID "8wfzx3nyvma8m".
Recommendation
2: SQL Tuning
Estimated benefit
is .54 active sessions, 5.41% of total activity.
------------------------------------------------------------------
Action
Run SQL
Tuning Advisor on the UPSERT statement with SQL_ID
"672j4rmvvnmru".
Related
Object
SQL
statement with SQL_ID 672j4rmvvnmru.
MERGE INTO
SIMSWAP_REPROCESS X USING ( SELECT SYSDATE CREATION_DATE,
B.SER_SERVICE_ORDER_ID,
A.SER_MSISDN, A.SER_IMSI_NO,
A.SER_SERVICE_VALUE,
B.SER_RECIEVED_DT_TIME, A.SER_ERROR, 'PENDING'
STATUS, NULL
HLR_IMSI, NULL REPROCESS_DATE, D.SIM_SIM_NO NEW_SIM_NO
FROM
INCMS.CIN_M_SIM D, INCMS.CIN_T_SERVICE_ORDER_DETAIL A,
INCMS.CIN_T_SERVICE_ORDER
B, ( SELECT SER_MSISDN,
MAX(SER_RECIEVED_DT_TIME)
SER_RECIEVED_DT_TIME FROM
INCMS.CIN_T_SERVICE_ORDER_DETAIL
WHERE SER_RECIEVED_DT_TIME >
SYSDATE-3
AND SER_SERVICE_ID IN ('SIMCH','SMCHE') GROUP BY SER_MSISDN
) C WHERE
B.SER_SERVICE_ORDER_ID = A.SER_SERVICE_ORDER_ID AND
D.SIM_IMSI_NO
= SUBSTR(A.SER_SERVICE_VALUE,1,15) AND B.SER_OPERATION
= 'MODS' AND
B.SER_PROVISIONING_TYPE <>'BULK' AND
A.SER_RECIEVED_DT_TIME
> SYSDATE-3 AND A.SER_SERVICE_ID IN
('SIMCH','SMCHE')
AND A.SER_MSISDN = C.SER_MSISDN AND
A.SER_RECIEVED_DT_TIME
= C.SER_RECIEVED_DT_TIME AND A.SER_STATUS =
'FAIL' AND
(A.SER_ERROR LIKE 'Unknown Error:RESP:18310%' OR
A.SER_ERROR
= 'Ext. system communication link failure.Please contact
switch
people.' OR A.SER_ERROR = 'Unknown Error:RESP:18304 recieved
from
EMA.Please Contact switch people' OR A.SER_ERROR LIKE '%fail
Fail File of
Zero Size' OR A.SER_ERROR LIKE 'Unknown
Error:RESP:18398%'
) ) Y ON (X.SER_SERVICE_ORDER_ID =
Y.SER_SERVICE_ORDER_ID)
WHEN NOT MATCHED THEN INSERT(X.CREATION_DATE,
X.SER_SERVICE_ORDER_ID,
X.SER_MSISDN, X.SER_IMSI_NO,
X.SER_SERVICE_VALUE,
X.SER_RECIEVED_DT_TIME, X.SER_ERROR, X.STATUS)
VALUES(SYSDATE,
Y.SER_SERVICE_ORDER_ID, Y.SER_MSISDN, Y.SER_IMSI_NO,
Y.SER_SERVICE_VALUE,
Y.SER_RECIEVED_DT_TIME, Y.SER_ERROR, 'PENDING')
WHEN MATCHED
THEN UPDATE SET X.CREATION_DATE = SYSDATE, X.STATUS =
'PENDING'
Rationale
The SQL
spent 100% of its database time on CPU, I/O and Cluster waits.
This part of
database time may be improved by the SQL Tuning Advisor.
Rationale
Database
time for this SQL was divided as follows: 100% for SQL
execution,
0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL
statement with SQL_ID "672j4rmvvnmru" was executed 109 times and had
an average
elapsed time of 17 seconds.
Rationale
Top level
calls to execute the PL/SQL statement with SQL_ID
"g2j4rw7vpbcbm"
are responsible for 100% of the database time spent on
the UPSERT
statement with SQL_ID "672j4rmvvnmru".
Related
Object
SQL
statement with SQL_ID g2j4rw7vpbcbm.
BEGIN
provisioning.simswap_reprocess; END;
Recommendation
3: SQL Tuning
Estimated
benefit is .53 active sessions, 5.26% of total activity.
------------------------------------------------------------------
Action
Run SQL
Tuning Advisor on the SELECT statement with SQL_ID
"52us6knb8uw7a".
Related
Object
SQL
statement with SQL_ID 52us6knb8uw7a.
SELECT
TO_CHAR("A1"."START_TIME",:"SYS_B_00"),TO_CHAR("A1"."START_TIM
E",:"SYS_B_01"),TO_CHAR("A1"."START_TIME",:"SYS_B_02"),"A1"."APN_NI",
CASE
"A1"."SGSN_ADDRESS" WHEN :"SYS_B_03" THEN
:"SYS_B_04" ELSE CASE
WHEN
("A1"."ORIGINAL_CALL_TYPE"=:"SYS_B_05" AND
"A1"."SGSN_ADDRESS"
IS NULL)
THEN :"SYS_B_06" ELSE :"SYS_B_07" END END
,COUNT(*),SUM(NVL("A1"."DATA_UP",:"SYS_B_08")),SUM(NVL("A1"."DATA_DOW
N",:"SYS_B_09")),SUM(NVL("A1"."DATA_UP",:"SYS_B_10"))+SUM(NVL("A1"."D
ATA_DOWN",:"SYS_B_11")),SUM(NVL("A1"."DATA_UP",:"SYS_B_12"))/:"SYS_B_
13"+SUM(NVL("A1"."DATA_DOWN",:"SYS_B_14"))/:"SYS_B_15",:"SYS_B_16",CA
SE
"A1"."SGSN_ADDRESS" WHEN :"SYS_B_17" THEN
:"SYS_B_18" ELSE CASE
WHEN
("A1"."ORIGINAL_CALL_TYPE"=:"SYS_B_19" AND
"A1"."SGSN_ADDRESS"
IS NULL)
THEN :"SYS_B_20" ELSE :"SYS_B_21" END END
FROM
"INCMS"."HBST_RAT_UNBILD_CALL""A1"
WHERE
"A1"."SERVICE_ID"=:"SYS_B_22"
AND
TO_NUMBER(TO_CHAR("A1"."START_TIME",:"SYS_B_23"))=:"SYS_B_24"
GROUP
BY
TO_CHAR("A1"."START_TIME",:"SYS_B_25"),TO_CHAR("A1"."START_TIME",:
"SYS_B_26"),TO_CHAR("A1"."START_TIME",:"SYS_B_27"),"A1"."APN_NI","A1"
."SGSN_ADDRESS","A1"."ORIGINAL_CALL_TYPE"
Rationale
The SQL
spent 100% of its database time on CPU, I/O and Cluster waits.
This part of
database time may be improved by the SQL Tuning Advisor.
Rationale
Database
time for this SQL was divided as follows: 100% for SQL
execution,
0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL
statement with SQL_ID "52us6knb8uw7a" was executed 4 times and had
an average
elapsed time of 446 seconds.
Recommendation
4: SQL Tuning
Estimated
benefit is .41 active sessions, 4.1% of total activity.
-----------------------------------------------------------------
Action
Run SQL
Tuning Advisor on the SELECT statement with SQL_ID
"cxwc6bnrmapfh".
Related
Object
SQL
statement with SQL_ID cxwc6bnrmapfh.
select
b.ser_service_order_id||:"SYS_B_0"||a.ser_msisdn||:"SYS_B_1"||
a.ser_service_value||:"SYS_B_2"||a.ser_error
from
incms.CIN_T_SERVICE_ORDER_DETAIL a, incms.CIN_T_SERVICE_ORDER b,
incms.cms_m_customer
c
where
b.SER_SERVICE_ORDER_ID = a.SER_SERVICE_ORDER_ID
and
a.ser_msisdn = c.cus_tel_no
and
b.ser_provisioning_type = :"SYS_B_3"
and
b.ser_operation in (:"SYS_B_4")
and
a.SER_RECIEVED_DT_TIME > sysdate-:"SYS_B_5"
and
a.SER_SERVICE_ID in (:"SYS_B_6",:"SYS_B_7")
and
a.ser_status = :"SYS_B_8"
order by
b.ser_service_order_id
Rationale
The SQL
spent 100% of its database time on CPU, I/O and Cluster waits.
This part of
database time may be improved by the SQL Tuning Advisor.
Rationale
Database
time for this SQL was divided as follows: 100% for SQL
execution,
0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL
statement with SQL_ID "cxwc6bnrmapfh" was executed 81 times and had
an average
elapsed time of 17 seconds.
Recommendation
5: SQL Tuning
Estimated
benefit is .41 active sessions, 4.07% of total activity.
------------------------------------------------------------------
Action
Run SQL
Tuning Advisor on the SELECT statement with SQL_ID
"an6y0xhff8hsc".
Related
Object
SQL
statement with SQL_ID an6y0xhff8hsc.
select
account_no,invoice_date,os,invoice_Date+:"SYS_B_0",floor(sysda
te-(invoice_date+:"SYS_B_1")),unadjusted_amount,add_months(invoice_da
te,-:"SYS_B_2")
from hbst_dunning_os
Rationale
The SQL
spent 100% of its database time on CPU, I/O and Cluster waits.
This part of
database time may be improved by the SQL Tuning Advisor.
Rationale
Database
time for this SQL was divided as follows: 100% for SQL
execution,
0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL
statement with SQL_ID "an6y0xhff8hsc" was executed 54 times and had
an average
elapsed time of 25 seconds.
Rationale
At least 2
distinct execution plans were utilized for this SQL statement
during the
analysis period.
Recommendation
6: SQL Tuning
Estimated
benefit is .25 active sessions, 2.5% of total activity.
-----------------------------------------------------------------
Action
Run SQL
Tuning Advisor on the SELECT statement with SQL_ID
"5rs41gbgw5x10".
Related
Object
SQL
statement with SQL_ID 5rs41gbgw5x10.
SELECT
ceq_msg_id, ceq_email_id, ceq_subject, ceq_msg_body,
CSQ_ATTACHMENT
FROM cin_t_email_queue WHERE ceq_status = :"SYS_B_0"
and
rownum< (select mdl_parameter_value from fds_m_module_param where
mod_1_mod_id=:"SYS_B_1" and mdl_parameter_id=:"SYS_B_2")
Rationale
The SQL
spent 100% of its database time on CPU, I/O and Cluster waits.
This part of
database time may be improved by the SQL Tuning Advisor.
Rationale
Database
time for this SQL was divided as follows: 100% for SQL
execution,
0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL
statement with SQL_ID "5rs41gbgw5x10" was executed 266 times and had
an average
elapsed time of 3 seconds.
Rationale
Full scan of
TABLE "INCMS.CIN_T_EMAIL_QUEUE" with object ID 83944
consumed 97%
of the database time spent on this SQL statement.
Finding 2:
Row Lock Waits
Impact is
4.17 active sessions, 41.7% of total activity.
--------------------------------------------------------
SQL
statements were found waiting for row lock waits.
Recommendation
1: Application Analysis
Estimated
benefit is 4.16 active sessions, 41.68% of total activity.
--------------------------------------------------------------------
Action
Significant
row contention was detected in the TABLE
"INCMS.FDS_M_MODULE_PARAM"
with object ID 84070. Trace the cause of row
contention
in the application logic using the given blocked SQL.
Related
Object
Database
object with ID 84070.
Rationale
The SQL
statement with SQL_ID "8wfzx3nyvma8m" was blocked on row locks.
Related
Object
SQL statement
with SQL_ID 8wfzx3nyvma8m.
SELECT
TRIM(MDL_PARAMETER_VALUE) FROM FDS_M_MODULE_PARAM WHERE
MOD_1_MOD_ID
='PROVS' AND MDL_PARAMETER_ID =:B1 FOR UPDATE OF
FDS_M_MODULE_PARAM.MDL_PARAMETER_VALUE
Rationale
The session
with ID 881 and serial number 40733 in instance number 1 was
the blocking
session responsible for 57% of this recommendation's
benefit.
Rationale
The session
with ID 94 and serial number 57631 in instance number 1 was
the blocking
session responsible for 42% of this recommendation's
benefit.
Symptoms
That Led to the Finding:
---------------------------------
Wait class
"Application" was consuming significant database time.
Impact is
4.25 active sessions, 42.58% of total activity.
Finding 3:
Undersized SGA
Impact is
.64 active sessions, 6.4% of total activity.
------------------------------------------------------
The SGA was
inadequately sized, causing additional I/O or hard parses.
The value of
parameter "sga_target" was "12288 M" during the analysis
period.
Recommendation
1: Database Configuration
Estimated
benefit is .32 active sessions, 3.22% of total activity.
------------------------------------------------------------------
Action
Increase the
size of the SGA by setting the parameter "sga_target" to
13824 M.
Symptoms
That Led to the Finding:
---------------------------------
Wait class
"User I/O" was consuming significant database time.
Impact is
1.26 active sessions, 12.6% of total activity.
Finding 4:
Top Segments by "User I/O" and "Cluster"
Impact is
.19 active sessions, 1.91% of total activity.
-------------------------------------------------------
Individual
database segments responsible for significant "User I/O" and
"Cluster"
waits were found.
Recommendation
1: Segment Tuning
Estimated
benefit is .19 active sessions, 1.91% of total activity.
------------------------------------------------------------------
Action
Run
"Segment Advisor" on TABLE "INCMS.CIN_T_EMAIL_QUEUE" with
object ID
83944.
Related
Object
Database
object with ID 83944.
Action
Investigate
application logic involving I/O on TABLE
"INCMS.CIN_T_EMAIL_QUEUE"
with object ID 83944.
Related
Object
Database
object with ID 83944.
Action
Look at the
"Top SQL Statements" finding for SQL statements consuming
significant
I/O on this segment. For example, the SELECT statement with
SQL_ID
"5rs41gbgw5x10" is responsible for 100% of "User I/O" and
"Cluster"
waits for this segment.
Rationale
The I/O
usage statistics for the object are: 0 full object scans,
18116994
physical reads, 0 physical writes and 18116994 direct reads.
Symptoms
That Led to the Finding:
---------------------------------
Wait class
"User I/O" was consuming significant database time.
Impact is
1.26 active sessions, 12.6% of total activity.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional
Information
----------------------
Miscellaneous
Information
-------------------------
Wait class
"Commit" was not consuming significant database time.
Wait class
"Concurrency" was not consuming significant database time.
Wait class
"Configuration" was not consuming significant database time.
CPU was not
a bottleneck for the instance.
Wait class
"Network" was not consuming significant database time.
Session
connect and disconnect calls were not consuming significant database
time.
Hard parsing of SQL statements was not consuming significant
database time.
Use of ADDM Reports alongside AWR
ADDM reports can be reviewed along with AWR to assist in
diagnosis since they provide specific recommendations which can help point at
potential problems. The following is a sample ADDM report taken from:
Document 250655.1How to use the Automatic Database
Diagnostic Monitor:
Example Output:
DETAILED ADDM REPORT FOR TASK 'SCOTT_ADDM' WITH ID 5
----------------------------------------------------
Analysis Period: 17-NOV-2003 from 09:50:21 to 10:35:47
Database ID/Instance: 494687018/1
Snapshot Range: from 1 to 3
Database Time: 4215 seconds
Average Database Load: 1.5 active sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FINDING 1: 65% impact (2734 seconds)
------------------------------------
PL/SQL execution consumed significant database time.
RECOMMENDATION 1: SQL Tuning, 65% benefit (2734 seconds)
ACTION: Tune the PL/SQL block with SQL_ID fjxa1vp3yhtmr.
Refer to
the "Tuning PL/SQL Applications" chapter of
Oracle's "PL/SQL
User's Guide and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID fjxa1vp3yhtmr
BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;
FINDING 2: 35% impact (1456 seconds)
------------------------------------
SQL statements consuming significant database time were
found.
RECOMMENDATION 1: SQL Tuning, 35% benefit (1456 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with
SQL_ID
gt9ahqgd5fmm2.
RELEVANT OBJECT: SQL statement with SQL_ID gt9ahqgd5fmm2 and
PLAN_HASH 547793521
UPDATE bigemp SET empno = ROWNUM
FINDING 3: 20% impact (836 seconds)
-----------------------------------
The throughput of the I/O subsystem was significantly lower
than expected.
RECOMMENDATION 1: Host Configuration, 20% benefit (836
seconds)
ACTION: Consider increasing the throughput of the I/O
subsystem.
Oracle's recommended solution is to stripe all data file
using
the SAME methodology. You might also need to increase the
number of disks for better performance.
RECOMMENDATION 2: Host Configuration, 14% benefit (584
seconds)
ACTION: The performance of file
D:\ORACLE\ORADATA\V1010\UNDOTBS01.DBF was significantly worse
than other files. If striping all files using the SAME
methodology is not possible, consider striping this file
over
multiple disks.
RELEVANT OBJECT: database file
"D:\ORACLE\ORADATA\V1010\UNDOTBS01.DBF"
SYMPTOMS THAT LED TO THE FINDING:
Wait class "User I/O" was consuming significant
database time.
(34% impact [1450 seconds])
FINDING 4: 11% impact (447 seconds)
-----------------------------------
Undo I/O was a significant portion (33%) of the total
database I/O.
NO RECOMMENDATIONS AVAILABLE
SYMPTOMS THAT LED TO THE FINDING:
The throughput of the I/O subsystem was significantly lower
than
expected. (20% impact [836 seconds])
Wait class "User I/O" was consuming significant
database time.
(34% impact [1450 seconds])
FINDING 5: 9.9% impact (416 seconds)
------------------------------------
Buffer cache writes due to small log files were consuming
significant
database time.
RECOMMENDATION 1: DB Configuration, 9.9% benefit (416
seconds)
ACTION: Increase the size of the log files to 796 M to hold at
least 20 minutes of redo information.
ADDM report gives possible recommendations in more readable
format than AWR. However, ADDM should be
interpreted along with AWR statistics for accurate diagnostics.
No comments:
Post a Comment