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
Pingback: Craig Shallahamer is now blogging! « Karl Arao's Blog
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..;)
Thanks for the kind words
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.
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
Thanks for the feedback Patrick… I’ll look into this…
Pingback: Blogroll Report 29/01/2009 – 05/02/2010 « Coskan’s Approach to Oracle
Pingback: Seeing Exadata in action « Karl Arao's Blog
Pingback: Graphing the AAS with Perfsheet a la Enterprise Manager « Karl Arao's Blog
Pingback: Using Grid to display database CPU usage « Oracle DBA – A lifelong learning experience
I am unable to download the script from the link. Can you provide me the script.
I have already sent it on your mail
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
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