Query to see # log switches per hour

From: Boivin, Patrice J <BoivinP_at_mar.dfo-mpo.gc.ca>
Date: Thu, 21 Dec 2000 16:19:26 -0400
Message-Id: <10717.125172@fatcity.com>

Fyi,

I adapted a query I found somewhere on the 'net, it used to give me ## = for
number of switches when massive data loads were taking place.

This will show you how often log switches take place on your database.

select substr(time, 1, 5) day,

to_char(sum(decode(substr(time,10,2),'00',1,0)),'999') " 00 ",
to_char(sum(decode(substr(time,10,2),'01',1,0)),'999') " 01 ",
to_char(sum(decode(substr(time,10,2),'02',1,0)),'999') " 02 ",
to_char(sum(decode(substr(time,10,2),'03',1,0)),'999') " 03 ",
to_char(sum(decode(substr(time,10,2),'04',1,0)),'999') " 04 ",
to_char(sum(decode(substr(time,10,2),'05',1,0)),'999') " 05 ",
to_char(sum(decode(substr(time,10,2),'06',1,0)),'999') " 06 ",
to_char(sum(decode(substr(time,10,2),'07',1,0)),'999') " 07 ",
to_char(sum(decode(substr(time,10,2),'08',1,0)),'999') " 08 ",
to_char(sum(decode(substr(time,10,2),'09',1,0)),'999') " 09 ",
to_char(sum(decode(substr(time,10,2),'10',1,0)),'999') " 10 ",
to_char(sum(decode(substr(time,10,2),'11',1,0)),'999') " 11 ",
to_char(sum(decode(substr(time,10,2),'12',1,0)),'999') " 12 ",
to_char(sum(decode(substr(time,10,2),'13',1,0)),'999') " 13 ",
to_char(sum(decode(substr(time,10,2),'14',1,0)),'999') " 14 ",
to_char(sum(decode(substr(time,10,2),'15',1,0)),'999') " 15 ",
to_char(sum(decode(substr(time,10,2),'16',1,0)),'999') " 16 ",
to_char(sum(decode(substr(time,10,2),'17',1,0)),'999') " 17 ",
to_char(sum(decode(substr(time,10,2),'18',1,0)),'999') " 18 ",
to_char(sum(decode(substr(time,10,2),'19',1,0)),'999') " 19 ",
to_char(sum(decode(substr(time,10,2),'20',1,0)),'999') " 20 ",
to_char(sum(decode(substr(time,10,2),'21',1,0)),'999') " 21 ",
to_char(sum(decode(substr(time,10,2),'22',1,0)),'999') " 22 ",
to_char(sum(decode(substr(time,10,2),'23',1,0)),'999') " 23 "=20

from v$log_history
group by substr(time,1,5)
/

Merry Holidays,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)

Systems Admin & Operations | Admin. et Exploit. des syst=E8mes
Technology Services        | Services technologiques
Informatics Branch         | Direction de l'informatique=20
Maritimes Region, DFO      | R=E9gion des Maritimes, MPO

E-Mail: boivinp_at_mar.dfo-mpo.gc.ca <mailto:boivinp_at_mar.dfo-mpo.gc.ca>=20 Received on Thu Dec 21 2000 - 14:19:26 CST