Level 1: Overview

These queries on ASH give an overview of the load on the database over time.

———————————————————————-

Without an overview of activity over time like in OEM 10g’s performance page, it is difficult to get a feel for what database performance looks like. The  following two scripts are attempts to provide similar informantion via SQL plus.

Explanation of Average Activity Scripts (PPT) 

Average Activity - query output the output column “graph” that shows a similar graph to OEM’s performance page. The “graph” column is composed of “+” and “-” signs. The “+” sign signifies average active sessions (AAS) on CPU and the “-” is sessions (AAS) waiting. The averages are done by default for each hour, but this could be changed to any time width.

 Average Activity with top 2 waits  - query output , same as aveact.sql but in addition it shows the top two waits for each time bucket

———————————————————————-

4 Responses to “Level 1: Overview”  

Feed for this Entry Trackback Address
  1. 1 Anthony Jul 14th, 2008 at 6:05 pm

    Just curious as to why you are counting occurrences of events/CPU instead of using wait_time/time_waited values in ASH.

  2. 2 khailey Jul 14th, 2008 at 6:52 pm

    Anthony, I think your question is “why am I graphing an events and cpu instead of wait time?” wait_time is just a proxy for whether we are waiting or on CPU. THe time_waited actually doesn’t tell us much about system load in ASH data since ASH is sampled we generally get long waits and miss short waits so the data is skewed. On the other hand the sampling of the number of active sessions is a great statistical approximation of load on the system and looking at how much o fhte load is waiting verses working (on CPU) gives us even more information.

  3. 3 pioro Oct 20th, 2009 at 1:19 pm

    Hi Kyle,

    I’m wondering if you still maintain that blog but I have a question about aveact.sql file linked above. In your script you are calculating waits using following formula (for both - online and historical)

    sum(decode(session_type,’BACKGROUND’,0,decode(session_state,’WAITING’,10,0))) waits

    but in results (linked above too) you have a strange values in wait column

    @aveact
    TM NPTS AVEACT GRAPH CPU WAITS
    —————- —— ——- ———————- —- —–
    06-AUG 13:00:00 270 .33 - 2 29 59
    06-AUG 14:00:00 1040 2.24 ——–2— 341 1984
    06-AUG 15:00:00 623 6.67 ——2———- 438 3718
    06-AUG 16:00:00 1088 2.59 ——–2—- 335 2486

    How is it possible that sum is 59 when we use 10s as a basic value ?
    I assume it should be 1 for both - CPU and wait - and there is a bug inside that script.

    I’m wondering why you using 1’s for ash and 10’s for historical ash ?

    This is very useful tool - Thanks for that

    regards,
    Marcin

  1. 1 Doug's Oracle Blog Pingback on Dec 10th, 2008 at 5:01 am

Leave a Reply

You must log in to post a comment.