{"id":466,"date":"2013-08-29T06:01:35","date_gmt":"2013-08-29T06:01:35","guid":{"rendered":"http:\/\/oranotes.tuktaroff.com\/?page_id=466"},"modified":"2013-08-29T06:01:35","modified_gmt":"2013-08-29T06:01:35","slug":"resource-intensive-sqls","status":"publish","type":"page","link":"http:\/\/oranotes.tuktaroff.com\/?page_id=466","title":{"rendered":"Resource intensive SQLs"},"content":{"rendered":"<p><code><br \/>\n<script type=\"text\/javascript\"> \nfunction copy(what) {\n    window.clipboardData.setData('Text',what.value);\n}\nfunction paste(what) {\n    what.value = window.clipboardData.getData('Text');\n}\n<\/script><\/p>\n<h2>\nTop 20 resource intensive SQLs for today<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nSELECT * \r\nFROM (SELECT RANK () OVER (PARTITION BY \"Snap Day\" ORDER BY \"CPU Time\" \r\n+ \"Disk Reads\" \r\n+ \"Buffer Gets\" \r\n+ \"Writes\" \r\n+ \"Sorts\" \r\n+ \"Parses\" DESC) AS \"Rank\", \r\ni1.* \r\nFROM (SELECT TO_CHAR (hs.begin_interval_time, \r\n'MM\/DD\/YY' \r\n) \"Snap Day\", \r\nshs.sql_id \"Sql id\", \r\nREPLACE \r\n(CAST \r\n(DBMS_LOB.SUBSTR (sht.sql_text, 40) AS VARCHAR (40) \r\n), \r\nCHR (10), \r\n'' \r\n) \"Sql\", \r\nSUM (shs.executions_delta) \"Execs\", \r\nROUND ( (SUM (shs.elapsed_time_delta) \/ 1000000) \r\n\/ SUM (shs.executions_delta), \r\n1 \r\n) \"Time Ea Sec\", \r\nROUND ( (SUM (shs.cpu_time_delta) \/ 1000000) \r\n\/ SUM (shs.executions_delta), \r\n1 \r\n) \"CPU Ea Sec\", \r\nROUND ( (SUM (shs.iowait_delta) \/ 1000000) \r\n\/ SUM (shs.executions_delta), \r\n1 \r\n) \"IO\/Wait Ea Sec\", \r\nSUM (shs.cpu_time_delta) \"CPU Time\", \r\nSUM (shs.disk_reads_delta) \"Disk Reads\", \r\nSUM (shs.buffer_gets_delta) \"Buffer Gets\", \r\nSUM (shs.direct_writes_delta) \"Writes\", \r\nSUM (shs.parse_calls_delta) \"Parses\", \r\nSUM (shs.sorts_delta) \"Sorts\", \r\nSUM (shs.elapsed_time_delta) \"Elapsed\" \r\nFROM dba_hist_sqlstat shs INNER JOIN dba_hist_sqltext sht \r\nON (sht.sql_id = shs.sql_id) \r\nINNER JOIN dba_hist_snapshot hs \r\nON (shs.snap_id = hs.snap_id) \r\nHAVING SUM (shs.executions_delta) > 0 \r\nGROUP BY shs.sql_id, \r\nTO_CHAR (hs.begin_interval_time, 'MM\/DD\/YY'), \r\nCAST (DBMS_LOB.SUBSTR (sht.sql_text, 40) AS VARCHAR (40) \r\n) \r\nORDER BY \"Snap Day\" DESC) i1 \r\nORDER BY \"Snap Day\" DESC) \r\nWHERE \"Rank\" <= 20 AND \"Snap Day\" = TO_CHAR (SYSDATE-1, 'MM\/DD\/YY'); \r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nSELECT *<br \/>\nFROM (SELECT RANK () OVER (PARTITION BY \"Snap Day\" ORDER BY \"CPU Time\"<br \/>\n+ \"Disk Reads\"<br \/>\n+ \"Buffer Gets\"<br \/>\n+ \"Writes\"<br \/>\n+ \"Sorts\"<br \/>\n+ \"Parses\" DESC) AS \"Rank\",<br \/>\ni1.*<br \/>\nFROM (SELECT TO_CHAR (hs.begin_interval_time,<br \/>\n'MM\/DD\/YY'<br \/>\n) \"Snap Day\",<br \/>\nshs.sql_id \"Sql id\",<br \/>\nREPLACE<br \/>\n(CAST<br \/>\n(DBMS_LOB.SUBSTR (sht.sql_text, 40) AS VARCHAR (40)<br \/>\n),<br \/>\nCHR (10),<br \/>\n''<br \/>\n) \"Sql\",<br \/>\nSUM (shs.executions_delta) \"Execs\",<br \/>\nROUND ( (SUM (shs.elapsed_time_delta) \/ 1000000)<br \/>\n\/ SUM (shs.executions_delta),<br \/>\n1<br \/>\n) \"Time Ea Sec\",<br \/>\nROUND ( (SUM (shs.cpu_time_delta) \/ 1000000)<br \/>\n\/ SUM (shs.executions_delta),<br \/>\n1<br \/>\n) \"CPU Ea Sec\",<br \/>\nROUND ( (SUM (shs.iowait_delta) \/ 1000000)<br \/>\n\/ SUM (shs.executions_delta),<br \/>\n1<br \/>\n) \"IO\/Wait Ea Sec\",<br \/>\nSUM (shs.cpu_time_delta) \"CPU Time\",<br \/>\nSUM (shs.disk_reads_delta) \"Disk Reads\",<br \/>\nSUM (shs.buffer_gets_delta) \"Buffer Gets\",<br \/>\nSUM (shs.direct_writes_delta) \"Writes\",<br \/>\nSUM (shs.parse_calls_delta) \"Parses\",<br \/>\nSUM (shs.sorts_delta) \"Sorts\",<br \/>\nSUM (shs.elapsed_time_delta) \"Elapsed\"<br \/>\nFROM dba_hist_sqlstat shs INNER JOIN dba_hist_sqltext sht<br \/>\nON (sht.sql_id = shs.sql_id)<br \/>\nINNER JOIN dba_hist_snapshot hs<br \/>\nON (shs.snap_id = hs.snap_id)<br \/>\nHAVING SUM (shs.executions_delta) > 0<br \/>\nGROUP BY shs.sql_id,<br \/>\nTO_CHAR (hs.begin_interval_time, 'MM\/DD\/YY'),<br \/>\nCAST (DBMS_LOB.SUBSTR (sht.sql_text, 40) AS VARCHAR (40)<br \/>\n)<br \/>\nORDER BY \"Snap Day\" DESC) i1<br \/>\nORDER BY \"Snap Day\" DESC)<br \/>\nWHERE \"Rank\" <= 20 AND \"Snap Day\" = TO_CHAR (SYSDATE-1, 'MM\/DD\/YY'); \n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<p><\/code><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Top 20 resource intensive SQLs for today SELECT * FROM (SELECT RANK () OVER (PARTITION BY &#8220;Snap Day&#8221; ORDER BY &#8220;CPU Time&#8221; + &#8220;Disk Reads&#8221; + &#8220;Buffer Gets&#8221; + &#8220;Writes&#8221; + &#8220;Sorts&#8221; + &#8220;Parses&#8221; DESC) AS &#8220;Rank&#8221;, i1.* FROM (SELECT TO_CHAR (hs.begin_interval_time, &#8216;MM\/DD\/YY&#8217; ) &#8220;Snap Day&#8221;, shs.sql_id &#8220;Sql id&#8221;, REPLACE (CAST (DBMS_LOB.SUBSTR (sht.sql_text, 40) AS [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":253,"menu_order":0,"comment_status":"closed","ping_status":"open","template":"","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"class_list":["post-466","page","type-page","status-publish","hentry"],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/P2NVny-7w","_links":{"self":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/466","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=466"}],"version-history":[{"count":1,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/466\/revisions"}],"predecessor-version":[{"id":467,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/466\/revisions\/467"}],"up":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/253"}],"wp:attachment":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=466"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}