{"id":1496,"date":"2009-05-07T12:00:00","date_gmt":"2009-05-07T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1496"},"modified":"2009-05-07T12:00:00","modified_gmt":"2009-05-07T12:00:00","slug":"adaptive-thresholds-in-10g-part-1-metric-baselines","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2009\/05\/07\/adaptive-thresholds-in-10g-part-1-metric-baselines\/","title":{"rendered":"Adaptive Thresholds in 10g – Part 1 (Metric Baselines)"},"content":{"rendered":"

Some features in this post require a Diagnostics Pack license.<\/em><\/p>\n

[I really didn’t want to get into another multi-part blog post, but this has grown longer than I hoped, so I’ll split it up …]<\/p>\n

One of the more interesting components of the 10gR2 instrumentation improvements is the use of Metric Baselines and Adaptive Thresholds by OEM DB\/Grid Control to generate alerts. (There\u00a0are further\u00a0improvements\u00a0in 11g but I\u2019ll address those in a later post.)<\/p>\n

Although these features have been designed to be extremely simple to implement without understanding the mechanics, I suspect that most Oracle DBAs still struggle with the concept of implementing something without having some idea how it works (at least I hope so). There are some\u00a0good technical documents floating around out there (see References below), but I feel they’re at one end of the technical spectrum or the other, so this and the next post are intended to bridge the gap as well as showing the features in use. (Harder than you might think, but I’ll come back to that.)<\/p>\n

How can we use Metric Baselines and Adaptive Thresholds? I\u2019ll try to put it as simply as possible :-<\/p>\n

\u2022\u00a0Generate a baseline of how a system looks during \u2018normal\u2019 operation.
\u2022\u00a0Generate alerts when specific metrics exceed threshold values relative to that baseline.<\/p>\n

There\u2019s quite a bit more to it than that, as I hope will become apparent.<\/p>\n

But first, a warning. This is not about system optimisation \u2013 that happened during the\u00a0design, development, test and initial implementation phases, right?\u00a0\ud83d\ude09\u00a0\u2013 but about monitoring and capturing unexpected variance in performance indicators. Because even with an optimised application and hardware infrastructure\u00a0 that generally meets requirements … <\/p>\n

\u2022\u00a0\u2026 a new Execution Plan could be produced for a SQL statement because of an overnight change in object statistics.
\u2022\u00a0\u2026 a failed cache battery somewhere in the storage infrastructure could cause a sudden increase in I\/O times.
\u2022\u00a0\u2026 you could encounter a larger number of concurrent users than the system has handled before or was designed for.<\/p>\n

I’ve seen all of these and more and I\u2019m sure you\u2019ve experienced plenty of your own. So there’s no denying that it\u2019s important to implement an optimised system first and foremost but, even when you\u2019ve done so, something will go wrong one day. I\u2019m probably making up my own phrases for things again, but I think these tools cover the areas of Performance Monitoring and Troubleshooting, rather than Performance Tuning (or Optimisation).<\/p>\n

For existing Production systems, I\u2019d like a facility that detects performance anomalies in the single database instance that has a problem right now (out of the hundreds I\u2019m managing) and notifies me so that I can focus my attention where it\u2019s required.<\/p>\n

You could argue that such a facility already exists – called Users<\/u>. They will<\/em> generally detect serious performance anomalies and they will<\/em> notify us using the tried and trusted telephone. If this happens too frequently, a further indicator would be the endless crisis meetings with white boards and wildly differing theories. However, it would be nice if I knew there was a problem before someone had to tell me. Users also don\u2019t tend to be very good at reporting overnight batch throughput problems until the next day!<\/p>\n

Metric Baselines<\/strong>
I’ll dig into Metric Baselines first because<\/p>\n

1) Defining a Baseline is the first thing you have to do.
2) Whilst configuring the Baseline might initially seem the simplest step, it’s the foundation on which the threshold alerts depend. (That’s why Oracle has tried to simplify it for busy DBAs.)<\/p>\n

There are really two types of Baselines, which differ in the way the time period is defined; the way\u00a0statistics are calculated and, consequently, their suitability for different uses.<\/p>\n

