workload characterization using dba_hist tables and ksar « karl arao's blog.pdf

Upload: modesto-lopez-ramos

Post on 02-Mar-2018

240 views

Category:

Documents


0 download

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-...