AWR Reports

Workload Repository Reports
AWR Snapshots and Baselines
Reading the AWR Report
        - Header Section:
                  - Cache Sizes
                  - Load Profile
                  - Instance Efficiency
                  - Shared Pool Statistics
                  - Top 10 Timed Foreground Events
        - Common Waits Events  *******
        - Time Model Statistics
        - Operating System Statistics
        - Foreground Wait Class and Foreground Wait Events
                  - Foreground Wait Class
                  - Foreground Wait Events
                  - Background Wait Events
                  - Service Statistics
        - SQL Information Section
        - Generate Execution Plan for given SQL statement
        - Instance Activity Stats Section
        - I/O Stats Section
                  - Tablespace IO Stats
                  - File IO Stats
        - Buffer Pool Statistics Section
        - Advisory Statistics Section
                  - Instance Recovery Stats
                  - Buffer Pool Advisory
        - PGA Reports
                  - PGA Aggr Target Stats
                  - PGA Aggr Target Histogram
                  - PGA Memory Advisory
        - Shared Pool Advisory
                  - SGA Target Advisory
        - Buffer Waits Statistics
        - Enqueue Statistics
        - UNDO Statistics Section
        - Latch Statistics
        - Segments by Logical Reads and Segments by Physical Reads
                  - Segments by Logical Reads
                  - Segments by Physical Reads
                  - Segments by Physical Read Requests
                  - Segments by UnOptimized Reads
                  - Segments by Direct Physical Reads
                  - Segments by Physical Writes
                  - Segments by Physical Write Requests
                  - Segments by Direct Physical Writes
                  - Segments by Table Scans
                  - Segments by DB Blocks Changes
                  - Segments by Row Lock Waits
                  - Segments by ITL Waits
                  - Segments by Buffer Busy Waits
Retrieve SQL and Execution Plan from AWR Snapshots
Get Data from ASH
Moving AWR Information
Links with AWR Analyzer

AWR periodically gathers and stores system activity and workload data which is then analyzed by ADDM. AWR looks periodically at the system performance (by default every 60 minutes) and stores the information found (by default up to 7 days). A new background server process (MMON) takes snapshots of the in-memory database statistics (much like STATSPACK) and stores this information in the repository. MMON also provides Oracle with a server initiated alert feature, which notifies database administrators of potential problems (out of space, max extents reached, performance thresholds, etc.). The information is stored in the SYSAUX tablespace. This information is the basis for all self-management decisions.

To access Automatic Workload Repository through Oracle Enterprise Manager Database Control:
    On the Administration page, select the Workload Repository link under Workload. From the Automatic Workload Repository page, you can manage snapshots or modify AWR settings.
          o To manage snapshots, click the link next to Snapshots or Preserved Snapshot Sets. On the Snapshots or Preserved Snapshot Sets pages, you can:
                + View information about snapshots or preserved snapshot sets (baselines).
                + Perform a variety of tasks through the pull-down Actions menu, including creating additional snapshots, preserved snapshot sets from an existing range of snapshots, or an ADDM task to perform analysis on a range of snapshots or a set of preserved snapshots.
          o To modify AWR settings, click the Edit button. On the Edit Settings page, you can set the Snapshot Retention period and Snapshot Collection interval.

Most informative sections of the report
I find the following sections most useful:
- Summary
- Top 5 timed events
- Top SQL (by elapsed time, by gets, sometimes by reads)

When viewing AWR report, always check corresponding ADDM report for actionable recommendations.
ADDM is a self diagnostic engine designed from the experience of Oracle’s best tuning experts and makes specific performance recommendations.

The snapshot frequency and retention time can be modified by the user. To see the present settings, you could use:
select snap_interval, retention from dba_hist_wr_control;

------------------- -------------------
+00000 01:00:00.0   +00007 00:00:00.0

select dbms_stats.get_stats_history_availability from dual;
select dbms_stats.get_stats_history_retention from dual;

This SQL shows that the snapshots are taken every hour and the collections are retained for 7 days

If you want to extend that retention period you can execute:
execute dbms_workload_repository.modify_snapshot_settings(
      interval => 60,        -- In Minutes.
      retention => 43200);   -- In Minutes (= 30 Days).

In this example the retention period is specified as 30 days (43200 min) and the interval between each snapshot is 60 min.

Workload Repository Reports
Oracle provide two main scripts to produce workload repository reports and they 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:

There are other scripts too, here is the full list:

Automatic Workload Repository Report awrrpt.sql
Automatic Database Diagnostics Monitor Report addmrpt.sql
ASH Report ashrpt.sql
AWR Diff Periods Report awrddrpt.sql
AWR Single SQL Statement Report awrsqrpt.sql
AWR Global Report awrgrpt.sql
AWR Global Diff Report awrgdrpt.sql
The scripts prompt you to enter the report format (html or text), the start snapshot id, the end snapshot id and the report filename. This script looks like Statspack; it shows all the AWR snapshots available and asks for two specific ones as interval boundaries.

AWR Snapshots and Baselines
You can create a snapshot manually using:
EXEC dbms_workload_repository.create_snapshot;

You can see what snapshots are currently in the AWR by using the DBA_HIST_SNAPSHOT view as seen in this example:

SELECT snap_id, to_char(begin_interval_time,'dd/MON/yy hh24:mi') Begin_Interval,
to_char(end_interval_time,'dd/MON/yy hh24:mi') End_Interval
FROM dba_hist_snapshot

---------- --------------- ---------------
       954 30/NOV/05 03:01 30/NOV/05 04:00
       955 30/NOV/05 04:00 30/NOV/05 05:00
       956 30/NOV/05 05:00 30/NOV/05 06:00
       957 30/NOV/05 06:00 30/NOV/05 07:00
       958 30/NOV/05 07:00 30/NOV/05 08:00
       959 30/NOV/05 08:00 30/NOV/05 09:00

Each snapshot is assigned a unique snapshot ID that is reflected in the SNAP_ID column. The END_INTERVAL_TIME column displays the time that the actual snapshot was taken.
Sometimes you might want to drop snapshots manually. The dbms_workload_repository.drop_snapshot_range procedure can be used to remove a range of snapshots from the AWR. This procedure takes two parameters, low_snap_id and high_snap_id, as seen in this example:

EXEC dbms_workload_repository.drop_snapshot_range(low_snap_id=>1107, high_snap_id=>1108);

AWR Automated Snapshots

Oracle uses a scheduled job, GATHER_STATS_JOB, to collect AWR statistics. This job is created, and enabled automatically, when you create a new Oracle database. To see this job, use the DBA_SCHEDULER_JOBS view as seen in this example:

SELECT a.job_name, a.enabled, c.window_name, c.schedule_name, c.start_date, c.repeat_interval
FROM dba_scheduler_jobs a, dba_scheduler_wingroup_members b, dba_scheduler_windows c
  And a.schedule_name=b.window_group_name
  And b.window_name=c.window_name;

You can disable this job using the dbms_scheduler.disable procedure as seen in this example:
Exec dbms_scheduler.disable('GATHER_STATS_JOB');

And you can enable the job using the dbms_scheduler.enable procedure as seen in this example:
Exec dbms_scheduler.enable('GATHER_STATS_JOB');

AWR Baselines
It is frequently a good idea to create a baseline in the AWR. A baseline is defined as a range of snapshots that can be used to compare to other pairs of snapshots. The Oracle database server will exempt the snapshots assigned to a specific baseline from the automated purge routine. Thus, the main purpose of a baseline is to preserve typical runtime statistics in the AWR repository, allowing you to run the AWR snapshot reports on the preserved baseline snapshots at any time and compare them to recent snapshots contained in the AWR. This allows you to compare current performance (and configuration) to established baseline performance, which can assist in determining database performance problems.

Creating baselines
You can use the create_baseline procedure contained in the dbms_workload_repository stored PL/SQL package to create a baseline as seen in this example:
EXEC dbms_workload_repository.create_baseline (start_snap_id=>1109, end_snap_id=>1111, baseline_name=>'EOM Baseline');

Baselines can be seen using the DBA_HIST_BASELINE view as seen in the following example:
SELECT baseline_id, baseline_name, start_snap_id, end_snap_id
FROM dba_hist_baseline;

----------- --------------- ------------- -----------
          1 EOM Baseline             1109        1111

In this case, the column BASELINE_ID identifies each individual baseline that has been defined. The name assigned to the baseline is listed, as are the beginning and ending snapshot IDs.

Removing baselines
The pair of snapshots associated with a baseline are retained until the baseline is explicitly deleted. You can remove a baseline using the dbms_workload_repository.drop_baseline procedure as seen in this example that drops the “EOM Baseline” that we just created.
EXEC dbms_workload_repository.drop_baseline (baseline_name=>'EOM Baseline', Cascade=>FALSE);

Note that the cascade parameter will cause all associated snapshots to be removed if it is set to TRUE; otherwise, the snapshots will be cleaned up automatically by the AWR automated processes.

Reading the AWR Report

The main sections in an AWR report include:

AWR Report Header:
This section shows basic information about the report like when the snapshot was taken, for how long, Cache Sizes at the beginning and end of the Snapshot, etc.

