Oracle took a completely different data collection strategy with ASH. Instead of instrumentation, ASH uses sampling. Built directly into Oracle's kernel, active sessions (server or background) will insert one additional row into the active session table each and every second. A session is deemed active if it is either consuming CPU or waiting on a non-idle event, such as an enqueue, latch, or IO. The sample information is buffered for around 30 minutes, but this is dependent on a number of factors. There is also a stunning array of information stored, such as the running SQL identifier, whether the session is consuming CPU or waiting on an event, the event name, and session identification information like the session identifier and the client identifier. If the sample rate is high enough yet not too high, you get fantastic performance data, without placing a significant burden on the system.

I am often asked why ASH is such a big deal. There are four reasons why ASH truly is a very big deal: back-in-time capabilities, configurable low-impact kernel-level data collection, clean data, and the connection of session activity with resource consumption (think service time) and wait time.

Suppose you received a call from a user who said performance was poor about 30 minutes ago, but now performance is just fine. With ASH, you can easily go back in time, and with a very simple query, perform a session-, instance-, or system-level ORTA. Usually, when someone calls with a performance problem, we get on the system and start running scripts. But even if our scripts are interval-based, they are probably based on the present going forward. This means if the problem is gone, so is our capability to diagnose that problem. Since ASH collects data and buffers it, that data is there for us to use in our "what just happened?" diagnosis. Even better, the ASH data is written into the automatic workload repository tables, creating the capability to generate advanced reports based on just about any time frame.