A Moving Window Baseline<\/strong> uses recent data from the AWR repository over 7, 21, 35 or 91 days. As each day passes, the window on the data progresses forward by one day. The statistics are re-calculated on a regular basis (possibly as frequently as every hour depending on the Time Grouping). The effect of this is that the statistics change to reflect the recent workload and performance characteristics of the system. <\/p>\n

A Static Baseline<\/strong> uses AWR data from a user-defined period which must be at least 7 days long. The statistics are calculated once, when you define the baseline, and are used forever until you switch to a new baseline. So Static Baselines don’t<\/em> make any allowance for the change in a system’s work profile over time. If you have a steadily increasing number of concurrent users, you will eventually reach a stage where the system is alerting regularly because the work profile is so much greater than the Baseline.<\/p>\n

Which is best for you depends on the characteristics of the system you’re managing and what you’re trying to achieve. I’d suggest that you need a Moving Window Baseline in most cases, unless you have very strict performance requirements and a very stable system that you don’t expect to change over time.<\/p>\n

One situation that almost demands the use of Static Baselines is\u00a0playing around\u00a0with this on your own setup at home. (In fact,\u00a0this is just the first of\u00a0a few difficulties I’ve faced playing around with this stuff because a single-user laptop is not the design target!) Think about it.\u00a0For a Moving Window Baseline to make any sense, your system has to have been processing\u00a0a ‘normal’ workload for\u00a0at least the past 7\u00a0days, which is pretty unlikely on a laptop\u00a0I switch off each night 😉 The design expects systems to be active on a more or less continuous basis, as most business systems are. So, in order to give me Metric Baseline statistics\u00a0that I could re-use in future without needing\u00a0ongoing continuous\u00a0activity, I created a Static Metric Baseline covering last week. Why last week? Well, that comes to the next difficulty I faced. The Baseline period must have included enough activity on which to base the statistical computations used (see next post). Most weeks there probably wouldn’t have been sufficient data on which to base the computations, but my laptop was more active during a week when I was teaching the course for two days and preparing in the evenings.<\/p>\n

The best way to show you what I mean is to create a new Static Metric Baseline. First click on the Metric Baselines link at the bottom of various pages (e.g. Database Home page, Performance Page). If you don’t have Baselines enabled, you’ll be prompted to confirm that you want to enable them. When you click yes, DB\/Grid Control will set the value of the _awr_flush_threshold_metrics hidden parameter to TRUE. (Unfortunately, this is thrown as a compliance error in DB Control 10.2.0.3 \u2013 see this <\/em>Metalink Forum Thread<\/a>\u00a0 relating to bug number 4749372<\/em>)<\/p>\n

There are 135 metrics in Oracle 10.2.0.4 (including some old friends like the Buffer Cache Hit Ratio!), which you can see by querying the V$SYSMETRIC_HISTORY view. e.g. <\/p>\n<\/p>\n

