{"id":329,"date":"2010-09-13T16:28:43","date_gmt":"2010-09-13T21:28:43","guid":{"rendered":"http:\/\/appcrawler.com\/wordpress\/?p=329"},"modified":"2011-07-06T10:34:09","modified_gmt":"2011-07-06T15:34:09","slug":"who-is-the-big-load-on-my-server","status":"publish","type":"post","link":"http:\/\/appcrawler.com\/wordpress\/2010\/09\/13\/who-is-the-big-load-on-my-server\/","title":{"rendered":"Who is the big load on my server?!"},"content":{"rendered":"<p>What is below will show SQL statements for a given window (the last four days in the example below) that are tightly correlated with load average on a server.  In general, a statistical correlation greater than 60 means there is an increasing degree of correlation between the two data sets.  If one goes up, so does the other.<\/p>\n<p>It ignores PL\/SQL blocks, so it only focuses on SELECT, INSERT, UPDATE, and DELETE statements.<\/p>\n<p>FWIW, an excellent write up on load average can be found at <a href=\"http:\/\/www.linuxjournal.com\/article\/9001\" target=\"_blank\">http:\/\/www.linuxjournal.com\/article\/9001.<\/a>  A hint is that it is not always user CPU utilization that drives up load average.  It is often high I\/O, or poor average I\/O response time that is the issue.<\/p>\n<pre lang=\"plsql\" line=\"1\">\r\nset serveroutput on size unlimited\r\ndeclare\r\n  cursor top_sql_execs is\r\n    select distinct sql_id\r\n      from (select dhss.sql_id,iowait_delta\r\n              from dba_hist_sqlstat dhss, dba_hist_sqltext dhst\r\n              where snap_id &gt;= (select min(snap_id)\r\n                                  from dba_hist_snapshot\r\n                                  where begin_interval_time > sysdate - 4)\r\n                and dhss.sql_id = dhst.sql_id\r\n                and lower(sql_text) not like 'begin%'\r\n              order by 2 desc)\r\n      where rownum <= 10;\r\n  l_corr number;\r\nbegin\r\n  for cur in top_sql_execs loop\r\n    select round((corr(val1,val2) * 100),0)\r\n      into l_corr\r\n      from (select snap_id,\r\n                   instance_number,\r\n                   iowait_delta val1\r\n              from dba_hist_sqlstat\r\n              where sql_id = cur.sql_id) a,\r\n           (select snap_id,\r\n                   instance_number,\r\n                   value val2\r\n              from dba_hist_osstat\r\n              where stat_name = 'LOAD') b\r\n      where a.snap_id = b.snap_id\r\n        and a.instance_number = b.instance_number;\r\n    dbms_output.put_line(cur.sql_id || ' ' || l_corr);\r\n  end loop;\r\nend;\r\n\/\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>What is below will show SQL statements for a given window (the last four days in the example below) that are tightly correlated with load average on a server. In general, a statistical correlation greater than 60 means there is&hellip;<\/p>\n<p class=\"more-link-p\"><a class=\"more-link\" href=\"http:\/\/appcrawler.com\/wordpress\/2010\/09\/13\/who-is-the-big-load-on-my-server\/\">Read more &rarr;<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"footnotes":""},"categories":[19,28,27,22],"tags":[],"_links":{"self":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/329"}],"collection":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/comments?post=329"}],"version-history":[{"count":20,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/329\/revisions"}],"predecessor-version":[{"id":1373,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/329\/revisions\/1373"}],"wp:attachment":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/media?parent=329"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/categories?post=329"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/tags?post=329"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}