How to monitor the amount of redo generated per hour
February 20th, 2006 By Karl Reitschuster
Hi,
At customer sites very often I can see the transaction load via the amount of redo which was generated.
For best overview I like to query the amount of redo generated per hour:
SELECT Start_Date,
Start_Time,
Num_Logs,
Round(Num_Logs * (Vl.Bytes / (1024 * 1024)),
2) AS Mbytes,
Vdb.NAME AS Dbname
FROM (SELECT To_Char(Vlh.First_Time,
'YYYY-MM-DD') AS Start_Date,
To_Char(Vlh.First_Time,
'HH24') || ':00' AS Start_Time,
COUNT(Vlh.Thread#) Num_Logs
FROM V$log_History Vlh
GROUP BY To_Char(Vlh.First_Time,
'YYYY-MM-DD'),
To_Char(Vlh.First_Time,
'HH24') || ':00') Log_Hist,
V$log Vl,
V$database Vdb
WHERE Vl.Group# = 1
ORDER BY Log_Hist.Start_Date,
Log_Hist.Start_Time;
Sample output:
START_DATE START_TIME NUM_LOGS MBYTES DBNAME ---------- ---------- ---------------------- ---------------------- --------- 2006-01-24 11:00 1 100 MYDB 2006-01-24 14:00 2 200 MYDB 2006-01-24 16:00 1 100 MYDB 2006-01-24 18:00 1 100 MYDB 2006-01-24 20:00 5 500 MYDB 2006-01-24 21:00 14 1400 MYDB 2006-01-24 22:00 11 1100 MYDB 2006-01-25 00:00 1 100 MYDB ...
HTH
Karl
February 20th, 2006 at 12:14 pm
Hello Karl,
it would be nice if for future postings you could provide some sample output as well.
This would make - beside analysis of your input - the copy&paste into a test database obsolete.
Thank you for sharing your experiences,
Martin
February 20th, 2006 at 12:33 pm
Martin, Karl did provide sample output, but the formatting was all scrambled. I ran the query on my database and added the output to the post (with the correct formatting).
February 20th, 2006 at 9:49 pm
Thanks for adding the output;
putting content in the right format into wordpress seems to be not so easy ?
Greetings Karl
@Martin,
are you Martin Strobl?
it could be :-)
Karl
February 20th, 2006 at 11:48 pm
@Karl - well guessed :-)
i’m from AUSTRIa and my name is martiN …
March 7th, 2006 at 5:17 am
There is funny script, showing in 3D matrix, on one axis is hours and on other is day of month
regards
SELECT SUBSTR(TO_CHAR(FIRST_TIME, ‘DD-MM-RR HH:MI:SS’), 1, 5) DAY,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH24:MI:SS’), 10, 2),
‘00′,
1,
0)) H00,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH24:MI:SS’), 10, 2),
‘01′,
1,
0)) H01,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH24:MI:SS’), 10, 2),
‘02′,
1,
0)) H02,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH24:MI:SS’), 10, 2),
‘03′,
1,
0)) H03,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH24:MI:SS’), 10, 2),
‘04′,
1,
0)) H04,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH24:MI:SS’), 10, 2),
‘05′,
1,
0)) H05,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH24:MI:SS’), 10, 2),
‘06′,
1,
0)) H06,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH24:MI:SS’), 10, 2),
‘07′,
1,
0)) H07,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH24:MI:SS’), 10, 2),
‘08′,
1,
0)) H08,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH24:MI:SS’), 10, 2),
‘09′,
1,
0)) H09,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH24:MI:SS’), 10, 2),
‘10′,
1,
0)) H10,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH24:MI:SS’), 10, 2),
‘11′,
1,
0)) H11,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH24:MI:SS’), 10, 2),
‘12′,
1,
0)) H12,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH24:MI:SS’), 10, 2),
‘13′,
1,
0)) H13,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH24:MI:SS’), 10, 2),
‘14′,
1,
0)) H14,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH24:MI:SS’), 10, 2),
‘15′,
1,
0)) H15,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH24:MI:SS’), 10, 2),
‘16′,
1,
0)) H16,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH24:MI:SS’), 10, 2),
‘17′,
1,
0)) H17,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH24:MI:SS’), 10, 2),
‘18′,
1,
0)) H18,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH24:MI:SS’), 10, 2),
‘19′,
1,
0)) H19,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH24:MI:SS’), 10, 2),
‘20′,
1,
0)) H20,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH24:MI:SS’), 10, 2),
‘21′,
1,
0)) H21,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH24:MI:SS’), 10, 2),
‘22′,
1,
0)) H22,
SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH24:MI:SS’), 10, 2),
‘23′,
1,
0)) H23,
COUNT(*) SUM_cum
FROM V$LOG_HISTORY A
WHERE (TO_DATE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH:MI:SS’), 1, 8),
‘MM/DD/RR’) >= TO_DATE(&DATA_OD, ‘DD-MM-YYYY’))
AND (TO_DATE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH:MI:SS’), 1, 8),
‘MM/DD/RR’)
March 9th, 2006 at 6:14 am
Hi Tmoore,
your script is very interesting - would you post the rest of it?
there is seomething missing at the end!
Greetings
Karl
March 10th, 2006 at 10:02 am
Sorry that is it. I think this form cut the end. So replace end with this:
FROM V$LOG_HISTORY A
WHERE (TO_DATE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH:MI:SS’), 1, 8),
‘MM/DD/RR’) >= TO_DATE(&BEGIN_DATE, ‘DD-MM-YYYY’))
AND (TO_DATE(SUBSTR(TO_CHAR(FIRST_TIME, ‘MM/DD/RR HH:MI:SS’), 1, 8),
‘MM/DD/RR’)
March 10th, 2006 at 11:36 am
I believe here is what’s missing:
Just make sure you put code between <pre> and </pre> because if you don’t, and you do not escape special html characters, you will get unexpected results.
March 11th, 2006 at 1:14 pm
Ok, you’re right thanks