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.

As discussed earlier in this chapter, modern Oracle architectures make linking a user's activity to an Oracle server process challenging. ASH helps in this endeavor because it stores not only the session identifier and serial number, but also the client identifier, the Oracle username, program, module, and action!

Let's not forget that ASH is built directly into Oracle's kernel, as opposed to a third-party data collection facility that either samples from Oracle's SGA or samples sessions through the SQL interface. While both approaches can work well, keeping the collection running constantly requires a professional-grade product. I have personally spent a considerable amount of time designing and building data collection tools. It is extremely difficult to perform a low-impact collection on a large system while supplying accurate data. Most third-party vendors spend a lot of time and money on their collection facilities. What anyone who has tried to create collectors quickly learns is that what initially seems like a few simple SQL queries turns out to be a daunting technical challenge. This is one reason why performance products seem relatively expensive.