Tags: 2gb, 7gb, database, exported, file, memory, mysql, oracle, overall, situationi, sql, srv, widnows
How much memory does oracle need?
Here's my situation:
I have Oracle 8.1.6 on Widnows 2000 Srv with 2GB memory.
My database is not very big, overall exported file is 7GB.
My oracle is using up all 2GB memory right now. My question is if this is normal? I know it has a lot to do with init.ora file setting, but still the question remains.
Leave a comment...
- 3 Comments
- Here's a liitle calculation that I use as a guideline ... AGAIN Guideline ... I make adjustments as needed once the system is up and running...
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
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.
Max Session Memory * No. of concurrent users
+ Total Shared SQL Area Usage
+ PLSQL Sharable Memory
+ Minimum 30% Free Space
= Minimum Allowable Shared Pool
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';
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;
1 rows selected.
Get the total shared SQL area:
SQLDBA> select sum(sharable_mem) from v$sqlarea;
1 row selected.
Get the PLSQL sharable memory area:
SQLDBA> select sum(sharable_mem) from v$db_object_cache;
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.
Gregg#1; Tue, 11 Dec 2007 13:41:00 GMT
- Oracle uses the System Global Area (SGA) for caching data, properly sizing this is important for getting good performance from an Oracle database. The size of the SGA is determined by the settings in your INIT.ORA file. On NT this is probably C:\ORANT\DATABASE\INIT<sid name>.ORA. Please post this file and someone can probably point out the parameter that is using up all your memory.
The parameters that chew up the most memory are DB_BLOCK_BUFFERS (Oracle's data cache), and SHARED_POOL_SIZE (cache for data dictionary). These are important for performance but they can probably be decreased based on the size of your database.
Oracle needs memory for the SGA, for the system processes, and for user processes (by default there is one user process created for each user attached to the database). You can also use the MTS (Multi-Threaded Server) to allow multiple users to connect to each user process. Normally, you take the total amount of memory, subtract out what is needed for other applications, subtract off the number of users times the size of each user process, and dedicate the rest to the SGA. In your case (because you have a pretty small database so your cache can be smaller too) you should have a lot of room for decreasing the size of the SGA without making much impact on performance.#2; Tue, 11 Dec 2007 13:42:00 GMT
You can calculate and measure until you're blue in the face, but on NT Oracle doesn't actually allocate the memory until it's needed. You can have an Oracle instance configured with a 10Gb SGA, shown as only using 100Mb under NT.
(I have no idea how it deals with this under *nix).
When sizing, comparing etc, add the line PRE_PAGE_SGA=YES into your Init.Ora file. This will force Oracle to pre-allocate all needed memory on startup.
You will get a much better idea of exactly what Oracle is using, but beware - it can still increase :-) Check the docs for further info.
Bill#3; Tue, 11 Dec 2007 13:43:00 GMT