Tuning Log_Buffer


--
-- tuning log_buffer parameter
--
select name, value from v$parameter where name='log_buffer';
alter system set log_buffer=1024 scope=spfile;
--alter system set log_buffer = [512*CPU_COUNT]
--
-- parallel log write
--
select name, value from v$parameter where name='log_parallelism';
alter system set log_parallelism=2 scope=spfile;
--
-- is there any session waiting for log buffer space
--
select
      sid,
      event,
      seconds_in_wait ,
      state
from
      v$session_wait
where
      event like '%log buffer space%';
--
-- check the percentage of redo writes and retries
--
-- the pct value should be very close to zero which means system
-- is not retrying for redo buffer allocation
select
        t1.value, t2.value,
       (t2.value*100)/(t1.value+t2.value) "retries/entries"
from
       v$sysstat t1,
       v$sysstat t2
where
       t1.name in ('redo entries') and
       t2.name in ('redo buffer allocation retries');

Views
      v$session_wait
      v$sysstat

Parameters
      log_parallelism
      log_buffer
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: