Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

How many memory a oracle9.2 server process allocate ?

SOLVED
Go to solution
稀里哗啦
Advisor

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?
6 REPLIES
Ravi_8
Honored Contributor

Re: How many memory a oracle9.2 server process allocate ?

Hi,

Oracle 9i needs more memory than 8i both in terms of file size and SGA
never give up
稀里哗啦
Advisor

Re: How many memory a oracle9.2 server process allocate ?

Can someone tell me How about memory usage in your site?
Christian Gebhardt
Honored Contributor

Re: How many memory a oracle9.2 server process allocate ?

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
Honored Contributor

Re: How many memory a oracle9.2 server process allocate ?

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
Occasional Visitor

Re: How many memory a oracle9.2 server process allocate ?

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
Honored Contributor
Solution

Re: How many memory a oracle9.2 server process allocate ?

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)