Tools and Utilities - Expert Oracle RAC Performance Diagnostics and Tuning - page 156

The ADDM report in Figure 6-13 makes a recommendation to increase the memory of the Oracle instances.
Before making any change it's always a good idea to explore the impact of the suggestion. For example, how many
times has this error occurred? To determine if this occurred in the past, click the Finding History option. Figure 6-12
illustrates the impact of this error over the entire timeframe.
ADDM is also a good source to identify high-load SQL statements. For instance, in Figure 6-11 the second highest
impact (90.5%) on the overall performance of the cluster are SQL statements, identified by Top SQL Statements. Every
run of ADDM produces a report containing analysis and recommendations. These reports are stored by default for
30 days in the database before being purged. Apart from identifying high-load SQL statements, ADDM also
recommends running SQL advisors on them to obtain optimal performance benefits.

Active Session History
AWR collects snapshots containing performance characteristics of the database; ADDM analyzes this information
and provides recommendations on what changes could be made to improve efficiency of the database. A onetime
occurrence of any issue is not a good indication of any specific performance problem. Similar to a situation where a
bug is reported by a user in his or her application, the developer is interested in finding out if this bug is repeatable
or if the error is reproducible. The developer may not spend time investigating it and could consider the situation a
minor issue and prioritize it for further investigation at a later time. This is because a one-time occurrence of an issue
does not really provide sufficient data to fix the problem. Repeated occurrences are when the data becomes consistent
and tunable. Similarly, in a database environment, problems do arise, systems do slow down, occasional high spikes
do occur. However, unless these spikes are reproducible or consistently happening over a length of time (past, present,
and future), no real concern is given.

■ a methodical way to address frequently occurring or reproducible spikes is to consider ash data for detailed
analysis and have osWatcher or the ipd/os (ChM) always running.

Note

Active session history (ASH, pronounced like the word “ash”) tries to bridge this gap. ASH performs analysis of transient
problems lasting for a few minutes or over various dimensions such as time, SQL_ID, module, action, etc. As mentioned,
unlike the other reactive reporting issues, ASH is based on a sampled history of all events happening in the database. ASH
data that is captured and stored for all active sessions is essentially a fact table ( GV$ACTIVE_SESSION_HISTORY ) in a data
warehouse environment, with the columns as the dimensions of the fact table. In this view, there are about 13 important
dimensions of data.
Similar to the AWR and ADDM features, ASH reports can also be generated from the EM console (Figure 6-14 )
or by using the ashrpt.sql script located in the $ORACLE_HOME/rdbms/admin directory on the database server.
Figure 6-14 has two sections, the basic section illustrating the overall performance over a period of time, and the
second section lists the load map of the wait event distribution categories under system I/O, CPU, cluster and commit.