db buffer cache

[nac]

Hi

what is actually consistent gets and db block gets?
Can any one explain in detail?

What to do if the value of 'relo log space requests' is not zero
ie., 5 or 6 something.

what is the relation between rollback segment and redo log buffers?

Help me knowing this funda clearly.

[hrishy]

db_block gets

if the fetch is from memory (data blocks that are read from disks and stored in memory until they are aged out)its known as consistent gets.

Consistent gets

if the data is to read from rollback segments (because the data is being changed when you requested the data )then it is known as consistent gets........

hope you get it....

hey sorry gtg

[padmam]

2)

The log buffer is normally small compared with the total SGA size, and a modest increase can significantly enhance throughput. A key ratio is the space request ratio: redo log space requests / redo entries. If this ratio is greater than 1:5000, increase the size of the redo log buffer until the space request ratio stops falling.

redo log space requests
===============
The active log file is full and Oracle is waiting for disk space to be allocated for the redo log entries. Space is created by performing a log switch.

Small Log files in relation to the size of the SGA or the commit rate of the work load can cause problems. When the log switch occurs, Oracle must ensure that all committed dirty buffers are written to disk before switching to a new log file. If you have a large SGA full of dirty buffers and small redo log files, a log switch must wait for DBWR to write dirty buffers to disk before continuing.

as per your condition the value column is 5 or so then you have the solution as said above.

i.e more logfile of larger size and frequent commits can solve your problem.

3)

what is the relation between rollback segment and redo log buffers?

All modified data (before image-DML ) is recorded in RBS.
RBS helps in read consistency .

RLBuffer records all changes made on DB(DML and DDL).

Redo log buffer is used for recovery after system crash.
During a instance failure uncomitted datas in the RBS is written to Redo log files.Already commited datas in the log file not written to datafiles after instance startup roll forward and Uncommited (DML)datas in the logfile==>log buffer==>RBS
before image is created and rollbackward happens.

[pando]

well consistent gets is tricky, I have tested in a test database with 0 users only me connected, stopped the database and started again then did

select * from emp;

consistent gets = 2

since no one was changing table emp why would I still reading RBS???

[vishywish]

HI Padmam,
Thank you for telling us the space request ratio.
Can you tell us where to look for this ratio?
Thanks

[LND]

"since no one was changing table emp why would I still reading RBS???" - I believe consistent get is only mode of get but not indication if RBS is used?

Anyway, RBS MAY be accesed for block cleanout - really ugly thing in oracle world.

[pando]

LND I tested this way

shutdown immediate
startup
select * from emp


all this in 1 minute or less, I really wonder how RBS are used?!

[LND]

Block cleanout happens when one queries the block.
So better query blocks to do a cleanout, then do a test query and see what happens.
But I am convinced that consistent gets are:
"Number of times a consistent read was requested for a
block." - oracle ref.

[pando]

i ran the same query over and over again still read consistent gets = 2
you can try it with a fresh database (i.e a database just started)
actually I have never ever seen a query with consistent gets = 0

[nac]

Hi gurus,

Thanx for everyone involved.

I think I have iniitated a good debate over
this topic

Thanx