Workload characterization using DBA_HIST tables and kSar 17

Been busy these past few days..

Well, this post is about taking advantage of the built in data store that started in 10gR1.. yeah it’s AWR, and it’s very much like a “Statspack on steroids” (got that from Kyle Hailey’s modules). What’s exciting about 10g and above is we could utilize the “Time Model” and the OSSTAT view and use them together with the SYSTAT view to have a far better workload information when going through all the SNAP_IDs.

I have this “little” issue before of getting lazy when generating multiple AWR reports… yeah it’s just so daunting, you’ll get tired just by generating those reports by hand and the catch is… you’ve not yet analyzed anything yet.. :p   (but yeah, you’re lucky if you’ve got access to the OEM performance page)

So.. I was thinking, if I get to see all the relevant workload info across SNAP_IDs it would be easier for me to notice trends and even possible for me to visualize the data, or even possible to do some statistics out of it.

On the Chapter 9 of Craig Shallahamer’s book (Oracle Performance Firefighting)… there he explained in detail what information you need to get for you to be able to define the Database Server’s Capacity, Requirements, and Utilization (U=R/C)…
I’ve outline some of the points here (see the Firefighting tab)

…. since the AWR report is based on DBA_HIST tables, I was able to make a script that shows the following:

1) CPU capacity – see image below, column 4 to 6
2) CPU requirements – see image below, column 7 to 14
3) Memory requirements – see image below, column 15
4) IO requirements – see image below, column 16 to 21
5) some SYSSTAT delta values – see image 22 to 23
6) Utilization – see image below, the last 6 columns

…. I’ve used the tables below for the query:

- dba_hist_snapshot
- dba_hist_osstat
- dba_hist_sys_time_model
- dba_hist_sysstat

Check out the scripts here:
For Linux/Unix: awr_genwl.sql
For Windows: awr_genwl_win.sql

IMPORTANT NOTE: Diagnostic Pack License is needed for the scripts, I’m updating my scripts frequently.. if the download link is not working here.. check out the Scripts Section for the file name and the most recent version of the script

Now time for some action! :)

As you can see from above.. there is a highlighted portion (in green). By running the script, we have identified that there was a sudden spike on the following:

- “DB Time” (7th column).. which means there is some significant user level activity going on!
- IOPs read (16th column) and IO read MB/s (19th column).. which means that the stresser is possibly more on SELECT operations
- Oracle and OS CPU% utilization (last 6 columns)… which means the spike on activity is attributed by Oracle and not other operations on the OS, also you could see that it is not attributed by an RMAN activity (because it’s zero!)
- Plus the Average Active Sessions (AAS) (11th column) and the OS Load (13th column) increased… which correlates to the high CPU%

The sudden spike happened around 6:20 to 7:01 (SNAP_ID 335-339)

If you are a fan of AAS (Average Active Sessions) see below how I was able to derive the value (example for SNAP_ID 335):

AAS = DB Time/Elapsed Time
= (1871.36/60)/10.06
= 3.100331345

BTW, I only have 1 CPU (see column 5)…

So you have a lot of info (facts, numbers, figures) to justify the drill down on specific SNAP_IDs or time frame. From here you could either use ASH, run the AWR report, run ADDM, or use OEM. The good thing here is, you are not guessing! and you’ve done some quick correlation on the OS and the database before drilling down further…

Now…

Let’s see if we could correlate the output of the script with the SAR data.. from the image below, you can see that the spike on load happened around 6:20 and ended around 7:30. And yes, attributed by wait on IO (see the red color).

BTW, I’m using kSar for visualizing the SAR data.

Also you’ll see the Load Average, which is the same from the output of the AWR script above.

Now, let’s check out the IO devices… the database is using ASM with 3 ASM disks, I would get the major and minor numbers and correlate these with the SAR data…