SQL> select group_name, metric_name, metric_unit from dba_hist_metric_name\n\u00a0 2* where metric_name like 'B%'\n
\nGROUP_NAME\n----------------------------------------------------------------\nMETRIC_NAME\n----------------------------------------------------------------\nMETRIC_UNIT\n----------------------------------------------------------------\nSystem Metrics Short Duration\nBuffer Cache Hit Ratio\n% (LogRead - PhyRead)\/LogRead<\/p>
\nSession Metrics Long Duration\nBlocked User Session Count\nSessions<\/p>
\nSystem Metrics Long Duration\nBranch Node Splits Per Txn\nSplits Per Txn<\/p>
\nSystem Metrics Long Duration\nBranch Node Splits Per Sec\nSplits Per Second<\/p>
\nSystem Metrics Long Duration\nBackground Checkpoints Per Sec\nCheck Points Per Second<\/p>
\nSystem Metrics Long Duration\nBuffer Cache Hit Ratio\n% (LogRead - PhyRead)\/LogRead<\/p>
\n6 rows selected.<\/p><\/pre>\n<\/p>\n
but there are only 15 used by Metric Baselines and for which you can set Adaptive Thresholds, which are persisted in the AWR repository when _awr_flush_threshold_metrics=TRUE<\/p>\n
SQL> select distinct metric_name from DBA_HIST_SYSMETRIC_HISTORY\n\u00a0 2\u00a0 order by metric_name;\n\n\n\nMETRIC_NAME\n----------------------------------------------------------------\nCurrent Logons Count\nDB Block Changes Per Txn\nDatabase Time Per Sec\nEnqueue Requests Per Txn\nExecutions Per Sec\nLogical Reads Per Txn\nNetwork Traffic Volume Per Sec\nPhysical Reads Per Sec\nPhysical Writes Per Sec\nRedo Generated Per Sec\nResponse Time Per Txn\nSQL Service Response Time\nTotal Parse Count Per Txn\nUser Calls Per Sec\nUser Transaction Per Sec
<\/p>
15 rows selected.<\/p><\/pre>\n<\/p>\n
\n
Actually, there are a further 7 metrics persisted in that view if you also have _awr_flush_workload_metrics=TRUE, although enabling Baselines doesn’t do this by default, you won’t be able to set\u00a0adaptive thresholds for these metrics\u00a0and you shouldn’t be changing the value of hidden parameters anyway … but worth mentioning should you ever see 22 rows returned by the previous query!<\/p>\n
METRIC_NAME\n----------------------------------------------------------------\nDB Block Changes Per User Call\nDB Block Gets Per User Call\nExecutions Per User Call\nLogical Reads Per User Call\nTotal Sorts Per User Call\nTotal Table Scans Per User Call\nUser Calls Per Txn
<\/p>
7 rows selected.<\/p><\/pre>\n<\/blockquote>\n
When you’ve enabled Baselines …<\/p>\n
\"\"<\/p>\n
you need to click the ‘Manage Static Metric Baselines’ link at the bottom of the screen. (No, you won’t find me disagreeing that the OEM web interface can be a frustrating search for the right screen\/link\/button\/breadcrumb, but I’ve sort of got used to it because of some of the cool stuff it can do.) Click the, erm, Create button to create a new baseline and you’ll see this screen. <\/p>\n
\"\"
Give it a name, pick a period of at least 7 days and then, if you want, you can just click OK and your Static Metric Baseline is created. The statistics will be computed and you’ll be able to see your new baseline in the dictionary.<\/p>\n
SQL> select name, type, status from dbsnmp.MGMT_BSLN_BASELINES;
<\/p>
NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 T\n---------------------------------------------------------------- -\nSTATUS\n----------------\nDoug Test\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 S\nINACTIVE<\/p><\/pre>\n<\/p>\n
(Note that it’s INACTIVE and the type is ‘S’ for Static.)<\/p>\n
But that simple creation of a Static Metric Baseline ignored the ‘Time Grouping’ and ‘Statistics Preview’ sections that I, and I suspect others, have found a little confusing at first. So they deserve a post of their own – the next one …<\/p>\n
References<\/strong><\/p>\n
Although the volume of information out there isn’t great, the quality of some of it is, so here’s some further reading if you’re interested in the subject.<\/p>\n
The Documentation<\/a><\/p>\n
“Metric Baselines: Detecting Unusual Performance Events Using System-Level Metrics in EM 10gR2”. John Beresniewicz’s White Paper. Seriously good stuff, but JB will need to post it at Ashmasters.com 😉 Updated later – thanks to JB for letting me host the document here<\/a>.<\/strong><\/p>\n
Arup Nanda article on OTN<\/a><\/p>\n
Cary Millsap blog post<\/a>\u00a0discussing the underlying concept, rather than this particular implementation. In the post, he mentions a couple of other resources …<\/p>\n
CMG Paper<\/a>\u2013 (Note also the discussion about Treemaps – blog post about that coming up later)<\/p>\n
Robyn Sands on Variance<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
Some features in this post require a Diagnostics Pack license. [I really didn’t want to get into another multi-part blog post, but this has grown longer than I hoped, so I’ll split it up …] One of the more interesting components of the 10gR2 instrumentation improvements is the use of Metric Baselines and Adaptive Thresholds… Continue reading Adaptive Thresholds in 10g – Part 1 (Metric Baselines)<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1496","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1497,"url":"http:\/\/orcldoug.com\/blog\/2009\/05\/07\/adaptive-thresholds-in-10g-part-2-time-grouping\/","url_meta":{"origin":1496,"position":0},"title":"Adaptive Thresholds in 10g – Part 2 (Time Grouping)","date":"May 7, 2009","format":false,"excerpt":"Some features in this post require a Diagnostics Pack license.Metric Baselines were designed to be easy to implement. There are only two options :-1) Pick how much recent\u00a0activity you want to use and let Oracle recompute the statistics based on the most recent metric values over time. (Moving Window) 2)\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"\/serendipity\/uploads\/640x377xoem3.jpg.pagespeed.ic.P-CkJpYqsK.jpg","width":350,"height":200},"classes":[]},{"id":1498,"url":"http:\/\/orcldoug.com\/blog\/2009\/05\/10\/adaptive-thresholds-in-10g-part-3-setting-thresholds\/","url_meta":{"origin":1496,"position":1},"title":"Adaptive Thresholds in 10g – Part 3 (Setting Thresholds)","date":"May 10, 2009","format":false,"excerpt":"Some features in this post require a Diagnostics Pack license.One of my favourite aspects of blogging is that if I were to write a proper article or conference paper, I'd be likely to fix any problems with it as I go (well, the ones I notice!). With blogging, it's more\u2026","rel":"","context":"With 7 comments","img":{"alt_text":"","src":"https:\/\/i0.wp.com\/18.133.199.212\/wp-content\/uploads\/recovered\/oem7.jpg?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1713,"url":"http:\/\/orcldoug.com\/blog\/2014\/07\/07\/recurring-conversations-awr-intervals-part-1\/","url_meta":{"origin":1496,"position":2},"title":"Recurring Conversations: AWR Intervals (Part 1)","date":"July 7, 2014","format":false,"excerpt":"I've seen plenty of blog posts and discussions over the years about the need to increase the default AWR retention period beyond the default value of 8 days. Experienced Oracle folk understand how useful it is to have a longer history of performance metrics to cover an entire workload period\u2026","rel":"","context":"With 6 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1504,"url":"http:\/\/orcldoug.com\/blog\/2009\/06\/28\/i-love-addm\/","url_meta":{"origin":1496,"position":3},"title":"I Love ADDM","date":"June 28, 2009","format":false,"excerpt":"Some features in this post require a Diagnostics Pack license.I'll get back to adaptive thresholds at some point but something's been bugging me.I'm not just trying to be controversial but I've a feeling I'm about to be, given that ADDM is the probably the most infamous of the 10g 'Automatic\u2026","rel":"","context":"With 8 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1460,"url":"http:\/\/orcldoug.com\/blog\/2008\/12\/05\/ukoug-day-3\/","url_meta":{"origin":1496,"position":4},"title":"UKOUG Day 3","date":"December 5, 2008","format":false,"excerpt":"After more work on the presentation and some very strange sleeping hours thanks to the raucuous ranks on Broad Street, it was time for the first of my presentations. But before that I wanted to attend JB's \"Proactive Detection of Oracle Performance Events using Adaptive Thresholds\" (skipped), the always entertaining\u2026","rel":"","context":"With 14 comments","img":{"alt_text":"","src":"\/serendipity\/uploads\/lion.jpg","width":350,"height":200},"classes":[]},{"id":1720,"url":"http:\/\/orcldoug.com\/blog\/2014\/08\/05\/get-up-offa-that-thing\/","url_meta":{"origin":1496,"position":5},"title":"Get Up Offa That Thing","date":"August 5, 2014","format":false,"excerpt":"No, no, no ... not *that* JB! As regular readers will know, the JB who tends to get mentioned most often around these parts is John Beresniewicz who, up until recently, worked at Oracle on all the cool OEM Performance Pages and related instrumentation (alongside others, of course, such as\u2026","rel":"","context":"With 1 comment","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1496","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=1496"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1496\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1496"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1496"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1496"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}