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
WHERE job_name='GATHER_STATS_JOB'
  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');

• 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.
• 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?
• Elapsed time
  The elapsed time indicates the duration of the report between the 2 selected snapshots. 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.
• 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!
  On the other hand, if this number is significantly smaller than the Elapsed time then it could be that the issue is not on the DB.
  
• 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? The DB has DB Time / Elapsed  (4689.46 / 2389.91) Active sessions = 1.97 active sessions.
  

Cache Sizes

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.


Parses: 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.

Sorts: 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”.

Logons: No of logons during the interval.

Executes: how many statements we are executing per second / transaction

Transactions: 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.

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.

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

Observations:
• 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
[NOTE:105813.1] SCRIPT TO SUGGEST MINIMUM SHARED POOL SIZE

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.

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

Host CPU

Instance CPU

IO Profile

Memory Statistics

Cache Sizes

Shared Pool Statistics

 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

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:
DB_BLOCK_SIZE x DB_FILE_MULTIBLOCK_READ_COUNT = max_io_size of system
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

Statistic Name                          Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time               1,247.7         95.5
DB CPU                                   129.9          9.9
connection management call elapsed time    5.5           .4
parse 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
sequence load elapsed time                 0.0           .0
failed parse elapsed time                  0.0           .0
DB time                                1,306.8
background elapsed time                3,395.9
background cpu time                       19.6
-------------------------------------------------------------

• Total time in database user-calls (DB Time): 20586.1s
• Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic
• Ordered by % or DB time desc, Statistic name

Another Example

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.

• *TIME statistic values are diffed. All others display actual values. End Value is displayed if different
• ordered by statistic type (CPU Use, Virtual Memory, Hardware Config), Name

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. 

• User I/O: Waits for user IO (db file sequential read, db file scattered read, direct path read, direct path write etc)
• DB CPU: See above for the definition of this class
• Configuration: Waits caused by inadequate configuration of database or instance resources (for example, undersized log file sizes, shared pool size etc)
• Commit: waits for redo log write confirmation after a commit
• Other: Waits which should not typically occur on a system (for example, ‘wait for EMON to spawn’)
• System I/O: Waits for background process IO
• Application: Waits resulting from user application code (for example, lock waits caused by row level locking or explicit lock commands)
• Network: waits for data to be sent over the network
• Concurrency: Waits for internal database resources (for example, latches)

• s - second, ms - millisecond - 1000th of a second
• ordered by wait time desc, waits desc
• %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
• Captured Time accounts for 107.5% of Total DB time 20,586.08 (s)
• Total FG Wait Time: 4,367.43 (s) DB CPU time: 17,767.20 (s)

• s - second, ms - millisecond - 1000th of a second
• Only events with Total Wait Time (s) >= .001 are shown
• ordered by wait time desc, waits desc (idle events last)
• %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0

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).

• ordered by wait time desc, waits desc (idle events last)
• Only events with Total Wait Time (s) >= .001 are shown
• %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0

• ordered by DB Time

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:

• SQL Ordered by Elapsed Time - IO waits
  
• SQL Ordered by CPU Time - Sorting, hashing
  
• SQL Ordered by Buffer Gets - High logical IO
  
• SQL Ordered by Disk Reads - High physical IO
  
• SQL Ordered by Executions - May indicate loop issues
  
• SQL Ordered by Parse Calls - Memory issues
  
• SQL Ordered by Sharable Memory - Informational
  
• SQL Ordered by Version Count - May indicate unsafe bind variables
• SQL Ordered by Cluster Wait Time - Indicates physical issues (RPB, block size)

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

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).

If you have identified one or more problematic SQL statement, you may want to check the execution plan. Remember the "Old Hash Value" from the report above (1279400914), then execute the scrip to generate the execution plan.

sqlplus perfstat/perfstat
SQL> @?/rdbms/admin/sprepsql.sql
Enter the Hash Value, in this example: 1279400914

SQL Text
~~~~~~~~
create table test as select * from all_objects

Known Optimizer Plan(s) for this Old Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows all known Optimizer Plans for this database instance, and the Snap Id's
they were first found in the shared pool.  A Plan Hash Value will appear
multiple times if the cost has changed
-> ordered by Snap Id

  First        First          Plan
 Snap Id     Snap Time     Hash Value        Cost
