| Instance parameters [message #369447] |
Fri, 22 September 2000 14:11  |
Salman Khan
Messages: 51
Registered: September 2000
|
Member |
|
|
Hi,
Could anyone of you can tell me that when Instances parameters value are in Bytes or in Kilo Bytes?
for eg.
db_block_size = 8192
db_files = 40
db_block_buffers= 20000
shared_pool_size = 3500000
sort_area_size =5242880
log_buffer = 163840
|
|
|
|
| Re: Instance parameters [message #369453 is a reply to message #369447] |
Mon, 25 September 2000 02:35  |
Thierry Van der Auwera
Messages: 44
Registered: January 2000
|
Member |
|
|
Hallo Salman,
They are in bytes, so:
db_block_size = 8192 (blocks of 8K)
db_files = 40 (a maximum of 40 data files are allowed)
db_block_buffers= 20000 (you will have so much buffers)
shared_pool_size = 3500000 (a pool off 3.5 Mega)
sort_area_size =5242880 (in bytes)
log_buffer = 163840 (in bytes)
...
Greetings,
Thierry
|
|
|
|
| Re: Instance parameters [message #369457 is a reply to message #369447] |
Tue, 26 September 2000 01:37 |
Thierry Van der Auwera
Messages: 44
Registered: January 2000
|
Member |
|
|
Hi Salman,
First, Db_Size * Db_Block_Buffers = 39 MB , this is correct.
I am not using a real formula to calculate the amount of
memory I allocate to Oracle. If you start searching the net,
you will find a lot of info about how to calucate all the sizes.
(I will include one formula I found somewhere)
You must always take following things in mind:
For the shared_pool_size, how more RAM you can give, how better,
but keep in mind that when you give to much, you OS will start
swapping it to disk, and you lose performance, and if you give
to little, oracle will start swapping. But also, when oracle for
example just uses 100MB, and you allocate 500MB, this is also
stupid.
For the db_block_buffers, the story is the same.
So if you have 2GB of RAM, and your server is only serving two
oracle-dbs, the try to estimate how big these dbs are, how much
users,programs,... and then set the parameters. But start analyzing
the db, (you find a lot of tools on the net) and alter your parameters
until you find they are ok.
I am sorry, but I can not give you the actual size you must take.
It is a little bit trial an error.
But search on the net and you will find a LOT.
Greetings,
Thierry.
PS: Something I found:
Subject: Calculating the size of the Shared Pool
Modified: 03 Oct 94 02:05:28
Author: EEHRSAM
___________________________________________________________________________
Shared Pool Calculation
Shared pool is the amount of fixed, preallocated space in the SGA for
use by multi-threaded server session PGA, shared SQL area, and other
small, dynamically allocated SGA data structures.
Shared Pool
===========
Since shared pool usage is highly application dependent, it is necessary
to examine each database application individually in order to project a
recommended shared pool value.
While analyzing shared pool sizing, it is helpful to first increase the
shared pool to a very large value, so that the dynamically allocated SGA
structures may be allowed to expand to a desirable size. Once this sizing
exercise has been completed, the shared pool may be downsized to the
appropriate value.
Shared pool calculation is especially critical when the multi-threaded
server is in use because the PGA for each multi-threaded server database
user will be allocated from shared pool.
Formula
-------
Max Session Memory * No. of concurrent users
+ Total Shared SQL Area Usage
+ PLSQL Sharable Memory
+ Minimum 30% Free Space
----------------------------------------------
= Minimum Allowable Shared Pool
Example
-------
Find the SID for an example user session:
SQLDBA> select sid from v$process p, v$session s
2> where p.addr=s.paddr and s.username='OPS$JSMITH';
SID
----------
29
1 rows selected.
Get the maximum session memory for this session:
SQLDBA> select value from v$sesstat s, v$statname n
2> where s.statistic# = n.statistic#
3> and n.name = 'session uga memory max'
4> and sid=29;
VALUE
----------
273877
1 rows selected.
Get the total shared SQL area:
SQLDBA> select sum(sharable_mem) from v$sqlarea;
SUM(SHARAB
----------
8936625
1 row selected.
Get the PLSQL sharable memory area:
SQLDBA> select sum(sharable_mem) from v$db_object_cache;
SUM(SHARAB
----------
4823537
1 row selected.
Example shared pool calculation:
274K shared memory * 400 users
+ 9M Shared SQL Area
+ 5M PLSQL Sharable Memory
+ 60M Free Space (30%)
---------------------------------
= 184M Shared Pool
In this example, the recommended shared pool value is 184M.
Out of Shared Memory (Error ORA-4031)
-------------------------------------
The 2 most common causes of the error "Out of shared memory" are lack of
available shared pool and lack of available contiguous shared pool into
which to map large PL/SQL packages. In order to avoid the latter, it is
recommended that the application DBA pin all large packages using the
sys.dbms_shared_pool.keep procedure.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
