redo log sizing

Quote:

---

Originally posted by julian
I see, you have a high activity database. Is it always like that?
You might want to consider using LOG_CHECKPOINT_TIMEOUT.

---

I am also interested in this subject.
How is the LOG_CHECKPOINT_TIMEOUT going to cause a log switch? Will it not only activate a checkpoint?

LOG_CHECKPOINT_TIMEOUT will not cause a log switch. I think julian was merely mentioning it so the poster would be protected if they had really large redo logs but with periods of very low activity.

Quote:

---

I am also interested in this subject.
How is the LOG_CHECKPOINT_TIMEOUT going to cause a log switch? Will it not only activate a checkpoint?

---

LOG_CHECKPOINT_TIMEOUT will not cause a log switch. Read Jeff's reply. It might be considered so that no buffer will remain dirty in the cache for more than X seconds.

Hi Julian,

Here is the ratio :

select (req.value*5000)/entries.value "Ratio"
from v$sysstat req, v$sysstat entries
where req.name = 'redo log space requests'
and entries.name = 'redo entries';

Ratio
----------
1.41768764

SQL>

As you suggested I am going to increase my LOG_BUFFER to 1 M. And let you Know the result.

Thanks

Hi Julian,

I had changed my log_buffer to 1M, and restarted the database. Still my log switches are more?

alter database open
Thu Dec 13 12:01:33 2001
Thread 1 opened at log sequence 312181
Current log# 5 seq# 312181 mem#0: /u01/u05/oradata/log7massbej2.dbf
Current log# 5 seq# 312181 mem#1: /u01/app/oracle/product/8.1.6/dbs/log8massbej2.dbf
Successful open of redo thread 1.
Thu Dec 13 12:01:33 2001
SMON: enabling cache recovery
SMON: enabling tx recovery
Thu Dec 13 12:01:35 2001
Completed: alter database open
Thu Dec 13 12:07:04 2001
Thread 1 advanced to log sequence 312182
Current log# 3 seq# 312182 mem#0: /u01/u03/oradata/massbej2log5massbej2.dbf
Current log# 3 seq# 312182 mem#1: /u01/app/oracle/product/8.1.6/dbs/log6massbej2.dbf
Thu Dec 13 12:07:44 2001
Thread 1 advanced to log sequence 312183
Current log# 4 seq# 312183 mem#0: /u01/u04/oradata/massbej2log2massbej2.dbf
Current log# 4 seq# 312183 mem#1: /u01/u04/oradata/massbej2masslog2massbej2.dbf
Thu Dec 13 12:08:23 2001
Thread 1 advanced to log sequence 312184
Current log# 5 seq# 312184 mem#0: /u01/u05/oradata/log7massbej2.dbf
Current log# 5 seq# 312184 mem#1: /u01/app/oracle/product/8.1.6/dbs/log8massbej2.dbf
Thu Dec 13 12:09:03 2001
Thread 1 advanced to log sequence 312185
Current log# 3 seq# 312185 mem#0: /u01/u03/oradata/massbej2log5massbej2.dbf
Current log# 3 seq# 312185 mem#1: /u01/app/oracle/product/8.1.6/dbs/log6massbej2.dbf
Thu Dec 13 12:09:43 2001
Thread 1 advanced to log sequence 312186
Current log# 4 seq# 312186 mem#0: /u01/u04/oradata/massbej2log2massbej2.dbf
Current log# 4 seq# 312186 mem#1: /u01/u04/oradata/massbej2masslog2massbej2.dbf
Thu Dec 13 12:10:22 2001

Ratio is :

Ratio
----------
1.87338914

These results are for taken, 15 mins later to my simulator start time.

Thanks

The redo log is small when compared to the SGA. Now it is 1M, right? More than 1M would not make sense. I suggest you increase now the size of the log files, can you do that, need help? How big are they now? How many groups and memebrs per group do you have?

Hi Julian,