--------- --------------- ------------ ----------
        6 14 Nov 04 11:26   1386862634        52

Plans in shared pool between Begin and End Snap Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows the Execution Plans found in the shared pool between the begin and end
snapshots specified.  The values for Rows, Bytes and Cost shown below are those
which existed at the time the first-ever snapshot captured this plan - these
values often change over time, and so may not be indicative of current values
-> Rows indicates Cardinality, PHV is Plan Hash Value
-> ordered by Plan Hash Value

--------------------------------------------------------------------------------
| Operation                      | PHV/Object Name     |  Rows | Bytes|   Cost |
--------------------------------------------------------------------------------
|CREATE TABLE STATEMENT          |----- 1386862634 ----|       |      |     52 |
|LOAD AS SELECT                  |                     |       |      |        |
| VIEW                           |                     |     1K|  216K|     44 |
|  FILTER                        |                     |       |      |        |
|   HASH JOIN                    |                     |     1K|  151K|     38 |
|    TABLE ACCESS FULL           |USER$                |    29 |  464 |      2 |
|    TABLE ACCESS FULL           |OBJ$                 |     3K|  249K|     35 |
|   TABLE ACCESS BY INDEX ROWID  |IND$                 |     1 |    7 |      2 |
|    INDEX UNIQUE SCAN           |I_IND1               |     1 |      |      1 |
|   NESTED LOOPS                 |                     |     5 |  115 |     16 |
|    INDEX RANGE SCAN            |I_OBJAUTH1           |     1 |   10 |      2 |
|    FIXED TABLE FULL            |X$KZSRO              |     5 |   65 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   VIEW                         |                     |     1 |   13 |      2 |
|    FAST DUAL                   |                     |     1 |      |      2 |
--------------------------------------------------------------------------------

This section provides us with a number of various statistics (such as, how many DBWR Checkpoints occurred, or how many consistent gets occurred during the snapshot). These are the statistics that the summary information is derived from. A list of the statistics maintained by the RDBMS kernel can be found in Appendix C of the Oracle Reference manual for the version being utilized.
Here is a partial example of the report: 

Instance Activity Stats for DB: PHS2  Instance: phs2  Snaps: 100 -104
Statistic                                    Total   per Second    per Trans
--------------------------------- ---------------- ------------ ------------
CPU used by this session                    84,161         23.4      3,825.5
CPU used when call started                 196,346         54.5      8,924.8
CR blocks created                              709          0.2         32.2
DBWR buffers scanned                             0          0.0          0.0
DBWR checkpoint buffers written                245          0.1         11.1
DBWR checkpoints                                33          0.0          1.5
DBWR cross instance writes                      93          0.0          4.2
DBWR free buffers found                          0          0.0          0.0
....
....
branch node splits                             7,162            0.1        0.0
consistent gets                       12,931,850,777      152,858.8    3,969.5
current blocks converted for CR               75,709            0.9        0.0
db block changes                         343,632,442        4,061.9      105.5
db block gets                            390,323,754        4,613.8      119.8
hot buffers moved to head of LRU         197,262,394        2,331.7       60.6
leaf node 90-10 splits                        26,429            0.3        0.0
leaf node splits                             840,436            9.9        0.3
logons cumulative                             21,369            0.3        0.0
physical reads                           504,643,275        5,965.1      154.9
physical writes                           49,724,268          587.8       15.3
session logical reads                 13,322,170,917      157,472.5    4,089.4
sorts (disk)                                   4,132            0.1        0.0
sorts (memory)                             7,938,085           93.8        2.4
sorts (rows)                             906,207,041       10,711.7      278.2
table fetch continued row                 25,506,365          301.5        7.8
table scans (long tables)                        111            0.0        0.0
table scans (short tables)                 1,543,085           18.2        0.5

Instance Activity Terminology

Session Logical Reads = All reads cached in memory.  Includes both consistent gets and also the db block gets.
Consistent Gets = These are the reads of a block that are in the cache.  They are NOT to be confused with consistent read (cr) version of a block in the buffer cache (usually the current version is read).
Db block gets = These are block gotten to be changed.  MUST be the CURRENT block and not a cr block.
Db block changes = These are the db block gets (above) that were actually changed.
Physical Reads = Blocks not read from the cache.  Either from disk, disk cache or O/S cache; there are also physical reads direct which bypass cache using Parallel Query (not in hit ratios).

