© 2008 Convio, Inc. A tour of the AWR tables Dave Abercrombie Principal Database Architect, Convio NoCOUG, August 21 2008.

Slide 1© 2008 Convio, Inc. A tour of the AWR tables Dave Abercrombie Principal Database Architect, Convio NoCOUG, August 21 2008 Slide 2© 2008 Convio, Inc. Agenda What is AWR? How can you benefit from AWR? Statistics: mean and variance Investigation phases: Time ranges of load spikes Aggregate statistics to find problem SQL Variance to find problem SQL Time behavior of problem SQL Overview of important AWR tables Not just SQL Details in paper online and printed up front Will pause between sections for questions Slide 3© 2008 Convio, Inc. What is AWR? Automatic Workload Repository Detailed & comprehensive performance history Based on snapshot (time interval) Canned reports Automatic Database Diagnostic Monitor (ADDM) About 79 DBA_HIST_* tables Easy to write your own queries Defaults: Hourly snapshots Weekly retention (month is better) Oracle's Diagnostic Pack License Slide 4© 2008 Convio, Inc. AWR Benefits Database that you rarely, if ever, review Find and drill down into recent load spikes Prioritize developer/DBA resources Load testing Capacity planning Real-time monitors might be: poor missing unreliable Slide 5© 2008 Convio, Inc. Write your own DBA_HIST Queries Fine tune for your unique needs (per DB) Simple enough for interactive use Almost like star-schema data warehouse Graphical UI not necessary Use spreadsheet to chart, etc. Simple examples: Easily ported Building blocks Generalize beyond SQL Slide 6© 2008 Convio, Inc. A bit mathematical… …but not too bad Intuitive or obvious definitions: High or Low Uniform or skewed Load spike Some statistics: Mean and variance Sampling Slide 7© 2008 Convio, Inc. Uniform vs. Skew Slide 8© 2008 Convio, Inc. Goals vary during investigation Find time ranges of load spikes Find specific problem SQLs ASH (load spikes) Sort by aggregated statistics Non-uniform statistics Characterize a problem SQLs behavior over time Slide 9© 2008 Convio, Inc. Example 1 (find spikes) SAMPLE_HOUR CPU_AVG WAIT_AVG ACT_AVG ACT_VAR_MEAN ----------------- ---------- ---------- ---------- ------------ 2008-07-26 04:00 1.5 0 1.5.4 2008-07-26 05:00 1.5.1 1.5.5 2008-07-26 06:00 1.5.1 1.6.5 2008-07-26 07:00 1.6 0 1.6.6 2008-07-26 08:00 5.5.1 5.6 14.2 2008-07-26 09:00 1.5.1 1.6.6 2008-07-26 10:00 2.3 2.3 1 2008-07-26 11:00 1.8.2 1.9.6 2008-07-26 12:00 1.9.3 2.1.6 2008-07-26 13:00 1.5.1 1.6.5 2008-07-26 14:00 1.5.1 1.6.4 2008-07-26 15:00 1.5.2 1.7.5 2008-07-26 16:00 1.5.1 1.7.5 2008-07-26 17:00 2.1.1 2.3 1.2 2008-07-26 18:00 16.4.4 16.8 52.2 2008-07-26 19:00 1.7.1 1.8.6 2008-07-26 20:00 3.5.1 3.6 4.1 2008-07-26 21:00 2.3.1 2.4 1 2008-07-26 22:00 2.7.4 3.1 1.3 2008-07-26 23:00 2.4 2.4.6 2008-07-27 00:00 2.1.2 2.4.7 2008-07-27 01:00 3.1 3.1 1.9 2008-07-27 02:00 2.1 2.1.9 2008-07-27 03:00 1.5 0 1.6.5 2008-07-27 04:00 1.6.1 1.6.5 2008-07-27 05:00 1.3.1 1.4.4 2008-07-27 06:00 2 0 2.1 1.3 2008-07-27 07:00 1.6 0 1.7.7 Slide 10© 2008 Convio, Inc. Example 1 (time behavior of a single SQL) SNAP_ID BEGIN_HOUR EXECS_PER_HOUR GETS_PER_HOUR GETS_PER_EXEC SECONDS_PER_HOUR ---------- ---------------- -------------- ------------- ------------- ---------------- 4602 2008-07-26 04:00 38 6461379 170036 756 4603 2008-07-26 05:00 36 6122377 170066 730 4604 2008-07-26 06:00 53 9013543 170067 1092 4605 2008-07-26 07:00 51 8442277 165535 1026 4606 2008-07-26 08:00 185 30853576 166776 13417 4607 2008-07-26 09:00 71 12150557 171135 1682 4608 2008-07-26 10:00 50 8174415 163488 1021 4609 2008-07-26 11:00 64 10756074 168064 1330 4610 2008-07-26 12:00 63 10831134 171923 1285 4611 2008-07-26 13:00 31 5272083 170067 626 4612 2008-07-26 14:00 40 6801527 170038 800 4613 2008-07-26 15:00 46 6976201 151657 828 4614 2008-07-26 16:00 49 7146955 145856 879 4615 2008-07-26 17:00 441 70291915 159392 166135 4616 2008-07-26 18:00 63 11142717 176869 1460 4617 2008-07-26 19:00 172 28876624 167887 6677 4618 2008-07-26 20:00 175 30131576 172180 4370 4619 2008-07-26 21:00 95 16155552 170058 2156 4620 2008-07-26 22:00 169 28325210 167605 3871 4621 2008-07-26 23:00 81 13773859 170048 1687 4622 2008-07-27 00:00 173 29016508 167725 4821 4623 2008-07-27 01:00 155 26108479 168442 3378 4624 2008-07-27 02:00 143 24265774 169691 3125 4625 2008-07-27 03:00 34 5781487 170044 684 4626 2008-07-27 04:00 59 9525398 161447 1120 4627 2008-07-27 05:00 113 19024549 168359 2838 4628 2008-07-27 06:00 61 10495706 172061 1234 4629 2008-07-27 07:00 39 6631444 170037 844 Slide 11© 2008 Convio, Inc. Find time ranges of load spikes Average Active Sessions (AAS) Kyle Hailey: "DB time / elapsed time" Used by Oracle (ADDM, etc.) "DB time" is the sum of time: spent by all sessions on the CPU, or stuck in non-idle wait states Calculated exactly, or estimated Slide 12© 2008 Convio, Inc. Estimate AAS Make frequent observations (samples) Count "active" sessions in each sample Define averaging interval Average sampled counts over this interval Simplified math: sum of counts divided by number of observations Include "variance" to detect skew ("variance" defined in a couple of slides) Slide 13© 2008 Convio, Inc. Averaging interval size Long intervals: More convenient Less clutter BUT short spikes hidden (averaged out) Short intervals: Less convenient, more clutter More "natural" variability Short spikes hidden (by clutter and noise) Slide 14© 2008 Convio, Inc. Variance to detect short spikes Robyn Sands, "An Industrial Engineer's Approach to Managing Oracle Databases", Hotsos 2008. "Variance" is a measure of statistical dispersion (aka "skew") Use variance divided by mean A generally useful technique, not just load spikes Slide 15© 2008 Convio, Inc. Method to estimate AAS and its variance S = single count of active sessions S i = set of observed counts (i from 1 to N) AAS = avg(S i ) Variance = variance(S i ) Variance/mean = variance(S i ) / avg(S i ) This paper uses ACT_AVG for AAS and ACT_VAR_MEAN for variance/mean Slide 16© 2008 Convio, Inc. AWR query to find load spikes (aas-per-hour.sql) select to_char(round(sub1.sample_time, 'HH24'), 'YYYY-MM-DD HH24:MI') as sample_hour, round(avg(sub1.on_cpu),1) as cpu_avg, round(avg(sub1.waiting),1) as wait_avg, round(avg(sub1.active_sessions),1) as act_avg, round( (variance(sub1.active_sessions)/avg(sub1.active_sessions)),1) as act_var_mean from ( -- sub1: one row per ASH/AWR sample observation select sample_id, sample_time, sum(decode(session_state, 'ON CPU', 1, 0)) as on_cpu, sum(decode(session_state, 'WAITING', 1, 0)) as waiting, count(*) as active_sessions from dba_hist_active_sess_history where sample_time > sysdate - (&hours/24) group by sample_id, sample_time ) sub1 group by round(sub1.sample_time, 'HH24') order by round(sub1.sample_time, 'HH24') ; Slide 17© 2008 Convio, Inc. Example 2: finding load spike with variance, part 1 (hour) This spike was invisible without looking at variance SQL> @aas-per-hour.sql Enter value for hours: 12 old 18: sample_time > sysdate - (&hours/24) new 18: sample_time > sysdate - (12/24) SAMPLE_HOUR CPU_AVG WAIT_AVG ACT_AVG ACT_VAR_MEAN ----------------- ---------- ---------- ---------- ------------ 2008-04-16 07:00 1.4.4 1.8.6 2008-04-16 08:00 1.8.5 2.3 1 2008-04-16 09:00 2.3.5 2.8 1.3 2008-04-16 10:00 2.6.6 3.2 2.3 2008-04-16 11:00 3.5.6 4.1 2.3 2008-04-16 12:00 2.4.6 3 1.1 2008-04-16 13:00 2.3.6 2.9 1 2008-04-16 14:00 3.7 2.7 6.4 95.4 2008-04-16 15:00 3.1.7 3.8 1.9 2008-04-16 16:00 2.9.7 3.6 1.6 2008-04-16 17:00 2.3.4 2.7.9 2008-04-16 18:00 2.1.6 2.7 2.6 Slide 18© 2008 Convio, Inc. Example 2: finding load spike with variance, part 2 (minute) SQL> @aas-per-min-awr.sql Enter value for minutes: 300 old 18: SAMPLE_TIME > sysdate - (&minutes/1440) new 18: SAMPLE_TIME > sysdate - (300/1440) SAMPLE_MINUTE CPU_AVG WAIT_AVG ACT_AVG ACT_VAR_MEAN -------------------- ---------- ---------- ---------- ------------ 2008-04-16 13:59:00 3.4.4 3.8.2 2008-04-16 14:00:00 8.3.5 8.8 1.8 2008-04-16 14:01:00 10.7 2.2 12.8.5 2008-04-16 14:02:00 3.5.7 4.2.5 2008-04-16 14:03:00 2.6 1.2 3.8 1.5 2008-04-16 14:04:00 3.3 1.2 4.5 1.3 2008-04-16 14:05:00 8.2.7 8.8 2.1 2008-04-16 14:06:00 6.7 1.3 8 1.1 2008-04-16 14:07:00 4.7 3.2 7.8 3.7 2008-04-16 14:08:00 20.5 109.8 130.3 170 2008-04-16 14:09:00 6 1.3 7.3 10.3 2008-04-16 14:10:00 2.6.4 3.8 2008-04-16 14:11:00 4.3 4.3 1.1 2008-04-16 14:12:00 5.7.8 6.5 1.6 2008-04-16 14:13:00 3.3 3.3.7 2008-04-16 14:14:00 1.8.7 2.5.6 2008-04-16 14:15:00 3.3 2 5.3 2.2 Slide 19© 2008 Convio, Inc. Is this just a "fishing expedition"? No! short spikes: might not generate user complaints, might be ignored by monitors, but are in fact problems No! Very high AAS values always a problem Use of variance to find skew minimizes limits of aggregated statistics Enough detail in AWR/ASH to find bottlenecks Perhaps you should go fishing sometimes! Month's worth of AWR makes this easy Slide 20© 2008 Convio, Inc. Goals vary during investigation Find time ranges of load spikes Find specific problem SQLs ASH (load spikes) Sort by aggregated statistics Non-uniform statistics Characterize a problem SQLs behavior over time Slide 21© 2008 Convio, Inc. DBA_HIST_SNAPSHOT PK: SNAP_ID (DBID, INSTANCE_NUMBER) Basic interval characteristics: BEGIN_INTERVAL_TIME END_INTERVAL_TIME Perhaps use ANSI "natural join" Convenient, lazy Not part of application! Slide 22© 2008 Convio, Inc. DBA_HIST_SQLSTAT PK: SNAP_ID, SQL_ID, PLAN_HASH_VALUE (DBID, INSTANCE_NUMBER) Basic stats: Executions, Gets, Reads Time: CPU, Elapsed Wait time: IO, Concurrency, App Use "deltas" rather than "totals" Time in microseconds Slide 23© 2008 Convio, Inc. Total vs. Delta Totals can decrease! Deltas are like data warehouse summable facts Total harder to aggregate over time range SNAP_ID BEGIN_HOUR EXECS_TOTAL EXECS_DELTA ------- ---------------- ----------- ----------- 4571 2008-07-24 21:00 52647 52647 4572 2008-07-24 22:00 63756 11109 4691 2008-07-29 21:00 27602 27576 4739 2008-07-31 21:00 77292 77280 4756 2008-08-01 14:00 79548 2256 4757 2008-08-01 15:00 109722 30174 4758 2008-08-01 16:00 137217 27495 4759 2008-08-01 17:00 155265 18048 4763 2008-08-01 21:00 237432 82167 4823 2008-08-04 09:00 97036 19744 4824 2008-08-04 10:00 11232 11232 4835 2008-08-04 21:00 2016 2016 Slide 24© 2008 Convio, Inc. Aggregate statistics (find-expensive.sql) select sub.sql_id, sub.seconds_since_date, sub.execs_since_date, sub.gets_since_date from ( -- sub to sort before rownum select sql_id, round(sum(elapsed_time_delta)/1000000) as seconds_since_date, sum(executions_delta) as execs_since_date, sum(buffer_gets_delta) as gets_since_date from dba_hist_snapshot natural join dba_hist_sqlstat where begin_interval_time > to_date('&start_YYYYMMDD','YYYY-MM-DD') group by sql_id order by 2 desc ) sub where rownum < 30 ; Slide 25© 2008 Convio, Inc. Example 3 (sqlstat, aggregate stats): some with high execution rates, some with high buffer gets, etc. SQL> @find-expensive.sql Enter value for start_yyyymmdd: 2008-08-01 old 16: begin_interval_time > to_date('&&start_YYYYMMDD','YYYY-MM-DD') new 16: begin_interval_time > to_date('2008-08-01','YYYY-MM-DD') SQL_ID SECONDS_SINCE_DATE EXECS_SINCE_DATE GETS_SINCE_DATE ------------- ------------------ ---------------- --------------- 1wc4bx0qap8ph 30617 21563 284059357 6hudrj03d3w5g 23598 20551110 472673974 6tccf6u9tf891 18731 33666 457970700 2u874gr7qz2sk 15175 29014 370715705 fpth08dw8pyr6 14553 2565 36018228 1jt5kjbg7fs5p 11812 12451 2004271887 2f75gyksy99zn 10805 21529 567776447 ccp5w0adc6xx9 5222 6167 222949142 gn26ddjqk93wc 3568 114084711 248687700 b6usrg82hwsa3 2888 2 165621244 ctaajfgak033z 2391 4 66644336 7zwhhjv42qmfq 2197 592377 31495833 96v8tzx8zb99s 2152 6167 117875813 cxjsw79bprkm4 1526 396277 137413869 f2awk3951dcxv 1500 3462 35853709 fzmzt8mf2sw16 1421 311 44067742 01bqmm3gcy9yj 1329 299778 23504806 Slide 26© 2008 Convio, Inc. Goals vary during investigation Find time ranges of load spikes Find specific problem SQLs ASH (load spikes) Sort by aggregated statistics Non-uniform statistics Characterize a problem SQLs behavior over time Slide 27© 2008 Convio, Inc. Non-uniform statistics (high-var-sql.sql) select sub1.sql_id, round(avg(sub1.seconds_per_hour)) as avg_seconds_per_hour, round(variance(sub1.seconds_per_hour)/avg(sub1.seconds_per_hour)) as var_over_mean, count(*) as ct from ( -- sub1 select snap_id, sql_id, elapsed_time_delta/1000000 as seconds_per_hour from dba_hist_snapshot natural join dba_hist_sqlstat where -- look at recent history only begin_interval_time > sysdate - &&days_back and executions_delta > 0 ) sub1 group by sub1.sql_id having -- only queries that consume 10 seconds per hour on the average avg(sub1.seconds_per_hour) > 10 and -- only queries that run 50% of the time, assumes hourly snapshots too count(*) > ( &&days_back * 24) * 0.50 order by 3; Slide 28© 2008 Convio, Inc. Example 4 (sqlstat, high variance): obvious outlier with high variance, but not the most elapsed time SQL_ID AVG_SECONDS_PER_HOUR VAR_OVER_MEAN CT ------------- -------------------- ------------- ---------- 72wuyy9sxdmpx 41 7 167 bgpag6tkxt34h 29 12 167 crxfkabz8atgn 14 14 167 66uc7dydx131a 16 16 167 334d2t692js2z 36 19 167 6y7mxycfs7afs 23 20 167 36vs0kyfmr0qa 17 21 129 fp10bju9zh6qn 45 22 167 fas56fsc7j9u5 10 22 167 61dyrn8rjqva2 17 22 129 4f8wgv0d5hgua 31 23 167 7wvy5xpy0c6k5 15 23 151 8v59g9tn46y3p 17 24 132 9pw7ucw4n113r 59 27 167 41n1dhb0r3dhv 32 32 120 8mqxjr571bath 35 38 117 8jp67hs2296v3 46 154 128 afdjq1cf8dpwx 34 184 150 6n3h2sgxpr78g 454 198 145 g3176qdxahvv9 42 383 92 b72dmps6rp8z8 209 1116 167 6qv7az2048hk4 3409 50219 167 Slide 29© 2008 Convio, Inc. Goals vary during investigation Find time ranges of load spikes Find specific problem SQLs ASH (load spikes) Sort by aggregated statistics Non-uniform statistics Characterize a problem SQLs behavior over time Helps correlation with application Slide 30© 2008 Convio, Inc. Behavior of a specific SQL over time (sql-stat-hist.sql) select snap_id, to_char(begin_interval_time,'YYYY-MM-DD HH24:MI') as begin_hour, executions_delta as execs_per_hour, buffer_gets_delta as gets_per_hour, round(buffer_gets_delta/executions_delta) as gets_per_exec, round(elapsed_time_delta/1000000) as seconds_per_hour from dba_hist_snapshot natural join dba_hist_sqlstat where begin_interval_time between to_date('&start_hour', 'YYYY-MM-DD HH24:MI') and to_date('&end_hour', 'YYYY-MM-DD HH24:MI') and sql_id = '&sql_id' and executions_delta > 0 order by snap_id ; Slide 31© 2008 Convio, Inc. Example 5 (sqlstat, one sql_id): sustained high execution rates, occasional wait pile-ups SNAP_ID BEGIN_HOUR EXECS_PER_HOUR GETS_PER_HOUR GETS_PER_EXEC SECONDS_PER_HOUR ---------- ---------------- -------------- ------------- ------------- ---------------- 1978 2008-04-07 20:00 140449 540639 4 11 1979 2008-04-07 21:00 124142 477807 4 17 1980 2008-04-07 22:00 90568 347286 4 20 1981 2008-04-07 23:00 83287 323100 4 30 1982 2008-04-08 00:00 57094 221166 4 49 1983 2008-04-08 01:00 43925 170594 4 7 1984 2008-04-08 02:00 38596 150277 4 4 1985 2008-04-08 03:00 35710 139576 4 4 1986 2008-04-08 04:00 29700 115429 4 4 1987 2008-04-08 05:00 43666 170520 4 5 1988 2008-04-08 06:00 50755 197116 4 6 1989 2008-04-08 07:00 80371 310652 4 9 1990 2008-04-08 08:00 111924 431470 4 11 1991 2008-04-08 09:00 127154 489649 4 27 1992 2008-04-08 10:00 139270 536962 4 25 1993 2008-04-08 11:00 128697 496013 4 18 1994 2008-04-08 12:00 158739 613554 4 45287 1995 2008-04-08 13:00 152515 587605 4 40 1996 2008-04-08 14:00 144389 555770 4 37589 1997 2008-04-08 15:00 149278 575827 4 26 1998 2008-04-08 16:00 140632 542580 4 12 1999 2008-04-08 17:00 120113 462665 4 11 2000 2008-04-08 18:00 121394 468684 4 12 2001 2008-04-08 19:00 127948 493084 4 13 Slide 32© 2008 Convio, Inc. Example 6 (sqlstat, one sql_id): nightly high execution rates SNAP_ID BEGIN_HOUR EXECS_PER_HOUR GETS_PER_HOUR GETS_PER_EXEC SECONDS_PER_HOUR ---------- ---------------- -------------- ------------- ------------- ---------------- 1811 2008-03-31 21:00 98550 893916 9 28 1812 2008-03-31 22:00 9794 89386 9 2 1823 2008-04-01 09:00 3038 27604 9 1 1824 2008-04-01 10:00 4360 39362 9 1 1825 2008-04-01 11:00 3608 32759 9 1 1859 2008-04-02 21:00 17369 156840 9 3 1883 2008-04-03 21:00 79566 717500 9 22 1884 2008-04-03 22:00 207334 1871430 9 38 1885 2008-04-03 23:00 276997 2500938 9 39 1886 2008-04-04 00:00 258505 2329526 9 36 1887 2008-04-04 01:00 190127 1710001 9 27 1888 2008-04-04 02:00 188449 1695215 9 24 1907 2008-04-04 21:00 102162 923998 9 20 1930 2008-04-05 20:00 17437 158213 9 3 1931 2008-04-05 21:00 196100 1768306 9 30 1932 2008-04-05 22:00 207867 1875544 9 40 1933 2008-04-05 23:00 230548 2079470 9 32 1934 2008-04-06 00:00 216352 1946824 9 31 1935 2008-04-06 01:00 207935 1871111 9 28 1936 2008-04-06 02:00 118544 1065785 9 15 Slide 33© 2008 Convio, Inc. Example 7 (sqlstat, one sql_id): sporadic high execution rates SNAP_ID BEGIN_HOUR EXECS_PER_HOUR GETS_PER_HOUR GETS_PER_EXEC SECONDS_PER_HOUR ---------- ---------------- -------------- ------------- ------------- ---------------- 1790 2008-03-31 00:00 6710 20340 3 0 1791 2008-03-31 01:00 83 253 3 0 1792 2008-03-31 02:00 18 54 3 0 1793 2008-03-31 03:00 18 54 3 0 1794 2008-03-31 04:00 1 3 3 0 1795 2008-03-31 05:00 16 48 3 0 1796 2008-03-31 06:00 1943358 5901783 3 85 1797 2008-03-31 07:00 5633 17195 3 0 1798 2008-03-31 08:00 927016 2815340 3 35 1799 2008-03-31 09:00 5843023 17744104 3 252 1800 2008-03-31 10:00 2929624 8896969 3 131 1801 2008-03-31 11:00 988709 3002649 3 45 1802 2008-03-31 12:00 1959757 5951342 3 108 1803 2008-03-31 13:00 10767 32728 3 1 1804 2008-03-31 14:00 997451 3028890 3 70 1805 2008-03-31 15:00 1000944 3039948 3 49 1806 2008-03-31 16:00 5166 15861 3 0 1807 2008-03-31 17:00 4821 14616 3 0 1808 2008-03-31 18:00 11639 35243 3 1 1809 2008-03-31 19:00 8346 25421 3 1 1810 2008-03-31 20:00 4731 14380 3 1 1811 2008-03-31 21:00 1975147 5998626 3 160 1812 2008-03-31 22:00 27361 83023 3 3 1813 2008-03-31 23:00 521 1589 3 0 Slide 34© 2008 Convio, Inc. Example 8 (sqlstat, one sql_id): changes in efficiency and rate SNAP_ID BEGIN_HOUR EXECS_PER_HOUR GETS_PER_HOUR GETS_PER_EXEC SECONDS_PER_HOUR ---------- ---------------- -------------- ------------- ------------- ---------------- 1848 2008-04-02 10:00 1028451 3155807 3 39 1849 2008-04-02 11:00 1015627 3116830 3 35 1850 2008-04-02 12:00 957525 2941788 3 34 1851 2008-04-02 13:00 7740 23486 3 0 1852 2008-04-02 14:00 2039987 6260065 3 86 1853 2008-04-02 15:00 1017857 3123548 3 33 1854 2008-04-02 16:00 3692 11286 3 0 1855 2008-04-02 17:00 8700 26482 3 0 1856 2008-04-02 18:00 5895 17937 3 0 1857 2008-04-02 19:00 7296 22103 3 0 1858 2008-04-02 20:00 2156 6526 3 0 1859 2008-04-02 21:00 2686 8186 3 0 1860 2008-04-02 22:00 5439 74432 14 14 1861 2008-04-02 23:00 227644 3152747 14 848 1861 2008-04-02 23:00 80 283 4 0 1862 2008-04-03 00:00 792146 7807033 10 1215 1865 2008-04-03 03:00 829 7464 9 1 1867 2008-04-03 05:00 432 3889 9 0 1868 2008-04-03 06:00 388 2720 7 0 1869 2008-04-03 07:00 1273 9142 7 1 1870 2008-04-03 08:00 28277 804514 28 190 1871 2008-04-03 09:00 399722 5372737 13 1461 1872 2008-04-03 10:00 1563634 17540545 11 4503 1873 2008-04-03 11:00 232 717 3 0 Slide 35© 2008 Convio, Inc. DBA_HIST_SYSSTAT PK: SNAP_ID STAT_ID (DBID, INSTANCE_NUMBER) AWR snapshots of V$SYSSTAT Includes STAT_NAME (almost 400!) Does NOT include deltas, you need to calculate these yourself Watch out for stat counter resets (bounces) "DB time" in units of centiseconds Use as basis of comparison Slide 36© 2008 Convio, Inc. System statistic history (sys-stat-hist.sql) select stat_start.snap_id, to_char(snap.begin_interval_time,'YYYY-MM-DD HH24:MI') as begin_hour, stat_end.value - stat_start.value as delta_value from dba_hist_sysstat stat_start, dba_hist_sysstat stat_end, dba_hist_snapshot snap where -- assumes the snap_id at the end of the interval -- is one greater than the snap_id at the start ofthe interval stat_end.snap_id = stat_start.snap_id + 1 and -- otherwise, we join stat_end and stat_start on exact matches of the remaining PK columns ( stat_end.dbid = stat_start.dbid and stat_end.instance_number = stat_start.instance_number and stat_end.stat_name = stat_start.stat_name ) and -- filter for the statistic we are interested in (might want to add date range filter too) stat_end.stat_name = '&stat_name' and -- join stat_start to snap on FK ( stat_start.snap_id = snap.snap_id and stat_start.dbid = snap.dbid and stat_start.instance_number = snap.instance_number ) order by stat_start.snap_id ; Slide 37 @sys-stat-hist.sql Enter value for stat_name: DB time old 27: stat_end.stat_name = '&stat_name' n">@sys-stat-hist.sql Enter value for stat_name: DB time old 27: stat_end.stat_name = '&stat_name' new 27: stat_end.stat_name = 'DB time' SNAP_ID BEGIN_HOUR DELTA_VALUE ---------- ---------------- ----------- 4159 2008-07-07 17:00 2089225 4160 2008-07-07 18:00 1505607 4161 2008-07-07 19:00 31188489 4162 2008-07-07 20:00 24930866 4163 2008-07-07 21:00 1828924 4164 2008-07-07 22:00 1258286 4165 2008-07-07 23:00 396076 4166 2008-07-08 00:00 688963 4167 2008-07-08 01:00 354481 4168 2008-07-08 02:00 411555 4169 2008-07-08 03:00 325875 4170 2008-07-08 04:00 328739 4171 2008-07-08 05:00 447432 4172 2008-07-08 06:00 838585 4173 2008-07-08 07:00 1138196 4174 2008-07-08 08:00 1852437 4175 2008-07-08 09:00 68736587 4176 2008-07-08 10:00 739175 4177 2008-07-08 11:00 647451 4178 2008-07-08 12:00 702787 4179 2008-07-08 13:00 3722000 4180 2008-07-08 14:00 712481 4181 2008-07-08 15:00 475688 4182 2008-07-08 16:00 416013">@sys-stat-hist.sql Enter value for stat_name: DB time old 27: stat_end.stat_name = '&stat_name' n" title="© 2008 Convio, Inc. Example 9: system "DB time" SQL> @sys-stat-hist.sql Enter value for stat_name: DB time old 27: stat_end.stat_name = '&stat_name' n">© 2008 Convio, Inc. Example 9: system "DB time" SQL> @sys-stat-hist.sql Enter value for stat_name: DB time old 27: stat_end.stat_name = '&stat_name' new 27: stat_end.stat_name = 'DB time' SNAP_ID BEGIN_HOUR DELTA_VALUE ---------- ---------------- ----------- 4159 2008-07-07 17:00 2089225 4160 2008-07-07 18:00 1505607 4161 2008-07-07 19:00 31188489 4162 2008-07-07 20:00 24930866 4163 2008-07-07 21:00 1828924 4164 2008-07-07 22:00 1258286 4165 2008-07-07 23:00 396076 4166 2008-07-08 00:00 688963 4167 2008-07-08 01:00 354481 4168 2008-07-08 02:00 411555 4169 2008-07-08 03:00 325875 4170 2008-07-08 04:00 328739 4171 2008-07-08 05:00 447432 4172 2008-07-08 06:00 838585 4173 2008-07-08 07:00 1138196 4174 2008-07-08 08:00 1852437 4175 2008-07-08 09:00 68736587 4176 2008-07-08 10:00 739175 4177 2008-07-08 11:00 647451 4178 2008-07-08 12:00 702787 4179 2008-07-08 13:00 3722000 4180 2008-07-08 14:00 712481 4181 2008-07-08 15:00 475688 4182 2008-07-08 16:00 416013 Slide 38© 2008 Convio, Inc. DBA_HIST_SQLTEXT PK: SQL_ID (DBID) No SNAP_ID, INSTANCE_NUMBER Preserves SQL_TEXT even when found nowhere else CLOB Slide 39© 2008 Convio, Inc. DBA_HIST_SYSTEM_EVENT PK: SNAP_ID EVENT_ID (DBID, INSTANCE_NUMBER) AWR snapshots of V$SYSTEM_EVENT Includes EVENT_NAME (almost 900!) Does NOT equal sum of ASH sampling Time is clearly in units of microseconds Slide 40© 2008 Convio, Inc. DBA_HIST_SEG_STAT PK: SNAP_ID, OBJ#, DATAOBJ# (DBID, INSTANCE_NUMBER) Alternative perspective from usual SQL focus Basic stats: Logical and physical reads Block changes Wait counts: Buffer busy, row locks Use "deltas" rather than "totals" Use with DBA_HIST_SEG_STAT_OBJ Slide 41© 2008 Convio, Inc. DBA_HIST_SEG_STAT_OBJ PK: OBJ#, DATAOBJ# (DBID) Object details (name, owner, type, tablespace) Consider "natural join" Several segment types: Table, Table Partition Index, Index Partition LOB Slide 42© 2008 Convio, Inc. End-To-End Metrics DBMS_APPLICATION_INFO Session level tags: module, action, client_id, client_info Works great with: Real-time monitoring Active Session History (ASH, both tables) But not usually with AWR: DBA_HIST_SQLSTAT only, and Values only from first time parsed Slide 43© 2008 Convio, Inc. AWR settings Defaults: one hour snapshots, one week retention (month is better) To change: DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention IN NUMBER DEFAULT NULL, interval IN NUMBER DEFAULT NULL, topnsql IN NUMBER DEFAULT NULL, dbid IN NUMBER DEFAULT NULL); Check with DBA_HIST_WR_CONTROL Slide 44© 2008 Convio, Inc. Why AWR? Database that you rarely, if ever, review Find and drill down into recent load spikes Prioritize developer/DBA resources Load testing Capacity planning Real-time monitors might be: poor missing unreliable Slide 45© 2008 Convio, Inc. AWR tables mentioned DBA_HIST_ACTIVE_SESS_HISTORY DBA_HIST_SNAPSHOT DBA_HIST_SQLSTAT DBA_HIST_SYSSTAT DBA_HIST_SQLTEXT DBA_HIST_SYSTEM_EVENT DBA_HIST_SEG_STAT DBA_HIST_SEG_STAT_OBJ DBA_HIST_WR_CONTROL Slide 46© 2008 Convio, Inc. Meets various goals Find time ranges of load spikes Find specific problem SQLs ASH (load spikes) Sort by aggregated statistics Non-uniform statistics Characterize a problem over time Not just SQL, also: Segments System-level performance stats Etc. Slide 47© 2008 Convio, Inc. Conclusion: Main lessons Easy to write custom AWR queries Look at variance to find skew