| Estimating cache size [message #187524] |
Mon, 14 August 2006 06:19  |
Nau
Messages: 24
Registered: October 2004
|
Junior Member |
|
|
I want to know how to estimate the size of tha cache memory, that is the value of the parameter:
DB_CACHE_SIZE
is there any criteria to estimate it??
I'm working with partiotioned tables of ten millions rows. This tables are indexed and a normal query on this tables return about 10000 rows.
The maximun number of users working simultaneous and making sql's to this tables are 60.
Any advice about cache size will be greatly apreciatted.
Thanks in advance
|
|
|
|
| Re: Estimating cache size [message #188167 is a reply to message #187524] |
Thu, 17 August 2006 06:08  |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
What version are you running? What O/S are you running?
DB_CACHE_SIZE Initialization Parameter The DB_CACHE_SIZE initialization
parameter has replaced the DB_BLOCK_BUFFERS initialization parameter, which was
used in earlier releases. The DB_CACHE_SIZE parameter specifies the size in bytes of
the cache of standard block size buffers. Thus, to specify a value for DB_CACHE_SIZE,
you would determine the number of buffers that you need and multiple that value
times the block size specified in DB_BLOCK_SIZE.
For example, suppose you currently have the following configuration of parameters
on a manual mode instance with SGA_MAX_SIZE set to 1200M:
■ SHARED_POOL_SIZE = 200M
■ DB_CACHE_SIZE = 500M
■ LARGE_POOL_SIZE=200M
Also assume that the result of the queries is as follows:
SELECT SUM(value) FROM V$SGA = 1200M
SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY = 208M
You can take advantage of automatic shared memory management by setting Total
SGA Size to 992M in Oracle Enterprise Manager, or by issuing the following
statements:
ALTER SYSTEM SET SGA_TARGET = 992M;
ALTER SYSTEM SET SHARED_POOL_SIZE = 0;
ALTER SYSTEM SET LARGE_POOL_SIZE = 0;
ALTER SYSTEM SET JAVA_POOL_SIZE = 0;
ALTER SYSTEM SET DB_CACHE_SIZE = 0;
ALTER SYSTEM SET STREAMS_POOL_SIZE = 0;
where 992M = 1200M minus 208M.
Setting Minimums for Automatically Sized SGA Components You can exercise some control
over the size of the automatically sized SGA components by specifying minimum
values for the parameters corresponding to these components. Doing so can be useful
if you know that an application cannot function properly without a minimum amount
of memory in specific components. You specify the minimum amount of SGA space
for a component by setting a value for its corresponding initialization parameter. Here
is an example configuration:
■ SGA_TARGET = 256M
■ SHARED_POOL_SIZE = 32M
■ DB_CACHE_SIZE = 100M
In this example, the shared pool and the default buffer pool will not be sized smaller
than the specified values (32 M and 100M, respectively). The remaining 124M (256
minus 132) is available for use by all the manually and automatically sized
components.
[Updated on: Thu, 17 August 2006 06:10] Report message to a moderator |
|
|
|
|
|
| Re: Estimating cache size [message #188246 is a reply to message #187524] |
Thu, 17 August 2006 10:15 |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
Ebrain,
Nice link! Lots of good advice there. I still would like to know what version this is on. I don't think you are going to get 10,000 rows in memory. But that depends on your hardware and the row size.
Use Ebrain's link for some great information on estimates.
If you expect 60 people to be working on the table make sure you have initrans and maxtrans parameters set high enough on the table. Although 60 people are probably not going to be looking at the same row at the same time.
Neil.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