Yes, I can do this, please tell me If I am wrong,
would like to increase my redo log files this way, first of all, I will create another 3 big redo log groups with 2 members each, let us say 5OM each group. And then I will switch all my old logs to new, by using alter system switch logfile and then I will drop my old redo log groups. Is this correct?

Presently I am having 3 redo log groups with 2 members each, size 5M.

Is it Okay! To resize my redologs to 50M, can it be reduce my frequent log switches? What is your recommended size?

Thanks In Advance.

Yes, you have the correct procedure. Add new log groups, swith logfiles so the small logs are not "active" or "current", and the drop the old log groups.

You can start with 50M and see how frequent your logswitch occurs.

Hi,

I have created 3 new redo log groups with 2 members each, with size of 100M.

Now my ratio is :

select (req.value*5000)/entries.value "Ratio"
from v$sysstat req, v$sysstat entries
where req.name = 'redo log space requests'
and entries.name = 'redo entries';

Ratio
----------
.027092121

And Log switches are :

Fri Dec 14 14:42:52 2001
Thread 1 advanced to log sequence 312260
Current log# 7 seq# 312260 mem#0: /u01/u03/oradata/massbej2/massbej2_log7a.dbf
Current log# 7 seq# 312260 mem#1: /u01/u04/oradata/massbej2/massbej2_log7b.dbf
Fri Dec 14 14:56:31 2001
Thread 1 advanced to log sequence 312261
Current log# 8 seq# 312261 mem#0: /u01/u03/oradata/massbej2/massbej2_log8a.dbf
Current log# 8 seq# 312261 mem#1: /u01/u04/oradata/massbej2/massbej2_log8b.dbf
Fri Dec 14 15:11:13 2001
Thread 1 advanced to log sequence 312262
Current log# 6 seq# 312262 mem#0: /u01/u03/oradata/massbej2/massbej2_log6a.dbf
Current log# 6 seq# 312262 mem#1: /u01/u04/oradata/massbej2/massbej2_log6b.dbf


Now, I am happy with these results. One thing I would like know, is still required to increase my log buffer? presently it is 1M.

Thanks in Advance.

Quote:

---

Now, I am happy with these results. One thing I would like know, is still required to increase my log buffer? presently it is 1M.

---

Probably not. In fact, very seldom (I might even say never) there is any use of a LOG_BUFFER > 1M.

Could you please tell me whet you get now from:

select (req.value*5000)/entries.value "Ratio (<1 on OK)"
from v$sysstat req, v$sysstat entries
where req.name = 'redo log space requests'
and entries.name = 'redo entries';

At the same time, you may run the following SQLs to see if something else might be wrong:

select (sum(pins)-sum(reloads))/sum(pins) as "LIBRARY CACHE HIT RATIO"
from v$librarycache;

select (sum(gets)-sum(getmisses))/sum(gets) as "ROW CACHE HIT RATIO"
from v$rowcache;

select gethitratio "GETHITRATIO >= .95 on OK" from v$librarycache where namespace = 'SQL AREA';

select (sum(gets)-sum(misses))*100/sum(gets) as "Latch Hit Ration (> 99 OK)" from v$latch;

select 1 - (phy.value / (cur.value + con.value)) "BUFFER HIT RATIO (> 0.90 OK)"
from v$sysstat cur, v$sysstat con, v$sysstat phy
where cur.name = 'db block gets'
and con.name = 'consistent gets'
and phy.name = 'physical reads';

select sum(waits)* 100 / sum(gets) "RATIO < 5 on OK", sum(waits) "WAITS", sum(gets) "GETS "
from v$rollstat;

select class, count from v$WAITSTAT where class in
('undo header','undo block','system undo header',
'system undo block');

select sum(count) as "All waits" from v$WAITSTAT where class in
('undo header','undo block','system undo header',
'system undo block');

select sum(value) "Data Requests" from v$SYSSTAT
where name in ('db block gets', 'consistent gets');