Of particular interest are the following statistics.
- CPU USED BY THIS SESSION, PARSE TIME CPU or RECURSIVE CPU USAGE:  These numbers are useful to diagnose CPU saturation on the system (usually a query tuning issue). The formula to calculate the CPU usage breakdown is:
Service (CPU) Time = other CPU + parse time CPU
Other CPU = "CPU used by this session" - parse time CPU
Some releases do not correctly store this data and can show huge numbers. 

recursive cpu usage =  This component can be high if large amounts of PL/SQL are being processed. It is outside the scope of this document to go into detail with this, but you will need to identify your complete set of PL/SQL, including stored procedures, finding the ones with the highest CPU load and optimize these. If most work done in PL/SQL is procedural processing (rather than executing SQL), a high recursive cpu usage can actually indicate a potential tuning effort.

parse time cpu= Parsing SQL statements is a heavy operation, that should be avoided by reusing SQL statements as much as possible. In precompiler programs, unnecessary parting of implicit SQL statements can be avoided by increasing the cursor cache (MAXOPENCURSORS parameter) and by reusing cursors. In programs using Oracle Call Interface, you need to write the code, so that it re-executes (in stead of reparse) cursors with frequently executed SQL statements. The v$sql view contains PARSE_CALLS and EXECUTIONS columns, that can be used to identify SQL, that is parsed often or is only executed once per parse.

other cpu= The source of other cpu is primarily handling of buffers in the buffer cache. It can generally be assumed, that the CPU time spent by a SQL statement is approximately proportional to the number of buffer gets for that SQL statements, hence, you should identify and sort SQL statements by buffer gets in v$sql. In your report, look at the part ‘SQL ordered by Gets for DB’. Start tuning SQL statements from the top of this list. In Oracle, the v$sql view contain a column, CPU_TIME, which directly shows the cpu time associated with executing the SQL statement.

- DBWR BUFFERS SCANNED:  the number of buffers looked at when scanning the lru portion of the buffer cache for dirty buffers to make clean. Divide by "dbwr lru scans" to find the average number of buffers scanned. This count includes both dirty and clean buffers. The average buffers scanned may be different from the average scan depth due to write batches filling up before a scan is complete. Note that this includes scans for reasons other than make free buffer requests.
- DBWR CHECKPOINTS: the number of checkpoints messages that were sent to DBWR and not necessarily the total number of actual checkpoints that took place.  During a checkpoint there is a slight decrease in performance since data blocks are being written to disk and that causes I/O. If the number of checkpoints is reduced, the performance of normal database operations improve but recovery after instance failure is slower.
- DBWR TIMEOUTS: the number of timeouts when DBWR had been idle since the last timeout.  These are the times that DBWR looked for buffers to idle write.
- DIRTY BUFFERS INSPECTED: the number of times a foreground encountered a dirty buffer which had aged out through the lru queue, when foreground is looking for a buffer to reuse. This should be zero if DBWR is keeping up with foregrounds.
- FREE BUFFER INSPECTED: the number of buffers skipped over from the end of the LRU queue in order to find a free buffer.  The difference between this and "dirty buffers inspected" is the number of buffers that could not be used because they were busy or needed to be written after rapid aging out. They may have a user, a waiter, or being read/written.
- RECURSIVE CALLS:  Recursive calls occur because of cache misses and segment extension. In general if recursive calls is greater than 30 per process, the data dictionary cache should be optimized and segments should be rebuilt with storage clauses that have few large extents.  Segments include tables, indexes, rollback segment, and temporary segments.
NOTE: PL/SQL can generate extra recursive calls which may be unavoidable.
- REDO BUFFER ALLOCATION RETRIES: total number of retries necessary to allocate space in the redo buffer.  Retries are needed because either the redo writer has gotten behind, or because an event  (such as log switch) is occurring
- REDO LOG SPACE REQUESTS:  indicates how many times a user process waited for space in the redo log buffer.  Try increasing the init.ora parameter LOG_BUFFER so that zero Redo Log Space Requests are made.
- REDO WASTAGE: Number of bytes "wasted" because redo blocks needed to be written before they are completely full.   Early writing may be needed to commit transactions, to be able to write a database buffer, or to switch logs
- SUMMED DIRTY QUEUE LENGTH: the sum of the lruw queue length after every write request completes. (divide by write requests to get average queue length after write completion)
- TABLE FETCH BY ROWID: the number of rows that were accessed by a rowid.  This includes rows that were accessed using an index and rows that were accessed using the statement where rowid = 'xxxxxxxx.xxxx.xxxx'.
- TABLE FETCH BY CONTINUED ROW: indicates the number of rows that are chained to another block. In some cases (i.e. tables with long columns) this is unavoidable, but the ANALYZE table command should be used to further investigate the chaining, and where possible, should be eliminated by rebuilding the table.
- Table Scans (long tables) is the total number of full table scans performed on tables with more than 5 database blocks.  If the number of full table scans is high the application should be tuned to effectively use Oracle indexes. Indexes, if they exist, should be used on long tables if less than 10-20% (depending on parameter settings and CPU count) of the rows from the table are returned. If this is not the case, check the db_file_multiblock_read_count parameter setting. It may be too high.  You may also need to tweak optimizer_index_caching and optimizer_index_cost_adj.
- Table Scans (short tables) is the number of full table scans performed on tables with less than 5 database blocks.  It is optimal to perform full table scans on short tables rather than using indexes.

