{"id":186,"date":"2012-03-02T02:28:29","date_gmt":"2012-03-02T02:28:29","guid":{"rendered":"http:\/\/oranotes.tuktaroff.com\/?page_id=186"},"modified":"2012-03-02T02:29:10","modified_gmt":"2012-03-02T02:29:10","slug":"awr-snapshots","status":"publish","type":"page","link":"http:\/\/oranotes.tuktaroff.com\/?page_id=186","title":{"rendered":"AWR snapshots"},"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<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nSET LINESIZE   145<br \/>\nSET PAGESIZE   9999<br \/>\nSET TRIMSPOOL  ON<br \/>\nSET VERIFY     off<\/p>\n<p>COLUMN instance_name_print  FORMAT a13                   HEADING 'Instance Name'<br \/>\nCOLUMN snap_id              FORMAT 9999999               HEADING 'Snap ID'<br \/>\nCOLUMN startup_time         FORMAT a21                   HEADING 'Instance Startup Time'<br \/>\nCOLUMN begin_interval_time  FORMAT a20                   HEADING 'Begin Interval Time'<br \/>\nCOLUMN end_interval_time    FORMAT a20                   HEADING 'End Interval Time'<br \/>\nCOLUMN elapsed_time         FORMAT 999,999,999,999.99    HEADING 'Elapsed Time (min)'<br \/>\nCOLUMN db_time              FORMAT 999,999,999,999.99    HEADING 'DB Time (min)'<br \/>\nCOLUMN pct_db_time          FORMAT 999999999             HEADING '% DB Time'<br \/>\nCOLUMN cpu_time             FORMAT 999,999,999.99        HEADING 'CPU Time (min)'<\/p>\n<p>BREAK ON instance_name_print ON startup_time<\/p>\n<p>SPOOL awr_snapshots_dbtime.lst<\/p>\n<p>SELECT<br \/>\n    i.instance_name                                                                     instance_name_print<br \/>\n  , s.snap_id                                                                           snap_id<br \/>\n  , TO_CHAR(s.startup_time, 'mm\/dd\/yyyy HH24:MI:SS')                                    startup_time<br \/>\n  , TO_CHAR(s.begin_interval_time, 'mm\/dd\/yyyy HH24:MI:SS')                             begin_interval_time<br \/>\n  , TO_CHAR(s.end_interval_time, 'mm\/dd\/yyyy HH24:MI:SS')                               end_interval_time<br \/>\n  , ROUND(EXTRACT(DAY FROM  s.end_interval_time - s.begin_interval_time) * 1440 +<br \/>\n          EXTRACT(HOUR FROM s.end_interval_time - s.begin_interval_time) * 60 +<br \/>\n          EXTRACT(MINUTE FROM s.end_interval_time - s.begin_interval_time) +<br \/>\n          EXTRACT(SECOND FROM s.end_interval_time - s.begin_interval_time) \/ 60, 2)     elapsed_time<br \/>\n  , ROUND((e.value - b.value)\/1000000\/60, 2)                                            db_time<br \/>\n  , ROUND(((((e.value - b.value)\/1000000\/60) \/ (EXTRACT(DAY FROM  s.end_interval_time - s.begin_interval_time) * 1440 +<br \/>\n                                                EXTRACT(HOUR FROM s.end_interval_time - s.begin_interval_time) * 60 +<br \/>\n                                                EXTRACT(MINUTE FROM s.end_interval_time - s.begin_interval_time) +<br \/>\n                                                EXTRACT(SECOND FROM s.end_interval_time - s.begin_interval_time) \/ 60) ) * 100), 2)   pct_db_time<br \/>\nFROM<br \/>\n    dba_hist_snapshot       s<br \/>\n  , gv$instance             i<br \/>\n  , dba_hist_sys_time_model e<br \/>\n  , dba_hist_sys_time_model b<br \/>\nWHERE<br \/>\n      i.instance_number = s.instance_number<br \/>\n  AND e.snap_id         = s.snap_id<br \/>\n  AND b.snap_id         = s.snap_id - 1<br \/>\n  AND e.stat_id         = b.stat_id<br \/>\n  AND e.instance_number = b.instance_number<br \/>\n  AND e.instance_number = s.instance_number<br \/>\n  AND e.stat_name       = 'DB time'<br \/>\nORDER BY<br \/>\n    i.instance_name<br \/>\n  , s.snap_id;<\/p>\n<p>SPOOL OFF<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<pre class=\"qcode\">\r\nSET LINESIZE   145\r\nSET PAGESIZE   9999\r\nSET TRIMSPOOL  ON\r\nSET VERIFY     off\r\n\r\nCOLUMN instance_name_print  FORMAT a13                   HEADING 'Instance Name'\r\nCOLUMN snap_id              FORMAT 9999999               HEADING 'Snap ID'\r\nCOLUMN startup_time         FORMAT a21                   HEADING 'Instance Startup Time'\r\nCOLUMN begin_interval_time  FORMAT a20                   HEADING 'Begin Interval Time'\r\nCOLUMN end_interval_time    FORMAT a20                   HEADING 'End Interval Time'\r\nCOLUMN elapsed_time         FORMAT 999,999,999,999.99    HEADING 'Elapsed Time (min)'\r\nCOLUMN db_time              FORMAT 999,999,999,999.99    HEADING 'DB Time (min)'\r\nCOLUMN pct_db_time          FORMAT 999999999             HEADING '% DB Time'\r\nCOLUMN cpu_time             FORMAT 999,999,999.99        HEADING 'CPU Time (min)'\r\n\r\nBREAK ON instance_name_print ON startup_time\r\n\r\nSPOOL awr_snapshots_dbtime.lst\r\n\r\nSELECT\r\n    i.instance_name                                                                     instance_name_print\r\n  , s.snap_id                                                                           snap_id\r\n  , TO_CHAR(s.startup_time, 'mm\/dd\/yyyy HH24:MI:SS')                                    startup_time\r\n  , TO_CHAR(s.begin_interval_time, 'mm\/dd\/yyyy HH24:MI:SS')                             begin_interval_time\r\n  , TO_CHAR(s.end_interval_time, 'mm\/dd\/yyyy HH24:MI:SS')                               end_interval_time\r\n  , ROUND(EXTRACT(DAY FROM  s.end_interval_time - s.begin_interval_time) * 1440 +\r\n          EXTRACT(HOUR FROM s.end_interval_time - s.begin_interval_time) * 60 +\r\n          EXTRACT(MINUTE FROM s.end_interval_time - s.begin_interval_time) +\r\n          EXTRACT(SECOND FROM s.end_interval_time - s.begin_interval_time) \/ 60, 2)     elapsed_time\r\n  , ROUND((e.value - b.value)\/1000000\/60, 2)                                            db_time\r\n  , ROUND(((((e.value - b.value)\/1000000\/60) \/ (EXTRACT(DAY FROM  s.end_interval_time - s.begin_interval_time) * 1440 +\r\n                                                EXTRACT(HOUR FROM s.end_interval_time - s.begin_interval_time) * 60 +\r\n                                                EXTRACT(MINUTE FROM s.end_interval_time - s.begin_interval_time) +\r\n                                                EXTRACT(SECOND FROM s.end_interval_time - s.begin_interval_time) \/ 60) ) * 100), 2)   pct_db_time\r\nFROM\r\n    dba_hist_snapshot       s\r\n  , gv$instance             i\r\n  , dba_hist_sys_time_model e\r\n  , dba_hist_sys_time_model b\r\nWHERE\r\n      i.instance_number = s.instance_number\r\n  AND e.snap_id         = s.snap_id\r\n  AND b.snap_id         = s.snap_id - 1\r\n  AND e.stat_id         = b.stat_id\r\n  AND e.instance_number = b.instance_number\r\n  AND e.instance_number = s.instance_number\r\n  AND e.stat_name       = 'DB time'\r\nORDER BY\r\n    i.instance_name\r\n  , s.snap_id;\r\n\r\nSPOOL OFF\r\n<\/pre>\n<p><\/code><\/p>\n","protected":false},"excerpt":{"rendered":"<p>SET LINESIZE 145 SET PAGESIZE 9999 SET TRIMSPOOL ON SET VERIFY off COLUMN instance_name_print FORMAT a13 HEADING &#8216;Instance Name&#8217; COLUMN snap_id FORMAT 9999999 HEADING &#8216;Snap ID&#8217; COLUMN startup_time FORMAT a21 HEADING &#8216;Instance Startup Time&#8217; COLUMN begin_interval_time FORMAT a20 HEADING &#8216;Begin Interval Time&#8217; COLUMN end_interval_time FORMAT a20 HEADING &#8216;End Interval Time&#8217; COLUMN elapsed_time FORMAT 999,999,999,999.99 HEADING [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":179,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"class_list":["post-186","page","type-page","status-publish","hentry"],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/P2NVny-30","_links":{"self":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/186","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=186"}],"version-history":[{"count":2,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/186\/revisions"}],"predecessor-version":[{"id":189,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/186\/revisions\/189"}],"up":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/179"}],"wp:attachment":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=186"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}