GROUP_NUMBER NAME		  HEADER_STATU MOUNT_S STATE	  TOTAL_MB    FREE_MB LABEL		   PATH 		REDUNDANCY
------------ -------------------- ------------ ------- -------- ---------- ---------- -------------------- -------------------- --------------------
	   1 DATA_1_0000	  UNKNOWN      OPENED  NORMAL	      3067	    0			   /dev/sdc1		UNKNOWN
	   1 DATA_1_0001	  UNKNOWN      OPENED  NORMAL	      3067	    0			   /dev/sdd1		UNKNOWN
	   1 DATA_1_0002	  UNKNOWN      OPENED  NORMAL	      3067	    0			   /dev/sdf1		UNKNOWN

[oracle@dbrocaix01 osm]$ ls -l /dev/sd[c,d,f]
brw-rw----  1 root disk 8, 32 Jan 17  2010 /dev/sdc
brw-rw----  1 root disk 8, 48 Jan 17  2010 /dev/sdd
brw-rw----  1 root disk 8, 80 Jan 17  2010 /dev/sdf

You can see from the images of the block devices below that they are mainly on Read Activity.. which
validates the output of IOPs read (16th column) and IO read MB/s (19th column) of the AWR script… cool!

Another thing… When you get to run the script on your environment also try to generate AWR report on particular SNAP_IDs… you’ll notice that the values you got from the query will be the same on the AWR report…



Update (20100201):

… I’d like you to see the output of the Enterprise Manager Performance Page on the time when the sudden workload spike happened.

The first and second images above came from the root page, you’ll see the following similarities on the AWR script output:

- Load Average, which is on the range of 3.2 to 4.4
- Average Active Sessions (AAS), which is on the range of 2.2 to 3.5
- Disk IO read MB/s… see that the graph is reaching the 80000 Physical Reads KB/s.. check the SNAP_ID 338 (Time – 6:50) from the AWR script output you’ll see that it generated 73.372 MB/s! Also, see the output of the ADDM run below… which further validates the output of the script:

FINDING 2: 18% impact (236 seconds)
-----------------------------------
The throughput of the I/O subsystem was significantly lower than expected.

   RECOMMENDATION 1: Host Configuration, 18% benefit (236 seconds)
      ACTION: Consider increasing the throughput of the I/O subsystem.
         Oracle's recommended solution is to stripe all data file using the
         SAME methodology. You might also need to increase the number of disks
         for better performance. Alternatively, consider using Oracle's
         Automatic Storage Management solution.
      RATIONALE: During the analysis period, the average data files' I/O
         throughput was 74 M per second for reads and 102 K per second for
         writes. The average response time for single block reads was 13
         milliseconds.

   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: Wait class "User I/O" was consuming significant database time.
               (37% impact [488 seconds])

The third image is the Top Activity section… you can see that most of the activity are SELECTs and the database is waiting on User and System I/O… (see the Top SQLs and Top Sessions)

BTW, this script could also be used on a RAC environment… you have to run it on each node. I’ve made it pull the DBID and instance_number automatically..



Update (20100202):

On Windows environment for the DBA_HIST_OSSTAT view it does not contain the LOAD and IOWAIT_TIME stat_name so I removed these columns from the query. See the sample output below:

That’s all for this post! Now you’ll know better about the workload of your environment :)