- Tablespace I/O Stats for DB: Ordered by total IO per tablespace.
- File I/O Stats for DB: Ordered alphabetically by tablespace, filename.

If the statistic "Buffer Waits" for a tablespace is greater than 1000, you may want to consider tablespace reorganization in order to spread tables within it across another tablespaces.

Note that Oracle considers average read times of greater than 20 ms unacceptable.  If a datafile consistently has average read times of 20 ms or greater then:
- The queries against the contents of the owning tablespace should be examined and tuned so that less data is retrieved.
- If the tablespace contains indexes, another option is to compress the indexes so that they require less space and hence, less IO.
- The contents of that datafile should be redistributed across several disks/logical volumes to more easily accommodate the load.
- If the disk layout seems optimal, check the disk controller layout.  It may be that the datafiles need to be distributed across more disk sets.

• ordered by IOs (Reads + Writes) desc

• ordered by Tablespace, File

Buffer Pool Statistics Section

The buffer pool statistics report follows. It provides a summary of the buffer pool configuration and usage statistics.

The buffer statistics are comprised of two sections:

- Buffer Pool Statistics:  This section can have multiple entries if multiple buffer pools are allocated. A baseline of the database's buffer pool statistics should be available to compare with the current report buffer pool statistics.  A change in that pattern unaccounted for by a change in workload should be a cause for concern. Also check the Buffer Pool Advisory to identify if increasing that parameter (db_cache_size) would help to reduce Physical Reads.

• Standard block size Pools D: default, K: keep, R: recycle
• Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k

- Checkpoint Activity: 

Advisory Statistics Section

In this section, we receive several recommendations on changes that we can perform and how that will affect the overall performance of the DB.

The instance recovery stats report provides information related to instance recovery. By analyzing this report, you can determine roughly how long your database would have required to perform crash recovery during the reporting period. Here is an example of this report:

• B: Begin Snapshot, E: End Snapshot

Buffer Pool Advisory

• Only rows with estimated physical reads >0 are displayed
• ordered by Block Size, Buffers For Estimate

