SHARED_POOL_SIZE and DB_BLOCK_BUFFERS sizing

[samdba]

Hi,

Can somebody confirm the forumla for sizing SHARED_POOL_SIZE?

select sum(sharable_mem) from v$db_object_cache;

select sum(sharable_mem) from v$sqlarea;

select sum(250 * users_opening) from v$sqlarea;

Sum of all above sums...

In my database, sum comes to 525 MB. We are running Oracle Financials apps. I have pinned all packages. Still, I wonder if it can be so big.

I do not know if there is such formula for DB_BLOCK_BUFFERS too.

Does any body know?

Thanks in Adv.

[gandolf989]

If you are using 8i do show sga from a dql window. Buffer Blocks are calculated by taking db_block_buffers * db_block_size.

In this case it would be 1000 * 8192 = 8,192,000 or 8 Megs.

db_block_buffers = 1000
db_block_size = 8192

[sjadhavdba]

You have to monitor it continously. At the end or after some working hours calculate the HIT RATIO for SHARED POOL (data dictionary & library cache) as well as DATABASE BUFFER CACHE. it should not be less that approx 96% .

if it is, then you have to increase the SHARED_POOL_SIZE or DB_BLOCK_BUFFERS size accordingly and again check the HIT RATIO.

[samdba]

Hi both,

I think either I have not explained my question properly or it is not understood properly.

I do not want to know how to calculate DB_BLOCK_BUFFERS. I know it.

I simply want to know if the forumla for sizing SHARED_POOL_SIZE

select sum(sharable_mem) from v$db_object_cache;

select sum(sharable_mem) from v$sqlarea;

select sum(250 * users_opening) from v$sqlarea;

Sum of all above sums...

is correct or not....

And similarly if there is any forumla for sizing (not calculating) the DB_BLOCK_BUFFERS.


Thanks.

[Shestakov]

SQL> select pool, sum(bytes) from V$SGASTAT group by pool;

POOL SUM(BYTES)
----------- ----------
java pool 24002560
large pool 2000000
shared pool 65806948
...
...

This is current size of pools.

[samdba]

Originally posted by Shestakov
SQL> select pool, sum(bytes) from V$SGASTAT group by pool;

POOL SUM(BYTES)
----------- ----------
java pool 24002560
large pool 2000000
shared pool 65806948
...
...

This is current size of pools.

Hi,

It seems I am not able to explain my question properly.

For an old running database, how to determine the size of SHARED_POOL_SIZE and DB_BLOCK_BUFFERS.

For SHARED_POOL_SIZE, I follow the formula, which I have posted here.

For DB_BLOCK_BUFFERS, I don't know.

[Shestakov]

Ur formula not correct.

1) shared pool has free memory (usually)
2) not all part of shared pool u descride in this formula.
3) simplest way to get pool's sizes --> use v$parameter view.
in this case u can use query (for old versions of oracle):

select to_number(nvl(value,'0')) shared_mem
from v$system_parameter
where name = 'shared_pool_size';

select to_number(b.value)*to_number(a.value) db_buffer_size
from v$system_parameter a,
v$system_parameter b,
where a.name = 'db_block_buffers' and
b.name = 'db_block_size';

4) stucture of shared memory:
free memory 10628912
miscellaneous 805148
db_block_buffers 4080000 (in shared pool)
State objects 183744
db_files 72496
dictionary cache 2522900
library cache 16230044
sql area 25545532
processes 115200
sessions 359040
heap 1802412
event statistics per sess 576640
locks 86768

!! And there are not all parts of shared pool.