Scripts & Resources 24

Click on this link to be redirected to the Scripts section. Some of my favorite tools I’ve created are the following:

AWR Mining

    • r2toolkit – a regression analysis tool for Oracle workload performance updated 20110709
    • AWR Tableau Toolkit – create your own performance data warehouse and easily characterize the workload, CPU, IO of the entire cluster (30 instances) with months of perf data in less than 1 hour updated 20120912
      • I no longer update this toolkit. This served as a version 1 of a more comprehensive tool called eAdam which I started with Carlos Sierra (the main developer), Frits Hoogland, and Randy Johnson at Enkitec.
    • run_awr-quickextract - set of scripts used for Performance Tuning, Capacity Planning and Sizing updated 20140624
      • This doesn’t create a warehouse, all you need is to consolidate the CSV files and you’re good to go. I use this a lot for performance firefighting or one time sizing engagements.

CPU

    • cputoolkit – a simple toolkit that allows you to control the saturation of specific number of CPUs (AAS CPU) updated 20130901
      • I made use of this toolkit for my Cores vs Threads investigation, data points and plots are available here

Storage

    • oriontoolkit – an automated Orion and collectl runs for OLTP and DSS updated 20110916
    • IOsaturationtoolkit-v2 - a benchmark tool to validate the database IO performance from the Orion and Calibrate IO runs
      • updated 20120511: the version 2 now contains a section for doing IORM test cases and some cool smart scan monitoring scripts
    • cell_iops.sh - a “sort of” end to end Exadata IO monitoring script, see the script for README

SQL

    • runbenchtoolkit - a simple tookit to benchmark your new SQLs updated 20120702
    • plan_change_toolkit - a tool that looks for big variations in elapsed times across multiple databases (which could be brought by plan change, workload growth, and optimizer changes) and sends a summary report for every occurrence updated 20121121

————————————————————————————————————————

Investigations and WhitePapers

.