17 comments

  1. Pingback: Craig Shallahamer is now blogging! « Karl Arao's Blog

  2. Karl,

    Good work. Now I can take your idea(your script) and merge with PerfSheet that my friend Tanel Poder created and design a nice visual data of my system. You guys are just too good..;)

  3. I did not see mention of a key licensing fact – since you are using the DBA_HIST_ views – you must be licensed for AWR usage. Therefore I think you must have purchased the optional, extra $$$ OEM diagnostics pack and possibly the OEM tuning pack (I did not review the scripts in detail to see all that you used).

    • Hi Bert,

      Thanks for your comment. I agree with you.. Diagnostic Pack License is needed for this script.
      However, I could make use of the counterpart tables in Statspack.. and possibly achieve similar results.

      dba_hist_snapshot = STATS$SNAPSHOT
      dba_hist_osstat = STATS$OSSTAT
      dba_hist_sys_time_model = STATS$SYS_TIME_MODEL
      dba_hist_sysstat = STATS$SYSSTAT

      If I find time, I’ll create the query… :) the same concept applies…

      Also, I’m curious on putting additional info for capturing the Network and Memory requirements on each SNAP_ID

      - For the memory usage.. I’ll put in the sysstat metric “session pga memory”, in that way I’ll have rough estimate on memory requirements for the sessions
      - Then for the Network usage.. I’ll put in “bytes sent via SQL*Net to client” and “bytes sent via SQL*Net to dblink”.. each on separate columns.. in this way I’ll know the network requirements (transfer rate) on specific workloads which will be useful for determining the right network capacity (on the hardware & on wire – bandwidth). Could also be useful on a WAN setup, but I still have to do some tests.

  4. Nice post But I would suggest you rewrite your code
    before you create a P1 issue on a production system .

    I give you some hints :using lag, when, case

    CASE
    WHEN e.stat_name = ‘AVG_IOWAIT_TIME’
    THEN CASE
    WHEN s.begin_interval_time = s.startup_time
    THEN e.VALUE
    ELSE e.VALUE – Lag(e.VALUE)
    OVER(PARTITION BY e.stat_name,e.dbid,e.instance_number,s.startup_time ORDER BY e.snap_id)
    END
    ELSE 0
    END avg_iowait_time,

    ………
    patrick

  5. Pingback: Blogroll Report 29/01/2009 – 05/02/2010 « Coskan’s Approach to Oracle

  6. Pingback: Seeing Exadata in action « Karl Arao's Blog

  7. Pingback: Graphing the AAS with Perfsheet a la Enterprise Manager « Karl Arao's Blog

  8. Pingback: Using Grid to display database CPU usage « Oracle DBA – A lifelong learning experience

  9. Hi Karl
    The script mentioned in this post is no longer available, I knew you have moved all amazing scripts into a new google docs page, but update the URL in this post would be better.

    • Hi Kamus,

      Yes, I’ve had several emails regarding the access of the scripts. I’ll probably move all of them on my DropBox and share the entire folder.

      I’ll let you know once I’m done with it :)

  10. Hi
    please find you nice script , re-written using analytical functions

    define instance_num=”1″
    define start_date=”2010_0428_0009″
    define end_date=”2010_0428_1900″
    define start_hour=”9″
    define end_hour=”19″
    define date_format=”YYYY_MMDD_HH24MI”
    set pagesize 1000
    col dur format 999990.00 heading “Snap|Dur|(m)”
    col id format 99999 heading “Snap|ID”
    col tm format a15 heading “Snap|Start|Time”
    col inst format 90 heading “i|n|s|t|#”
    col cpu format 90 heading “C|P|U”
    col cap format 9999990.00 heading “***|Total|CPU|Time|(s)”
    col dbt format 999999990.00 heading “DB|Time”
    col dbc format 99990.00 heading “DB|CPU”
    col bgc format 99990.00 heading “Bg|CPU”
    col rman format 9990.00 heading “RMAN|CPU”
    col aas format 90.0 heading “A|A|S”
    col totora format 9999990.00 heading “***|Total|Oracle|CPU|(s)”
    col busy format 9999990.00 heading “Busy|Time”
    col load format 990.00 heading “OS|Load”
    col totos format 9999990.00 heading “***|Total|OS|CPU|(s)”
    col mem format 999990.00 heading “Physical|Memory|(mb)”
    col iors format 9990.000 heading “IOPs|r”
    col iows format 9990.000 heading “IOPs|w”
    col ioredo format 9990.000 heading “IOPs|redo”
    col iormbs format 9990.000 heading “IO r|(mb)/s”
    col iowmbs format 9990.000 heading “IO w|(mb)/s”
    col redosizesec format 999990.000 heading “Redo|(mb)/s”
    col logons format 99990 heading “Sess”
    col logone format 990 heading “Sess|End”
    col exsraw format 99990.000 heading “Exec|raw|delta”
    col exs format 999990.000 heading “Exec|/s”
    col oracpupct format 990 heading “Oracle|CPU|%”
    col rmancpupct format 990 heading “RMAN|CPU|%”
    col oscpupct format 990 heading “OS|CPU|%”
    col oscpuusr format 990 heading “U|S|R|%”
    col oscpusys format 990 heading “S|Y|S|%”
    col oscpuio format 990 heading “I|O|%”
    WITH sub_snap
    AS (SELECT dbid,
    instance_number,
    snap_id,
    fin end_interval_time,
    Round(Extract(DAY FROM fin – debut) * 1440 + Extract(HOUR FROM fin – debut) * 60 + Extract(MINUTE FROM fin – debut) + Extract(SECOND FROM fin – debut) / 60, 2) dur
    FROM (SELECT dbid,
    instance_number,
    snap_id,
    end_interval_time fin,
    ( Lag(end_interval_time, 1) over (PARTITION BY dbid, instance_number ORDER BY snap_id) ) debut
    FROM dba_hist_snapshot
    WHERE instance_number = &instance_num
    AND begin_interval_time >= To_date(‘&start_date’, ‘&date_format’)
    AND end_interval_time = 7
    AND To_number(To_char(end_interval_time, ‘HH24′)) <= 19
    AND To_number(To_char(begin_interval_time, 'D', 'NLS_DATE_LANGUAGE=AMERICAN')) < 6)),
    sub_sys_time_model
    AS (SELECT dbid,
    snap_id,
    instance_number,
    SUM(db_time) db_time,
    SUM(db_cpu) db_cpu,
    SUM(bg_cpu) bg_cpu,
    SUM(rm_cpu) rm_cpu
    FROM (SELECT dbid,
    snap_id,
    instance_number,
    CASE
    WHEN stat_name = 'DB time' THEN CASE
    WHEN begin_interval_time = startup_time THEN VALUE
    ELSE VALUE – Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
    END
    ELSE NULL
    END db_time,
    CASE
    WHEN stat_name = 'DB CPU' THEN CASE
    WHEN begin_interval_time = startup_time THEN VALUE
    ELSE VALUE – Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
    END
    ELSE NULL
    END db_cpu,
    CASE
    WHEN stat_name = 'background cpu time' THEN CASE
    WHEN begin_interval_time = startup_time THEN VALUE
    ELSE VALUE – Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
    END
    ELSE NULL
    END bg_cpu,
    CASE
    WHEN stat_name = 'RMAN cpu time (backup/restore)' THEN CASE
    WHEN begin_interval_time = startup_time THEN VALUE
    ELSE VALUE – Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
    END
    ELSE NULL
    END rm_cpu
    FROM dba_hist_snapshot
    natural join dba_hist_sys_time_model
    WHERE stat_name IN ( 'DB time', 'DB CPU', 'background cpu time', 'RMAN cpu time (backup/restore)' ))
    GROUP BY dbid,
    snap_id,
    instance_number),
    sub_sys_stat
    AS (SELECT dbid,
    snap_id,
    instance_number,
    MAX(logons_current) logons_current,
    SUM(execute_count) execute_count,
    SUM(physical_reads) physical_reads,
    SUM(physical_writes) physical_writes,
    SUM(redo_writes) redo_writes,
    SUM(redo_size) redo_size,
    SUM(physical_read_io_requests) physical_read_io_requests,
    SUM(physical_write_io_requests) physical_write_io_requests
    FROM (SELECT dbid,
    snap_id,
    instance_number,
    CASE
    WHEN stat_name = 'logons current' THEN VALUE
    ELSE NULL
    END logons_current,
    CASE
    WHEN stat_name = 'execute count' THEN CASE
    WHEN begin_interval_time = startup_time THEN VALUE
    ELSE VALUE – Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
    END
    ELSE NULL
    END execute_count,
    CASE
    WHEN stat_name = 'physical reads' THEN CASE
    WHEN begin_interval_time = startup_time THEN VALUE
    ELSE VALUE – Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
    END
    ELSE NULL
    END physical_reads,
    CASE
    WHEN stat_name = 'physical writes' THEN CASE
    WHEN begin_interval_time = startup_time THEN VALUE
    ELSE VALUE – Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
    END
    ELSE NULL
    END physical_writes,
    CASE
    WHEN stat_name = 'redo writes' THEN CASE
    WHEN begin_interval_time = startup_time THEN VALUE
    ELSE VALUE – Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
    END
    ELSE NULL
    END redo_writes,
    CASE
    WHEN stat_name = 'redo size' THEN CASE
    WHEN begin_interval_time = startup_time THEN VALUE
    ELSE VALUE – Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
    END
    ELSE NULL
    END redo_size,
    CASE
    WHEN stat_name = 'physical read IO requests' THEN CASE
    WHEN begin_interval_time = startup_time THEN VALUE
    ELSE VALUE – Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
    END
    ELSE NULL
    END physical_read_io_requests,
    CASE
    WHEN stat_name = 'physical write IO requests' THEN CASE
    WHEN begin_interval_time = startup_time THEN VALUE
    ELSE VALUE – Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
    END
    ELSE NULL
    END physical_write_io_requests
    FROM dba_hist_snapshot
    natural join dba_hist_sysstat
    WHERE stat_name IN ( 'logons current', 'execute count', 'physical reads', 'physical writes',
    'redo writes', 'redo size', 'physical read IO requests', 'physical write IO requests' ))
    GROUP BY dbid,
    snap_id,
    instance_number),
    sub_osstat
    AS (SELECT dbid,
    snap_id,
    instance_number,
    MAX(load) load,
    SUM(busy_time) busy_time,
    SUM(user_time) user_time,
    SUM(sys_time) sys_time,
    SUM(io_wait_time) io_wait_time,
    MAX(cpu) cpu,
    MAX(phy_mem) phy_mem,
    SUM(avg_sys_time),
    SUM(avg_busy_time),
    SUM(avg_iowait_time),
    SUM(avg_idle_time),
    SUM(avg_user_time)
    FROM (SELECT e.snap_id,
    e.dbid,
    e.instance_number,
    CASE
    WHEN e.stat_name = 'LOAD' THEN e.VALUE
    ELSE 0
    END load,
    CASE
    WHEN e.stat_name = 'NUM_CPUS' THEN e.VALUE
    ELSE 0
    END cpu,
    CASE
    WHEN e.stat_name = 'AVG_BUSY_TIME' THEN CASE
    WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
    ELSE e.VALUE – Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
    END
    ELSE 0
    END avg_busy_time,
    CASE
    WHEN e.stat_name = 'AVG_SYS_TIME' THEN CASE
    WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
    ELSE e.VALUE – Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
    END
    ELSE 0
    END avg_sys_time,
    CASE
    WHEN e.stat_name = 'AVG_USER_TIME' THEN CASE
    WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
    ELSE e.VALUE – Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
    END
    ELSE 0
    END avg_user_time,
    CASE
    WHEN e.stat_name = 'AVG_IOWAIT_TIME' THEN CASE
    WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
    ELSE e.VALUE – Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
    END
    ELSE 0
    END avg_iowait_time,
    CASE
    WHEN e.stat_name = 'AVG_IDLE_TIME' THEN CASE
    WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
    ELSE e.VALUE – Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
    END
    ELSE 0
    END avg_idle_time,
    CASE
    WHEN e.stat_name = 'BUSY_TIME' THEN CASE
    WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
    ELSE e.VALUE – Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
    END
    ELSE 0
    END busy_time,
    CASE
    WHEN e.stat_name = 'USER_TIME' THEN CASE
    WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
    ELSE e.VALUE – Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
    END
    ELSE 0
    END user_time,
    CASE
    WHEN e.stat_name = 'SYSR_TIME' THEN CASE
    WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
    ELSE e.VALUE – Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
    END
    ELSE 0
    END sys_time,
    CASE
    WHEN e.stat_name = 'IOWAIT_TIME' THEN CASE
    WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
    ELSE e.VALUE – Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
    END
    ELSE 0
    END io_wait_time,
    CASE
    WHEN e.stat_name = 'PHYSICAL_MEMORY_BYTES' THEN e.VALUE
    ELSE 0
    END phy_mem
    FROM dba_hist_osstat e,
    dba_hist_snapshot s
    WHERE s.snap_id = e.snap_id
    AND e.instance_number = s.instance_number
    AND e.dbid = s.dbid
    AND e.stat_name IN ( 'LOAD', 'AVG_BUSY_TIME', 'AVG_IOWAIT_TIME', 'AVG_USER_TIME',
    'AVG_SYS_TIME', 'AVG_IDLE_TIME', 'NUM_CPUS', 'BUSY_TIME',
    'PHYSICAL_MEMORY_BYTES', 'USER_TIME', 'SYS_TIME', 'IOWAIT_TIME' ))
    GROUP BY dbid,
    snap_id,
    instance_number)
    SELECT snap_id id,
    To_char(end_interval_time, 'YYYYMMDD HH24MI') tm,
    instance_number inst,
    dur,
    cpu,
    ( dur * cpu * 60 ) cap,
    ( db_time / 1000000 ) dbt,
    ( db_cpu / 1000000 ) dbc,
    ( bg_cpu / 1000000 ) bgc,
    ( rm_cpu / 1000000 ) rman,
    ( db_time / 1000000 / 60 ) / dur aas,
    Round(bg_cpu / 1000000 + db_cpu / 1000000, 2) totora,
    Round(load, 2) AS load,
    ( busy_time ) / 100 AS totos,
    ( phy_mem / 1024 / 1024 ) AS mem,
    physical_reads / ( dur * 60 ) AS iors,
    physical_writes / ( dur * 60 ) AS iows,
    redo_writes / ( dur * 60 ) AS ioredo,
    physical_read_io_requests / ( dur * 60 ) AS iormbs,
    — physical_write_IO_requests/(dur*60) as IOWmbs,
    — redo_size/(dur*60) as redosizesec,
    — logons_current as logons,
    — execute_count as exs,
    ( ( Round(( ( db_cpu ) / 1000000 ) + ( ( bg_cpu ) / 1000000 ), 2) ) / ( dur * 60 * cpu ) ) * 100 AS oracpupct,
    ( ( Round(Decode(rm_cpu, NULL, 'null',
    rm_cpu / 1000000), 2) ) / ( dur * 60 * cpu ) ) * 100 AS rmancpupct,
    ( ( ( busy_time ) / 100 ) / ( dur * 60 * cpu ) ) * 100 AS oscpupct,
    ( ( user_time / 100 ) / ( dur * 60 * cpu ) ) * 100 AS oscpuusr,
    ( ( ( sys_time ) / 100 ) / ( dur * 60 * cpu ) ) * 100 AS oscpusys,
    ( ( ( io_wait_time ) / 100 ) / ( dur * cpu * 60 ) ) * 100 AS oscpuio
    FROM sub_snap
    natural join sub_sys_time_model
    natural join sub_sys_stat
    natural join sub_osstat
    ORDER BY id ASC
    /

    • I don’t completely agree with this one.. I’ve done some intensive testing and comparing the different approach on doing this but when all the columns of awr_genwl are already added, the snap_id + 1 approach wins ;)
      The test case could be downloaded at http://karlarao.wordpress.com/scripts-resources/ on the zip file HOTSOS2011 – Karl Arao – Mining the AWR Repository.zip\HOTSOS2011 – Karl Arao – Mining the AWR Repository\hotsos2011_files\Visualization\slide23 – SQLTrickAndLAGComparison

      You’ll see the comparison of the following:
      - WITH
      - WITH and LAG
      - and the snap_id + 1 approach

      Also I’ve presented this topic at Hotsos and there are also presenters doing mining on AWR and we are aware of different things to do it and kind of agree that “it depends”. So in my case, the way I pull the data from various tables the snap_id + 1 approach is the winner.. and I have evidence (test case) to prove it.

      -Karl

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s