{"id":336,"date":"2018-10-15T07:35:08","date_gmt":"2018-10-14T21:35:08","guid":{"rendered":"http:\/\/www.mrmarkyoung.com\/oracle\/?p=336"},"modified":"2018-10-15T13:45:16","modified_gmt":"2018-10-15T03:45:16","slug":"how-many-log-switches-per-hour","status":"publish","type":"post","link":"http:\/\/www.mrmarkyoung.com\/oracle\/2018\/10\/15\/how-many-log-switches-per-hour\/","title":{"rendered":"How many log switches per hour"},"content":{"rendered":"<p>Calculate the log switches per hour over the last week.<\/p>\n<pre class=\"lang:default decode:true \" title=\"Log switches per hour\" >SET PAGESIZE 90\r\nSET LINESIZE 150\r\nset heading on\r\ncolumn \"00:00\" format 9999\r\ncolumn \"01:00\" format 9999\r\ncolumn \"02:00\" format 9999\r\ncolumn \"03:00\" format 9999\r\ncolumn \"04:00\" format 9999\r\ncolumn \"05:00\" format 9999\r\ncolumn \"06:00\" format 9999\r\ncolumn \"07:00\" format 9999\r\ncolumn \"08:00\" format 9999\r\ncolumn \"09:00\" format 9999\r\ncolumn \"10:00\" format 9999\r\ncolumn \"11:00\" format 9999\r\ncolumn \"12:00\" format 9999\r\ncolumn \"13:00\" format 9999\r\ncolumn \"14:00\" format 9999\r\ncolumn \"15:00\" format 9999\r\ncolumn \"16:00\" format 9999\r\ncolumn \"17:00\" format 9999\r\ncolumn \"18:00\" format 9999\r\ncolumn \"19:00\" format 9999\r\ncolumn \"20:00\" format 9999\r\ncolumn \"21:00\" format 9999\r\ncolumn \"22:00\" format 9999\r\ncolumn \"23:00\" format 9999\r\nSELECT * 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;8;<\/pre>\n<p>We can also caculate how many log switches per hour using the following statement<\/p>\n<pre class=\"lang:default decode:true \" title=\"Log switches\" >WITH row_source AS\r\n( select\r\ntrunc(first_time) DAY,\r\ntrunc(first_time,'HH24') DAY_HOUR,\r\ncount(*) LOG_SWITCHES_PER_HOUR\r\nfrom v$log_history\r\nwhere trunc(first_time) &gt; sysdate-8\r\ngroup by trunc(first_time) , trunc(first_time,'HH24') )\r\nselect DAY, DAY_HOUR, LOG_SWITCHES_PER_HOUR,\r\nsum(LOG_SWITCHES_PER_HOUR) OVER (PARTITION BY DAY ORDER BY DAY_HOUR) \"TOTAL_SWITCHES_SOFAR_TODAY\"\r\nfrom row_source\r\norder by 1,2;<\/pre>\n<p>or this<\/p>\n<pre class=\"lang:default decode:true \" >select\r\nrecid,\r\nto_char(first_time, 'dd-mon-yy hh:mi:ss') start_time,\r\nto_char(lead(first_time) over (order by recid), 'dd-mon-yy hh:mi:ss') as end_time,\r\nround((lead(first_time) over (order by recid)-first_time)*60, 2) as minutes\r\nfrom v$log_history a\r\nwhere trunc(first_time) &gt; sysdate-8\r\norder by a.first_time asc;<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Calculate the log switches per hour over the last week. SET PAGESIZE 90 SET LINESIZE 150 set heading on column &#8220;00:00&#8221; format 9999 column &#8220;01:00&#8221; format 9999 column &#8220;02:00&#8221; format 9999 column &#8220;03:00&#8221; format 9999 column &#8220;04:00&#8221; format 9999 column &#8220;05:00&#8221; format 9999 column &#8220;06:00&#8221; format 9999 column &#8220;07:00&#8221; format 9999 column &#8220;08:00&#8221; format 9999 &#8230; <a title=\"How many log switches per hour\" class=\"read-more\" href=\"http:\/\/www.mrmarkyoung.com\/oracle\/2018\/10\/15\/how-many-log-switches-per-hour\/\" aria-label=\"Read more about How many log switches per hour\">Read more<\/a><\/p>\n","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-336","post","type-post","status-publish","format-standard","hentry","category-oracle-database"],"_links":{"self":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/336","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=336"}],"version-history":[{"count":4,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/336\/revisions"}],"predecessor-version":[{"id":340,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/336\/revisions\/340"}],"wp:attachment":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/media?parent=336"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/categories?post=336"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/tags?post=336"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}