{"id":1168,"date":"2013-06-11T16:10:17","date_gmt":"2013-06-11T14:10:17","guid":{"rendered":"http:\/\/www.oradba.ch\/?p=1168"},"modified":"2013-06-11T16:18:21","modified_gmt":"2013-06-11T14:18:21","slug":"pivot-query-on-automatic-workload-repository","status":"publish","type":"post","link":"https:\/\/www.oradba.ch\/wordpress\/2013\/06\/pivot-query-on-automatic-workload-repository\/","title":{"rendered":"Pivot query on Automatic Workload Repository"},"content":{"rendered":"<p>I&#8217;ve just tried to get a few information from the Automatic Workload Repository (AWR). I actually wanted to put together an overview of various system metrics from <a href=\"http:\/\/docs.oracle.com\/cd\/E18283_01\/server.112\/e17110\/statviews_4059.htm\" title=\"DBA_HIST_SYSMETRIC_SUMMARY\">DBA_HIST_SYSMETRIC_SUMMARY<\/a> to create a chart. Unfortunately the data is stored as name\/value pairs and not in columns. So it&#8217;s time again to convert rows to columns. There are several more or less difficult solutions for any kind of oracle release. Enclosed I&#8217;ll show a simple variant for Oracle 11g with PIVOT.<\/p>\n<p>In my query I&#8217;m interested in the metrics <em><a href=\"http:\/\/docs.oracle.com\/cd\/E24628_01\/em.121\/e25160\/oracle_database.htm#sthref872\" title=\"Current Logons Count\" target=\"_blank\">Current Logons Count<\/a><\/em>, <em><a href=\"http:\/\/docs.oracle.com\/cd\/E24628_01\/em.121\/e25160\/oracle_database.htm#sthref875\" title=\"Process Limit Usage (%)\" target=\"_blank\">Process Limit Usage (%)<\/a><\/em> and <em><a href=\"http:\/\/docs.oracle.com\/cd\/E24628_01\/em.121\/e25160\/oracle_database.htm#sthref876\" title=\"Session Limit Usage (%)\" target=\"_blank\">Session Limit Usage (%)<\/a><\/em>. A description of these and other metrics can be found in the EM 12c online documentation <a href=\"http:\/\/docs.oracle.com\/cd\/E24628_01\/em.121\/e25160\/toc.htm\" title=\"Database-Related Metrics Reference Manual\" target=\"_blank\">Database-Related Metrics Reference Manual<\/a>. The AWR view <a href=\"http:\/\/docs.oracle.com\/cd\/E18283_01\/server.112\/e17110\/statviews_4059.htm\" title=\"DBA_HIST_SYSMETRIC_SUMMARY\" target=\"_blank\">DBA_HIST_SYSMETRIC_SUMMARY<\/a> is explained in the <a href=\"http:\/\/docs.oracle.com\/cd\/E18283_01\/server.112\/e17110\/toc.htm\" title=\"Oracle Database Reference\" target=\"_blank\">Oracle Database Reference<\/a>.<\/p>\n<p>Simple query with name \/ value pairs.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">\r\ncol  METRIC_NAME for a25\r\nalter session set nls_date_format=&#039;DD.MM.YYYY HH24:MI:SS&#039;;\r\nselect\r\n    SNAP_ID, \r\n    END_TIME,\r\n    METRIC_NAME, \r\n    MAXVAL \r\nfrom \r\n    DBA_HIST_SYSMETRIC_SUMMARY \r\nwhere \r\n    METRIC_NAME in (&#039;Current Logons Count&#039;,&#039;Process Limit %&#039;,&#039;Session Limit %&#039;)\r\norder by SNAP_ID;\r\n<\/pre>\n<pre class=\"EnlighterJSRAW\">\r\n   SNAP_ID END_TIME            METRIC_NAME                   MAXVAL\r\n---------- ------------------- ------------------------- ----------\r\n       589 03.06.2013 00:21:44 Process Limit %           31,7142857\r\n       589 03.06.2013 00:21:44 Current Logons Count             110\r\n       589 03.06.2013 00:21:44 Session Limit %           22,2222222\r\n       590 03.06.2013 01:20:44 Process Limit %           14,8571429\r\n       590 03.06.2013 01:20:44 Current Logons Count              51\r\n       590 03.06.2013 01:20:44 Session Limit %           11,8055556\r\n       591 03.06.2013 02:21:44 Session Limit %           11,6319444\r\n       591 03.06.2013 02:21:44 Process Limit %           14,2857143\r\n       591 03.06.2013 02:21:44 Current Logons Count              49\r\n       592 03.06.2013 03:20:44 Current Logons Count              47\r\n       592 03.06.2013 03:20:44 Process Limit %           13,7142857\r\n       592 03.06.2013 03:20:44 Session Limit %           11,1111111\r\n...\r\n<\/pre>\n<p>Lets query the same data with a PIVOT query.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">\r\nselect \r\n    *\r\nfrom \r\n    (select \r\n        SNAP_ID,\r\n        END_TIME,\r\n        METRIC_NAME,\r\n        MAXVAL\r\n    from \r\n        DBA_HIST_SYSMETRIC_SUMMARY)\r\npivot\r\n   ( max(MAXVAL) \r\n   for METRIC_NAME in (&#039;Current Logons Count&#039; ,&#039;Process Limit %&#039;,&#039;Session Limit %&#039;))\r\norder by SNAP_ID;\r\n<\/pre>\n<pre class=\"EnlighterJSRAW\">\r\n   SNAP_ID END_TIME            &#039;Current Logons Count&#039; &#039;Process Limit %&#039; &#039;Session Limit %&#039;\r\n---------- ------------------- ---------------------- ----------------- -----------------\r\n       589 03.06.2013 00:21:44                    110        31,7142857        22,2222222\r\n       590 03.06.2013 01:20:44                     51        14,8571429        11,8055556\r\n       591 03.06.2013 02:21:44                     49        14,2857143        11,6319444\r\n       592 03.06.2013 03:20:44                     47        13,7142857        11,1111111\r\n       593 03.06.2013 04:21:44                     47        13,7142857           10,9375\r\n       594 03.06.2013 05:21:44                     49        14,2857143        11,2847222\r\n       595 03.06.2013 06:21:44                     48                14        11,2847222\r\n       596 03.06.2013 07:21:44                     49        14,2857143        11,2847222\r\n       597 03.06.2013 08:21:44                     48                14        11,2847222\r\n       598 03.06.2013 09:21:45                     49        14,2857143        11,2847222\r\n       599 03.06.2013 10:21:45                     47        13,7142857           10,9375\r\n...\r\n<\/pre>\n<p>Et voil\u00e0, the result can now be used to create a fancy excel chart. <\/p>\n<p>Other solutions with DECODE, CASE or SYS_CONNECT_BY_PATH are also possible. These would even run on older Oracle releases. <\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;ve just tried to get a few information from the Automatic Workload Repository (AWR). I actually wanted to put together an overview of various system metrics from DBA_HIST_SYSMETRIC_SUMMARY to create a chart. Unfortunately the data is stored as name\/value pairs and not in columns. So it&#8217;s time again to convert rows to columns. There are [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[8,41],"tags":[80,82,81,79,18],"class_list":["post-1168","post","type-post","status-publish","format-standard","hentry","category-11gr2","category-enterprise-manager","tag-automatic-workload-repository","tag-hints","tag-pivot","tag-sql","tag-trivadiscontent"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p1aErb-iQ","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":2456,"url":"https:\/\/www.oradba.ch\/wordpress\/2017\/12\/oracle-unified-directory-on-docker\/","url_meta":{"origin":1168,"position":0},"title":"Oracle Unified Directory on Docker","author":"Stefan","date":"13. December 2017","format":false,"excerpt":"A bit a while ago I've started to use Docker for miscellaneous purposes. Not really an early adopter, but I still hope I caught the train just in time. :-) In one of my customer project, I did have to set up a couple of OUD instance to develop and\u2026","rel":"","context":"In &quot;Docker&quot;","block_context":{"text":"Docker","link":"https:\/\/www.oradba.ch\/wordpress\/category\/docker\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/OUD_Moby-300x209.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":10598,"url":"https:\/\/www.oradba.ch\/wordpress\/2022\/06\/uncover-free-hand-drawn-like-sketches-for-dbas\/","url_meta":{"origin":1168,"position":1},"title":"Uncover free hand-drawn like sketches for DBAs","author":"Stefan","date":"9. June 2022","format":false,"excerpt":"As you could see in my blog post as well as presentations of the last months, I use Excalidraw for drawing hand-drawn like diagrams for a while now. A colleague at work made me aware of the tool. A colleague at work made me aware of the tool. And since\u2026","rel":"","context":"In &quot;Good Practice&quot;","block_context":{"text":"Good Practice","link":"https:\/\/www.oradba.ch\/wordpress\/category\/good-practice\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/dbpitr.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/dbpitr.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/dbpitr.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/dbpitr.png?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/dbpitr.png?resize=1050%2C600&ssl=1 3x, https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/dbpitr.png?resize=1400%2C800&ssl=1 4x"},"classes":[]},{"id":236,"url":"https:\/\/www.oradba.ch\/wordpress\/2010\/11\/metalink-notes-some-favorites\/","url_meta":{"origin":1168,"position":2},"title":"Metalink Notes, some favorites","author":"Stefan","date":"29. November 2010","format":false,"excerpt":"If time permits, I try to have a look once a day at the Knowledge Articles listed on my My Oracle Support (MOS) Dashboard. I've configured my Dashboard to show recently updated knowledge articles and alerts. Before Metalink was migrated to the flash based My Oracle Support, it was possible\u2026","rel":"","context":"In &quot;Oracle Database&quot;","block_context":{"text":"Oracle Database","link":"https:\/\/www.oradba.ch\/wordpress\/category\/oracle-database\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1768,"url":"https:\/\/www.oradba.ch\/wordpress\/2014\/06\/enterprise-manager-cloud-control-12c-release-4\/","url_meta":{"origin":1168,"position":3},"title":"Enterprise Manager Cloud Control 12c Release 4","author":"Stefan","date":"4. June 2014","format":false,"excerpt":"A bit less than a year after Oracle Enterprise Manager Cloud Control 12c Release 3 has been released, Oracle has now released the latest version of its Enterprise Manager Cloud Control. The new release is immediately available for all supported platforms on OTN Oracle Enterprise Manager downloads or via the\u2026","rel":"","context":"In &quot;Bundle Patch&quot;","block_context":{"text":"Bundle Patch","link":"https:\/\/www.oradba.ch\/wordpress\/category\/patches\/bp\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":595,"url":"https:\/\/www.oradba.ch\/wordpress\/2011\/06\/oracle-audit-performance-guide\/","url_meta":{"origin":1168,"position":4},"title":"Oracle Audit Performance Guide","author":"Stefan","date":"21. June 2011","format":false,"excerpt":"What happens to my database when switching on Oracle standard audit? Should set AUDIT_TRAIL to the DB or to OS or XML? To answer the last question we have to consider the safe storage and analysis of audit data. Is that all? What about Performance?","rel":"","context":"In &quot;Audit&quot;","block_context":{"text":"Audit","link":"https:\/\/www.oradba.ch\/wordpress\/category\/audit\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3065,"url":"https:\/\/www.oradba.ch\/wordpress\/2019\/05\/soug-day-2019-oracle-database-in-docker\/","url_meta":{"origin":1168,"position":5},"title":"SOUG Day 2019 &#8211; Oracle Database in Docker","author":"Stefan","date":"22. May 2019","format":false,"excerpt":"Today I did have the opportunity to give a presentation on Oracle Database in Docker at the SOUG day in Olten. It was a great opportunity to discuss how Oracle database engineering can be simplified using Docker. Besides the demo the following topics were discussed: Docker images, container and volumes\u2026","rel":"","context":"In &quot;18c&quot;","block_context":{"text":"18c","link":"https:\/\/www.oradba.ch\/wordpress\/category\/oracle-database\/18c\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/D7KVdlnXsAAg0aX-300x225.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]}],"_links":{"self":[{"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/posts\/1168","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/comments?post=1168"}],"version-history":[{"count":12,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/posts\/1168\/revisions"}],"predecessor-version":[{"id":1179,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/posts\/1168\/revisions\/1179"}],"wp:attachment":[{"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/media?parent=1168"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/categories?post=1168"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/tags?post=1168"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}