| Anything comparable with Bufferpools? [message #178023] |
Mon, 19 June 2006 02:35  |
theo06
Messages: 29
Registered: February 2006
|
Junior Member |
|
|
Hi,
in IBM DB2 I can specify bufferpools and combine them with tablespaces. So each tablepsace can have its own bufferpool.
A bufferpool is space in main memory (RAM).
Is there a matchable thing in oracle too? I know that oracle uses tablespaces but I don't know if it is possible to assign parts of main memory to different tablespaces as in DB2.
Thank you in advance for your help!
Theo
|
|
|
|
|
|
| Re: Anything comparable with Bufferpools? [message #178085 is a reply to message #178023] |
Mon, 19 June 2006 06:26  |
theo06
Messages: 29
Registered: February 2006
|
Junior Member |
|
|
Thank you.
As far as I understood that means, that I can assign a db-cache with a specified blocksize to a tablespace. But that also means that I cannot create several db-caches with the same blocksize. Am I rigth?
|
|
|
|
|
|
| Re: Anything comparable with Bufferpools? [message #178145 is a reply to message #178136] |
Mon, 19 June 2006 10:07 |
theo06
Messages: 29
Registered: February 2006
|
Junior Member |
|
|
I think about having two db_Cache each of e.g. 4K blocksize. One of them for a tablespace only dealing with indexes and the other handling other tabledata. As far as I understood the information at the site you posted, I only can create one 4K blocksized db-cache. So in my example the tabledata and the indexdata would share one db-cache.
Isn't ist so?
|
|
|
|
| Re: Anything comparable with Bufferpools? [message #178325 is a reply to message #178023] |
Tue, 20 June 2006 11:13 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I'm not sure that you really need more than one buffer cache...that you really need one buffer cache per tablespace. What benefit is gained from this (I've never worked with DB2).
Oracle will manage the single buffer cache based on all of the data from all of the tablespaces flowing into memory. Why not just let it manage the most often used data blocks?
However, you it may help to read the chapters in concepts guide and admin guide and performance tuning guide regarding memory and memory architecture. There are for instance several options available which may help you.
You can influence, for example, at a table level, whether or not blocks should be cached if the table undergoes a FTS. Also, there are other buffer pools available besides the standard db cache size, such as the keep and recycle pools.
I would use the multiple block size feature, and its related multiple buffer caches, as a last resort. Explore your other options first, and be sure you need what you think you need.
|
|
|
|
| Re: Anything comparable with Bufferpools? [message #178329 is a reply to message #178325] |
Tue, 20 June 2006 11:33 |
theo06
Messages: 29
Registered: February 2006
|
Junior Member |
|
|
|
What I think about within this issue is, that I think it will be good if my index pages have their own bufferpool. I think that oracle will clean the index pages out of the bufferpool to have space for other tabledata. Just in that moment the tabledata comes in memory any other user maybe needs the index data. So the index data must be loaded again into the bufferpool. That is time consuming because there must be access to secondary memory. That is why I think about having two db-caches with the same blocksize.
|
|
|
|
| Re: Anything comparable with Bufferpools? [message #178368 is a reply to message #178023] |
Tue, 20 June 2006 19:00 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Ahh...a valid concern, but, at least in oracle, I wouldn't consider multiple buffer pools to be the solution. Oracle will be more inclined to keep index blocks in memory simply due to the access patterns that Oracle uses when pulling the blocks in to begin with.
Full table scans often put their blocks towards the least recently used end of the LRU buffer list, making them more likely to be purged out in favor of other blocks. But index blocks, or blocks not read in through a FTS, are more likely to be placed on the list in the middle. Also, if users are in fact using those index blocks, then their touch count will increase, making them more likely to stay in the memory buffer.
Also, look into table parameters such as keep, recycle, and cache, which can all be set at the table level, and can be set at the tablespace level as well to default for for all tables in that tablespace. And read up on the memory architecture chapters as I said before, and also the performance tuning guide for memory and for access patterns.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
