{"id":1302,"date":"2007-07-29T12:00:00","date_gmt":"2007-07-29T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1302"},"modified":"2007-07-29T12:00:00","modified_gmt":"2007-07-29T12:00:00","slug":"oracle-workload-metrics","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2007\/07\/29\/oracle-workload-metrics\/","title":{"rendered":"Oracle Workload Metrics"},"content":{"rendered":"<p>I&#8217;ve been asked this question at quite a few sites so thought it would be worth blogging about. (The fact that it&#8217;s a common question also implies that any criticism is aimed at the industry rather than individual companies or colleagues.)<\/p>\n<p>Someone, usually a manager, wants to see the workload history for each instance in the company. There are several reasons why this might be useful. Here are just a few off the top of my head.<\/p>\n<ul>\n<li>To identify workload peaks and troughs so that you have a good feel for what is normal or not.<\/li>\n<li>To identify workload that&#8217;s increasing steadily and might require a future hardware upgrade.<\/li>\n<li>To help define service level agreements so that, if the workload rises above a certain threshold, all bets are off.<\/li>\n<li>Because it makes a pretty picture when you graph it in Excel.<\/li>\n<\/ul>\n<p>Statspack and AWR are ideal for this type of request but the problem is that there are so many possible statistics available to look at that the problem is defining :-<\/p>\n<ul>\n<li>What do you mean by &#8216;workload&#8217;?<\/li>\n<li>How much detail are you looking for?<\/li>\n<li>Over what time intervals?<\/li>\n<\/ul>\n<p>Really, the most important question to ask is :-<\/p>\n<blockquote><p><b>&#8216;Why?&#8217; <\/b><\/p><\/blockquote>\n<p>Why do you want this information and what is the question you&#8217;re trying to answer? Most times the <i>purpose<\/i> of the information will exclude many possible approaches. For example, you might only want to measure the workload of a specific application, which would defeat instance-wide approaches.<\/p>\n<p>You might even discover that the question being asked doesn&#8217;t make sense (at least to me or you).<\/p>\n<p>The most recent case was one of those. The requirement was for a single number per day for an entire database instance. Think about that. A single number to describe a day&#8217;s workload? What does that really tell you? Even more worrying, the information about <i>which<\/i> number might be most useful was fuzzy.<\/p>\n<p>In the end, after all the warnings have been issued (and I never hesitate in that role), the business gets what the business wants. So here are a few examples of statistics I&#8217;ve used in the past. But, as you&#8217;re reading them, consider how useful one value per day would be?<\/p>\n<ul>\n<li>Redo Size, as an indication of the volume of insert\/update\/delete activity during a given interval.<\/li>\n<li>User Commits (and possibly rollbacks) for transactions per minute\/second\/hour or whatever.<\/li>\n<li>Switching on auditing to measure i\/o per session. When I suggested this the other week, I searched the net to remind myself of the precise meaning of one of the AUD$ columns and lo-and-behold, Jonathan Lewis has <a href=\"http:\/\/www.jlcomp.demon.co.uk\/audit.html\">written about this in the past<\/a>. Which was somewhat disappointing as I&#8217;d just re-written almost the same document in order to illustrate it to a colleague and possibly blog about it &#128577; Regardless, this one has worked well for me in the past, particularly when you&#8217;re interested in the workload for a particular user account (as we were in this case).<\/li>\n<\/ul>\n<p>I could go on with further examples, but my main purpose is to illustrate<\/p>\n<ul>\n<li>The type of question that crops up in day to day DBA life.<\/li>\n<li>The compromises we face.<\/li>\n<li>The fact that Oracle usually offers many different approaches. The best will depend on the requirement.<\/li>\n<\/ul>\n<p>To me, this is what&#8217;s fun about being a DBA &#8211; developing solutions to fit people&#8217;s needs. If it was all about call-outs and database creation, I&#8217;d pack it in.<\/p>\n<p>Oh, what did we go with in the end? Well, we use <a href=\"http:\/\/www.quest.com\/foglight\/\">Quest&#8217;s Foglight tool<\/a> and one of the reports it offers is transactions per minute (I think it is, I only played a consultative role on this and stuck to the native Oracle approaches). It hardly seemed worth working on a new solution when we&#8217;ve got a report that does this already. My guess is that it just uses user commits over time.<\/p>\n<p>Oh, and I <i>still<\/i> think a single number per day is virtually meaningless &#128521;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;ve been asked this question at quite a few sites so thought it would be worth blogging about. (The fact that it&#8217;s a common question also implies that any criticism is aimed at the industry rather than individual companies or colleagues.) Someone, usually a manager, wants to see the workload history for each instance in&hellip; <a class=\"more-link\" href=\"http:\/\/orcldoug.com\/blog\/2007\/07\/29\/oracle-workload-metrics\/\">Continue reading <span class=\"screen-reader-text\">Oracle Workload Metrics<\/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-1302","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1470,"url":"http:\/\/orcldoug.com\/blog\/2009\/02\/08\/time-matters-throughput-vs-response-time\/","url_meta":{"origin":1302,"position":0},"title":"Time Matters: Throughput vs. Response Time","date":"February 8, 2009","format":false,"excerpt":"Niall Litchfield made an interesting comment in an email thread that prompted this post. \"... I can see that once you start to define workload, or transactions, in business terms (I need to get all these things done, what works best overall?) then workload response time* does make sense, both\u2026","rel":"","context":"With 27 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1417,"url":"http:\/\/orcldoug.com\/blog\/2008\/06\/08\/real-application-testing-and-more-relinking\/","url_meta":{"origin":1302,"position":1},"title":"Real Application Testing and more Relinking","date":"June 8, 2008","format":false,"excerpt":"I'd been thinking of blogging about the appearance of Real Application Testing in 10.2.0.4 since I noticed it post-upgrade, just before the course in Prague. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing optionsIt looked like a\u2026","rel":"","context":"With 9 comments","img":{"alt_text":"","src":"\/serendipity\/uploads\/xRAT_7.png.pagespeed.ic.3MsqSFp_gp.png","width":350,"height":200},"classes":[]},{"id":1395,"url":"http:\/\/orcldoug.com\/blog\/2008\/03\/25\/ash-and-the-psychology-of-hidden-parameters\/","url_meta":{"origin":1302,"position":2},"title":"ASH and the psychology of Hidden Parameters","date":"March 25, 2008","format":false,"excerpt":"Time for a quick break from the final push to complete the course slides. I've (probably foolishly) decided to apply the 10.2.0.4 patch to my test database.As I was confirming the details of when Oracle starts to flush information from the ASH Buffer to the workload repository, I thought I'd\u2026","rel":"","context":"With 5 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1498,"url":"http:\/\/orcldoug.com\/blog\/2009\/05\/10\/adaptive-thresholds-in-10g-part-3-setting-thresholds\/","url_meta":{"origin":1302,"position":3},"title":"Adaptive Thresholds in 10g &#8211; 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":1613,"url":"http:\/\/orcldoug.com\/blog\/2010\/09\/19\/alternative-pictures-demo\/","url_meta":{"origin":1302,"position":4},"title":"Alternative Pictures Demo","date":"September 19, 2010","format":false,"excerpt":"Note - features in this post require the Diagnostics Pack licenseNot long after I'd finished the last post, I realised I could reinforce the points I was making with a quick post showing another one of the example tests supplied with Swingbench - the Calling Circle (CC) application. Like the\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"https:\/\/i0.wp.com\/18.133.199.212\/wp-content\/uploads\/recovered\/190910_1.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":979,"url":"http:\/\/orcldoug.com\/blog\/2005\/04\/25\/more-on-system-managed-undo\/","url_meta":{"origin":1302,"position":5},"title":"More on System Managed Undo","date":"April 25, 2005","format":false,"excerpt":"In an earlier blog entry, I mentioned that we'd run into pretty severe undo segment corruption problems using SMU on a high-throughput database. Whilst looking for something else on Metalink, I noticed Note 301432.1 which says SymptomsSevere database performance slowdown.(Text snipped out here)CauseLarge numbers of Undo Segment onlines are being\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1302","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=1302"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1302\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1302"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1302"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1302"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}