Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Saturday, 5 May 2012

Script: Monitor and size the Redo Log buffer

As a DBA, you are responsible to monitor and resize the Redo Log buffer in the SGA memory in case of performance problems. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:
– Monitoring the Redo Log Buffer memory size
– Re-sizing the Redo Log Buffer memory size
– Checking the Redo allocation entries ratio
– Checking waiting sessions
– Checking for an Online Full Redo Log file
– Using the V$SESSION_WAIT view
******************************************************************************************************************************************************************
CONNECT sys AS SYSDBA
SELECT name, value FROM v$sysstat
WHERE name = 'redo buffer allocation retries';
Note that if you have a positive number, that means that you may have a problem. Be sure that you have compared the above positive number with the Redo entries and it should not be more than 1%.
SELECT a.value/b.value "redo buffer entries ratio" FROM v$sysstat a, v$sysstat b
WHERE a.name = 'redo buffer allocation entries' AND b.name = 'redo entries';
If the number is greater than 1%, you should increase the size of the Redo Log buffer. You should also check the checkpoint and size of the online redo log file.
SELECT sid, event, seconds_in_wait, state FROM v$session_wait
WHERE event = 'log buffer space';
If the Log Buffer space waits exist, consider increasing the size of the redo log. Also you should check the speed of the disk that the Online Redo Log files are in.
SELECT name, value FROM v$sysstat
WHERE name = 'redo log space requests';
Now, check to see if that Online Redo Log file is full and the server is waiting for the next Redo Log file.
To resize the Online Log Buffer do the following:
ALTER SYSTEM SET log_buffer=###M scope=SPFILE; -- ### is a integer number that you want to assign to your log buffer.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Check Database Start Time and Log on Time of User
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM   sys.v_$instance;
select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss'),osuser,status,schemaname,machine from v$session where type !='BACKGROUND'; ‎
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
To Monitor the Redolog file Statsu (How much it is fill)?
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT lq.leseq  "Current log sequence No", 100*cr.cpodr_bno/lq.lesiz "Percent Full",
cr.cpodr_bno "Current Block No", lq.lesiz  "Size of Log in Blocks"
FROM x$kcccp cr, x$kccle lq
WHERE lq.leseq =CR.cpodr_seq AND bitand(lq.leflg,24) = 8;
 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
To check the Group and Member of Redolog
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Prompt
Prompt Redo Log File Locations >>>>
Prompt
col Grp    format 9999
col member format a50 heading "Online REDO Logs"
col File#  format 9999
col name   format a50 heading "Online REDO Logs"
break on Grp
select group#,member
from   sys.v_$logfile;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
To Monitor  Redolog Lateches Statistics
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COLUMN name  format a30      heading Name
COLUMN percent format 999.999  heading Percent
COLUMN total  heading Total
SELECT l2.NAME, immediate_gets + gets total, immediate_gets "Immediates",
       misses + immediate_misses "Total Misses",
       DECODE (100. * (  GREATEST (  misses + immediate_misses, 1)
       / GREATEST (  immediate_gets + gets, 1) ), 100, 0 ) PERCENT
FROM v$latch l1, v$latchname l2
 WHERE l2.NAME LIKE '%redo%' AND l1.latch# = l2.latch#;

2 comments:

  1. Hi Shahid,
    Can I know which version of Oracle you used for this example. I am using Oracle11g and couldn't find any tuple that has 'redo buffer allocation entries'. I have 'redo buffer allocation retries' is both are same?.

    Thanks

    ReplyDelete
  2. You are right, it should be 'redo buffer allocation retries'. It may be due to spell check the word retries is replaced with entries.

    ReplyDelete