Please clarify my 3 doubts [message #9456] |
Wed, 12 November 2003 00:05 |
Sudhakar
Messages: 34 Registered: May 2002
|
Member |
|
|
1) In what environment more than one instance for a database can exist?
2) Following is a statement which I read in a Oracle material.
'The largest size a table can ever reach is identified by High-water mark.'
What is High-water mark?
3) How the performance is improved by configuring multiple buffer pools?
|
|
|
|
Re: Please clarify my 3 doubts [message #9458 is a reply to message #9456] |
Wed, 12 November 2003 03:44 |
Daljit Singh
Messages: 290 Registered: October 2003 Location: Texas
|
Senior Member |
|
|
Hi,
Well all of ur three ques are good enough to understand the basic working pro of oracle. I m going to explain all of them one by one.
1)
For multiple instances u should have enough memory for multiple SGA's. Each instance will have its own SGA. Then u should have enough processing speed to handle parallel trans. ur DB should also be properly configured to handle multiple trans.
2)
HWM is actually a location of a segment which is the highest level of block ever touched by the data. Even after deleting the data too the HWM does not decreased.
And it is important to take a look on the HWM of the table bcz whenever a FTS(Full Table Scan) is performed on the table then oracle reads the data upto the HWM of that table.And there may be a huge difference between the actual data and HWM of the table which increase I/O and degrade performance.
Lets take a small and simple exaple : You insert 1000 records in a table (assuming that the table is empty) then that 1000 records will be the HWM of the table (for understanding we are taking record actually its block) than u delete 900 records from the table.Now table contain only 100 records but the HWM is still 1000. So whenever you perform FTS on table oracle will read 1000 records 100 with data and 900 without data.
3)
Performance can be increased by having multiple DB Buffer cache. U can configure KEEP,RECYCLE & DEFAULT DB buffer cache. out os which keep will keep the data segments and they will not aged out, So ur query will find the data blocks in buffer. In recycle ur buffer will get aged out as per LRU algo. and if u dont specify any then default will be used.
By having these u can place ur frequently accessed data buffers in keep pool so that they will not be aged out. And in recycle u can have other data buffers all of these pools will have diff LRU list and u can manage them individually.
I hope will get much cleat idea with this. For further info refer to oracle doc.
Thanx.
|
|
|