But this problem is solved for a price. A license is required to legally query ASH views or run ASH reports. Since ASH data feeds the AWR, the same legal limitation applies. The nasty thing about this is that, by default, Oracle's base product consumes your computing system resources gathering detailed performance statistics, yet a simple query to retrieve this information is illegal without the additional license. While not a great public relations move, it is a shrewd corporate maneuver.10

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.