DB Name DB Id Instance Inst num Startup Time Release RAC
FGUARD 750434027 FGUARD 1 03-Jul-13 21:07 NO
Host Name Platform CPUs Cores Sockets Memory (GB) Linux x86 64-bit 16 8 2 11.72

Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 20813 08-Jul-13 00:00:19 267 3.1
End Snap: 20854 09-Jul-13 15:54:14 278 3.6
Elapsed:   2,393.91 (mins)    
DB Time:   4,689.46 (mins)    

Cache Sizes

Begin End

Buffer Cache: 1,520M 1,344M Std Block Size: 8K
Shared Pool Size: 1,120M 1,296M Log Buffer: 8,632K

Elasped Time: It represents the snapshot window or the time between the two snapshots.
DB TIME: Represents the activity on the database.

If DB TIME is Greater than Elapsed Time then it means that database has high workload.

Load Profile:
The load profile provides an at-a-glance look at some specific operational statistics. You can compare these statistics with a baseline snapshot report to determine if database activity is different. Values for these statistics are presented in two formats. The first is the value per second (for example, how much redo was generated per second) and the second is the value per transaction (for example, 1,024 bytes of redo were generated per transaction).Also "Per Exec" and "Per Call" for DB Time and CPU.

Per Second Per Transaction Per Exec Per Call
DB Time(s): 2.0 0.9 0.02 0.02
DB CPU(s): 0.5 0.2 0.01 0.01
Redo size: 25,972.2 12,131.8    
Logical reads: 9,444.6 4,411.6    
Block changes: 144.7 67.6    
Physical reads: 8,671.9
Physical writes: 2,641.5 1,233.9    
User calls: 83.9 39.2    
Parses: 30.7 14.3    
Hard parses: 0.4 0.2    
W/A MB processed: 4.6 2.1    
Logons: 2.5 1.2    
Executes: 88.6 41.4    
Rollbacks: 0.0 0.0    
Transactions: 2.1      


DB time(s): It's the amount of time oracle has spent performing database user calls. It doesn't include background processes. From the system view, the DB Time is the sum of system usage, which is the CPU usage plus any system call to lower systems (storage, network, etc).Application waits (latches, locks, dbms_lock.sleep,... ) are implemented as system calls as well.

DB CPU(s): It's the amount of CPU time spent on user calls. It doesn't include background process. The value is in microseconds.
Here are few important stats for a DBA to look into. Fist is "DB CPU(s)" per second. Before that let's understand how DB CUP's work.
Suppose you have 12 cores into the system. So, per wall clock second you have 12 seconds to work on CPU. So, if "
DB CPU(s)" per second in this report > cores in (Host Configuration (#2)) it means env is CPU bound and either need more CPU's or need to further check is this happening all the time or just for a fraction of time. As per my experience there are very few cases, when system is CPU bound.

Redo size: This is the amount of DML happening in the DB. High redo figures mean that either lots of new data is being saved into the database, or existing data is undergoing lots of changes. For example, the table below shows that an average transaction generates about 12,000 bytes of redo data along with around 26,000 redo bytes per second.

Logical reads: This is calculated as Consistent Gets + DB Block Gets =  Logical Reads. Logical reads is simply the number of blocks read by the database, including physical (i.e. disk) reads.

Block Changes:
The number of blocks modified during the sample interval. If you see an increase here then more DML statements are taking place (meaning your users are doing more INSERTs, UPDATEs, and DELETEs than before).

Physical reads:
The number of requests for a block that caused a physical I/O.

Physical writes:
Number of physical writes performed

User calls:
Indicates how many user calls have occurred during the snapshot period. This value can give you some indication if usage has increased. The user calls is the database client requesting the server to to do something like login, parse,etc while SQL Execute is executing the sql.  Depending on how the client is connecting, the numbers can be higher or lower. In particular, when the database is executing many times per a user call, this could be an indication of excessive context switching (e.g. a PL/SQL function in a SQL statement called too often because of a bad plan). In such cases looking into “SQL ordered by executions” will be the logical next step.

The total of all parses; both hard and soft.

Hard Parses:
Those parses requiring a completely new parse of the SQL statement.  A ‘hard parse’ rate of greater than 100 per second indicates there is a very high amount of hard parsing on the system. High hard parse rates cause serious performance issues, and must be investigated. A high hard parse rate is usually accompanied by latch contention on the shared pool and library cache latches. Check whether waits for ‘latch free’ appear in the top-5 wait events, and if so, examine the latching sections of the report. Of course, we want a low number here. Possible reasons for excessive hard parses may be a small shared pool or may be that bind variables are not being used.
As a rule of a thumb, anything below 1 hard parse per second is probably okay, and everything above 100 per second suggests a problem (if the database has a large number of CPUs, say, above 100, those numbers should be scaled up accordingly). It also helps to look at the number of hard parses as % of executions (especially if you’re in the grey zone).

If you suspect that excessive parsing is hurting your database’s performance:

1) check “time model statistics” section (hard parse elapsed time, parse time elapsed etc.)
2) see if there are any signs of library cache contention in the top-5 events
3) see if CPU is an issue.