In this example we currently have 1.488 GB allocated to the SGA (represented by the size factor column with a value of 1.0.
It appears that if we were to reduce the memory allocated to the SGA to half of the size of the current SGA (freeing the memory to the OS for other processes) we would incur an increase of just a few more physical Reads in the process.

PGA Reports
The PGA reports provide some insight into the health of the PGA.
- The PGA Aggr Target Stats report provides information on the configuration of the PGA Aggregate Target parameter during the reporting period.
- The PGA Aggregate Target Histogram report provides information on the size of various operations (e.g. sorts). It will indicate if PGA sort operations occurred completely in memory, or if some of those operations were written out to disk.
- The PGA Memory Advisor, much like the buffer pool advisory report, provides some insight into how to properly size your PGA via the PGA_AGGREGATE_TARGET database parameter.

Here we show these reports:

• B: Begin Snap E: End Snap (rows dentified with B or E contain data which is absolute i.e. not diffed over the interval)
• Auto PGA Target - actual workarea memory target
• W/A PGA Used - amount of memory used for all Workareas (manual + auto)
• %PGA W/A Mem - percentage of PGA memory allocated to workareas
• %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
• %Man W/A Mem - percentage of workarea memory under manual control

• Optimal Executions are purely in-memory operations

• When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0

Buffer Wait Statistics

The buffer wait statistics report helps you drill down on specific buffer wait events, and where the waits are occurring. In the following report we find that the 13 buffer busy waits we saw in the buffer pool statistics report earlier are attributed to data block waits. We might then want to pursue tuning remedies to these waits if the waits are significant enough. Here is an example
of the buffer wait statistics report:

Buffer Wait Statistics       DB/Inst: AULTDB/aultdb1Snaps: 91-92
-> ordered by wait time desc, waits desc
Class                    Waits Total Wait Time (s)  Avg Time (ms)
------------------ ----------- ------------------- --------------
data block                  13                   0              1
undo header                  1                   0             10

Enqueue Statistics

An enqueue is simply a locking mechanism. This section is very useful and must be used when the wait event "enqueue" is listed in the "Top 5 timed events".
The Enqueue activity report provides information on enqueues (higher level Oracle locking) that occur.
As with other reports, if you see high levels of wait times in these reports, you might dig further into the nature of the enqueue and determine the cause of the delays.
Here is an example of this report section:

EnqueueActivity                          DB/Inst: AULTDB/aultdb1 Snaps:91-92
-> only enqueues with waits are shown
-> Enqueue stats gathered prior to 10g should not be compared with 10g data
-> ordered by Wait Time desc, Waits desc

Enqueue Type (Request Reason)
------------------------------------------------------------------------------
    Requests    Succ Gets Failed Gets       Waits  Wt Time (s) Av Wt Time(ms)
------------ ------------ ----------- ----------- ------------ --------------
PS-PX Process Reservation
         386          358          28         116            0            .43
US-Undo Segment
         276          276           0         228            0            .18
TT-Tablespace
          90           90           0          42            0            .71
WF-AWR Flush
          12           12           0           7            0           1.43
MW-MWIN Schedule
           2            2           0           2            0           5.00
TA-Instance Undo
          12           12           0          12            0            .00
UL-User-defined
           7            7           0           7            0            .00
CF-Controlfile Transaction
       5,737        5,737           0           5            0            .00

- TX (Transaction Lock): Generally due to application concurrency mechanisms, or table setup issues. The TX lock is acquired when a transaction initiates its first change and is held until the transaction does a COMMIT or ROLLBACK. It is used mainly as a queuing mechanism so that other resources can wait for a transaction to complete.

- TM (DML enqueue): Generally due to application issues, particularly if foreign key constraints have not been indexed. This lock/enqueue is acquired when performing an insert, update, or delete on a parent or child table.

- ST (Space management enqueue): Usually caused by too much space management occurring. For example: create table as select on large tables on busy instances, small extent sizes, lots of sorting, etc. These enqueues are caused if a lot of space management activity is occurring on the database (such as small extent size, several sortings occurring on the disk).

V$SESSION_WAIT and V$LOCK give more data about enqueues
- The P1, P2 and P3 values tell what the enqueuemay have been waiting on
- For BF we get node#, parallelizer#, and bloom#

column parameter1 format a15
column parameter2 format a15
column parameter3 format a15
column lock format a8
Select substr(name,1,7) as "lock",parameter1,parameter2,parameter3
from v$event_name
where name like 'enq%';

Undo Statistics Section
The undo segment summary report provides basic information on the performance of undo tablespaces.

Undo information is provided in the following sections:
- Undo Segment Summary
- Undo Segment Stats

The examples below show typical performance problem related to Undo (rollback) segments:

- Undo Segment Summary for DB

Undo Segment Summary for DB: S901  Instance: S901  Snaps: 2 -3
-> Undo segment block stats:
-> uS - unexpired Stolen,   uR - unexpired Released,   uU - unexpired reUsed
-> eS - expired   Stolen,   eR - expired   Released,   eU - expired   reUsed
 
Undo           Undo        Num  Max Qry     Max Tx Snapshot Out of uS/uR/uU/
 TS#         Blocks      Trans  Len (s)   Concurcy  Too Old  Space eS/eR/eU
---- -------------- ---------- -------- ---------- -------- ------ -------------
   1         20,284      1,964        8         12        0      0 0/0/0/0/0/0

The description of the view V$UNDOSTAT in the Oracle Database Reference guide provides some insight as to the columns definitions.  Should the client encounter SMU problems, monitoring this view every few minutes would provide more useful information.

- Undo Segment Stats for DB

Undo Segment Stats for DB: S901  Instance: S901  Snaps: 2 -3
-> ordered by Time desc
 
                     Undo      Num Max Qry   Max Tx  Snap   Out of uS/uR/uU/
End Time           Blocks    Trans Len (s)    Concy Too Old  Space eS/eR/eU
------------ ------------ -------- ------- -------- ------- ------ -------------
12-Mar 16:11       18,723    1,756       8       12       0      0 0/0/0/0/0/0
12-Mar 16:01        1,561      208       3       12       0      0 0/0/0/0/0/0

This section provides a more detailed look at the statistics in the previous section by listing the information as it appears in each snapshot.

Use of UNDO_RETENTION can potentially increase the size of the undo segment for a given period of time, so the retention period should not be arbitrarily set too high. The UNDO tablespace still must be sized appropriately. The following calculation can be used to determine how much space a given undo segment will consume given a set value of UNDO_RETENTION.
Undo Segment Space Required = (undo_retention_time * undo_blocks_per_seconds)

As an example, an UNDO_RETENTION of 5 minutes (default) with 50 undo blocks/second (8k blocksize) will generate:

Undo Segment Space Required = (300 seconds * 50 blocks/ seconds * 8K/block) = 120 M

The retention information (transaction commit time) is stored in every transaction table block and each extent map block. When the retention period has expired, SMON will be signaled to perform undo reclaims, done by scanning each transaction table for undo timestamps and deleting the information from the undo segment extent map. Only during extreme space constraint issues will retention period not be obeyed.

Latch Statistics Section

The latch activity report provides information on Oracle's low level locking mechanism called a latch. From this report you can determine if Oracle is suffering from latching problems, and if so, which latches are causing the greatest amount of contention on the system.

Latch information is provided in the following three sections:
. Latch Activity
. Latch Sleep breakdown
. Latch Miss Sources

This information should be checked whenever the "latch free" wait event or other latch wait events experience long waits. This section is particularly useful for determining latch contention on an instance.  Latch contention generally indicates resource contention and supports indications of it in other sections. Latch contention is indicated by a Pct Miss of greater than 1.0% or a relatively high value in Avg Sleeps/Miss. While each latch can indicate contention on some resource, the more common latches to watch are:

cache buffer chain= The cache buffer chain latch protects the hash chain of cache buffers, and is used for each access to cache buffers. Contention for this latch can often only be reduced by reducing the amount of access to cache buffers. Using the X$BH fixed table can identify if some hash chains have many buffers associated with them. Often, a single hot block, such as an index root block, can cause contention for this latch. Contention on this latch confirms a hot block issue.
shared pool= The shared pool latch is heavily used during parsing, in particular during hard parse. If your application is written so that it generally uses literals in stead of bind variables, you will have high contention on this latch. Contention on this latch in conjunction with reloads in the SQL Area of the library cache section indicates that the shared pool is too small. You can set the cursor_sharing parameter in init.ora to the value ‘force’ to reduce the hard parsing and reduce some of the contention for the shared pool latch. Applications that are coded to only parse once per cursor and execute multiple times will almost completely avoid contention for the shared pool latch.

• Literal SQL is being used. See Note 62143.1 'Understanding and Tuning the Shared Pool for an excellent discussion of this topic.
• The parameter session_cached_cursors might need to be set.  See enhancement bug 1589185 for details.

Here is a partial example of the latch activity report (it is quite long):

                                           Pct    Avg   Wait                 Pct
                                    Get    Get   Slps   Time       NoWait NoWait
Latch Name                     Requests   Miss  /Miss    (s)     Requests   Miss
------------------------ -------------- ------ ------ ------ ------------ ------
ASM allocation                      122    0.0    N/A      0            0    N/A
ASM map headers                      60    0.0    N/A      0            0    N/A
ASM map load waiting lis             11    0.0    N/A      0            0    N/A
ASM map operation freeli             30    0.0    N/A      0            0    N/A
ASM map operation hash t         45,056    0.0    N/A      0            0    N/A
ASM network background l          1,653    0.0    N/A      0            0    N/A
AWR Alerted Metric Eleme         14,330    0.0    N/A      0            0    N/A
Consistent RBA                      107    0.0    N/A      0            0    N/A
FAL request queue                    75    0.0    N/A      0            0    N/A
FAL subheap alocation                75    0.0    N/A      0            0    N/A
FIB s.o chain latch                  14    0.0    N/A      0            0    N/A
FOB s.o list latch                   93    0.0    N/A      0            0    N/A
JS broadcast add buf lat            826    0.0    N/A      0            0    N/A
JS broadcast drop buf la            826    0.0    N/A      0            0    N/A

In this example our database does not seem to be experiencing any major latch problems, as the wait times on the latches are 0, and our get miss pct (Pct Get Miss) is 0 also.

There is also a latch sleep breakdown report which provides some additional detail if a latch is being constantly moved into the sleep cycle, which can cause additional performance issues.

The latch miss sources report provides a list of latches that encountered sleep conditions. This report can be of further assistance when trying to analyze which latches are causing problems with your database.

Segments Statistics Sections

This is a series of reports that let you identify objects that are heavily used. It contains several sub-sections like:

Segments by Logical Reads
Segments by Physical Reads
Segments by Physical Read Requests
Segments by UnOptimized Reads
Segments by Optimized 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

The "segments by logical reads" and "segments by physical reads" reports provide information on the database segments (tables, indexes) that are receiving the largest number of logical or physical reads. These reports can help you find objects that are "hot" objects in the database. You may want to review the objects and determine why they are hot, and if there are any tuning opportunities available on those objects (e.g. partitioning), or on SQL accessing those objects.

For example, if an object is showing up on the physical reads report, it may be that an index is needed on that object. Here is an example of the segments by logical reads report:

Segments by Logical Reads

• Total Logical Reads: 393,142,567
• Captured Segments account for 99.4% of Total

Segments by Physical Reads
Queries using these segments should be analyzed to check whether any FTS is happening on these segments. In case FTS is happening then proper indexes should be created to eliminate FTS.
Most of these SQLs can be found under section SQL Statistics -> SQL ordered by Reads.

• Total Physical Reads: 415,478,983
• Captured Segments account for 86.3% of Total

Several segment related reports appear providing information on:
•Segments with ITL waits
•Segments with Row lock waits
•Segments with buffer busy waits
•Segments with global cache buffer waits
•Segments with CR Blocks received
•Segments with current blocks received

• Total Physical Read Requests: 4,701,222
• Captured Segments account for 60.7% of Total

• Total UnOptimized Read Requests: 4,701,222
• Captured Segments account for 60.7% of Total

• Total Direct Physical Reads: 415,248,809
• Captured Segments account for 86.3% of Total

• Total Physical Writes: 57,054,872
• Captured Segments account for 0.1% of Total

• Total Physical Write Requestss: 1,866,713
• Captured Segments account for 0.9% of Total

• Total Direct Physical Writes: 56,969,427
• Captured Segments account for 0.0% of Total

• Total Table Scans: 18,527
• Captured Segments account for 95.6% of Total

• % of Capture shows % of DB Block Changes for each top segment compared
• with total DB Block Changes for all segments captured by the Snapshot

Segments by Row Lock Waits

The statistic displays segment details based on total “Row lock waits” which happened during snapshot period. Data displayed is sorted on “Row Lock Waits” column in descending order. It provides information about segments for which more database locking is happening.

DML statements using these segments should be analysed further to check the possibility of reducing concurrency due to row locking.

Segments by ITL Waits

Whenver a transaction modifies segment block, it first add transaction id in the Internal Transaction List table of the block. Size of this table is a block level configurable parameter. Based on the value of this parameter those many ITL slots are created in each block.

ITL wait happens in case total trasactions trying to update same block at the same time are greater than the ITL parameter value.

Total waits happening in the example are very less, 23 is the Max one. Hence it is not recommended to increase the ITL parameter value.

Segments by Buffer Busy Waits

Buffer busy waits happen when more than one transaction tries to access same block at the same time. In this scenario, the first transaction which acquires lock on the block will able to proceed further whereas other transaction waits for the first transaction to finish.

If there are more than one instances of a process continuously polling database by executing same SQL (to check if there are any records available for processing), same block is read concurrently by all the instances of a process and this result in Buffer Busy wait event.