Skip to main content

Oracle Database Find-out hourly log switch count.


 Find-out hourly log switch count

This will help you to figure out:
– The transnational work load on your database.
– The total number of archive log files being generated.
– To schedule or Re-schedule your RMAN transnational log backup policy.

// Script to find-out hourly log switch count:

set pages 1000

select to_char(COMPLETION_TIME,'dd mon yyyy hh24') as Hour,
thread#,
round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log
group by to_char(COMPLETION_TIME,'dd mon yyyy hh24'),thread# order by 1;

// Script to find-out daily log switch count:
 
SELECT to_char(first_time, 'dd-mon-yyyy') as "Date",
 count(*) as "Daily log switch count"
 FROM V$log_history
 GROUP BY to_char(first_time, 'dd-mon-yyyy');


 SELECT A.*, Round(A.Count*B.AVG/1024/1024) Daily_average_MB
 FROM ( SELECT To_Char(First_Time,'YYYY-MM-DD') Day,
 Count(1) Count,
 Min(RECID) Min,
 Max(RECID) Max
 FROM v$log_history
 GROUP BY To_Char(First_Time,'YYYY-MM-DD')
 ORDER BY 1 DESC ) A,
 ( SELECT Avg(BYTES) AVG,
 Count(1) Count#,
 Max(BYTES) Max_Bytes,
 Min(BYTES) Min_Bytes
 FROM v$log ) B;


 // Script to find-out monthly log switch count:

 
SELECT to_char(first_time, 'yyyy-mon') as "Year:Month",
 count(*) as "Daily log switch count"
 FROM V$log_history
 GROUP BY to_char(first_time, 'yyyy-mon')
 ORDER BY 1;


 // Script to find-out yearly log switch count:


 SELECT to_char(first_time, 'yyyy') as "Year",
 count(*) as "Yearly log switch count"
 FROM V$log_history
 GROUP BY to_char(first_time, 'yyyy');
Labels:

Comments

Post a Comment

Popular posts from this blog

Oracle Database How much redo generated per hour

Track the Amount of Redo Generated per Hour / Day  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; Redo generated Per day: select trunc(completion_time) rundate ,count(*)  logswitch ,round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)" from v$archived_log group by trunc(completion_time) order by 1;
3 comments
Read more

Oracle database check db_recovery_file_dest_size usage

db_recovery_file_dest_size usage The following scripts will check space utilization for  db_recovery_file_dest_size : col name     format a32 col size_mb  format 999,999,999 col used_mb  format 999,999,999 col pct_used format 999 select    name,    ceil( space_limit / 1024 / 1024) size_mb,    ceil( space_used / 1024 / 1024) used_mb,    decode( nvl( space_used, 0),0, 0,    ceil ( ( space_used / space_limit) * 100) ) pct_used from     v$recovery_file_dest order by    name desc; ********************************* set lines 100 col name format a60 select    name,    floor(space_limit / 1024 / 1024) "Size MB",    ceil(space_used / 1024 / 1024)   "Used MB", from    v$recovery_file_dest order by    name;
Post a Comment
Read more

Oracle Database Check the usage of SGA

SQL> show sga Total System Global Area 524288000 bytes Fixed Size                  2046008 bytes Variable Size             423626696 bytes Database Buffers           92274688 bytes Redo Buffers                6340608 bytes SQL> show parameter sga_max_size NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ sga_max_size                         big integer 500M select value/1024/1024 shared_pool_size from v$parameter where name = 'shared_pool_size'; select * From v$sgastat; select 'Memo...
1 comment
Read more