redo log sizing

Hi

Here are the results :

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

LIBRARY CACHE HIT RATIO
-----------------------
.999986486

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

ROW CACHE HIT RATIO
-------------------
.931820377

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

GETHITRATIO >= .95 on OK
------------------------
.998493985

4. 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';

BUFFER HIT RATIO (> 0.90 OK)
----------------------------
.999853683

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

RATIO < 5 on OK WAITS GETS
--------------- ---------- ----------
.00082572 4 484426

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

CLASS COUNT
------------------ ----------
system undo header 0
system undo block 0
undo header 860
undo block 1069

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

All waits
----------
1929

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

Data Requests
-------------
13963115

Thanks in Advance

Very good values. You have very well written application GETHITRATIO is almost 1.

Since we started, let's check the sorts and the latch contention:

select name, value from v$sysstat where name like '%sort%';

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

Hi

1.select name, value from v$sysstat where name like '%sort%';


NAME VALUE
---------------------------------------------------------------- ----------
sorts (memory) 47292
sorts (disk) 6
sorts (rows) 115268

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


Latch Hit Ration (> 99 OK)
--------------------------
99.6109578

Thank You Very much for your attention.

You are doing just fine here too. What values do you have for sort_area_size and sort_area_retained_size? You have even "too high" sort ratio. You may even decrease sort_area_size if you need the memory anywhere else but don't bother if you have the RAM.

Hi

Here are details:

$ prtconf
System Configuration: Sun Microsystems sun4u
Memory size: 1024 Megabytes

my sort_area_size is 165536
sort_area_retained_size is 165536


Thanks