How many memory a oracle9.2 server process allocate ?

[稀里哗啦]

In oracle8i, a server process allocate about 37m memory.
But when I install oracle9.2, I found each process occupy about 67m memory.
Is it OK?

[Ravi_8]

Hi,

Oracle 9i needs more memory than 8i both in terms of file size and SGA

[稀里哗啦]

Can someone tell me How about memory usage in your site?

[Christian Gebhardt]

Hi

The size of the server proceses depends on many init parameters such as SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE, etc ...
It's known as PGA (Process Global Area).

Oracle9i provides an option to completely automate the management of PGA memory available to an instance using a newly introduced initialization parameter PGA_AGGREGATE_TARGET. The database server automatically distributes this memory among various active queries in an intelligent manner so as to ensure maximum performance benefits and the most efficient utilization of memory.

More about sizing of PGA in METALINK Note:223730.1

Chris

[Christian Gebhardt]

Hi

The size of the server proceses depends on many init parameters such as SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE, etc ...
It's known as PGA (Process Global Area).

Oracle9i provides an option to completely automate the management of PGA memory available to an instance using a newly introduced initialization parameter PGA_AGGREGATE_TARGET. The database server automatically distributes this memory among various active queries in an intelligent manner so as to ensure maximum performance benefits and the most efficient utilization of memory.

More about sizing of PGA in METALINK Note:223730.1

Chris

[Steven Givens]

I'm not sure where you are at on this issue, but I thought I'd let you know that the Oracle patch 9.2.0.2.0 contained a number of bug fixes that reduced the amount of memory each shadow process was taking up. We saw a decrease of about 24 Meg for each process.

[Yogeeraj_1 · Accepted solution]

hi,

this is from metalink:

There are no thumb rules or exact observations as to increase in CPU or memory usage in 9i vis-a-vis 8i. However one can always have a comparison after migrating from 8i to 9i , keeping in mind the associated changes in the environment that may have happened (e.g. init.ora parameters).

However I would also like to comment that in 9i , as far as, memory usage is concerned we have now have better control than in 8i.
Prior to 9i the memory required for running an instance was roughly calculated as SGA size + (number> of users * ?Meg).

If you use the Automatic SQL Execution Memory Management (In short, Auto PGA:) ) feature of Oracle9i, you should not have to perform any calculation based on the number of users. In many sense, the number of users hardly affects memory utilization, it is actually your workload. You should start with a reasonable sized SGA and PGA (using the PGA_AGGREGATE_TARGET parameters) and adjust the configuration according to the database feedback.
There are many ways to collect this feedback. The buffer cache advisory is one of them. A high numbers of RELOADS as indicated by the v$librarycache view should help you in sizing the shared pool. The V$PGASTAT view can be used to adjust the size the PGA. The next 9i release may make the memory tuning of an Oracle instance even easier by including advisories for the shared pool and PGA ,besides the buffer cache.

For more details on memory tuning, please take a look at our performance tuning guide at:

http://st-doc.us.oracle.com/9.0/901/server.901/a87503/memory.htm.

As for CPU load , I do not see any significant change , unless your explain plans for sqls changes, become poorer in respect to IO, or you are using more parallelization etc.


hth
Yogeeraj