{"id":109,"date":"2011-02-01T12:33:18","date_gmt":"2011-02-01T11:33:18","guid":{"rendered":"http:\/\/jal-bs.de\/blog\/?p=109"},"modified":"2012-09-11T10:28:46","modified_gmt":"2012-09-11T08:28:46","slug":"oracle-redo-log-switch-ubersicht-pro-tag-je-stunde","status":"publish","type":"post","link":"https:\/\/jal-bs.de\/blog\/?p=109","title":{"rendered":"Oracle Redo-Log-Switch \u00dcbersicht pro Tag je Stunde"},"content":{"rendered":"

Folgende SQL-Statements liefern eine gute \u00dcbersicht das zeitliche Verhalten der \u00a0Oracle Redo-Log-Switches.<\/p>\n

— Berechnet die Zeit zwischen redo log switches<\/strong><\/p>\n

col start_time format a18
\ncol end_time\u00a0\u00a0 format a18<\/p>\n

select b.recid,
\n\u00a0\u00a0 to_char(b.first_time,’dd-mon-yy hh:mi:ss‘) start_time, a.recid,
\n\u00a0\u00a0 to_char(a.first_time,’dd-mon-yy hh:mi:ss‘) end_time,
\n\u00a0\u00a0 round(((a.first_time – b.first_time)*24)*60,2) minutes
\nfrom v$log_history a, v$log_history b
\nwhere a.recid = b.recid + 1
\norder by a.first_time asc;<\/p>\n

—
\n— liefert als Ergebnis
\n—<\/p>\n

RECID START_TIME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RECID END_TIME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MINUTES
\n———- \u00a0\u00a0—————— \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0———- \u00a0\u00a0\u00a0—————— \u00a0\u00a0———-
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 18-mar-10 05:43:14\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2 18-mar-10 05:46:03\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2.82
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2 18-mar-10 05:46:03\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3 18-mar-10 05:47:03\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3 18-mar-10 05:47:03\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4 18-mar-10 05:47:59\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .93
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4 18-mar-10 05:47:59\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5 18-mar-10 05:48:40\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .68
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5 18-mar-10 05:48:40\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6 19-mar-10 08:46:19\u00a0\u00a0\u00a0\u00a0 897.65
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6 19-mar-10 08:46:19\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 7 24-mar-10 12:49:03\u00a0\u00a0\u00a0 7442.73
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 7 24-mar-10 12:49:03\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8 24-mar-10 01:23:49\u00a0\u00a0\u00a0\u00a0\u00a0 34.77<\/span><\/p>\n

—
\n— \u00dcbersicht\u00a0 Redo-Log switches pro Tage\/Stunde
\n<\/strong>—<\/p>\n

set lines 200;
\nset pages 999;
\nselect
\nto_char(first_time,’YYYY-MON-DD‘) day,
\nto_char(sum(decode(to_char(first_time,’HH24′),’00‘,1,0)),’99‘) „00“,
\nto_char(sum(decode(to_char(first_time,’HH24′),’01‘,1,0)),’99‘) „01“,
\nto_char(sum(decode(to_char(first_time,’HH24′),’02‘,1,0)),’99‘) „02“,
\nto_char(sum(decode(to_char(first_time,’HH24′),’03‘,1,0)),’99‘) „03“,
\nto_char(sum(decode(to_char(first_time,’HH24′),’04‘,1,0)),’99‘) „04“,
\nto_char(sum(decode(to_char(first_time,’HH24′),’05‘,1,0)),’99‘) „05“,
\nto_char(sum(decode(to_char(first_time,’HH24′),’06‘,1,0)),’99‘) „06“,
\nto_char(sum(decode(to_char(first_time,’HH24′),’07‘,1,0)),’99‘) „07“,
\nto_char(sum(decode(to_char(first_time,’HH24′),’08‘,1,0)),’99‘) „08“,
\nto_char(sum(decode(to_char(first_time,’HH24′),’09‘,1,0)),’99‘) „09“,
\nto_char(sum(decode(to_char(first_time,’HH24′),’10‘,1,0)),’99‘) „10“,
\nto_char(sum(decode(to_char(first_time,’HH24′),’11‘,1,0)),’99‘) „11“,
\nto_char(sum(decode(to_char(first_time,’HH24′),’12‘,1,0)),’99‘) „12“,
\nto_char(sum(decode(to_char(first_time,’HH24′),’13‘,1,0)),’99‘) „13“,
\nto_char(sum(decode(to_char(first_time,’HH24′),’14‘,1,0)),’99‘) „14“,
\nto_char(sum(decode(to_char(first_time,’HH24′),’15‘,1,0)),’99‘) „15“,
\nto_char(sum(decode(to_char(first_time,’HH24′),’16‘,1,0)),’99‘) „16“,
\nto_char(sum(decode(to_char(first_time,’HH24′),’17‘,1,0)),’99‘) „17“,
\nto_char(sum(decode(to_char(first_time,’HH24′),’18‘,1,0)),’99‘) „18“,
\nto_char(sum(decode(to_char(first_time,’HH24′),’19‘,1,0)),’99‘) „19“,
\nto_char(sum(decode(to_char(first_time,’HH24′),’20‘,1,0)),’99‘) „20“,
\nto_char(sum(decode(to_char(first_time,’HH24′),’21‘,1,0)),’99‘) „21“,
\nto_char(sum(decode(to_char(first_time,’HH24′),’22‘,1,0)),’99‘) „22“,
\nto_char(sum(decode(to_char(first_time,’HH24′),’23‘,1,0)),’99‘) „23“
\nfrom
\nv$log_history
\nGROUP by
\nto_char(first_time,’YYYY-MON-DD‘)
\nORDER by day;<\/p>\n

—
\n— Ergebnis
\n—<\/p>\n

DAY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a01\u00a0\u00a0 2\u00a0 3\u00a0 4\u00a0 5\u00a0 6\u00a0 7\u00a0 8\u00a0 9\u00a0 10\u00a0 11\u00a0 12\u00a0 13\u00a0 14\u00a0 15\u00a0 16\u00a0 17\u00a0 18\u00a0 19\u00a0 20\u00a0 21\u00a0 22\u00a0 23
\n———– — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
\n2010-AUG-01\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 9\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 3\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 2
\n2010-AUG-02\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 1
\n2010-AUG-03\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 6\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0 \u00a01\u00a0\u00a0 0
\n2010-AUG-04\u00a0\u00a0 0\u00a0\u00a0 2\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 0
\n2010-AUG-05\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0
\n2010-AUG-06\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 2\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0
\n2010-AUG-07\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 7\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0
\n2010-AUG-08\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 2
\n2010-AUG-09\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 8\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 1\u00a0\u00a0 0
\n2010-AUG-10\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 4\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 5\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 2
\n2010-AUG-11\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 5\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0
\n2010-AUG-12\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 7\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0 \u00a0\u00a00\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1
\n2010-AUG-13\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 3\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 1\u00a0\u00a0 0<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

Folgende SQL-Statements liefern eine gute \u00dcbersicht das zeitliche Verhalten der \u00a0Oracle Redo-Log-Switches. — Berechnet die Zeit zwischen redo log switches col start_time format a18 col end_time\u00a0\u00a0 format a18 select b.recid, \u00a0\u00a0 to_char(b.first_time,’dd-mon-yy hh:mi:ss‘) start_time, a.recid, \u00a0\u00a0 to_char(a.first_time,’dd-mon-yy hh:mi:ss‘) end_time, \u00a0\u00a0 … Weiterlesen →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[12],"_links":{"self":[{"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=\/wp\/v2\/posts\/109"}],"collection":[{"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=109"}],"version-history":[{"count":23,"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=\/wp\/v2\/posts\/109\/revisions"}],"predecessor-version":[{"id":132,"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=\/wp\/v2\/posts\/109\/revisions\/132"}],"wp:attachment":[{"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=109"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=109"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=109"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}