If that confirms your suspicions, then find the source of excessive parsing (for soft parsing, use “SQL by parse calls”

Soft Parses:
Not listed but derived by subtracting the hard parses from parses.  A soft parse reuses a previous hard parse and hence consumes far fewer resources. A high soft parse rate could be anywhere in the rate of 300 or more per second. Unnecessary soft parses also limit application scalability; optimally a SQL statement should be soft-parsed once per session, and executed many times.

Number of sorts occurring in the database. Establishing a new database connection is also expensive (and even more expensive in case of audit or triggers). If you suspect that high number of logons is degrading your performance, check “connection management elapsed time” in “Time model statistics”.

No of logons during the interval.

how many statements we are executing per second / transaction

How many transactions per second we process. 

Logical and Physical Reads combined shows measure of how many I/O the DB is performing. If this is too high, go to section “SQL by Logical Reads” or “SQL by Physical Reads”

Next stat to look at are Parses and Hard parses. If the ratio of hard parse to parse is high, this means Database is performing more hard parse. So, needs to look at parameters like cursor_sharing and application level for bind variables etc.

The per-second statistics show you the changes in throughput (i.e. whether the instance is performing more work per second). For example:
• a significant increase in ‘redo size’, ‘block changes’ and ‘pct of blocks changed per read’ would indicate the instance is performing more inserts/updates/deletes.
• an increase in the ‘redo size’ without an increase in the number of ‘transactions per second’ would indicate a changing transaction profile.
Similarly, looking at the per-transaction statistics allows you to identify changes in the application characteristics by comparing these to the corresponding statistics from the baseline report.

Additionally, the load profile section provides the percentage of blocks (Block Changes) that were changed per read, the percentage of recursive calls that occurred, the percentage of transactions that were rolled back and the number of rows sorted per sort operation.

In a predominately online transaction processing system (OLTP) we would expect to see more logical reads, few physical reads, and many user calls, parses, and executes, as well as rollbacks and transactions. Generally speaking, report environments have fewer, longer transactions that utilize the Work Area, while OLTP environments tend to have numerous small transactions with many commits and rollbacks.

In this example
• Comparing the number of Physical reads per second to the number of Physical writes per second shows the physical read to physical write ratio is very high. Typical OLTP systems have a read-to-write ratio of 10:1 or 5:1
• This system is busy, with 84 User calls per second.

Instance Efficiency Percentages (Target 100%)
These statistics include several buffer related ratios including the buffer hit percentage and the library hit percentage. Also, shared pool memory usage statistics are included in this section.
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.32 In-memory Sort %: 100.00
Library Hit %: 98.94 Soft Parse %: 97.25
Execute to Parse %: 75.00 Latch Hit %: 98.78
Parse CPU to Parse Elapsd %: 22.99 % Non-Parse CPU: 99.93

Buffer Nowait Ratio. This is the percentage of time that the instance made a call to get a buffer (all buffer types are included here) and that buffer was made available immediately (meaning it didn't have to wait for the buffer...hence "Buffer Nowait"). If the ratio is low, then could be a (hot) block(s) being contended for that should be found in the Buffer Wait Section.. If the ratio is low, check the Buffer Wait Statistics section of the report for more detail on which type of block is being contended for.
Buffer Hit Ratio. (also known as the buffer-cache hit ratio) Ideally more than 95 percent. It shows the % of times a particular block was found in buffer cache insted of performing a physical I/O (reading from disk).
Although historically known as one of the most important statistics to evaluate, this ratio can sometimes be misleading. A low buffer hit ratio does not necessarily mean the cache is too small; it may be that potentially valid full-table scans are artificially reducing what is otherwise a good ratio. Similarly, a high buffer hit ratio (say, 99 percent) normally indicates that the cache is adequately sized, but this assumption may not always be valid. For example, frequently executed SQL statements that repeatedly refer to a small number of buffers via indexed lookups can create a misleadingly high buffer hit ratio. When these buffers are read, they are placed at the most recently used (MRU) end of the buffer cache; iterative access to these buffers can artificially inflate the buffer hit ratio. This inflation makes tuning the buffer cache a challenge. Sometimes you can identify a too-small buffer cache by the appearance of the write complete waits event, which indicates that hot blocks (that is, blocks that are still being modified) are aging out of the cache while they are still needed; check the Wait Events list for evidence of this event. If the number is negative, the BUFFER_CACHE is too small and the data is bein aged out before it can be used.
Library Hit Ratio. This ratio, also known as the library-cache hit ratio, gives the percentage of pin requests that result in pin hits. A pin hit occurs when the SQL or PL/SQL code to be executed is already in the library cache and is valid to execute. If the "Library Hit ratio" is low, it could be indicative of a shared pool that is too small (SQL is prematurely pushed out of the shared pool), or just as likely, that the system did not make correct use of bind variables in the application. If the soft parse ratio is also low, check whether there's a parsing issue. A lower ratio could also indicate that bind variables are not used or some other issue is causing SQL not to be reused (in which case a smaller shared pool may only be a band-aid that will potentially fix a library latch problem which may result).
Execute to Parse. If value is negative, it means that the number of parses is larger than the number of executions. Another cause for a negative execute to parse ratio is if the shared pool is too small and queries are aging out of the shared pool and need to be reparsed.  This is another form of thrashing which also degrades performance tremendously. So, if you run some SQL and it has to be parsed every time you execute it (because no plan exists for this statement) then your percentage would be 0%. The more times that your SQL statement can reuse an existing plan the higher your Execute to Parse ratio is. This is very BAD!! One way to increase your parse ratio is to use bind variables.
Parse CPU to Parse Elapsd %: Generally, this is a measure of how available your CPU cycles were for SQL parsing. If this is low, you may see "latch free" as one of your top wait events.
Redo Nowait Ratio.
This ratio indicates the amount of redo entries generated for which there was space available in the redo log. The instance didn't have to wait to use the redo log if this is 100%
The redo-log space-request statistic is incremented when an Oracle process attempts to write a redo-log entry but there is not sufficient space remaining in the online redo log. Thus, a value close to 100 percent for the redo nowait ratio indicates minimal time spent waiting for redo logs to become available, either because the logs are not filling up very often or because the database is able to switch to a new log quickly whenever the current log fills up.
If your alert log shows that you are switching logs frequently (that is, more than once every 15 minutes), you may be able to reduce the amount of switching by increasing the size of the online redo logs. If the log switches are not frequent, check the disks on which the redo logs reside to see why the switches are not happening quickly. If these disks are not overloaded, they may be slow, which means you could put the files on faster disks.
In-Memory Sort %. This ratio gives the percentage of sorts that were performed in memory, rather than requiring a disk-sort segment to complete the sort. Optimally, in an OLTP environment, this ratio should be high. Setting the PGA_AGGREGATE_TARGET (or SORT_AREA_SIZE) initialization parameter effectively will eliminate this problem, as a minimum you pretend to have this one in 92%
Soft Parse %. This ratio gives the percentage of parses that were soft, as opposed to hard. A soft parse occurs when a session attempts to execute a SQL statement and a usable version of the statement is already in the shared pool. In other words, all data (such as the optimizer execution plan) pertaining to the statement in the shared pool is equally applicable to the statement currently being issued. A hard parse, on the other hand, occurs when the current SQL statement is either not in the shared pool or not there in a shareable form. An example of the latter case would be when the SQL statement in the shared pool is textually identical to the current statement but the tables referred to in the two statements resolve to physically different tables.
Hard parsing is an expensive operation and should be kept to a minimum in an OLTP environment. The aim is to parse once, execute many times.
Ideally, the soft parse ratio should be greater than 95 percent. When the soft parse ratio falls much below 80 percent, investigate whether you can share SQL by using bind variables or force cursor sharing by using the init.ora parameter cursor_sharing.
Before you jump to any conclusions about your soft parse ratio, however, be sure to compare it against the actual hard and soft parse rates shown in the Load Profile. If the rates are low (for example, 1 parse per second), parsing may not be a significant issue in your system. Another useful standard of comparison is the proportion of parse time that was not CPU-related, given by the following ratio:
(parse time CPU) / (parse time elapsed)

A low value for this ratio could mean that the non-CPU-related parse time was spent waiting for latches, which might indicate a parsing or latching problem. To investigate further, look at the shared-pool and library-cache latches in the Latch sections of the report for indications of contention on these latches.
Latch Hit Ratio. This is the ratio of the total number of latch misses to the number of latch gets for all latches. A low value for this ratio indicates a latching problem, whereas a high value is generally good. However, as the data is rolled up over all latches, a high latch hit ratio can artificially mask a low get rate on a specific latch. Cross-check this value with the Top 5 Wait Events to see if latch free is in the list, and refer to the Latch sections of the report. Latch Hit % of less than 99 percent is usually a big problem.

Also check the "Shared Pool Statistics", if the "End" value is in the high 95%-100% range ,this is a indication that the shared pool needs to be increased (especially if the "Begin" value is much smaller)

Example: Evaluating the Instance Efficiency Percentages Section

Instance Efficiency Percentages (Target 100%)
            Buffer Nowait %:   99.99       Redo NoWait %:  100.00
            Buffer  Hit   %:   95.57    In-memory Sort %:   97.55
            Library Hit   %:   99.89        Soft Parse %:   99.72
         Execute to Parse %:   88.75         Latch Hit %:   99.11
Parse CPU to Parse Elapsd %:   52.66     % Non-Parse CPU:   99.99

Interpreting the ratios in this section can be slightly more complex than it may seem at first glance. While high values for the ratios are generally good (indicating high efficiency), such values can be misleading your system may be doing something efficiently that it would be better off not doing at all. Similarly, low values aren't always bad. For example, a low in-memory sort ratio (indicating a low percentage of sorts performed in memory) would not necessarily be a cause for concern in a decision- support system (DSS) environment, where user response time is less critical than in an online transaction processing (OLTP) environment.
Basically, you need to keep in mind the characteristics of your application - whether it is query-intensive or update-intensive, whether it involves lots of sorting, and so on - when you're evaluating the Instance Efficiency Percentages.

The following ratios should be above 90% in a database.
Buffer Nowait
Buffer  Hit  
Library Hit
Redo NoWait
In-memory Sort
Soft Parse
Latch Hit
Non-Parse CPU

The execute to parse ratio should be very high in a ideal database.
The execute to parse ratio is basically a measure between the number of times a sql is executed versus the number of times it is parsed.
The ratio will move higher as the number of executes go up, while the number of parses either go down or remain the same.
The ratio will be close to zero if the number of executes and parses are almost equal.
The ratio will be negative executes are lower but the parses are higher.

Another Sample Analysis
Instance Efficiency Percentages (Target 100%)
            Buffer Nowait %:   98.56       Redo NoWait %:  100.00
            Buffer  Hit   %:   99.96    In-memory Sort %:   99.84
            Library Hit   %:   99.99        Soft Parse %:  100.00 (A)
         Execute to Parse %:    0.10 (A)     Latch Hit %:   99.37
Parse CPU to Parse Elapsd %:   58.19 (A) % Non-Parse CPU:   99.84

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   28.80   29.04 (B)
    % SQL with executions>1:   75.91   76.03
  % Memory for SQL w/exec>1:   83.65   84.09

• The 100% soft parse ratio (A) indicates the system is not hard-parsing. However the system is soft parsing a lot, rather than only re-binding and re-executing the same cursors, as the Execute to Parse % is very low (A). Also, the CPU time used for parsing (A) is only 58% of the total elapsed parse time (see Parse CPU to Parse Elapsd). This may also imply some resource contention during parsing (possibly related to the latch free event?).
• There seems to be a lot of unused memory in the shared pool (only 29% is used) (B). If there is insufficient memory allocated to other areas of the database (or OS), this memory could be redeployed

***Please see the following NOTES on shared pool issues
[NOTE:146599.1] Diagnosing and Resolving Error ORA-04031
[NOTE:62143.1] Understanding and Tuning the Shared Pool

Top 10 Foreground Events by Total Wait Time
This section provides insight into what events the Oracle database is spending most of it's time on (see wait events). Each wait event is listed, along with the number of waits, the time waited (in seconds), the average wait per event (in microseconds) and the associated wait class.
This is one of the most important sections of the report

Event Waits
Total Wait Time (sec) Wait Avg (ms) % DB time Wait Class
PX Deq: Slave Session Stats 912,927 3006.7 3
99.4 Other
DB CPU   70,609   64.84  
log file sync 247,094 6,933 28 2.46 Commit
db file sequential read 221,301 5,813 26 2.07 User I/O
library cache: mutex X 27,915 57.9 2 1.9 Concurrency

If you turn off the statistic parameter, then the Time(s) wont appear. Wait analysis should be done with respect to Time(s) as there could be million of waits but if that happens for a second or so then who cares. Therefore, time is very important component.
When you are trying to eliminate bottlenecks on your system, your report's Top 10 Timed Events section is the first place to look and you should use the HIGHEST WAIT TIMES to guide the investigation.

Here, first of all check for wait class if wait class is  User I/O , System I/O,  Others etc this could be fine but if wait class has value "Concurrency" then there could be some serious problem.
Next to look at is 
Total Wait Time (s) which show how many times DB was waiting in this class and then Wait Avg (ms). If Total Wait Time(s) are high but  Wait Avg (ms) is low then you can ignore this. If both are high or Wait Avg (ms) is high then this has to further investigate.

Here, first check if Wait Class. If their values are in User I/O , System I/O,  Others this could be fine. But if wait class has value "Concurrency" then there could be some serious problem.
Next to look at is Total Wait Time (s) which show how many times DB was waiting in this class and then Wait Avg (ms).
If Total Wait Time(s) are high but Wait Avg (ms) is low then you can ignore this.

If both are high or Wait Avg (ms) is high then this has to further investigate.

In the above screen shot, most of the resource are taken by DB CPU = 64% DB time. Taking resource by DB CPU is a normal situation.

As you will see, you have several different types of waits, so let's discuss the most common waits on the next section.

Wait Classes by Total Wait Time

Wait Class Waits Total Wait Time (sec) Avg Wait (ms) % DB time Avg Active Sessions
Other 5,081,996 2,851 1 84.2 0.4
DB CPU   1,223   36.1 0.2
Cluster 246,728 661 3 19.5 0.1
User I/O 226,368 425 2 12.6 0.1
Scheduler 51,051 94 2 2.8 0.0
Commit 61,242 60 1 1.8 0.0
Concurrency 103,675 43 0 1.3 0.0
System I/O 210,905 30 0 .9 0.0
Network 1,599,588 23 0 .7 0.0
Application 3,073 1 0 .0 0.0
Configuration 46 0 2 .0 0.0

Host CPU

CPUs Cores Sockets Load Average Begin Load Average End %User %System %WIO %Idle
20 20 2     0.9 0.2   98.9

Instance CPU

%Total CPU %Busy CPU %DB time waiting for CPU (Resource Manager)
0.9 82.9 2.1

IO Profile

Read+Write Per Second Read per Second Write Per Second
Total Requests: 71.0 47.6 23.4
Database Requests: 44.8 41.1 3.7
Optimized Requests: 0.0 0.0 0.0
Redo Requests: 16.5 0.1 16.4
Total (MB): 1.2 1.0 0.2
Database (MB): 0.9 0.9 0.0
Optimized Total (MB): 0.0 0.0 0.0
Redo (MB): 0.1 0.0 0.0
Database (blocks): 117.7 112.0 5.7
Via Buffer Cache (blocks): 71.6 67.3 4.4
Direct (blocks): 46.0 44.7 1.3

Memory Statistics

Begin End
Host Mem (MB): 32,643.8 32,643.8
SGA use (MB): 16,384.0 16,384.0
PGA use (MB): 2,500.9 2,505.5
% Host Mem used for SGA+PGA: 57.85 57.87

Cache Sizes

Begin End

Buffer Cache: 7,168M 7,168M Std Block Size: 8K
Shared Pool Size: 8,301M 8,298M Log Buffer: 21,148K

Shared Pool Statistics

Begin End
Memory Usage %: 87.72 87.54
% SQL with executions>1: 83.65 83.33
% Memory for SQL w/exec>1: 78.30 80.36

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

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   42.07   43.53
    % SQL with executions>1:   73.79   75.08
  % Memory for SQL w/exec>1:   76.93   77.64

Memory Usage % = It's the shared pool usage. So here we have use 73.86 per cent of our shared pool and out of that almost 94 percent is being re-used. If Memory Usage % is too large like 90 % it could mean that your shared pool is tool small and if the percent is in 50 for example then this could mean that you shared pool is too large. In general, Memory usage % statistics should be ~70% after the DB has been running a long time. If its quite low, memory is being wasted.
% SQL with executions>1
= Shows % of SQLs executed more than 1 time. The % should be very near to value 100. If we get a low number here, then the DB is not using shared SQL statements. May be because bind variables are not being used.
% memory for SQL w/exec>1
: From the memory space allocated to cursors, shows which % has been used by cursors more than 1.

The values should not be very high (preferably less than 75%).


If you want a quick instance wide wait event status, showing which events are the biggest contributors to total wait time, you can use the following query :

select event, total_waits,time_waited from V$system_event
  where event NOT IN
  ('pmon timer', 'smon timer', 'rdbms ipc reply', 'parallel deque wait',
  'virtual circuit', '%SQL*Net%', 'client message', 'NULL event')
order by time_waited desc;

EVENT                              TOTAL_WAITS         TIME_WAITED
------------------------          -------------       -------------
db file sequential read              35051309            15965640
latch free                            1373973             1913357
db file scattered read                2958367             1840810
enqueue                                  2837              370871
buffer busy waits                      444743              252664
log file parallel write                146221              123435

1. DB File Scattered Read.

That generally happens during a full scan of a table or Fast Full Index Scans. As full table scans are pulled into memory, they rarely fall into contiguous buffers but instead are scattered throughout the buffer cache. A large number here indicates that your table may have missing indexes, statistics are not updated or your indexes are not used. Although it may be more efficient in your situation to perform a full table scan than an index scan, check to ensure that full table scans are necessary when you see these waits. Try to cache small tables to avoid reading them in over and over again, since a full table scan is put at the cold end of the LRU (Least Recently Used) list. You can use the report to help identify the query in question and fix it.
The init.ora parameter db_file_multiblock_read_count specifies the maximum numbers of blocks read in that way. Typically, this parameter should have values of 4-16 independent of the size of the database but with higher values needed with smaller Oracle block sizes. If you have a high wait time for this event, you either need to reduce the cost of I/O, e.g. by getting faster disks or by distributing your I/O load better, or you need to reduce the amount of full table scans by tuning SQL statements. The appearance of the‘db file scattered read’ and ‘db file sequential read’events may not necessarily indicate a problem, as IO is a normal activity on a healthy instance. However, they can indicate problems if any of the following circumstances are true:
• The data-access method is bad (that is, the SQL statements are poorly tuned), resulting in unnecessary or inefficient IO operations
• The IO system is overloaded and performing poorly
• The IO system is under-configured for the load
• IO operations are taking too long

If this Wait Event is a significant portion of Wait Time then a number of approaches are possible:
o Find which SQL statements perform Full Table or Fast Full Index scans and tune them to make sure these scans are necessary and not the result of a suboptimal plan.
- The view V$SQL_PLAN view can help:
For Full Table scans:
select sql_text from v$sqltext t, v$sql_plan p
  where t.hash_value=p.hash_value
    and p.operation='TABLE ACCESS'

    and p.options='FULL'
  order by p.hash_value, t.piece;

For Fast Full Index scans:
select sql_text from v$sqltext t, v$sql_plan p
  where t.hash_value=p.hash_value
    and p.operation='INDEX'

    and p.options='FULL SCAN'
  order by p.hash_value, t.piece;

o In cases where such multiblock scans occur from optimal execution plans it is possible to tune the size of multiblock I/Os issued by Oracle by setting the instance parameter DB_FILE_MULTIBLOCK_READ_COUNT so that:
Query tuning should be used to optimize online SQL to use indexes.

2. DB File Sequential Read.
Is the wait that comes from the physical side of the database. It related to memory starvation and non selective index use. Sequential read is an index read followed by table read because it is doing index lookups which tells exactly which block to go to.
This could indicate poor joining order of tables or un-selective indexes in your SQL or
waiting for writes to TEMP space (direct loads, Parallel DML (PDML) such as parallel updates. It could mean that a lot of index reads/scans are going on. Depending on the problem it may help to tune PGA_AGGREGATE_TARGET and/or DB_CACHE_SIZE.
The sequential read event identifies Oracle reading blocks sequentially, i.e. one after each other. It is normal for this number to be large for a high-transaction, well-tuned system, but it can indicate problems in some circumstances. You should correlate this wait statistic with other known issues within the report, such as inefficient SQL. Check to ensure that index scans are necessary, and check join orders for multiple table joins. The DB_CACHE_SIZE will also be a determining factor in how often these waits show up. Problematic hash-area joins should show up in the PGA memory, but they're also memory hogs that could cause high wait numbers for sequential reads. They can also show up as direct path read/write waits. These circumstances are usually interrelated. When they occur in conjunction with the appearance of the 'db file scattered read' and 'db file sequential read' in the Top 5 Wait Events section, first you should examine the SQL Ordered by Physical Reads section of the report, to see if it might be helpful to tune the statements with the highest resource usage.
It could be because the indexes are fragmented. If that is the case, rebuilding the index will compact it and will produce to visit less blocks.
Then, to determine whether there is a potential I/O bottleneck, examine the OS I/O statistics for corresponding symptoms. Also look at the average time per read in the Tablespace and File I/O sections of the report. If many I/O-related events appear high in the Wait Events list, re-examine the host hardware for disk bottlenecks and check the host-hardware statistics for indications that a disk reconfiguration may be of benefit.
Block reads are fairly inevitable so the aim should be to minimize unnecessary I/O. I/O for sequential reads can be reduced by tuning SQL calls that result in full table scans and using the partitioning option for large tables.

3. Free Buffer Waits.
When a session needs a free buffer and cannot find one, it will post the database writer process asking it to flush dirty blocks (No place to put a new block). Waits in this category may indicate that you need to increase the DB_BUFFER_CACHE, if all your SQL is tuned. Free buffer waits could also indicate that unselective SQL is causing data to flood the buffer cache with index blocks, leaving none for this particular statement that is waiting for the system to process. This normally indicates that there is a substantial amount of DML (insert/update/delete) being done and that the Database Writer (DBWR) is not writing quickly enough; the buffer cache could be full of multiple versions of the same buffer, causing great inefficiency. To address this, you may want to consider accelerating incremental checkpointing, using more DBWR processes, or increasing the number of physical disks. To investigate if this is an I/O problem, look at the report I/O Statistics. Increase the DB_CACHE_SIZE; shorten the checkpoint; tune the code to get less dirty blocks, faster I/O, use multiple DBWR’s.

4. Buffer Busy Waits. A buffer busy wait happens when multiple processes concurrently want to modify the same block in the buffer cache. This typically happens during massive parallel inserts if your tables do not have free lists and it can happen if you have too few rollback segments. Buffer busy waits should not be greater than 1 percent. Check the Buffer Wait Statistics section (or V$WAITSTAT) to find out if the wait is on a segment header. If this is the case, increase the freelist groups or increase the pctused to pctfree gap. If the wait is on an undo header, you can address this by adding rollback segments; if it's on an undo block, you need to reduce the data density on the table driving this consistent read or increase the DB_CACHE_SIZE. If the wait is on a data block, you can move data to another block to avoid this hot block, increase the freelists on the table, or use Locally Managed Tablespaces (LMTs). If it's on an index block, you should rebuild the index, partition the index, or use a reverse key index. To prevent buffer busy waits related to data blocks, you can also use a smaller block size: fewer records fall within a single block in this case, so it's not as "hot." When a DML (insert/update/ delete) occurs, Oracle writes information into the block, including all users who are "interested" in the state of the block (Interested Transaction List, ITL). To decrease waits in this area, you can increase the initrans, which will create the space in the block to allow multiple ITL slots. You can also increase the pctfree on the table where this block exists (this writes the ITL information up to the number specified by maxtrans, when there are not enough slots built with the initrans that is specified). Buffer busy waits can be reduced by using reverse-key indexes for busy indexes and by partitioning busy tables.
Buffer Busy Wait on Segment Header – Add freelists (if inserts) or freelist groups (esp. RAC). Use ASSM.
Buffer Busy Wait on Data Block – Separate ‘hot’ data; potentially use reverse key indexes; fix queries to reduce the blocks popularity, use smaller blocks, I/O, Increase initrans and/or maxtrans (this one’s debatable). Reduce records per block
Buffer Busy Wait on Undo Header – Add rollback segments or increase size of segment area (auto undo) 
Buffer Busy Wait on Undo block – Commit more (not too much) Larger rollback segments/area. Try to fix the SQL.

5. Latch Free
Latches are low-level queuing mechanisms (they're accurately referred to as mutual exclusion mechanisms) used to protect shared memory structures in the system global area (SGA). Latches are like locks on memory that are very quickly obtained and released. Latches are used to prevent concurrent access to a shared memory structure. If the latch is not available, a latch free miss is recorded. Most latch problems are related to the failure to use bind variables (library cache latch), redo generation issues (redo allocation latch), buffer cache contention issues (cache buffers LRU chain), and hot blocks in the buffer cache (cache buffers chain). There are also latch waits related to bugs; check MetaLink for bug reports if you suspect this is the case. When latch miss ratios are greater than 0.5 percent, you should investigate the issue. If latch free waits are in the Top 5 Wait Events or high in the complete Wait Events list, look at the latch-specific sections of the report to see which latches are contended for.

6. Enqueue. An enqueue is a lock that protects a shared resource. Locks protect shared resources, such as data in a record, to prevent two people from updating the same data at the same time application, e.g. when a select for update is executed.. An enqueue includes a queuing mechanism, which is FIFO (first in, first out). Note that Oracle's latching mechanism is not FIFO. Enqueue waits usually point to the ST enqueue, the HW enqueue, the TX4 enqueue, and the TM enqueue. The ST enqueue is used for space management and allocation for dictionary-managed tablespaces. Use LMTs, or try to preallocate extents or at least make the next extent larger for problematic dictionary-managed tablespaces. HW enqueues are used with the high-water mark of a segment; manually allocating the extents can circumvent this wait. TX4s are the most common enqueue waits. TX4 enqueue waits are usually the result of one of three issues. The first issue is duplicates in a unique index; you need to commit/rollback to free the enqueue. The second is multiple updates to the same bitmap index fragment. Since a single bitmap fragment may contain multiple rowids, you need to issue a commit or rollback to free the enqueue when multiple users are trying to update the same fragment. The third and most likely issue is when multiple users are updating the same block. If there are no free ITL slots, a block-level lock could occur. You can easily avoid this scenario by increasing the initrans and/or maxtrans to allow multiple ITL slots and/or by increasing the pctfree on the table. Finally, TM enqueues occur during DML to prevent DDL to the affected object. If you have foreign keys, be sure to index them to avoid this general locking issue.
Enqueue - ST Use LMT’s or pre-allocate large extents
Enqueue - HW Pre-allocate extents above HW (high water mark.)
Enqueue – TX Increase initrans and/or maxtrans (TX4) on (transaction) the table or index.  Fix locking issues if TX6.  Bitmap (TX4) & Duplicates in Index (TX4).
Enqueue - TM Index foreign keys; Check application (trans. mgmt.) locking of tables.  DML Locks.

7. Log Buffer Space
Look at increasing log buffer size. This wait occurs because you are writing the log buffer faster than LGWR can write it to the redo logs, or because log switches are too slow. To address this problem, increase the size of the redo log files, or increase the size of the log buffer, or get faster disks to write to. You might even consider using solid-state disks, for their high speed.

The session is waiting for space in the log buffer. (Space becomes available only after LGWR has written the current contents of the log buffer to disk.) This typically happens when applications generate redo faster than LGWR can write it to disk.

8. Log File Switch
log file switch (checkpoint incomplete): May indicate excessive db files or slow IO subsystem
log file switch (archiving needed):  Indicates archive files are written too slowly
log file switch completion: May need more log files per
May indicate excessive db files or slow IO subsystem. All commit requests are waiting for "logfile switch (archiving needed)" or "logfile switch (chkpt. Incomplete)." Ensure that the archive disk is not full or slow. DBWR may be too slow because of I/O. You may need to add more or larger redo logs, and you may potentially need to add database writers if the DBWR is the problem.

9. Log File Sync
Could indicate excessive commits. A Log File Sync happens each time a commit (or rollback) takes place. If there are a lot of waits in this area then you may want to examine your application to see if you are committing too frequently (or at least more than you need to). When a user commits or rolls back data, the LGWR flushes the session's redo from the log buffer to the redo logs. The log file sync process must wait for this to successfully complete. To reduce wait events here, try to commit more records (try to commit a batch of 50 instead of one at a time, use BULKS, , for example). Put redo logs on a faster disk, or alternate redo logs on different physical disks (with no other DB Files, ASM, etc) to reduce the archiving effect on LGWR. Don't use RAID 5, since it is very slow for applications that write a lot; potentially consider using file system direct I/O or raw devices, which are very fast at writing information. The associated event, ‘log buffer parallel write’ is used by the redo log writer process, and it will indicate if your actual problem is with the log file I/O. Large wait times for this event can also be caused by having too few CPU resources available for the redolog writer process.

10. Idle Event. There are several idle wait events listed after the output; you can ignore them. Idle events are generally listed at the bottom of each section and include such things as SQL*Net message to/from client and other background-related timings. Idle events are listed in the stats$idle_event table.

11. global cache cr request: (OPS) This wait event shows the amount of time that an instance has waited for a requested data block for a consistent read and the transferred block has not yet arrived at the requesting instance. See Note 157766.1 'Sessions Wait Forever for 'global cache cr request' Wait Event in OPS or RAC'. In some cases the 'global cache cr request' wait event may be perfectly normal if large buffer caches are used and the same data is being accessed concurrently on multiple instances.  In a perfectly tuned, non-OPS/RAC database, I/O wait events would be the top wait events but since we are avoiding I/O's with RAC and OPS the 'global cache cr request' wait event often takes the place of I/O wait events.

12. library cache pin: Library cache latch contention may be caused by not using bind variables. It is due to excessive parsing of SQL statement.
The session wants to pin an object in memory in the library cache for examination, ensuring no other processes can update the object at the same time. This happens when you are compiling or parsing a PL/SQL object or a view.

13. CPU time
This is not really a wait event (hence, the new name), but rather the sum of the CPU used by this session, or the amount of CPU time used during the snapshot window. In a heavily loaded system, if the CPU time event is the biggest event, that could point to some CPU-intensive processing (for example, forcing the use of an index when a full scan should have been used), which could be the cause of the bottleneck. When CPU Other is a significant component of total Response Time the next step is to find the SQL statements that access the most blocks. Block accesses are also known as Buffer Gets and Logical I/Os. The report lists such SQL statements in section SQL ordered by Gets.

14. DB File Parallel Read  If you are doing a lot of partition activity then expect to see that wait even. it could be a table or index partition. This Wait Event is used when Oracle performs in parallel reads from multiple datafiles to non-contiguous buffers in memory (PGA or Buffer Cache). This is done during recovery operations or when buffer prefetching is being used as an optimization i.e. instead of performing multiple single-block reads. If this wait is an important component of Wait Time, follow the same guidelines as 'db file sequential read'.
This may occur during recovery or during regular activity when a session batches many single block I/O requests together and issues them in parallel.

15. PX qref latch  Can often mean that the Producers are producing data quicker than the Consumers can consume it. Maybe we could increase parallel_execution_message_size to try to eliminate some of these waits or we might decrease the degree of parallelism. If the system workload is high consider to decrease the degree of parallelism. If you have DEFAULT parallelism on your object  you can decrease the value of PARALLEL_THREADS_PER_CPU.  Have in mind  DEFAULT degree = PARALLEL_THREADS_PER_CPU * #CPU's 

16. Log File Parallel Write. It occurs when waiting for writes of REDO records to the REDO log files to complete. The wait occurs in log writer (LGWR) as part of normal activity of copying records from the REDO log buffer to the current online log. The actual wait time is the time taken for all the outstanding I/O requests to complete. Even though the writes may be issued in parallel, LGWR needs to wait for the last I/O to be on disk before the parallel write is considered complete. Hence the wait time depends on the time it takes the OS to complete all requests.
Log file parallel write waits can be reduced by moving log files to the faster disks and/or separate disks where there will be less contention.

17. SQL*Net more data to client
This means the instance is sending a lot of data to the client. You can decrease this time by having the client bring back less data. Maybe the application doesn't need to bring back as much data as it is.

18. SQL*Net message to client
The “SQL*Net message to client” Oracle metric indicates the server (foreground process) is sending a message to the client, and it can be used to identify throughput issues over a network, especially distributed databases with slow database links. The SQL*Net more data to client event happens when Oracle writes multiple data buffers (sized per SDU) in a single logical network call.

19. enq: TX - row lock contention:
Oracle keeps data consistency with the help of locking mechanism. When a particular row is being modified by the process, either through Update/ Delete or Insert operation, oracle tries to acquire lock on that row. Only when the process has acquired lock the process can modify the row otherwise the process waits for the lock. This wait situation triggers this event. The lock is released whenever a COMMIT is issued by the process which has acquired lock for the row. Once the lock is released, processes waiting on this event can acquire lock on the row and perform DML operation

20. direct Path writes: You wont see them unless you are doing some appends or data loads. The session has issued asynchronous I/O requests that bypass the buffer cache and is waiting for them to complete. These wait events often involve temporary segments, sorting activity, parallel query or hash joins.

21. direct Path reads / direct path writes: Could happen if you are doing a lot of parallel query activity. The session has issued asynchronous I/O requests that bypass the buffer cache and is waiting for them to complete. These wait events often involve temporary segments, sorting activity, parallel query or hash joins. Usually sorting to Temp. Can also be parallel query. Could also be insert append, etc Adjust PGA_AGGREGATE_TARGET to fix it.

22. write complete waits: The session is waiting for a requested buffer to be written to disk; the buffer cannot be used while it is being written.

23. direct path read temp or direct path write temp: This wait event shows Temp file activity (sort,hashes,temp tables, bitmap) check pga parameter or sort area or hash area parameters. You might want to increase them

24. Undo segment extension: The session is waiting for an undo segment to be extended or shrunk. If excessive, tune undo

25. wait for a undo record: Usually only during recovery of large transactions, look at turning off parallel undo recovery.

26. Control File Parallel Write: The session has issued multiple I/O requests in parallel to write blocks to all control files, and is waiting for all of the writes to complete.

27. Control File Sequential Read: The session is waiting for blocks to be read from a control file.

28. DB File Parallel Write: The process, typically DBWR, has issued multiple I/O requests in parallel to write dirty blocks from the buffer cache to disk and is waiting for all requests to complete.

29. Library Cache load lock: The session is waiting for the opportunity to load an object or a piece of an object into the library cache. (Only one process can load an object or a piece of an object at a time.)

30. log file sequential read: The session is waiting for blocks to be read from the online redo log into memory. This primarily occurs at instance startup and when the ARCH process archives filled online redo logs.

Time Model Statistics
Time model statistics tells you the processing time spent on various metrics during the snapshot interval.
You should not expect the % of DB Time to add up to 100% because there is overlap among statistics. For example "sql execute elapsed time" requires CPU time for sql execution. Note that some statistics such as "background elapsed time" and "background cpu time" are shown as well but these are not part of  % of DB Time.

Here is an example of the time model statistic report:
Statistic Name                          Time (s) % of DB Time
------------------------------------------ ------------------ ------------

execute elapsed time               1,247.7         95.5
DB CPU                                   129.9          9.9

management call elapsed time    5.5           .4
time elapsed                         4.2           .3
hard parse elapsed
time                    3.7           .3
PL/SQL execution elapsed
time              1.2           .1
PL/SQL compilation elapsed
time            0.4           .0
hard parse (sharing criteria) elapsed
time 0.1           .0
repeated bind elapsed
time                 0.0           .0
hard parse (bind mismatch) elapsed
time    0.0           .0
load elapsed time                 0.0           .0
failed parse elapsed
time                  0.0           .0
time                                1,306.8
background elapsed
time                3,395.9
background cpu
time                       19.6

We can go through these figures now.
CPU time used 129.9 seconds  for all user sessions. This was just under 10% of database resources.
In total there was 1,306.8 seconds database time used.
The total wait event time can be calculated as DB time – DB CPU, i.e. 1,306.8 – 129.9 = 1,176.9 seconds.
The lion share of database time (95.5%) was spent on executing sql ("sql execute elapsed time") which is a good sign.
The total parse time was 5.5 seconds of which 4.2 seconds was hard parsing. The rest of statistics is tiny in this example.

Here is another example of the time model statistic report:
Statistic Name Time (s) % of DB Time
sql execute elapsed time 19,640.87 95.41
DB CPU 17,767.20 86.31
parse time elapsed 73.75 0.36
hard parse elapsed time 38.35 0.19
PL/SQL execution elapsed time 32.04 0.16
hard parse (sharing criteria) elapsed time 6.98 0.03
connection management call elapsed time 4.25 0.02
repeated bind elapsed time 3.43 0.02
PL/SQL compilation elapsed time 3.04 0.01
hard parse (bind mismatch) elapsed time 1.62 0.01
sequence load elapsed time 0.74 0.00
failed parse elapsed time 0.04 0.00
DB time 20,586.08  
background elapsed time 859.22  
background cpu time 68.05

If parsing time is very high, or if hard parsing is significant, you must investigate it further. Generally you want SQL processing time high, parsing and other stuff low. Time related statistics presents the various operations which are consuming most of the database time.
If SQL time>>DB CPU time then probably have IO issues. 
If Hard parses or parsing time is very high then further investigation should be done to resolve the problem. .

Another Example

Statistic Name

Time (s)

% of DB Time

sql execute elapsed time






parse time elapsed



hard parse elapsed time



failed parse elapsed time



PL/SQL execution elapsed time



hard parse (sharing criteria) elapsed time



connection management call elapsed time



hard parse (bind mismatch) elapsed time



PL/SQL compilation elapsed time



repeated bind elapsed time



sequence load elapsed time



DB time



background elapsed time



background cpu time



In the above example, 9,223.70 seconds CPU time was used for all user sessions. This was just under 65% of database resources. 
In total there was 14363 seconds database time used. 
The total wait event time can be calculated as 14363 – 9223.70 = 5139.3 seconds. The lion share of database time (86.45%) was spent on executing sql which is a good sign. The total parse time was 935.61 seconds of which 884.73 seconds was hard parsing. The rest of statistics is tiny in this case

Then, it’s a good idea to check if most of the statements are identified in the SQL sections:
SQL ordered by Elapsed Time                DB/Inst: ORCL/orcl  Snaps: 330-336
-> Captured SQL account for   94.6% of Total DB Time (s):           4,589
-> Captured PL/SQL account for   94.5% of Total DB Time (s):           4,589
If only a low percentage has been capture, that usually means that the report cover a period where the database had an heterogeneous activity, either the  duration is too long or there is  too many unshared SQL (not using bind variables). Here I know I’ll have detail about 94% of the SQL activity, which is good.

Operating System Statistics

This part of the report provides some basic insight into OS performance, and OS configuration too. This report may vary depending on the OS platform that your database is running on. Here is an example from a Linux system:
Statistic Value End Value
BUSY_TIME 1,831,850  
IDLE_TIME 26,901,106  
IOWAIT_TIME 226,948  
SYS_TIME 212,021  
USER_TIME 1,596,003  
LOAD 1 1
VM_IN_BYTES 1,560,961,024  
VM_OUT_BYTES 475,336,945,664  
PHYSICAL_MEMORY_BYTES 12,582,432,768  

In the previous example we can see that CPU Utilization is 6%   (BUSY_TIME and divide by BUSY_TIME + IDLE_TIME) = 1,831,850 /  (1,831,850 + 26,901,106) = 0.06
In this example output, for example, we have 16 CPU's on the box.

Operating System Statistics - Detail
Snap Time Load %busy %user %sys %idle %iowait
12-Jul 13:00:59 0.99          
12-Jul 14:00:03 2.67 6.47 5.63 0.77 93.53 0.56
12-Jul 15:00:08 2.45 11.67 10.22 1.30 88.33 1.47
12-Jul 16:00:12 2.88 11.93 10.43 1.35 88.07 1.45
12-Jul 17:00:16 0.74 1.61 1.37 0.21 98.39 0.46
12-Jul 18:00:21 0.80 0.19 0.13 0.06 99.81 0.01

This report shows, system is 97 to 98% idle at time of report taken.
If you found very high %busy, %user or sys % and indeed this will led to low idle %. Investigate what is causing this.
 OS Watcher is the tool which can help in this direction. 

If the $Idle is TOO HIGH, it means that the DB is not doing much.

Foreground Wait Class and Foreground Wait Events  
Closely associated with the time model section of the report are the Foreground wait class and Foreground wait event statistics sections. 
Within Oracle, the duration of a large number of operations  (e.g. Writing to disk or to the control file) is metered. These are known as wait events, because each of these operations requires the system to wait for the event to complete. 
Thus, the execution of some database operation (e.g. a SQL query) will have a number of wait events associated with it. We can try to determine which wait events are causing us problems by looking at the wait classes and the wait event reports generated from AWR.
Wait classes define "buckets" that allow for summation of various wait times. Each wait event is assigned to one of these buckets (for example System I/O or User I/O). These buckets allow one to quickly determine which subsystem is likely suspect in performance problems  (e.g. the network, or the cluster).

The following list includes common examples of the waits in some of the classes:

Foreground Wait Class
Wait Class Waits %Time -outs Total Wait Time (s) Avg wait (ms) %DB time
DB CPU     17,767   86.31
User I/O 6,536,576 0 3,500 1 17.00
Commit 169,635 0 666 4 3.24
Other 350,080 21 140 0 0.68
Concurrency 78,002 0 58 1 0.28
Network 1,755,547 0 2 0 0.01
Application 579 0 1 2 0.00
System I/O 584 0 0 0 0.00
Configuration 1 0 0 0 0.00

User I/O taking 17% of database time was explained before.
The CPU usage was over 86%. Commit contributed 3.24% of wait time.

Foreground Wait Events
Wait events are normal occurrences, but if a particular sub-system is having a problem performing (e.g. the disk sub-system) this fact will appear in the form of one or more wait events with an excessive duration. 
The wait event report then provides some insight into the detailed wait events. Here is an example of the wait event report (we have eliminated some of the bulk of this report, because it can get quite long).
Note that this section is sorted by wait time (listed in microseconds).

Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn % DB time
direct path write temp 1,837,854 0 2,267 1 10.53 11.01
direct path read 2,838,190 0 930 0 16.26 4.52
log file sync 169,635 0 666 4 0.97 3.24
db file sequential read 13,222 0 143 11 0.08 0.69
direct path read temp 1,837,007 0 131 0 10.53 0.64
PX Deq: Slave Session Stats 131,555 0 107 1 0.75 0.52
db file scattered read 8,448 0 26 3 0.05 0.13
kksfbc child completion 441 100 22 51 0.00 0.11
latch: shared pool 4,849 0 16 3 0.03 0.08
library cache: mutex X 67,703 0 14 0 0.39 0.07
library cache lock 346 0 12 35 0.00 0.06
cursor: pin S wait on X 582 0 9 16 0.00 0.05
latch free 9,647 0 7 1 0.06 0.03
os thread startup 116 0 4 32 0.00 0.02
SQL*Net message to client 1,739,132 0 2 0 9.97 0.01
cursor: mutex S 1,666 0 2 1 0.01 0.01
latch: row cache objects 1,658 0 1 1 0.01 0.01
read by other session 92 0 1 12 0.00 0.01
db file parallel read 344 0 1 3 0.00 0.00
PX Deq: Signal ACK EXT 65,787 0 1 0 0.38 0.00
PX Deq: Signal ACK RSG 65,787 0 1 0 0.38 0.00
enq: PS - contention 758 0 1 1 0.00 0.00
enq: RO - fast object reuse 40 0 1 13 0.00 0.00
Disk file operations I/O 1,386 0 0 0 0.01 0.00
enq: KO - fast object checkpoint 539 0 0 1 0.00 0.00
PX qref latch 964 100 0 0 0.01 0.00
latch: parallel query alloc buffer 836 0 0 0 0.00 0.00
latch: cache buffers chains 174 0 0 1 0.00 0.00
SQL*Net more data to client 16,415 0 0 0 0.09 0.00
enq: TX - index contention 5 0 0 37 0.00 0.00
library cache load lock 1 0 0 139 0.00 0.00
asynch descriptor resize 71,974 100 0 0 0.41 0.00
PX Deq: Table Q Get Keys 486 0 0 0 0.00 0.00
reliable message 577 0 0 0 0.00 0.00
buffer busy waits 676 0 0 0 0.00 0.00
cursor: pin S 189 0 0 0 0.00 0.00
row cache lock 17 0 0 2 0.00 0.00
direct path sync 15 0 0 2 0.00 0.00
latch: cache buffer handles 1 0 0 29 0.00 0.00
utl_file I/O 18 0 0 1 0.00 0.00
PX Deq: Table Q qref 1,160 0 0 0 0.01 0.00
wait list latch free 13 0 0 1 0.00 0.00
latch: object queue header operation 32 0 0 0 0.00 0.00
control file sequential read 584 0 0 0 0.00 0.00
SQL*Net message from client 1,739,106 0 260,904 150 9.97  
jobq slave wait 41,892 100 20,964 500 0.24  
PX Deq: Execution Msg 746,687 0 1,612 2 4.28  
PX Deq: Table Q Normal 1,057,627 0 387 0 6.06  
PX Deq Credit: send blkd 128,373 0 266 2 0.74  
PX Deq: Execute Reply 710,735 0 51 0 4.07  
PX Deq: Parse Reply 65,790 0 13 0 0.38  
PX Deq: Join ACK 65,790 0 4 0 0.38  
PX Deq Credit: need buffer 1,783 0 3 1 0.01  
PX Deq: Table Q Sample 1,275 0 1 0 0.01  

Foreground Wait Events 
Avg %Time Total Wait wait Waits Event Waits -outs Time (s) (ms) /txn ---------------------------- -------------- ------ ----------- ------- --------- control file parallel write 1,220 .0 18 15 1.6 control file sequential read 6,508 .0 6 1 8.7 CGS wait for IPC msg 422,253 100.0 1 0 566.0 change tracking file synchro 60 .0 1 13 0.1 db file parallel write 291 .0 0 1 0.4 db file sequential read 90 .0 0 4 0.1 reliable message 136 .0 0 1 0.2 log file parallel write 106 .0 0 2 0.1 lms flush message acks 1 .0 0 60 0.0 gc current block 2-way 200 .0 0 0 0.3 change tracking file synchro 59 .0 0 1 0.1

In this example our control file parallel write waits (which occurs during writes to the control file) are taking up 18 seconds total, with an average wait of 15 milliseconds per wait. 
Additionally we can see that we have 1.6 waits per transaction (or 15ms * 1.6 per transaction = 24ms).

Background Wait Events

Background wait events are those not associated with a client process. They indicate waits encountered by system and non-system processes. Examples of background system processes are LGWR and DBWR.  An example of a non-system background process would be a parallel query slave.  Note that it is possible for a wait event to appear in both the foreground and background wait events statistics, for examples the enqueue and latch free events.  The idle wait events appear at the bottom of both sections and can generally safely be ignored. Typically these type of events keep record of the time while the client is connected to the database but not requests are being made to the server. Usually not a big contributor.

                              %Time Total Wait  wait  Waits   % bg
Event                Waits    -outs   Time (s)  (ms)   /txn   time
-----------------  ------------ ---------- ------- -------- ------
log file parallel write 10,319    0   845      82      0.3   24.9
db file parallel write  43,425    0   207       5      1.2    6.1
direct path write           55    0     2      40      0.0     .1
control file seq re     2,501     0     2       1      0.1     .1

The log file parallel write shows LGWR is waiting for blocks to be written to all online redo log members in one group. LGWR will wait until all blocks have been written to all members.   So here we had 24.9% of backhround total time spent on log file parallel write. The db file parallel write wait event belongs to DBWR process since it is the only process that writes dirty blocks from the SGA to datafiles.  DBWR process compiles a set of dirty blocks, hands the batch over to the OS, and waits on the db file parallel write event for the I/O to complete. The parameter of interest here is Avg wait (ms). In our case it is 5ms which is a perfectably respetable figure. Obviously larger average wait times point to slower I/O subsystem or poor I/O configurations.

Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn % bg time
log file parallel write 189,549 0 615 3 1.09 71.53
db file async I/O submit 28,334 0 37 1 0.16 4.27
os thread startup 711 0 25 35 0.00 2.88
control file parallel write 32,030 0 16 1 0.18 1.88
db file sequential read 899 0 14 15 0.01 1.57
latch: shared pool 7,111 0 9 1 0.04 1.09
latch: call allocation 15,537 0 9 1 0.09 1.07
latch free 8,264 0 5 1 0.05 0.63
ARCH wait on ATTACH 259 0 3 11 0.00 0.34
Log archive I/O 726 0 2 3 0.00 0.24
row cache lock 2 0 1 746 0.00 0.17
control file sequential read 83,303 0 1 0 0.48 0.12
db file parallel read 30 0 1 17 0.00 0.06
Disk file operations I/O 1,214 0 0 0 0.01 0.05
log file sequential read 838 0 0 0 0.00 0.05
direct path sync 5 0 0 31 0.00 0.02
ADR block file read 80 0 0 2 0.00 0.02
log file sync 11 0 0 11 0.00 0.01
enq: PR - contention 3 0 0 23 0.00 0.01
latch: cache buffers chains 1 0 0 46 0.00 0.01
db file single write 264 0 0 0 0.00 0.00
latch: parallel query alloc buffer 63 0 0 1 0.00 0.00
LGWR wait for redo copy 521 0 0 0 0.00 0.00
Data file init write 20 0 0 1 0.00 0.00
latch: row cache objects 1 0 0 15 0.00 0.00
latch: session allocation 27 0 0 1 0.00 0.00
db file scattered read 3 0 0 4 0.00 0.00
reliable message 50 0 0 0 0.00 0.00
direct path write 14 0 0 1 0.00 0.00
asynch descriptor resize 1,442 100 0 0 0.01 0.00
wait list latch free 10 0 0 1 0.00 0.00
rdbms ipc reply 57 0 0 0 0.00 0.00
log file single write 40 0 0 0 0.00 0.00
ADR block file write 25 0 0 0 0.00 0.00
ADR file lock 30 0 0 0 0.00 0.00
library cache: mutex X 2 0 0 1 0.00 0.00
SQL*Net message to client 587 0 0 0 0.00 0.00
rdbms ipc message 323,540 38 356,760 1103 1.85  
PX Idle Wait 65,885 0 115,165 1748 0.38  
DIAG idle wait 35,863 100 35,900 1001 0.21  
Space Manager: slave idle wait 5,626 97 27,710 4925 0.03  
pmon timer 5,984 100 17,959 3001 0.03  
Streams AQ: qmn slave idle wait 642 0 17,953 27964 0.00  
Streams AQ: qmn coordinator idle wait 1,282 50 17,953 14004 0.01  
shared server idle wait 598 100 17,946 30010 0.00  
dispatcher timer 299 100 17,942 60007 0.00  
smon timer 113 33 17,719 156808 0.00  
SQL*Net message from client 795 0 1 1 0.00  
class slave wait 83 0 0 0 0.00  

Service Statistics
A service is a grouping of processes. Users may be grouped in SYS$USER. Application logins (single user) may be grouped with that user name

Service Name DB Time (s) DB CPU (s) Physical Reads (K) Logical Reads (K) 19,903 17,555 413,742 386,056
SYS$USERS 683 212 1,733 6,954

SQL Information Section

Next in the report we find several different reports that present SQL statements that might be improved by tuning. Any SQL statement appears in the top 5 statements in two or more areas below, then it is a prime candidate for tuning. The sections are:

It's interesting to mention that the SUM of columns %CPU + %IO should be close to 100. If this is far from 100, this can indicate a problem

Let try to see what these mean.

SQL Ordered by Elapsed Time
Total Elapsed Time = CPU Time + Wait Time.
Shows which SQL statement runs for a longer time. If a SQL statement appears in the total elapsed time area of the report this means its CPU time plus any other wait times made it pop to the top of the pile. Excessive Elapsed Time could be due to excessive CPU usage or excessive wait times.
This is the area that you need to examine and probably the one that will be reported by the users or application support. From a consumer perspective, the finer details don’t matter. The application is slow. Full stop.
In conjunction with excessive Elapsed time check to see if this piece of SQL is also a high consumer under Total CPU Time. It is normally the case. Otherwise check the wait times and Total Disk Reads. They can either indicate issues with wait times (slow disks, latch gets etc) or too much Physical IO associated with tables scans or sub-optimal indexes.  This section is a gate opener and often you will need to examine other sections.

SQL Ordered by CPU Time
When a statement appears in the Total CPU Time area this indicates it used excessive CPU cycles during its processing. Excessive CPU processing time can be caused by sorting, excessive function usage or long parse times. Indicators that you should be looking at this section for SQL tuning candidates include high CPU percentages in the service section for the service associated with this SQL (a hint, if the SQL is uppercase it probably comes from a user or application; if it is lowercase it usually comes from the internal or background processes). To reduce total CPU time, reduce sorting by using composite indexes that can cover sorting and use bind variables to reduce parse times.

SQL Ordered by Buffer Gets
Total buffer gets mean a SQL statement is reading a lot of data from the db block buffers. Generally speaking buffer gets (AKA logical IO or LIO) are OK, except when they become excessive. The old saying that you reduce the logical IO, because then the physical IO (disk read) will take care of itself holds true. LIO may  have incurred a PIO in order to get the block into the buffer in the first place. Reducing buffer gets is very important and should not be underestimated. To get a block from db block buffers, we have to latch it (i.e. in order to prevent someone from modifying the data structures we are currently reading from the buffer). Although latches are less persistent than locks, a latch is still a serialization device. Serialization devices inhibit scalability, the more you use them, the less concurrency you get. Therefore in most cases optimal buffer gets can result in improved performance. Also note that by lowering buffer gets you will require less CPU usage and less latching. |Thus to reduce excessive buffer gets, optimize SQL to use appropriate indexes and reduce full table scans. You can also look at improving the indexing strategy and consider deploying partitioning (licensed).

SQL Ordered by Disk Reads
High total disk reads mean a SQL statement is reading a lot of data from disks rather than being able to access that data from the db block buffers. High physical reads after a server reboot are expected as the cache is cold and data is fetched from the disk. However, disk reads (or physical reads) are undesirable in an OLTP system, especially when they become excessive. Excessive disk reads do cause performance issues. The usual norm is to increase the db buffer cache to allow more buffers and reduce ageing . Total disk reads are typified by high physical reads, a low buffer cache hit ratio, with high IO wait times. Higher wait times for Disk IO can be associated with a variety of reasons (busy or over saturated SAN, slower underlying storage, low capacity in HBC and other hardware causes). Statistics on IO section in AWR, plus the Operating System diagnostic tools as simple as iostatcan help in identifying these issues. To reduce excessive disk reads, consider partitioning, use indexes and look at optimizing SQL to avoid excessive full table scans.

SQL Ordered by Executions
High total executions need to be reviewed to see if they are genuine executions or loops in SQL code. I have also seen situations where autosys jobs fire duplicate codes erroneously. In general statements with high numbers of executions usually are being properly reused. However, there is always a chance of unnecessary loop in PL/SQL, Java or C#. Statements with high number of executions, high number of logical and or physical reads are candidates for review to be sure they are not being executed multiple times when a single execution would serve. If the database has excessive physical and logical reads or excessive IO wait times, then look at the SQL statements that show excessive executions and show high physical and logical reads.

Parse Calls
Whenever a statement is issued by a user or process, regardless of whether it is in the SQL pool it undergoes a parse.  As explained under Parsing, the parse can be a hard parse or a soft parse. Excessive parse calls usually go with excessive executions. If the statement is using what are known as unsafe bind variables then the statement will be reparsed each time. If the header parse ratios are low look here and in the version count areas.

SQL Ordered by Memory
Sharable Memory refers to Shared Pool memory area in SGA , hence this particular section in AWR Report states about the SQL STATEMENT CURSORS which consumed the maximum amount of the Shared Pool for their execution.
In general high values for Sharable Memory doesn’t necessary imply there is an issue It simply means that:
    - These SQL statements are big or complex and Oracle has to keep lots of information about these statements OR
    - big number of child cursors exist for those parent cursors
    - combination of 1 & 2
In case of point 2, it may be due to poor coding such as bind variables mismatch, security mismatch  or overly large SQL statements that join many tables. In a DSS or  DW environment large complex statements are normal. In an OLTP database large or complex statements are usually the result of over-normalization of the database design, attempts to use an OLTP system as a DW or simply poor coding techniques. Usually large statements will result in excessive parsing, recursion, and large CPU usage.

SQL Ordered by Version Count
High version counts are usually due to multiple identical-schema databases, unsafe bind variables, or Oracle bugs.

The SQL that is stored in the shared pool SQL area (Library cache) is reported in this section in different ways:
. SQL ordered by Buffer Gets
. SQL ordered by Physical Reads
. SQL ordered by Executions
. SQL ordered by Parse Calls

- SQL ordered by Gets:
This section reports the contents of the SQL area ordered by the number of buffer gets and can be used to identify the most CPU Heavy SQL.
- Many DBAs feel that if the data is already contained within the buffer cache the query should be efficient.  This could not be further from the truth.  Retrieving more data than needed, even from the buffer cache, requires CPU cycles and interprocess IO. Generally speaking, the cost of physical I/O is not 10,000 times more expensive.  It actually is in the neighborhood of 67 times and actually almost zero if the data is stored in the UNIX buffer cache.
- The statements of interest are those with a large number of gets per execution especially if the number of executions is high.
- High buffer gets generally correlates with heavy CPU usage

- SQL ordered by Reads:
This section reports the contents of the SQL area ordered by the number of reads from the data files and can be used to identify SQL causing IO bottlenecks which consume the following resources.
- CPU time needed to fetch unnecessary data.
- File IO resources to fetch unnecessary data.
- Buffer resources to hold unnecessary data.
- Additional CPU time to process the query once the data is retrieved into the buffer.

- SQL ordered by Executions:
This section reports the contents of the SQL area ordered by the number of query executions. It is primarily useful in identifying the most frequently used SQL within the database so that they can be monitored for efficiency.  Generally speaking, a small performance increase on a frequently used query provides greater gains than a moderate performance increase on an infrequently used query. Possible reasons for high Reads per Exec are use of unselective indexes require large numbers of blocks to be fetched where such blocks are not cached well in the buffer cache, index fragmentation, large Clustering Factor in index etc.

- SQL ordered by Parse Calls:
This section shows the number of times a statement was parsed as compared to the number of times it was executed.  One to one parse/executions may indicate that:
- Bind variables are not being used.
  The shared pool may be too small and the parse is not being retained long enough for multiple executions.
- cursor_sharing is set to exact (this should NOT be changed without considerable testing on the part of the client).