ASH Masters

January 30th, 2013

Resources

  • ASH SQL script repository README
  • ASH SQL script repository
  • Introduction to ASH (ppt)
  • ASHMON – free graphical browser for ASH data
  • S-ASH : simulated ASH, collect ASH on any Oracle version or edition for free
  • Average Active Sessions explains AAS

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. December 28th, 2012: http://dboptimizer.com/tools-2/ | xfang
  3. April 11th, 2013: Another BASH | DNADBA
  4. July 16th, 2013: Monitoring Oracle Performance with AppDynamics | Oraculix (en)
  1. Comments

  2. LittleFoot
    December 30th, 2012 at 19:40 | #1

    HI,

    A REAL PIECE OF INFO FOR STARTERS TO UNDERSTAND ABOUT ASH.
    I THINK SECOND AND THIRD PARA ARE REPETED.
    THANK YOU.

    Littlefoot

  3. Haris
    January 30th, 2013 at 15:53 | #2

    Hi Kyle

    Thank you for sharing very informative topic on “Active Session History”.

    May i get your permission to download your ASH SQL scripts and how do i do that.

    Please advice

    Thank you
    -Haris

  4. January 30th, 2013 at 16:28 | #3

    Hi Haris,

    I updated the links above to
    * ASH SQL script repository README
    * ASH SQL script repository
    The README points to a README file that explains the scripts.
    The repository link points to an actual list of the scripts. Just click on the script of interest to get the code.
    If you have github account (which is free) you can fork the whole repository and then have your own copy in source control that you can modify.

    – Kyle

  5. figi damskie
    February 18th, 2013 at 14:38 | #4

    I got this website from my friend who informed me on the topic of this web site and now this time I am browsing this website and reading very informative content at this time.

  6. Bjorn
    February 22nd, 2013 at 12:09 | #5

    Kyle, just a couple of quick questions.
    I’m mainly interested in S-ASH as a replacement for ASH on oracle 10g, and 11g.
    Our databases, and the ones we install for our customers have the tuning and diagnostics packs disabled to prevent “accidental” use.
    1. How does S-ASH work alongside the official Oracle schema objects such as v$a_s_h and DBA_HIST* tables. Is there any conflict? I have not peeked at ashmon yet, but when I read the targ_1_pkg.sql it looks like it should be ok, with the data going to a repository databases, and inserted into SASH.sash_* _tables in the repository DB.
    2. License. I note that the files are copyrighted, which means they are your intellectual property, and it is up to you who gets to use it and for what purposes (even without the copyright mark this is true. Anything you create is by definition your own IP, and others can not copy, change, redistribute, read it out loud, sing it, etc. without expressed permission. If it was a song, or a text, if you didn’t want XYZ to perform it public or on the radio, because you don’t want to be associated with that artist, you have the power to stop that. Since I work in a software company, any third party code we use must be vetted for license. For example GPL code is OK, as long as we will not modify and distribute. Do you use a standard license of any sort? I see your comments about it being OK to get it from git, and fork your own branch etc. So I assume for individual DBAs using this to monitor their own databases you’re fine. How about commercial products? Is it ok for the dbas in a company to include this in their own toolchest to troubleshoot customer’s databases? What about teh lead DBAS packacking it up for the whole company to use? Include it in a software distribution, as part of the overall health check, DB monitoring and troubleshooting tool, along with the real products the company sells? What kind of limitations do you impose on the use of this code? I am sure you would not be too pleased if someone grabbed the code and started shipping for example as “s-ash++”, didn’t share the improvements back to the community, charged through the nose, and so on. We are not in the database business, except that our applications, that we sell and charge money for, use Oracle as the RDBMS of choice.

  7. February 22nd, 2013 at 21:52 | #6

    @Bjorn
    1. S-ASH works fine along side regular ASH. S-ASH goes into it’s own schema so there is no conflict.
    2. I’m happy with anyone using S-ASH for anything as long as it’s not packaged and sold. My main reason for putting a copy right on it was because Quest , now Dell, was interested in using it as a possible alternative to their collection agents which require putting binaries on each machine. If someone wants to modify it and give it away for free, that’s fine with me.

  8. Michael
    March 4th, 2013 at 06:10 | #7

    In the Average Active Sessions PDF, there are several slides that reference scripts (e.g., SQL\ASHcpuiowaitAS.sql). Are these scripts accessible/downloadable from somewhere?

    Thanks. Great site.

  9. March 4th, 2013 at 06:57 | #8

    All the scripts that are online are at
    https://github.com/khailey/ashmasters
    will add some more over time.
    – Kyle

  10. EB
    April 9th, 2013 at 10:33 | #9

    Hii Kyle
    I am seeing inequality between “DB time” statistic and ASh when I looked past.
    I asked same question in Otn forums too but nobody replied yet may be you know where I am making fault

    time model presantation says select count(*) from v$active_session_history = DB Time.
    I am taking two values to compare, one is delta value of ‘Db time’ between two snap_id from DBA_HIST_SYS_TIME_MODEL and second from “select count(*) from DBA_HIST_ACTIVE_SESS_HISTORY” between same snap_ids.Result is that values not even close
    Also If ‘Db time’ – ‘DB CPU’ = “Wait time” then I compare ‘Db time’ – ‘DB CPU value with sum(time_waited_micro) delta value of “dba_hist_system_event” between same snap_id values I getting same result
    have you any idea where I am wrong ?

    Best Regards

  11. April 10th, 2013 at 05:18 | #10

    @EB
    Personally, I don’t use DB Time. I use Average Active Sessions.
    Do you have the text from some example queries? Might help to understand better what you are doing,
    There have been some threads in the past showing disparities between different sources in Oracle.
    There is ASH, wait events and time model for example.
    – Kyle

  12. EB
    April 10th, 2013 at 10:29 | #11

    Hello Kyle I use the following sqls

    SQL> select snap_id,begin_interval_time,end_interval_time from DBA_HIST_SNAPSHOT where to_char(end_interval_time,’DD/MM/YYYY HH24:MI’)=’05/04/2013 15:00′ and instance_number=1;

    SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
    ———- ————————————————————————— —————————————————————————
    55387 05/04/2013 14:00:19,228 05/04/2013 15:00:28,048

    SQL> select snap_id,begin_interval_time,end_interval_time from DBA_HIST_SNAPSHOT where to_char(end_interval_time,’DD/MM/YYYY HH24:MI’)=’05/04/2013 14:00′ and instance_number=1;

    SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
    ———- ————————————————————————— —————————————————————————
    55386 05/04/2013 13:00:46,972 05/04/2013 14:00:19,228

    SQL> SELECT *
    2 FROM DBA_HIST_SYS_TIME_MODEL
    3 WHERE snap_id IN (‘55387’, ‘55386’) and stat_id=3649082374 and instance_number=1
    4 ORDER BY 1 DESC
    5 ;

    SNAP_ID DBID INSTANCE_NUMBER STAT_ID STAT_NAME VALUE
    ———- ———- ————— ———- —————————————————————- ———-
    55387 976214629 1 3649082374 DB time 782147124135
    55386 976214629 1 3649082374 DB time 773479718813

    so delta is 8667405322 microsecond

    SQL> SELECT snap_id,COUNT (*)
    2 FROM DBA_HIST_ACTIVE_SESS_HISTORY
    3 WHERE
    4 sample_time BETWEEN TO_DATE (’05/04/2013 13:59′, ‘DD/MM/YYYY HH24:MI’)
    5 AND TO_DATE (’05/04/2013 15:01′, ‘DD/MM/YYYY HH24:MI’) and session_type’BACKGROUND’ and instance_number=1 group by snap_id;

    SNAP_ID COUNT(*)
    ———- ———-
    55386 32
    55388 21
    55387 1072

  13. Carl
    April 15th, 2013 at 01:15 | #12

    Hi Kyle,

    Congratulations, this web site is very nice. One of the best that I know.

    I read a pdf here in your website about AAS and there, i saw some names of scripts that I have not found, please Could you show me where I download it ?
    scripts: SQL\ASHdumpAAS.sql, SQL\ASHcpuiowaitAS.sql, SQL\ASHmetricsAAS.sql , SQL\AWRsnapAAS.sql, SQL\LittlesLawAAS.sql

    Thank you and congratulations again.

  14. April 17th, 2013 at 18:42 | #13

    @Carl: I believe that was a presentation by John Beresniewicz
    http://ashmasters.com/wp-content/uploads/2008/10/average-active-sessions-rmoug2007.pdf
    that I linked into ashmasters.
    Will ping John to see if he can contribute these to ashmasters on github

  15. April 17th, 2013 at 18:48 | #14

    @EB: why did you filter for ‘BACKGROUND’ in the ASH query?
    One thing to keep in mind is that DBA_HIST_ACTIVE_SESS_HISTORY is 1 in 10 ASH samples so you also have to multiply the result by 10.
    Try taking out the filter on BACKGROUND processes and multiply the result by 10.

  16. Peter
    May 1st, 2013 at 14:36 | #15

    Hello,
    i nice idea and a nice project which show that not all DBA`s needs to buy extra cost option to get out that there session are doing.

  17. May 1st, 2013 at 15:25 | #16

    @Peter: thanks for stopping by. Nice comparison of options for EE and SE tuning on your blog at http://petesdbablog.wordpress.com/2013/05/01/how-to-monitor-oracle-standard-edition-without-option-packs/

  18. Sanju
    May 9th, 2013 at 09:08 | #17

    Hi I am using the ASHMON V2 on Oracle 9i database. I must say its brilliant. There are couple of things which I was not able get working(probably I am not using it the right way). Under the “Functions” menu, I am not able to get any info from ‘waits’, ‘stats’ and the ‘ash init’. Please can you help? Also I tried to use it on a system without Oracle Installed and it doesn’t seem to be working with ashmon/Oracle/network/admin.

  19. May 11th, 2013 at 00:36 | #18

    @Sanju: do you get any of the graphs? If you get the Active Session History graph then the other should work as well. “ash init” just takes you to the active session history graph. The option “stats” takes you to the stats graphs which are all empty at he beginning. You have to click on the boxes at the top. Each box will hide or show stats from that group. The option “waits” should bring up a graph. It’s empty for 10-15 seconds until it starts pulling in data.

You must be logged in to post a comment.