{"id":473,"date":"2013-08-29T06:10:37","date_gmt":"2013-08-29T06:10:37","guid":{"rendered":"http:\/\/oranotes.tuktaroff.com\/?page_id=473"},"modified":"2013-08-29T06:10:37","modified_gmt":"2013-08-29T06:10:37","slug":"top-queries","status":"publish","type":"page","link":"http:\/\/oranotes.tuktaroff.com\/?page_id=473","title":{"rendered":"Top queries"},"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 10 SQL_ID's for the last 7 days as identified by ADDM<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\ncol SQL_ID form a16\r\ncol Benefit form 9999999999999\r\nselect * from (\r\nselect b.ATTR1 as SQL_ID, max(a.BENEFIT) as \"Benefit\" \r\nfrom DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b \r\nwhere a.REC_ID = b.OBJECT_ID\r\nand a.TASK_ID = b.TASK_ID\r\nand a.TASK_ID in (select distinct b.task_id\r\nfrom dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l\r\nwhere a.begin_interval_time > sysdate - 7 \r\nand  a.dbid = (select dbid from v$database) \r\nand a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance) \r\nand to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24') \r\nand b.advisor_name = 'ADDM' \r\nand b.task_id = l.task_id \r\nand l.status = 'COMPLETED') \r\nand length(b.ATTR4) > 1 group by b.ATTR1\r\norder by max(a.BENEFIT) desc) where rownum < 6;\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\ncol SQL_ID form a16<br \/>\ncol Benefit form 9999999999999<br \/>\nselect * from (<br \/>\nselect b.ATTR1 as SQL_ID, max(a.BENEFIT) as \"Benefit\"<br \/>\nfrom DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b<br \/>\nwhere a.REC_ID = b.OBJECT_ID<br \/>\nand a.TASK_ID = b.TASK_ID<br \/>\nand a.TASK_ID in (select distinct b.task_id<br \/>\nfrom dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l<br \/>\nwhere a.begin_interval_time > sysdate - 7<br \/>\nand  a.dbid = (select dbid from v$database)<br \/>\nand a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)<br \/>\nand to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24')<br \/>\nand b.advisor_name = 'ADDM'<br \/>\nand b.task_id = l.task_id<br \/>\nand l.status = 'COMPLETED')<br \/>\nand length(b.ATTR4) > 1 group by b.ATTR1<br \/>\norder by max(a.BENEFIT) desc) where rownum < 6;\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<p><\/code><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Top 10 SQL_ID&#8217;s for the last 7 days as identified by ADDM col SQL_ID form a16 col Benefit form 9999999999999 select * from ( select b.ATTR1 as SQL_ID, max(a.BENEFIT) as &#8220;Benefit&#8221; from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b where a.REC_ID = b.OBJECT_ID and a.TASK_ID = b.TASK_ID and a.TASK_ID in (select distinct b.task_id from dba_hist_snapshot a, dba_advisor_tasks b, [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":471,"menu_order":0,"comment_status":"closed","ping_status":"open","template":"","meta":{"footnotes":""},"class_list":["post-473","page","type-page","status-publish","hentry"],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/P2NVny-7D","_links":{"self":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/473","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=473"}],"version-history":[{"count":1,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/473\/revisions"}],"predecessor-version":[{"id":474,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/473\/revisions\/474"}],"up":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/471"}],"wp:attachment":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=473"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}