| Low Buffer cache hit ratio [message #53270] |
Tue, 10 September 2002 07:57  |
Venkat
Messages: 110
Registered: February 2001
|
Senior Member |
|
|
I have a tablespace and the corresponding datafiles are spread across all six different raid sets. And recently I faced a problem of very low buffer cache hit ratio. I suspect this is because of the existing configuration as incase of a query against a very large object might result in fetching the data from 6 different raid sets. The hit ratio has even come down to 6%.
My understanding is that, for better I/O, we have to separate the data tablespace from that of the index tablespace by maintaining the corresponding files in two different raid sets.
What I do not know is, can we spread the datfiles corresponding to the same tablespace across the raid sets.
This way I suspect, we are causing the oracle to read the data from 6 raid sets instead of just one raid set.
Would any body through some light on this?
Thanks,
Venkat
|
|
|
|
|
|
| Re: Low Buffer cache hit ratio [message #53276 is a reply to message #53270] |
Tue, 10 September 2002 13:51  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
Note that spreading indexes and tables (as Mahesh says) is common but the aim is still to spread IO across physical discs. You could have also put objects (tables and indexes) A-M in one TS and N-Z in another as long as you are on different physical devices.
Regarding the low buffer hit ratio, remember that data read which results from a full table scan is NOT cached (it would hog all the buffer space and you would be constantly flushing out old data to make space for the next FTS). Make sure that your application is "warm" - the first time your app accesses the data it's not in the buffer - second time maybe. If your data is really large then there is no hope of buffering much of it, so hit ratio will be low (e.g. data warehouse).
If you put each TS on a diffent mount point, but those mount points all end up on the same physical disk(s) then there is no point in having multiple tablespaces (apart from managing space etc.).
|
|
|
|
| Re: Low Buffer cache hit ratio [message #53281 is a reply to message #53270] |
Tue, 10 September 2002 21:02 |
K.K. Raj kumar
Messages: 33
Registered: July 2002
|
Member |
|
|
Hi,
Even in this case also Oracle will use Paraller Read option only to get the data from all the 6 Disks and hence it should not be the problem of I/O.
Just check up by using explain for the select statements and see where things is going wrong.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
