{"id":1559,"date":"2017-05-24T16:31:11","date_gmt":"2017-05-24T14:31:11","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=1559"},"modified":"2020-08-18T16:14:27","modified_gmt":"2020-08-18T14:14:27","slug":"oracle-db-cpu-load-on-consolidated-servers","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/oracle-db-cpu-load-on-consolidated-servers\/","title":{"rendered":"Which Oracle Databases use most CPU on my server?"},"content":{"rendered":"<p><strong>Assumptions<\/strong><\/p>\n<ul>\n<li>You have many (hundreds) of instances and more than a couple of servers<\/li>\n<li>One of your servers have high CPU Load<\/li>\n<li>You have Enterprise Manager 12c but the Database Load does not filter by server<\/li>\n<li>You want to have an historical representation of the user CPU utilization, per instance<\/li>\n<\/ul>\n<p><strong>Getting the data from the EM Repository<\/strong><\/p>\n<p>With the following query, connected to the SYSMAN schema of your EM repository, you can get the hourly max() and\/or avg() of user CPU by instance and time.<\/p>\n<pre class=\"lang:plsql decode:true\">SELECT entity_name,\r\n  ROUND(collection_time,'HH') AS colltime,\r\n  ROUND(avg_value,2)\/16*100   AS avgv, -- 16 is my number of CPU\r\n  ROUND(max_value,2)\/16*100   AS maxv  -- same here\r\nFROM gc$metric_values_hourly mv\r\nJOIN em_targets t\r\nON (t.target_name         =mv.entity_name)\r\nWHERE t.host_name         ='myserver1'  -- myserver1 is the server that has high CPU Usage\r\nAND mv.metric_column_name = 'user_cpu_time_cnt' -- let's get the user cpu time\r\nAND collection_time&gt;sysdate-14  -- for the lase 14 days\r\nORDER BY entity_name,\r\n  ROUND(collection_time,'HH');<\/pre>\n<p>Suppose you select just the max value: the result will be similar to this:<\/p>\n<pre class=\"lang:plsql decode:true\">ENTITY_ COLLTIME          MAXV\r\n------- ----------------  ------\r\nmydbone\t10.05.2017 16:00  0.3125\r\nmydbone\t10.05.2017 17:00  0.1875\r\nmydbone\t10.05.2017 18:00  0.1875\r\nmydbone\t10.05.2017 19:00  0.1875\r\nmydbone\t10.05.2017 20:00  0.25\r\nmydbone\t10.05.2017 21:00  0.125\r\nmydbone\t10.05.2017 22:00  0.125\r\nmydbone\t10.05.2017 23:00  0.125\r\nmydbone\t11.05.2017 00:00  0.1875\r\nmydbone\t11.05.2017 01:00  0.125\r\nmydbone\t11.05.2017 02:00  0.1875\r\nmydbone\t11.05.2017 03:00  0.1875\r\n....                      \r\nmydbone\t23.05.2017 20:00  0.125\r\nmydbone\t23.05.2017 21:00  0.125\r\nmydbone\t23.05.2017 22:00  0.125\r\nmydbone\t23.05.2017 23:00  0.0625\r\nmydbtwo\t10.05.2017 16:00  0.3125\r\nmydbtwo\t10.05.2017 17:00  0.25\r\nmydbtwo\t10.05.2017 18:00  0.1875\r\nmydbtwo\t10.05.2017 19:00  0.1875\r\nmydbtwo\t10.05.2017 20:00  0.3125\r\nmydbtwo\t10.05.2017 21:00  0.125\r\nmydbtwo\t10.05.2017 22:00  0.125\r\nmydbtwo\t10.05.2017 23:00  0.125\r\n.....                     \r\nmydbtwo\t14.05.2017 19:00  0.125\r\nmydbtwo\t14.05.2017 20:00  0.125\r\nmydbtwo\t14.05.2017 21:00  0.125\r\nmydbtwo\t14.05.2017 22:00  0.125\r\nmydbtwo\t14.05.2017 23:00  0.125\r\ndbthree\t10.05.2017 16:00  1.1875\r\ndbthree\t10.05.2017 17:00  0.6875\r\ndbthree\t10.05.2017 18:00  0.625\r\ndbthree\t10.05.2017 19:00  0.5625\r\ndbthree\t10.05.2017 20:00  0.8125\r\ndbthree\t10.05.2017 21:00  0.5\r\ndbthree\t10.05.2017 22:00  0.4375\r\ndbthree\t10.05.2017 23:00  0.4375\r\n...\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p><strong>Putting it into excel<\/strong><\/p>\n<p>There are one million ways to do something more reusable than excel (like rrdtool scripts, gnuplot, R, name it), but Excel is just right for most people out there (including me when I feel lazy).<\/p>\n<ul>\n<li>Configure an Oracle Client and add the ODBC data source to the EM repository:<\/li>\n<\/ul>\n<p><a href=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/odbc_emrep.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1561\" src=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/odbc_emrep.png\" alt=\"odbc_emrep\" width=\"779\" height=\"702\" srcset=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/odbc_emrep.png 779w, https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/odbc_emrep-300x270.png 300w, https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/odbc_emrep-768x692.png 768w, https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/odbc_emrep-333x300.png 333w\" sizes=\"auto, (max-width: 779px) 100vw, 779px\" \/><\/a><\/p>\n<ul>\n<li>Open Excel, go to &#8220;Data&#8221; &#8211; &#8220;Connections&#8221; and add a new connection:\n<ul>\n<li>Search&#8230;<\/li>\n<li>New Source<\/li>\n<li>DSN ODBC<\/li>\n<\/ul>\n<\/li>\n<li>Select your new ODBC data source, user, password<\/li>\n<li>Uncheck &#8220;Connection to a specific table&#8221;<\/li>\n<li>Give a name and click Finish<\/li>\n<li>On the DSN -&gt; Properties -&gt; Definition, enter the SQL text I have provided previously<\/li>\n<\/ul>\n<p><a href=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/connection_properties_odbc_excel.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1562\" src=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/connection_properties_odbc_excel.png\" alt=\"connection_properties_odbc_excel\" width=\"591\" height=\"622\" srcset=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/connection_properties_odbc_excel.png 591w, https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/connection_properties_odbc_excel-285x300.png 285w\" sizes=\"auto, (max-width: 591px) 100vw, 591px\" \/><\/a><\/p>\n<p>The result should be something similar: ( but much longer :-))<\/p>\n<p><a href=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/first_step_excel.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-1563\" src=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/first_step_excel.png\" alt=\"first_step_excel\" width=\"424\" height=\"417\" srcset=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/first_step_excel.png 424w, https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/first_step_excel-300x295.png 300w, https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/first_step_excel-305x300.png 305w\" sizes=\"auto, (max-width: 424px) 100vw, 424px\" \/><\/a><strong>Pivoting the results<\/strong><\/p>\n<p>Create e new sheet and name it &#8220;pivot&#8221;, Click on &#8220;Create Pivot Table&#8221;, select your data and your dimensions:<\/p>\n<p><a href=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/pivot.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1564\" src=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/pivot.png\" alt=\"pivot\" width=\"348\" height=\"401\" srcset=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/pivot.png 348w, https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/pivot-260x300.png 260w\" sizes=\"auto, (max-width: 348px) 100vw, 348px\" \/><\/a>The result:<\/p>\n<p><strong><a href=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/pivoted.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-1565\" src=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/pivoted-1024x578.png\" alt=\"pivoted\" width=\"584\" height=\"330\" srcset=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/pivoted-1024x578.png 1024w, https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/pivoted-300x169.png 300w, https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/pivoted-768x433.png 768w, https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/pivoted-500x282.png 500w, https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/pivoted.png 1428w\" sizes=\"auto, (max-width: 584px) 100vw, 584px\" \/><\/a>Creating the Graph<\/strong><\/p>\n<p>Now that the data is correctly formatted, it&#8217;s easyy to add a graph:<\/p>\n<p>just select the entire pivot table and create a new stacked area graph.<\/p>\n<p>The result will be similar to this:<\/p>\n<p><a href=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/graph_cpu_load_excel-1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-1567\" src=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/graph_cpu_load_excel-1-1024x516.png\" alt=\"graph_cpu_load_excel\" width=\"584\" height=\"294\" srcset=\"https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/graph_cpu_load_excel-1-1024x516.png 1024w, https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/graph_cpu_load_excel-1-300x151.png 300w, https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/graph_cpu_load_excel-1-768x387.png 768w, https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/graph_cpu_load_excel-1-500x252.png 500w, https:\/\/www.ludovicocaldara.net\/dba\/wp-content\/uploads\/2017\/05\/graph_cpu_load_excel-1.png 1539w\" sizes=\"auto, (max-width: 584px) 100vw, 584px\" \/><\/a><\/p>\n<p>With such graph, it is easy to spot which databases consumed most CPU on the system in a defined period, and to track the progress if you start a &#8220;performance campaign&#8221;.<\/p>\n<p>For example, you can see that the &#8220;green&#8221; and &#8220;red&#8221; databases were consuming constantly some CPU up to 17.05.2017 and then some magic solved the CPU problem for those instances.<\/p>\n<p>It is also quite convenient for checking the results of new instance caging settings&#8230;<\/p>\n<p>The resulting CPU will not necessarily be 100%: the SYS CPU time is not included, as well as the user CPU of all the other processes that are either not DB or not monitored with Enterprise Manager.<\/p>\n<p>HTH<\/p>\n<p>&#8212;<\/p>\n<p>Ludovico<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Assumptions You have many (hundreds) of instances and more than a couple of servers One of your servers have high CPU Load You have Enterprise Manager 12c but the Database Load does not filter by server You want to have &hellip; <a href=\"https:\/\/www.ludovicocaldara.net\/dba\/oracle-db-cpu-load-on-consolidated-servers\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[326,3,330,6,132],"tags":[],"class_list":["post-1559","post","type-post","status-publish","format-standard","hentry","category-oracle","category-oracledb","category-oracle-inst-upg","category-perf","category-triblog"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1559","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=1559"}],"version-history":[{"count":4,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1559\/revisions"}],"predecessor-version":[{"id":1571,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1559\/revisions\/1571"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=1559"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=1559"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=1559"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}