Oracle Behavoir

[Sofiane]

Hello,

I have 2 questions :

1. When oracle writes a block to disk, this block is copied from the SGA data buffer
to disk directly or to system buffer then to disk. So if the answer is from SGA buffer
to disk directly, if you have to write 100Mo and a db_block of 4Ko,
we will have around 25000 I/O ?

2. What is the behavior of oracle b-tree index when you insert records
(not lexically adjacent key. PCT free is low 10% for instance) ?

Thanks in advance for the reply

Sofiane

[padmam]

Hi, 3rd May 2001 17:43 hrs chennai

Imagine a situation like this.

You have a 300mb log buffer size=so how many OS blocks and size ? (the log buffer size is determined in multiples of OS blocks size )when a log buffer gets one third full then log switch will automatically happen there by checkpoint happens. so that the logbuffers are flushed to log files and dbwr process will write all the modified buffers to datafiles.

Why i meant 300 Mb is if 100 mb in db buffer cache of modified blocks to be written to datafiles at single point then it
the logbuffer should have been a very large size to wait and write before it could flush to log files. (and the LOG_CHECKPOINT_INTERVAL,LOG_CHECKPOINT_TIMEOUT should have been set so high so that it wont prompt lgwr to flush).

Log buffer is normally the smallest size in SGA so how much big the size of shared pool and db buffer cache ?

There are other points you have to see which also periodically fires dbwr to write modified blocks to DB such as 3 seconds timeout etc.If this happens then LGWR will fire.so....you have to imagine and let me know on all this....

All this are available to safe guard from loss of datas.

can you think about the memory requirement etc.

So a well designed DB the situation as you have metioned wont arise and ORACLE wont advice the same.

You ahve to claculate the cost on HW , maintenance,recoverability and several other factors .So natually the option is not of benefit.

The main idea of ORACLE is to make available the enough memory in shared pool,db buffer cache always for transactions.

Another important point you have to consider is under any db crash a minimal damage to happen log files size will also be kept to minimum due to frequent log switch to happen and there by archived files will be useful for future recovery situations.

II)The in between spaces are unclaimed unless you rebuild the index regularly.There by space is wasted.Naturally if the space is not vailable for future inserts it will go for new blocks to insert.

Either you can increase the pctfree or regularly rebulid the index which will improve the performance.

Cheers

Padmam

[Edited by padmam on 05-03-2001 at 09:20 AM]