24 comments

  1. i have a questions about oracle metrics[spreadsheet] note 5. about physical read. The interval sec must be the elapsed time otherwise which interval do you have in mind?

    following your example i have a query (collected by AWR) for a 4k block database with the following metrics

    Physical Reads: 3,228,634
    Executions: 4
    Read per Exec: 807,158.5
    CPU time (secs)75.87
    Elapsed time (sec) 131.59

    So, the throughput per sec is calculated as follows

    (3228634 / 131.59) = 24479.75
    ((24479.75 * 4096)/ 1024) / 1024 = 95.62 MB per sec

    my best wishes for happy Easter

    • Hi Kostas,

      Sorry I just replied now, I was on a 6 day holiday trip (Bangkok & Cambodia) :)

      How have you been? where are you based now?

      About your question…
      Yes you are correct, this is the metric “Elapsed Time” (above the DB Time in AWR) which is usually in minutes.
      So let’s say you have 131.59 sec snap interval (equivalent to 2.19 min), if this is automated snaps this is very frequent..
      The normal 1hour snap interval is enough to notice workload trends over a long period of time, I just set 10mins interval on my blog post
      just to show how could the awr_genwl.sql script be useful to notice trends because I’m running a controlled benchmark workload for just a short period.

      And also your computations are correct:

      To get the Physical Reads/sec
      3,228,634 Physical Read sysstat raw value / 131.59 elapsed seconds
      = 24535.557413177 Physical Reads/sec

      To get the MB/sec
      (24535.557413177 Physical Reads/sec * 4096 block size) / 1024 / 1024
      = 95.842021145 MB Read/sec

      So the metric 95.842021145 MB Read/sec should be added to the MB Write/sec & MB Redo/sec to get the Total MB throughput/sec
      Apparently, if the 95.842021145 MB Read/sec is attributed by a long running data warehouse select, that’s 345GB/h :)

  2. Pingback: Workload characterization using DBA_HIST tables and kSar « Karl Arao's Blog

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

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

  5. Good, solid script- I like the result sets that show me “just the facts” info. I had to tweak a few columns to get my silly databases results to show, (oh please don’t ask… :)) but it is the kind of data you can easily turn into a pretty picture managers love to see!
    Thanks Karl!

    • Hi Kellyn,
      Glad you liked it! yes that’s one script followed by another using the same concept (time series data as you see per snap_id) but added some more columns (some are already derived) to be more useful and you could easily see the textual trends. what more if you put that into pictures! btw, there’s still a lot of dba_hist views to explore & I’m working on it :)

  6. Karl,

    Below is a version of your awr_genwl.sql script that I modified to utilize the “lag” syntax. This is particularly helpful on heavily loaded systems that have a tendency to fail to complete snapshots and simply adding 1 to the previous snap_id doesn’t necessarily yield a valid snap_id. Thanks for all of your excellent info and insights.

    ————————————————————————
    set echo off verify off pagesize 50000 linesize 250 trimsp on

    – adjust as needed for preferred range of snapshots:
    COL begin_snap_in NEW_VALUE BEGIN_SNAP NOPRINT
    COL end_snap_in NEW_VALUE END_SNAP NOPRINT
    select min(snap_id) begin_snap_in, max(snap_id) end_snap_in from dba_hist_snapshot;

    COL dbid_in NEW_VALUE DBID NOPRINT
    select dbid dbid_in from v$database;

    COL instance_number_in NEW_VALUE INSTANCE_NUMBER NOPRINT
    select instance_number instance_number_in from v$instance;

    COL blocksize_in NEW_VALUE BLOCKSIZE NOPRINT
    select block_size blocksize_in from (
    select block_size,count(*) from v$datafile group by block_size order by 2 desc
    ) where rownum=1;

    ttitle center ‘AWR CPU and IO Workload Report’ skip 2

    col id format 99999 heading “Snap|ID”
    col tm format a16 heading “Snap|Start Time”
    col dur format 990.00 heading “Snap|Dur|(m)”
    col mem format 9,999,990 heading “Physical|Memory|(megs)”
    col cpu format 90 heading “C|P|U”
    col load format 990.00 heading “OS|Load|Ave”
    col cap format 999990.00 heading “***|Avail|OS|CPU|(s)”
    col busy format 999990.00 heading “Busy|Time”
    col totos format 9990.00 heading “***|Busy|OS|CPU|(s)”
    col dbt format 999990.00 heading “Ora|DB|Time|(s)”
    col dbc format 99990.00 heading “Ora|DB|CPU|(s)”
    col bgc format 99990.00 heading “Ora|Bg|CPU|(s)”
    col rman format 9990.00 heading “Ora|RMAN|CPU|(s)”
    col totora format 9990.00 heading “***|Total|Oracle|CPU|(s)”
    col IORs format 9990.000 heading “IOPs|read”
    col IOWs format 9990.000 heading “IOPs|write”
    col IORedo format 9990.000 heading “IOPs|redo”
    col IORmbs format 9990.000 heading “IO read|(mb)/s”
    col IOWmbs format 9990.000 heading “IO wrt|(mb)/s”
    col redosizesec format 9990.000 heading “Redo|(mb)/s”
    col logons format 990 heading “Sess|Start”
    col logone format 990 heading “Sess|End”
    col exsraw format 99990.000 heading “Exec|raw|delta”
    col exs format 990.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 “OS|USR|%”
    col oscpusys format 990 heading “OS|SYS|%”
    col oscpuio format 990 heading “OS|IO|Wait|%”

    SELECT nvl((lag(s1.snap_id) over(order by s1.snap_id)),1) as id,
    TO_CHAR(s1.END_INTERVAL_TIME,’YYYY/MM/DD HH24:MI’) as tm,
    ( EXTRACT(DAY FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *1440*60
    + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *3600
    + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *60
    + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id))
    ) / 60 as dur,
    s4t1.value/1024/1024 as mem,
    s3t1.value as cpu,
    round(s2t1.value,2) as load,
    ( EXTRACT(DAY FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *1440*60
    + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *3600
    + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *60
    + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id))
    )*s3t1.value as cap,
    –s1t1.value – lag(s1t1.value) over(order by s1.snap_id) as busy,
    (s1t1.value – lag(s1t1.value) over(order by s1.snap_id)) / 100 as totos,
    round(((s6t1.value – lag(s6t1.value) over(order by s1.snap_id)) / 1000000) + ((s7t1.value – lag(s7t1.value) over(order by s1.snap_id)) / 1000000),2) as totora,
    (s6t1.value – lag(s6t1.value) over(order by s1.snap_id)) / 1000000 as dbc,
    (s7t1.value – lag(s7t1.value) over(order by s1.snap_id)) / 1000000 – round(DECODE(s8t1.value,null,’null’,(s8t1.value – lag(s8t1.value) over(order by s1.snap_id)) / 1000000),2) as bgc,
    round(DECODE(s8t1.value,null,’null’,(s8t1.value – lag(s8t1.value) over(order by s1.snap_id)) / 1000000),2) as rman,
    (s5t1.value – lag(s5t1.value) over(order by s1.snap_id)) / 1000000 as dbt,
    ((s15t1.value – lag(s15t1.value) over(order by s1.snap_id)) /
    ( EXTRACT(DAY FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *1440*60
    + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *3600
    + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *60
    + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) )
    ) as IORs,
    ((s16t1.value – lag(s16t1.value) over(order by s1.snap_id)) /
    ( EXTRACT(DAY FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *1440 *60
    + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *3600
    + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *60
    + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) )
    ) as IOWs,
    ((s13t1.value – lag(s13t1.value) over(order by s1.snap_id)) /
    ( EXTRACT(DAY FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *1440*60
    + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *3600
    + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *60
    + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) )
    ) as IORedo,
    ((((s11t1.value – lag(s11t1.value) over(order by s1.snap_id))* &&BLOCKSIZE)/1024/1024) /
    ( EXTRACT(DAY FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *1440*60
    + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *3600
    + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *60
    + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) )
    ) as IORmbs,
    ((((s12t1.value – lag(s12t1.value) over(order by s1.snap_id))* &&BLOCKSIZE)/1024/1024) /
    ( EXTRACT(DAY FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *1440 *60
    + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *3600
    + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *60
    + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) )
    ) as IOWmbs,
    (((s14t1.value – lag(s14t1.value) over(order by s1.snap_id))/1024/1024) /
    ( EXTRACT(DAY FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *1440*60
    + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *3600
    + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *60
    + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) )
    ) as redosizesec,
    ((s10t1.value – lag(s10t1.value) over(order by s1.snap_id)) /
    ( EXTRACT(DAY FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *1440*60
    + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *3600
    + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *60
    + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) )
    ) as exs,
    lag(s9t1.value) over(order by s1.snap_id) as logons,
    — s9t1.value as logone,
    (round(((s6t1.value – lag(s6t1.value) over(order by s1.snap_id)) / 1000000) + ((s7t1.value – lag(s7t1.value) over(order by s1.snap_id)) / 1000000),2) /
    (( EXTRACT(DAY FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *1440*60
    + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *3600
    + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *60
    + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) ) *s3t1.value)
    )*100 as oracpupct,
    (round(DECODE(s8t1.value,null,’null’,(s8t1.value – lag(s8t1.value) over(order by s1.snap_id)) / 1000000),2) /
    (( EXTRACT(DAY FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *1440*60
    + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *3600
    + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *60
    + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) ) *s3t1.value)
    )*100 as rmancpupct,
    (((s1t1.value – lag(s1t1.value) over(order by s1.snap_id))/100) /
    (( EXTRACT(DAY FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *1440*60
    + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *3600
    + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *60
    + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) ) *s3t1.value)
    )*100 as oscpupct,
    (((s17t1.value – lag(s17t1.value) over(order by s1.snap_id))/100) /
    (( EXTRACT(DAY FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *1440*60
    + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *3600
    + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *60
    + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) ) *s3t1.value)
    )*100 as oscpuusr,
    (((s18t1.value – lag(s18t1.value) over(order by s1.snap_id))/100) /
    (( EXTRACT(DAY FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *1440*60
    + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *3600
    + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *60
    + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) ) *s3t1.value)
    )*100 as oscpusys,
    (((s19t1.value – lag(s19t1.value) over(order by s1.snap_id))/100) /
    (( EXTRACT(DAY FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *1440*60
    + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *3600
    + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) *60
    + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME – lag(s1.END_INTERVAL_TIME) over(order by s1.snap_id)) ) *s3t1.value)
    )*100 as oscpuio
    FROM
    dba_hist_snapshot s1,
    dba_hist_osstat s1t1, — busy_time
    dba_hist_osstat s2t1, — load_ave, just get the end value
    dba_hist_osstat s3t1, — num_cpus, just get the end value
    dba_hist_osstat s4t1, — physical_memory, just get the end value
    dba_hist_sys_time_model s5t1, — db time
    dba_hist_sys_time_model s6t1, — db cpu
    dba_hist_sys_time_model s7t1, — background cpu
    dba_hist_sys_time_model s8t1, — RMAN cpu
    dba_hist_sysstat s9t1, — logons current, sysstat absolute value should not be diffed
    dba_hist_sysstat s10t1, — execute count, diffed
    dba_hist_sysstat s11t1, — physical reads, diffed
    dba_hist_sysstat s12t1, — physical writes, diffed
    dba_hist_sysstat s13t1, — redo writes, diffed
    dba_hist_sysstat s14t1, — redo size, diffed
    dba_hist_sysstat s15t1, — physical read IO requests, diffed
    dba_hist_sysstat s16t1, — physical write IO requests, diffed
    dba_hist_osstat s17t1, — user time
    dba_hist_osstat s18t1, — sys time
    dba_hist_osstat s19t1 — iowait time
    WHERE s1.dbid = &&DBID
    AND s1t1.dbid = &&DBID
    AND s2t1.dbid = &&DBID
    AND s3t1.dbid = &&DBID
    AND s4t1.dbid = &&DBID
    AND s5t1.dbid = &&DBID
    AND s6t1.dbid = &&DBID
    AND s7t1.dbid = &&DBID
    AND s8t1.dbid = &&DBID
    AND s9t1.dbid = &&DBID
    AND s10t1.dbid = &&DBID
    AND s11t1.dbid = &&DBID
    AND s12t1.dbid = &&DBID
    AND s13t1.dbid = &&DBID
    AND s14t1.dbid = &&DBID
    AND s15t1.dbid = &&DBID
    AND s16t1.dbid = &&DBID
    AND s17t1.dbid = &&DBID
    AND s18t1.dbid = &&DBID
    AND s19t1.dbid = &&DBID
    AND s1.instance_number = &&INSTANCE_NUMBER
    AND s1t1.instance_number = &&INSTANCE_NUMBER
    AND s2t1.instance_number = &&INSTANCE_NUMBER
    AND s3t1.instance_number = &&INSTANCE_NUMBER
    AND s4t1.instance_number = &&INSTANCE_NUMBER
    AND s5t1.instance_number = &&INSTANCE_NUMBER
    AND s6t1.instance_number = &&INSTANCE_NUMBER
    AND s7t1.instance_number = &&INSTANCE_NUMBER
    AND s8t1.instance_number = &&INSTANCE_NUMBER
    AND s9t1.instance_number = &&INSTANCE_NUMBER
    AND s10t1.instance_number = &&INSTANCE_NUMBER
    AND s11t1.instance_number = &&INSTANCE_NUMBER
    AND s12t1.instance_number = &&INSTANCE_NUMBER
    AND s13t1.instance_number = &&INSTANCE_NUMBER
    AND s14t1.instance_number = &&INSTANCE_NUMBER
    AND s15t1.instance_number = &&INSTANCE_NUMBER
    AND s16t1.instance_number = &&INSTANCE_NUMBER
    AND s17t1.instance_number = &&INSTANCE_NUMBER
    AND s18t1.instance_number = &&INSTANCE_NUMBER
    AND s19t1.instance_number = &&INSTANCE_NUMBER
    AND s1.snap_id >= &&BEGIN_SNAP
    AND s1.snap_id <= &&END_SNAP
    AND s1t1.snap_id = s1.snap_id
    AND s2t1.snap_id = s1.snap_id
    AND s3t1.snap_id = s1.snap_id
    AND s4t1.snap_id = s1.snap_id
    AND s5t1.snap_id = s1.snap_id
    AND s6t1.snap_id = s1.snap_id
    AND s7t1.snap_id = s1.snap_id
    AND s8t1.snap_id = s1.snap_id
    AND s9t1.snap_id = s1.snap_id
    AND s10t1.snap_id = s1.snap_id
    AND s11t1.snap_id = s1.snap_id
    AND s12t1.snap_id = s1.snap_id
    AND s13t1.snap_id = s1.snap_id
    AND s14t1.snap_id = s1.snap_id
    AND s15t1.snap_id = s1.snap_id
    AND s16t1.snap_id = s1.snap_id
    AND s17t1.snap_id = s1.snap_id
    AND s18t1.snap_id = s1.snap_id
    AND s19t1.snap_id = s1.snap_id
    AND s1t1.stat_name = 'BUSY_TIME'
    AND s2t1.stat_name = 'LOAD'
    AND s3t1.stat_name = 'NUM_CPUS'
    AND s4t1.stat_name = 'PHYSICAL_MEMORY_BYTES'
    AND s5t1.stat_name = 'DB time'
    AND s6t1.stat_name = 'DB CPU'
    AND s7t1.stat_name = 'background cpu time'
    AND s8t1.stat_name = 'RMAN cpu time (backup/restore)'
    AND s9t1.stat_name = 'logons current'
    AND s10t1.stat_name = 'execute count'
    AND s11t1.stat_name = 'physical reads'
    AND s12t1.stat_name = 'physical writes'
    AND s13t1.stat_name = 'redo writes'
    AND s14t1.stat_name = 'redo size'
    AND s15t1.stat_name = 'physical read IO requests'
    AND s16t1.stat_name = 'physical write IO requests'
    AND s17t1.stat_name = 'USER_TIME'
    AND s18t1.stat_name = 'SYS_TIME'
    AND s19t1.stat_name = 'IOWAIT_TIME'
    ORDER BY id DESC;

    • Hi Troy,

      Cool! ;) I’m also currently working on the LAG function, I tested it against the old script and this one is faster. I noticed you re-arranged some of the columns so I suppose you already know the simple math behind the scripts (rates,time,IOPS,CPU,latency,utilization,AAS).. if not I suggest you play further with the data & tables and you’ll appreciate that you have a better understanding on how the data are derived on the plain AWR report.

      Thanks for sharing this to me.. I’m glad you find the script very useful.

      • BTW, I have to push back on my initial comment about your modified script.. you were just using a subset of the columns I’m using on the original awr_genwl.sql.. but when all the columns of awr_genwl are already added using your approach, the snap_id + 1 (my way) approach wins ;)

        I’ve done some intensive testing and comparing the different approach on doing this. 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

  7. Pingback: VirtaThon – Mining the AWR « Karl Arao's Blog

  8. Hi Karl, awr_genwl script is great addition to our library of scirpts. Wondering if you have equivalent of this to report from perstat. As we have databases that don’t have diag pack licensed.

    Thanks.

    • Hi Steven,

      Yes I think I had a working version of that before and suddenly I got busy with the DBA_HIST views.. but creating a statspack version will still use the logic and formulas of awr_genwl only that statspack has slightly different columns which lead me to join it to additional tables to derive some of the values making it perform poorly.. but I already have an idea to get around it I just don’t have the time to work on it :) I will let you know once I’m done with it! Glad to know awr_genwl is useful :)

  9. Karl,

    Excellent script. I have used for one of performance issues comparision workload for given databases and its rocked ….

    Thanks for the good work.. saves a lot of time.

    Keep going…

    Surya

  10. Hi karlarao,

    When we generate AWR report in oracle 10g then there is sections called SQL Statistics and under which following sub sections are there:

    SQL ordered by Elapsed Time
    SQL ordered by CPU Time
    SQL ordered by Gets
    SQL ordered by Reads
    SQL ordered by Executions
    SQL ordered by Parse Calls
    SQL ordered by Sharable Memory
    SQL ordered by Version Count
    Complete List of SQL Text

    I can not generate AWR report as per license issue also Statspack is free but it is not giving enough information.

    Is there any SQL script which can present similar data from statspack tables as per AWR.

    Please provide such oracle script.

    Thanking You

    • Yes there should be corresponding stats$ views where you can pull the same info/columns as the AWR views. I have a statspack version of awr_genwl here http://goo.gl/XvMIF
      but it’s not yet optimized. That was my 1st working version back then.

      -Karl

  11. Karlarao,

    I was trying to download your scripts, which says not availlable… can you please help me to download them

    Thanks,
    Raghav

  12. Hello,
    Thanks for your scripts and explanations.
    I would like to ask a question. I have a question regarding snap intervals timing.
    I’ll show with a simple example:

    (1) From AWR I get that snap_id (6784) starts at 10:00:35

    SQL> select output from table(dbms_workload_repository.AWR_REPORT_TEXT(2461100887,1,6784,6785));
    WORKLOAD REPOSITORY report for

    DB Name DB Id Instance Inst Num Startup Time Release RAC
    ————- ——————- ———— ———— ——————– ———– —–
    ECCDV3 2461100887 eccdv3 1 22-Dec-11 23:11 11.2.0.2.0 NO

    Host Name Platform CPUs Cores Sockets Memory(GB)
    ————————– ————————————— ——- ——— ———— —————-
    VECC-ORACLE2-DE Microsoft Windows x86 64-bit 8 8 8 10.00

    Snap Id Snap Time Sessions Curs/Sess
    ——— ————————- ———- ————–
    Begin Snap: 6784 02-Feb-12 10:00:35 331 5.6
    End Snap: 6785 02-Feb-12 11:00:25 349 4.2
    Elapsed: 59.83 (mins)
    DB Time: 70.59 (mins)

    (2) But if I check snaps timing intervals from dba_hist_active_sess_history, then I get that snap_id 6784 starts at 09:00.

    select SNAP_ID, min(SAMPLE_TIME) as mins, max(SAMPLE_TIME) as maxs
    from dba_hist_active_sess_history h
    where h.sample_time BETWEEN TO_DATE(’20120202070000′,’YYYYMMDDHH24MISS’) AND TO_DATE(’20120202140000′,’YYYYMMDDHH24MISS’)
    group by snap_id
    order by 1,2
    /
    SNAP_ID MINS MAXS
    —————- ———————————— ———————————–
    6782 02-FEB-12 07.00.26.797 AM 02-FEB-12 08.00.07.092 AM
    6783 02-FEB-12 08.00.27.092 AM 02-FEB-12 09.00.17.436 AM
    6784 02-FEB-12 09.00.47.484 AM 02-FEB-12 09.59.48.821 AM
    6785 02-FEB-12 10.00.38.825 AM 02-FEB-12 11.00.10.118 AM
    6786 02-FEB-12 11.00.20.118 AM 02-FEB-12 12.00.22.882 PM
    6787 02-FEB-12 12.00.32.885 PM 02-FEB-12 01.00.04.662 PM
    6788 02-FEB-12 01.00.14.662 PM 02-FEB-12 01.59.59.035 PM

    (3) Even from dba_hist_snaphot I get which would be the correct snap_id interval

    select snap_id, BEGIN_INTERVAL_TIME, end_interval_time from dba_hist_snapshot where snap_id in (6782, 6783, 6784, 6785,6786, 6787, 6788) order by 1;

    SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
    ———- ————————- ————————-
    6782 02-FEB-12 07.00.19.643 AM 02-FEB-12 08.00.25.016 AM
    6783 02-FEB-12 08.00.25.016 AM 02-FEB-12 09.00.37.499 AM
    6784 02-FEB-12 09.00.37.499 AM 02-FEB-12 10.00.35.529 AM
    6785 02-FEB-12 10.00.35.529 AM 02-FEB-12 11.00.25.308 AM
    6786 02-FEB-12 11.00.25.308 AM 02-FEB-12 12.00.33.729 PM
    6787 02-FEB-12 12.00.33.729 PM 02-FEB-12 01.00.25.383 PM
    6788 02-FEB-12 01.00.25.383 PM 02-FEB-12 02.00.38.022 PM

    - Why awr report is saying that snap_id (6784) starts at 10:00 ?

    Best Regards
    LuisGomez

    • Try using @?/rdbms/admin/ashrpt then feed the time period 02-Feb-12 10:00:35 then 60mins, that will pull data points from DBA_HIST_ACTIVE_SESS_HISTORY from the snaps 6784 to 6785. You will see that part on the Begin/End data source of the ASH report.

      -Karl

  13. Pingback: RMOUG 2012 training days « Karl Arao's Blog

  14. Pingback: IOsaturationtoolkit-v2 with IORM and AWESOME text graph « Karl Arao's Blog

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