Lesson 2. Active Session History (ASH). Wait Event-Based Tuning with Activity Explorer.
The purpose of an Oracle server is to serve queries. While serving a query, the server process can be either running on the CPU or waiting. The goal of database tuning is to reduce query execution time, translating into reduced time spent both on CPU and in waits.
Historically, Oracle wait event-tuning was based on the v$system_event view. This approach has been limited to instance-level tuning and lacked CPU data. In recent years, another technique proved to be more effective, based on active session data snapshots (v$session, status='ACTIVE'). The active session data is then aggregated to expose components of the database time at various levels, most commonly at the individual SQL statement level.
Active session history (ASH) collection has been built into Oracle 10g. This data can be accessed through v$active_session_history but using that data requires purchasing the license. Lab128 has an independent ASH collection engine that collects more statistics and stores more history. The ASH collection is integrated into Lab128 and doesn't require other licenses. Optionally, Lab128 can use the v$active_session_history view (if the user owns the corresponding license) to populate ASH data that can be later analyzed using sophisticated analytical facilities built into the application.
Let's take a tour of the Activity Explorer, which supports database tuning using both approaches mentioned above. Although somewhat redundant, we decided to include the older technique using v$system_event. This provides an opportunity to reconcile and validate ASH data and to catch even the smallest waits which may not be detected by the ASH method. You should be familiar with these two sources of wait data when using the Activity Explorer.
There are many ways to open the Activity Explorer. For now, let's open it by using main menu: View | Activity Explorer, or pressing the appropriate button in the tool bar, or simply pressing F5. There are three parts in this window: upper - the graphical charts, middle - the tabular view of system wait values, and lower - Active Session History (ASH).
In the upper part, select the time interval around interesting activity by highlighting with the mouse. The other two sections will show data for the selected time interval. Note that the first chart shows data obtained from v$system_event and the second chart shows data obtained from ASH. These two charts should be similar with the exception that the ASH chart will also have a CPU component (shown in green). The CPU component can be turned off with the check box at the top of this window, but because the CPU time is very valuable information, you should keep it on in most cases.
The tabular view in the middle section presents "delta" values from v$system_event for the selected time. As a reminder, this data for wait events is at an instance level. This data is complementary because it can be indirectly inferred from the ASH data. Therefore, the v$system_event section has lower priority and you can resize it to a smaller size and give more space to the lower ASH section.
The ASH section has several tabs. The Summary view is displayed by default when the Activity Explorer window opens. In this view, the stacked colored bars show wait components for each SQL statement. This helps quickly assess which SQL statements are causing which problems. Select an individual SQL statement to see its details. Now, switch to the History view. Each row in this view has active session data for a particular moment in time. By default, data in this view is sorted by Statement ID and time of the snapshot. Therefore, the rows are grouped by SQL statement to show multiple snapshots of a single SQL statement. Please note that selection is placed on the first row of the SQL statement group highlighted in the Summary view. The history view shows every single detail about active sessions and the number of rows depends on the length of the selected time period so the amount of data can be huge. Typically, you shouldn't need to go to this view unless you are examining the finest details of session activity, e.g. you need to see which table or index the session was reading (there is a better way to do that in the Tree view, discussed later) or find a blocking session for the blocked session etc. If you must work in this view, please note that the timestamp column is a link to the Active Session History window, where you can see the entire snapshot with all sessions that have been active at that moment. If you wish to track an individual session, select the row, then right click and select "Selected Session History".
The Tree view in the ASH section deserves special attention. This tree-style view shows the sum of elapsed time (sum of all response times) broken down by groups defined by you, the user. For example, if you select a SQL statement in the first box, you will get data similar to the Summary view you have seen earlier. This is because the Summary view represents just one (although very important) way of grouping by SQL statement. Let's select "Event" in the second box, and "Object" in the third. The tree view will update. Now traverse the tree by expanding nodes. If there were read events, you should see which objects have been accessed and see the percentage of time spent for each of them. This is much easier than digging through the details history as we tried minutes earlier.
The Tree view shows the response time aggregated by dimensions, and Lab128 helps you analyze the components of response time. This is called dimensional analysis. There are many dimensions which can be selected at each level. However, which dimensions should be selected? This depends on the type of problem. Generally, you should first select dimensions which you can influence directly or indirectly. For example, SQL statement can be tuned or changed, so it is common to break down the response time by SQL statement. If, for example, you have a hot segment (table, or index, or partition etc) which you can repartition or move to another tablespace, you should select "Object", then "Event" (or "Event" then "Object"). You need to select "Event" because you want to make sure you are looking at the object which was causing a "db file sequential read" event, for example, and not by "enq: TX row lock contention". As another example, if you distribute I/O load to other tablespaces / datafiles, you may choose the "Event" and "File" dimensions. Another guideline is to use the leading dimension as a filter. For example, if you are concerned about locking issues in your database, you may select first "Event" as a filter, and then select "SQL" in the second box. Expanding the Event dimension, you can find "enq: TX row lock contention" and then see which SQL statements were causing this wait.
As a reminder, all data shown in the Activity Explorer pertains to the selected period of time. You may want to explore the same period of time in other windows. Lab128 can switch to another window and automatically make the same time selection. For example, if you decide to examine the same period of time in the SQL Explorer, an option in the right-click menu of any graphical chart will call the appropriate tool. This is a powerful feature, which can make monitoring and tuning much easier. Another useful feature is the ability to add more statistics from other windows into any graphical section. Similarly, multiple graphs can be merged into a separate window. See Lesson 4, which provides more information on graphical charts.
Lesson Summary. The Activity Explorer provides extensive information on wait events by using its own implementation of ASH collection and by using the system-wide view v$system_event; you should be familiar with these two sources of data. All data presented in this window pertains to the selected time period, so it is critical to make the selection around interesting activity in the graphical chart section. Alternatively, the Activity Explorer can be called from other windows while preserving the time selection. The middle section presents instance-wide waits; it is included for completeness but you should focus on the ASH section first because it generally contains more pertinent information. The goal of tuning activity is to reduce database response time (elapsed time), which has two components: time spent on CPU and wait time. The Summary view presents top SQL statements with the highest elapsed time and shows the components of elapsed time: waits and CPU time. The History view provides finer details, but the amount of information is usually too large to handle. If you ever need to explore details, you should select a SQL statement in Summary and then switch to History. From the History view, the Session History window and individual Session History window can be opened for a particular snapshot or session ID. The Tree view can be used for advanced analysis of response time, allowing for user-defined dimensions. The selection of dimensions should be driven by user-influenced attributes. Alternatively, selected dimensions can act as filters for the analysis of response time.