workload characterization using dba_hist tables and ksar « karl arao's blog.pdf
TRANSCRIPT
-
7/26/2019 Workload characterization using DBA_HIST tables and kSar Karl Arao's Blog.pdf
1/15
Karl Araos Blog
Just another weblog about
Oracle,Linux,Troubleshooting,Performance,etc..etc..
J ,
Workload characterization using DBA_HIST tables
and kSar
erformance, Troubleshooting Tags: Performance, Troubleshooting, VisualizationBeen busy these past few days..
Well, this post is about taking advantage of the built in data store that started in 10gR1.. yeah itsAWR, and its very much like a Statspack on steroids (got that from Kyle Haileysmodules). Whats exciting about 10g and above is we could utilize the Time Model and theOSSTAT view and use them together with the SYSTAT view to have a far be er workload
information when going through all the SNAP_IDs.
I have this li le issue before of ge ing lazy when generating multiple AWR reports yeah its justso daunting, youll get tired just by generating those reports by hand and the catch is youve notyet analyzed anything yet.. :p (but yeah, youre lucky if youve got access to the OEM performancepage)
So.. I was thinking, if I get to see all the relevant workload info across SNAP_IDs it would be easierfor me to notice trends and even possible for me to visualize the data, or even possible to do somestatistics out of it.
On the Chapter 9 of Craig Shallahamers book (Oracle Performance Firefighting(h p://resources.orapub.com/?Click=35884)) there he explained in detail what information youneed to get for you to be able to define the Database Servers Capacity, Requirements, andUtilization(U=R/C)Ive outline some of the points here (h ps://spreadsheets.google.com/ccc?key=0ApH46jS7ZPdJdFdqZmtmRVRUQWFQZUk1Zi1DT0dwNWc&hl=en) (see the Firefightingtab)
. 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 62) CPU requirements see image below, column 7 to 143) Memory requirements see image below, column 15
17
REPLYREPLYREPLYREPLYREPLYREPLYREPLYREPLYREPLYREPLYREPLYREPLY
kload characterization using DBA_HIST tables and kSar Karl Ar... https://karlarao.wordpress.com/2010/01/31/workload-characterization-...
15 27/09/2015 11:54
-
7/26/2019 Workload characterization using DBA_HIST tables and kSar Karl Arao's Blog.pdf
2/15
4) IO requirements see image below, column 16 to 215) some SYSSTAT delta values see image 22 to 236) Utilization see image below, the last 6 columns
. Ive 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 (h ps://docs.google.com/leaf?id=0B5H46jS7ZPdJNmIzZTRlYjMtZWQwNi00YzhmLTk0MTctMWMzNTEzY2NjZjMx&hl=en)For Windows: awr_genwl_win.sql (h ps://docs.google.com/leaf?id=0B5H46jS7ZPdJZmI5YzJiNmItYWQ1OC00NDIyLWFhOTktNjllZmU1OWI3YTg3&hl=en)
MPORTANT NOTE: Diagnostic Pack License is needed for the scripts, Im updating my scripts
requently.. if the download link is not working here.. check out the Scripts Section(h ps://k arl arao.wordpress.com/scri pt s-resources) for t he fil e name and the most recent versi on oft he scri pt
(h p://lh3.ggpht.com/_F2x5WXOJ6Q8/S2hR6V8NjCI/AAAAAAAAAo0/YM_c7VhFKiI/dba_hist3.png)
Now time for some action!
As you can see from above.. there is a highlighted portion (in green). By running the script, we haveidentified 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 isa ributed by Oracle and not other operations on the OS, also you could see that it is not a ributed byan RMAN activity (because its zero!) Plus the Average Active Sessions (AAS) (11th column)and the OS Load (13th column)
:)
kload characterization using DBA_HIST tables and kSar Karl Ar... https://karlarao.wordpress.com/2010/01/31/workload-characterization-...
15 27/09/2015 11:54
-
7/26/2019 Workload characterization using DBA_HIST tables and kSar Karl Arao's Blog.pdf
3/15
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 ortime frame. From here you could either use ASH, run the AWR report, run ADDM, or use OEM. Thegood thing here is, you are not guessing! and youve done some quick correlation on the OS and thedatabase before drilling down further
Now
Lets see if we could correlate the output of the script with the SAR data.. from the image below, youcan see that the spike on load happened around 6:20 and ended around 7:30. And yes, a ributed bywait on IO (see the red color).
BTW, Im using kSar (h p://ksar.atomique.net) for visualizing the SAR data.
(h p://lh4.ggpht.com/_F2x5WXOJ6Q8/S2WdQ-v_hxI/AAAAAAAAAnQ/DwfXAlbyWPg/ksar1.png)
Also youll see the Load Average, which is the same from the output of the AWR script above.
(h p://lh6.ggpht.com/_F2x5WXOJ6Q8/S2WdQ-oJbBI/AAAAAAAAAnU/9oqBrfLGNfc/loadaverage1.png)
Now, lets check out the IO devices the database is using ASM with 3 ASM disks, I would get themajor and minor numbers and correlate these with the SAR data
kload characterization using DBA_HIST tables and kSar Karl Ar... https://karlarao.wordpress.com/2010/01/31/workload-characterization-...
15 27/09/2015 11:54
-
7/26/2019 Workload characterization using DBA_HIST tables and kSar Karl Arao's Blog.pdf
4/15
You can see from the images of the block devices below that they are mainly on Read Activity.. whichvalidates the output of IOPs read (16th column) and IO read MB/s (19th column)of the AWRscript cool!
(h p://lh6.ggpht.com/_F2x5WXOJ6Q8/S2WdRDdc4gI
/AAAAAAAAAnY/N2z2ZTOSeko/dev8-32.png)
(h p://lh5.ggpht.com/_F2x5WXOJ6Q8/S2WdRJb_7UI
/AAAAAAAAAnc/wVH42EOo4-8/dev8-48.png)
(h p://lh6.ggpht.com/_F2x5WXOJ6Q8/S2WdaPSE5RI
/AAAAAAAAAng/gsQIGeSqZY8/dev8-80.png)
Another thing When you get to run the script on your environment also try to generate AWRreport on particular SNAP_IDs youll notice that the values you got from the query will be thesame on the AWR report
Updat e (20100201):
Id like you to see the output of the Enterprise Manager Performance Pageon the time when thesudden workload spike happened.
(h p://lh4.ggpht.com/_F2x5WXOJ6Q8/S2a45qQX4aI
/AAAAAAAAAoA/qPg_TrYxxlA
/PerformancePage1.png)
(h p://lh6.ggpht.com/_F2x5WXOJ6Q8/S2a45hDdpJI
/AAAAAAAAAoE/zUzo6tLbOE8
/PerformancePage2.png)
(h p://lh4.ggpht.com/_F2x5WXOJ6Q8
/S2a45gvDB7I/AAAAAAAAAoI/LOv7qMAaOUo/TopActivity.png)
The firstand secondimages above came from the root page, youll 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
12345678910
GROUP_NUMBER NAME HEADER_STATU MOUNT_S STATE TOTAL_MB FREE_------------ -------------------- ------------ ------- -------- ----------
1 DATA_1_0000 UNKNOWN OPENED NORMAL 3067 01 DATA_1_0001 UNKNOWN OPENED NORMAL 3067 01 DATA_1_0002 UNKNOWN OPENED NORMAL 3067 0
[oracle@dbrocaix01 osm]$ ls -l /dev/sd[c,d,f]brw-rw---- 1 root disk 8, 32 Jan 17 2010 /dev/sdcbrw-rw---- 1 root disk 8, 48 Jan 17 2010 /dev/sddbrw-rw---- 1 root disk 8, 80 Jan 17 2010 /dev/sdf
kload characterization using DBA_HIST tables and kSar Karl Ar... https://karlarao.wordpress.com/2010/01/31/workload-characterization-...
15 27/09/2015 11:54
-
7/26/2019 Workload characterization using DBA_HIST tables and kSar Karl Arao's Blog.pdf
5/15
SNAP_ID 338 (Time 6:50) from the AWR script output youll 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 disk
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 th i rdimage is the Top Activity section you can see that most of the activity are SELECTs andthe database is waiting on User and System I/O (see the Top SQLs and Top Sessions)
TW, this script could also be used on a RAC environment you have to run i t on each node. Ive made it
pull the DBID and instance_number automatically..
Updat e (20100202):
On Windows environment for the DBA_HIST_OSSTAT view it does not contain the LOADandIOWAIT_TIMEstat_name so I removed these columns from the query. See the sample output below:
(h p://lh4.ggpht.com/_F2x5WXOJ6Q8/S2hR6URKt1I/AAAAAAAAAow/HgTEtkuD4sU/dba_hist_windows.png)
Thats all for this post! Now youll know be er about the workload of your environment :)
kload characterization using DBA_HIST tables and kSar Karl Ar... https://karlarao.wordpress.com/2010/01/31/workload-characterization-...
15 27/09/2015 11:54
-
7/26/2019 Workload characterization using DBA_HIST tables and kSar Karl Arao's Blog.pdf
6/15
17 comments
Pingback: Craig Shallahamer is now blogging! Karl Araos Blog
L.Ney says:FEBRUARY 2, 2010 AT 9:56 PMKarl,
Good work. Now I can take your idea(your script) and merge with PerfSheet that my friend TanelPoder created and design a nice visual data of my system. You guys are just too good..;)
karlarao says:FEBRUARY 2, 2010 AT 11:25 PMThanks for the kind words
bert says:FEBRUARY 8, 2010 AT 12:39 PMI did not see mention of a key licensing fact since you are using the DBA_HIST_ views youmust 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 indetail to see all that you used).
karlarao says:FEBRUARY 8, 2010 AT 1:55 PM
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 achievesimilar results.
dba_hist_snapshot = STATS$SNAPSHOTdba_hist_osstat = STATS$OSSTATdba_hist_sys_time_model = STATS$SYS_TIME_MODELdba_hist_sysstat = STATS$SYSSTAT
If I find time, Ill create the query the same concept applies
Also, Im curious on pu ing additional info for capturing the Network and Memoryrequirements on each SNAP_ID
For the memory usage.. Ill put in the sysstat metric session pga memory, in that way Illhave rough estimate on memory requirements for the sessions Then for the Network usage.. Ill put in bytes sent via SQL*Net to client and bytes sentvia SQL*Net to dblink.. each on separate columns.. in this way Ill know the networkrequirements (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 aWAN setup, but I still have to do some tests.
patrick says:FEBRUARY 28, 2010 AT 1:35 PM
:)
:)
kload characterization using DBA_HIST tables and kSar Karl Ar... https://karlarao.wordpress.com/2010/01/31/workload-characterization-...
15 27/09/2015 11:54
-
7/26/2019 Workload characterization using DBA_HIST tables and kSar Karl Arao's Blog.pdf
7/15
Nice post But I would suggest you rewrite your codebefore you create a P1 issue on a production system .
I give you some hints :using lag, when, caseCASEWHEN e.stat_name = AVG_IOWAIT_TIMETHEN CASEWHEN s.begin_interval_time = s.startup_timeTHEN e.VALUEELSE e.VALUE Lag(e.VALUE)OVER(PARTITION BY e.stat_name,e.dbid,e.instance_number,s.startup_time ORDER BYe.snap_id)ENDELSE 0END avg_iowait_time,
patrick
karlarao says:MARCH 1, 2010 AT 2:23 AMThanks for the feedback Patrick Ill look into this
Pingback: Blogroll Report 29/01/2009 05/02/2010 Coskans Approach to Oracle
Pingback: Seeing Exadata in action Karl Araos Blog
Pingback: Graphing the AAS with Perfsheet a la Enterprise Manager Karl Araos Blog
Pingback: Using Grid to display database CPU usage Oracle DBA A lifelong learningexperience
Arun says:JANUARY 20, 2011 AT 8:58 PMI am unable to download the script from the link. Can you provide me the script.
karlarao says:JANUARY 27, 2011 AT 11:25 AM
I have already sent it on your mail
Kamus says:MARCH 18, 2011 AT 10:51 AMHi KarlThe script mentioned in this post is no longer available, I knew you have moved all amazingscripts into a new google docs page, but update the URL in this post would be be er.
karlarao says:MARCH 18, 2011 AT 4:18 PM
Hi Kamus,
Yes, Ive had several emails regarding the access of the scripts. Ill probably move all of themon my DropBox and share the entire folder.
:)
:)
kload characterization using DBA_HIST tables and kSar Karl Ar... https://karlarao.wordpress.com/2010/01/31/workload-characterization-...
15 27/09/2015 11:54
-
7/26/2019 Workload characterization using DBA_HIST tables and kSar Karl Arao's Blog.pdf
8/15
Ill let you know once Im done with it
Patrick Boulay says:JUNE 29, 2011 AT 1:15 PMHiplease find you nice script , re-wri en using analytical functions
define instance_num=1
define start_date=2010_0428_0009define end_date=2010_0428_1900define start_hour=9define end_hour=19define date_format=YYYY_MMDD_HH24MIset pagesize 1000col dur format 999990.00 heading Snap|Dur|(m)col id format 99999 heading Snap|IDcol tm format a15 heading Snap|Start|Timecol inst format 90 heading i|n|s|t|#
col cpu format 90 heading C|P|Ucol cap format 9999990.00 heading ***|Total|CPU|Time|(s)col dbt format 999999990.00 heading DB|Timecol dbc format 99990.00 heading DB|CPUcol bgc format 99990.00 heading Bg|CPUcol rman format 9990.00 heading RMAN|CPUcol aas format 90.0 heading A|A|Scol totora format 9999990.00 heading ***|Total|Oracle|CPU|(s)col busy format 9999990.00 heading Busy|Timecol 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|rcol iows format 9990.000 heading IOPs|wcol ioredo format 9990.000 heading IOPs|redocol iormbs format 9990.000 heading IO r|(mb)/scol iowmbs format 9990.000 heading IO w|(mb)/scol redosizesec format 999990.000 heading Redo|(mb)/scol logons format 99990 heading Sesscol logone format 990 heading Sess|Endcol exsraw format 99990.000 heading Exec|raw|deltacol exs format 999990.000 heading Exec|/scol 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_snapAS (SELECT dbid,instance_number,snap_id,fin end_interval_time,Round(Extract(DAY FROM fin debut) * 1440 + Extract(HOUR FROM fin debut) * 60 +
kload characterization using DBA_HIST tables and kSar Karl Ar... https://karlarao.wordpress.com/2010/01/31/workload-characterization-...
15 27/09/2015 11:54
-
7/26/2019 Workload characterization using DBA_HIST tables and kSar Karl Arao's Blog.pdf
9/15
Extract(MINUTE FROM fin debut) + Extract(SECOND FROM fin debut) / 60, 2) durFROM (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_snapshotWHERE instance_number = &instance_numAND begin_interval_time >= To_date(&start_date, &date_format)AND end_interval_time = 7AND To_number(To_char(end_interval_time, HH24))
-
7/26/2019 Workload characterization using DBA_HIST tables and kSar Karl Arao's Blog.pdf
10/15
ELSE VALUE Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number,startup_time ORDER BY snap_id)ENDELSE NULLEND rm_cpuFROM dba_hist_snapshotnatural 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_statAS (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_requestsFROM (SELECT dbid,snap_id,instance_number,
CASEWHEN stat_name = logons current THEN VALUEELSE NULLEND logons_current,CASEWHEN stat_name = execute count THEN CASEWHEN begin_interval_time = startup_time THEN VALUEELSE VALUE Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number,startup_time ORDER BY snap_id)END
ELSE NULLEND execute_count,CASEWHEN stat_name = physical reads THEN CASEWHEN begin_interval_time = startup_time THEN VALUEELSE VALUE Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number,startup_time ORDER BY snap_id)ENDELSE NULLEND physical_reads,
CASEWHEN stat_name = physical writes THEN CASEWHEN begin_interval_time = startup_time THEN VALUEELSE VALUE Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number,startup_time ORDER BY snap_id)
kload characterization using DBA_HIST tables and kSar Karl Ar... https://karlarao.wordpress.com/2010/01/31/workload-characterization-...
e 15 27/09/2015 11:54
-
7/26/2019 Workload characterization using DBA_HIST tables and kSar Karl Arao's Blog.pdf
11/15
ENDELSE NULLEND physical_writes,CASEWHEN stat_name = redo writes THEN CASEWHEN begin_interval_time = startup_time THEN VALUEELSE VALUE Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number,
startup_time ORDER BY snap_id)ENDELSE NULLEND redo_writes,CASEWHEN stat_name = redo size THEN CASEWHEN begin_interval_time = startup_time THEN VALUEELSE VALUE Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number,startup_time ORDER BY snap_id)END
ELSE NULLEND redo_size,CASEWHEN stat_name = physical read IO requests THEN CASEWHEN begin_interval_time = startup_time THEN VALUEELSE VALUE Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number,startup_time ORDER BY snap_id)ENDELSE NULLEND physical_read_io_requests,
CASEWHEN stat_name = physical write IO requests THEN CASEWHEN begin_interval_time = startup_time THEN VALUEELSE VALUE Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number,startup_time ORDER BY snap_id)ENDELSE NULLEND physical_write_io_requestsFROM dba_hist_snapshotnatural 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_osstatAS (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,
kload characterization using DBA_HIST tables and kSar Karl Ar... https://karlarao.wordpress.com/2010/01/31/workload-characterization-...
e 15 27/09/2015 11:54
-
7/26/2019 Workload characterization using DBA_HIST tables and kSar Karl Arao's Blog.pdf
12/15
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,CASEWHEN e.stat_name = LOAD THEN e.VALUEELSE 0END load,CASEWHEN e.stat_name = NUM_CPUS THEN e.VALUEELSE 0END cpu,
CASEWHEN e.stat_name = AVG_BUSY_TIME THEN CASEWHEN s.begin_interval_time = s.startup_time THEN e.VALUEELSE e.VALUE Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number,s.startup_time ORDER BY e.snap_id)ENDELSE 0END avg_busy_time,CASEWHEN e.stat_name = AVG_SYS_TIME THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUEELSE e.VALUE Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number,s.startup_time ORDER BY e.snap_id)ENDELSE 0END avg_sys_time,CASEWHEN e.stat_name = AVG_USER_TIME THEN CASEWHEN s.begin_interval_time = s.startup_time THEN e.VALUEELSE e.VALUE Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number,
s.startup_time ORDER BY e.snap_id)ENDELSE 0END avg_user_time,CASEWHEN e.stat_name = AVG_IOWAIT_TIME THEN CASEWHEN s.begin_interval_time = s.startup_time THEN e.VALUEELSE 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 0END avg_iowait_time,CASEWHEN e.stat_name = AVG_IDLE_TIME THEN CASEWHEN s.begin_interval_time = s.startup_time THEN e.VALUE
kload characterization using DBA_HIST tables and kSar Karl Ar... https://karlarao.wordpress.com/2010/01/31/workload-characterization-...
e 15 27/09/2015 11:54
-
7/26/2019 Workload characterization using DBA_HIST tables and kSar Karl Arao's Blog.pdf
13/15
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)ENDELSE 0END avg_idle_time,CASEWHEN e.stat_name = BUSY_TIME THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUEELSE e.VALUE Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number,s.startup_time ORDER BY e.snap_id)ENDELSE 0END busy_time,CASEWHEN e.stat_name = USER_TIME THEN CASEWHEN s.begin_interval_time = s.startup_time THEN e.VALUEELSE e.VALUE Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number,
s.startup_time ORDER BY e.snap_id)ENDELSE 0END user_time,CASEWHEN e.stat_name = SYSR_TIME THEN CASEWHEN s.begin_interval_time = s.startup_time THEN e.VALUEELSE 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 0END sys_time,CASEWHEN e.stat_name = IOWAIT_TIME THEN CASEWHEN s.begin_interval_time = s.startup_time THEN e.VALUEELSE e.VALUE Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number,s.startup_time ORDER BY e.snap_id)ENDELSE 0END io_wait_time,
CASEWHEN e.stat_name = PHYSICAL_MEMORY_BYTES THEN e.VALUEELSE 0END phy_memFROM dba_hist_osstat e,dba_hist_snapshot sWHERE s.snap_id = e.snap_idAND e.instance_number = s.instance_numberAND e.dbid = s.dbidAND 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)
kload characterization using DBA_HIST tables and kSar Karl Ar... https://karlarao.wordpress.com/2010/01/31/workload-characterization-...
e 15 27/09/2015 11:54
-
7/26/2019 Workload characterization using DBA_HIST tables and kSar Karl Arao's Blog.pdf
14/15
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 ASoracpupct,( ( 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 oscpuioFROM sub_snapnatural join sub_sys_time_modelnatural join sub_sys_statnatural join sub_osstatORDER BY id ASC/
karlarao says:JULY 9, 2011 AT 8:33 AMI dont completely agree with this one.. Ive done some intensive testing and comparing thedifferent approach on doing this but when all the columns of awr_genwl are already added,the snap_id + 1 approach winsThe test case could be downloaded at h ps://karlarao.wordpress.com/scripts-resources/ on thezip file HOTSOS2011 Karl Arao Mining the AWR Repository.zip\HOTSOS2011 KarlArao Mining the AWR Repository\hotsos2011_files\Visualization\slide23 SQLTrickAndLAGComparison
Youll see the comparison of the following: WITH WITH and LAG and the snap_id + 1 approach
;)
kload characterization using DBA_HIST tables and kSar Karl Ar... https://karlarao.wordpress.com/2010/01/31/workload-characterization-...
e 15 27/09/2015 11:54
-
7/26/2019 Workload characterization using DBA_HIST tables and kSar Karl Arao's Blog.pdf
15/15
Also Ive presented this topic at Hotsos and there are also presenters doing mining on AWRand we are aware of different things to do it and kind of agree that it depends. So in mycase, the way I pull the data from various tables the snap_id + 1 approach is the winner.. and Ihave evidence (test case) to prove it.
-Karl
Create a free website or blog at WordPress.com. | The Newsy Theme. Design by Themify.
Follow
Follow Karl Araos Blog
Build a website with WordPress.com
kload characterization using DBA_HIST tables and kSar Karl Ar... https://karlarao.wordpress.com/2010/01/31/workload-characterization-...