|
Screenshots |
|---|
 |
AWR Average Active Session |
 |
AWR DB Time |
 |
ASH Average Active Session |
|
 |
Dimension:
Average Active Session
Average Active Session (AAS) is a sensitive indicator of database workload and performance.
There are two ways to calculate it: either from AWR data (DB time) or ASH data (sample counts).
AWR AAS is usually called as 'cumulative AAS' while ASH AAS is call as 'sampled AAS'.
Each AAS has its talent to tell when performance issue starts.
| |
 |
AWR Top 5 Foreground Wait Events |
 |
ASH Top User Events |
 |
ASH Top Latch Events |
|
|
Dimension:
Wait Event
Wait event is one of the golden metrics for troubleshooting performance issue. Both AWR data and ASH data can
provide ‘top’ list which should be cross-checked. AWR data can be further drilled down to wait statstics details.
ASH data can provide ‘tracing’ type of information to reveal session details related to wait events.
| |
 |
AWR Top SQL by Elapsed Time |
 |
ASH Top SQL |
|
|
Dimension:
SQL
SQL is another golden metric for troubleshooting performance issue.
Both AWR data and ASH data can provide ‘top’ list. The ‘top’ list from ASH data can be
filtered out according to session pattern such as user name, service name, module, action, program, client id.
| |
 |
ASH Top SQL Plan |
 |
SQL Plan Change by SQL ID |
|
|
Dimension:
SQL Plan
SQL Plan is another golden metric for troubleshooting performance issue. AWR data can provide ‘top’ list based on
how many SQLs are related to a specific SQL plan, and what are the footprints (sql stats) those SQLs incur on the database.
ASH data can provide ‘top’ list based on how significant a SQL plan shows up in the sampling.
| |
 |
AWR Top Segments by Logical Reads |
 |
ASH Top Objects |
|
|
Dimension:
Object
Object, or segment, is also a golden metric for troubleshooting performance issue. Pay attention to ‘% of total’ or ‘% of capture’ when reviewing
the ‘top’ list provided by AWR data for the purpose of accuracy. The ‘top’ list from ASH data has constraints because the object
information is limited to certain wait events.
| |
 |
AWR IO Load Profile per Snapshot |
 |
AWR IO Response Time by Wait Event |
 |
ASH Top Files |
|
|
Dimension:
IO Statistics
AWR data provides dominant IO statistics for performance tuning and capacity planning purposes.
Three primary metrics (IO load profile, throughput and response time) are lined up to reveal
what is database’s IO characteristic, whether the increase of workload leads to the performance degradation at IO path, etc.
ASH data is limited to situations where certain certain data files are significately accessed by sampled sessions.
| |
 |
AWR GC Throughput |
 |
AWR GC Blocks Served Time |
|
|
Dimension:
GC Statistics
GC statistics is for RAC database and it primarily comes from AWR data. Like IO statistics,
both throughput and response time are examined at various angles to reveal if there is performance issue at interconnect.
| |
 |
AWR Service Load Profile |
 |
ASH Top Applications |
 |
ASH Top Blocking Sessions |
|
|
Dimension:
Service, Application, Session
AWR data is cumulative at instance level and plays limited role here: only the load profile can be
broken down to service level. ASH data is sampled at session level and has its strength at various angles:
the ‘session’ refers to not only session id, but also application level and service level information
such as user name, service name, module, action, program, client id.
| |
