نمایش تعداد log switchها در هر ساعت

اسکریپت زیر، تعداد log switchهایی که در هر ساعت در دیتابیس رخ داده است را نمایش می دهد البته این پرس  و جو برای DBAها بسیار شناخته شده و پراستفاده می باشد.

SELECT trunc(first_time) “Date”,
to_char(first_time, ‘Dy’) “Day”,
count(1) “Total”,
SUM(decode(to_char(first_time, ‘hh24′),’00’,1,0)) “h0”,
SUM(decode(to_char(first_time, ‘hh24′),’01’,1,0)) “h1”,
SUM(decode(to_char(first_time, ‘hh24′),’02’,1,0)) “h2”,
SUM(decode(to_char(first_time, ‘hh24′),’03’,1,0)) “h3”,
SUM(decode(to_char(first_time, ‘hh24′),’04’,1,0)) “h4”,
SUM(decode(to_char(first_time, ‘hh24′),’05’,1,0)) “h5”,
SUM(decode(to_char(first_time, ‘hh24′),’06’,1,0)) “h6”,
SUM(decode(to_char(first_time, ‘hh24′),’07’,1,0)) “h7”,
SUM(decode(to_char(first_time, ‘hh24′),’08’,1,0)) “h8”,
SUM(decode(to_char(first_time, ‘hh24′),’09’,1,0)) “h9”,
SUM(decode(to_char(first_time, ‘hh24′),’10’,1,0)) “h10”,
SUM(decode(to_char(first_time, ‘hh24′),’11’,1,0)) “h11”,
SUM(decode(to_char(first_time, ‘hh24′),’12’,1,0)) “h12”,
SUM(decode(to_char(first_time, ‘hh24′),’13’,1,0)) “h13”,
SUM(decode(to_char(first_time, ‘hh24′),’14’,1,0)) “h14”,
SUM(decode(to_char(first_time, ‘hh24′),’15’,1,0)) “h15”,
SUM(decode(to_char(first_time, ‘hh24′),’16’,1,0)) “h16”,
SUM(decode(to_char(first_time, ‘hh24′),’17’,1,0)) “h17”,
SUM(decode(to_char(first_time, ‘hh24′),’18’,1,0)) “h18”,
SUM(decode(to_char(first_time, ‘hh24′),’19’,1,0)) “h19”,
SUM(decode(to_char(first_time, ‘hh24′),’20’,1,0)) “h20”,
SUM(decode(to_char(first_time, ‘hh24′),’21’,1,0)) “h21”,
SUM(decode(to_char(first_time, ‘hh24′),’22’,1,0)) “h22”,
SUM(decode(to_char(first_time, ‘hh24′),’23’,1,0)) “h23”,
decode(trunc(first_time),
trunc(sysdate), round(count(1) / (24 * to_number(to_char(sysdate, ‘sssss’)+1) / 86400),2),
round(count(1) / 24, 2)) “Avg”
from GV$log_history
where 1=1
group by trunc(first_time), to_char(first_time, ‘Dy’)
Order by 1;

ارتباط با نویسنده مطلب:vahidusefzadeh@ کانال تخصصی اوراکل و لینوکس: OracleDB@

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد.