{"id":405,"date":"2021-02-26T08:07:00","date_gmt":"2021-02-25T22:07:00","guid":{"rendered":"http:\/\/www.mrmarkyoung.com\/oracle\/?p=405"},"modified":"2021-02-26T08:07:49","modified_gmt":"2021-02-25T22:07:49","slug":"how-many-log-switches","status":"publish","type":"post","link":"http:\/\/www.mrmarkyoung.com\/oracle\/2021\/02\/26\/how-many-log-switches\/","title":{"rendered":"How many log switches"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code>SELECT * FROM (\r\nSELECT * FROM (\r\nSELECT TO_CHAR(FIRST_TIME, 'DD\/MM') AS \"DAY\"\r\n, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0), '99')) \"00:00\"\r\n, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0), '99')) \"01:00\"\r\n, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0), '99')) \"02:00\"\r\n, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0), '99')) \"03:00\"\r\n, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0), '99')) \"04:00\"\r\n, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0), '99')) \"05:00\"\r\n, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0), '99')) \"06:00\"\r\n, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0), '99')) \"07:00\"\r\n, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0), '99')) \"08:00\"\r\n, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0), '99')) \"09:00\"\r\n, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0), '99')) \"10:00\"\r\n, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0), '99')) \"11:00\"\r\n, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0), '99')) \"12:00\"\r\n, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0), '99')) \"13:00\"\r\n, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0), '99')) \"14:00\"\r\n, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0), '99')) \"15:00\"\r\n, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0), '99')) \"16:00\"\r\n, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0), '99')) \"17:00\"\r\n, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0), '99')) \"18:00\"\r\n, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0), '99')) \"19:00\"\r\n, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0), '99')) \"20:00\"\r\n, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0), '99')) \"21:00\"\r\n, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0), '99')) \"22:00\"\r\n, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0), '99')) \"23:00\"\r\n    FROM V$LOG_HISTORY\r\n       WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)\r\n          GROUP BY TO_CHAR(FIRST_TIME, 'DD\/MM')\r\n  ) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD\/MM') DESC\r\n  ) WHERE ROWNUM &lt;31;<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>col STAT_NAME for a20\r\ncol VALUE_DIFF for 9999,999,999\r\ncol STAT_PER_MIN for 9999,999,999\r\nset lines 200 pages 1500 long 99999999\r\ncol BEGIN_INTERVAL_TIME for a30\r\ncol END_INTERVAL_TIME for a30\r\nset pagesize 40\r\nset pause on\r\n \r\n \r\nselect hsys.SNAP_ID,\r\n       hsnap.BEGIN_INTERVAL_TIME,\r\n       hsnap.END_INTERVAL_TIME,\r\n           hsys.STAT_NAME,\r\n           hsys.VALUE,\r\n           hsys.VALUE - LAG(hsys.VALUE,1,0) OVER (ORDER BY hsys.SNAP_ID) AS \"VALUE_DIFF\",\r\n           round((hsys.VALUE - LAG(hsys.VALUE,1,0) OVER (ORDER BY hsys.SNAP_ID)) \/\r\n           round(abs(extract(hour from (hsnap.END_INTERVAL_TIME - hsnap.BEGIN_INTERVAL_TIME))*60 +\r\n           extract(minute from (hsnap.END_INTERVAL_TIME - hsnap.BEGIN_INTERVAL_TIME)) +\r\n           extract(second from (hsnap.END_INTERVAL_TIME - hsnap.BEGIN_INTERVAL_TIME))\/60),1)) \"STAT_PER_MIN\"\r\nfrom dba_hist_sysstat hsys, dba_hist_snapshot hsnap\r\n where hsys.snap_id = hsnap.snap_id\r\n and hsnap.instance_number in (select instance_number from v$instance)\r\n and hsnap.instance_number = hsys.instance_number\r\n and hsys.STAT_NAME='user commits'\r\n order by 1;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-405","post","type-post","status-publish","format-standard","hentry","category-oracle-database"],"_links":{"self":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/405","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/comments?post=405"}],"version-history":[{"count":2,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/405\/revisions"}],"predecessor-version":[{"id":407,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/405\/revisions\/407"}],"wp:attachment":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/media?parent=405"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/categories?post=405"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/tags?post=405"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}