ASH Masters

November 19th, 2014


Welcome the ASH Masters and to the world of ASH, also know as “Active Session History”.

Oracle 10g introduced ASH and Oracle 11 builds upon that foundation. ASH is a radically new way to gather performance data. “Radical?” You might ask. Yes, “radical” because ASH only takes samples of data once a second and what happens between samples is lost. The lost data bothers many at first but this unease quickly passes when the power of ASH is understood. ASH provides information necessary to solve the toughest performance problems. Before ASH this information was often too difficult and expensive to get. Previous performance gathering techniques such as STATSPACK lacked the information to solve many performance bottlenecks. Session tracing provides much of the same information as ASH but is much more costly and has to be set up before a problem arises, proving to be impractical in many situations. Starting in Oracle 10g, ASH is always running, sampling every second, and saved for a week on disk (configurable), thus providing the data need to identify and solve a problem that may have only lasted seconds and days ago. ASH is way of sampling the state of sessions connected to an Oracle database in order to monitory database load as well as drill down into any performance issues that may arise. ASH is a technology that can be applied to any system with connected users such as another database like SQL Server or an operating system or even real life systems. Oracle is the first system that I know of using ASH to collect performance data. ASH by default on an Oracle database, samples once every second and logs some 30 to 60 (depending on version) pieces of information on any session that is active at the time of sampling. One hurtle to accepting ASH as a model had been the question “how fast do we have to sample to get worth while data”. There has been a tendency (I admit having felt this) to want to sample super fast like 10-100 times a second (easily possible with a C program) but experience has shown that most monitoring and problem resolution can be easily accomplished with 1 second sampling. Higher rate of sampling serve only for rare cases that are better left to tracing. For 99.9% of the time, sampling once a second is sufficient to both show clearly the load on the system as well as provide detailed information on system activity that would otherwise be too difficult or prohibitively expensive to collect (due to the load caused by the data collection itself).

What is ASH Masters? ASH Masters is the brain child of  John Beresniewicz,  Graham Wood and Kyle Hailey. We wanted to provide a web site where people could learn and exchange new ideas, experiences and tools to harness the power of ASH.

  1. Trackbacks


  2. kais
    | #1

    Great work .thanks.

  3. John
    | #2

    if I have more than one Oracle instance in a physical host,
    what do you think about the idea to pile the results CPU, WAITS and IO of each instance (possibly using one of yours ash_graph_ash.sql scripts)
    on just one graphic to show the resource consumption in just one big picture?

    The results would be usable?

    Thanks in advance

  4. khailey
    | #3

    Hi John, Sounds like a great idea. I think someone might have already done something like that, maybe Karl Arao or Tyler Muth?

  5. John
    | #4

    Thanks Kyle
    I will check it out.
    What did happened with your last blog?

  6. | #5

    Hi Kyle,

    very interesting page, thank you.
    I’ve run an GUI-app “Panorama” for performance analysis on Oracle-DBs for several years, but all this time with EE and diagnostics pack as precondition.
    Started now with a built-in sampling engine to make this app working without AWR and ASH, but with similar function.
    It is still not complete, but ASH-equivalent and SQLStat are still working.
    It should be complete enough within the next weeks to cover Panorama’s whole function set without diagnostics pack, so it would also be usable for evaluation of history in Standard Edition.
    May be it becomes interesting some times.

    Best regards

three + = 5