Oracle shadow process memory

[Ian Lochray]

We have an application that uses dedicated Oracle shadow processes. Currently the application used Oracle 8.1.7.3. I have built and run the same application code on Oracle 9.2.0.3 but I have noticed that the Oracle shadow processes use double the memory that they used at 8.1.7.3. I have tried playing with pga_aggregate_target but this had made no difference.
I have logged a call with Oracle but wanted to know if anyone out there had seen the same thing.

[Duncan Edmonstone]

Wild guess...

is the 8.1.7.3 a 32-bit app, and 9.2.0.3 a 64-bit app?

Cheers

Duncan

[Ian Lochray]

Duncan,
although Oracle 9i is 64-bit only on HP-UX, the application I am using is still 32-bit and uses the Oracle 32 bit libraries in $ORACLE_HOME/lib32.
Even if the application was 64-bit, would it really lead to an Oracle shadow that is twice the size?

[Duncan Edmonstone]

Ian,

I don't know! Like I said I was just guessing... I'm sure a better mind than mine will be able to give you a better answer..

Sorry

Duncan

[Gene Kornacki_3]

What method are you using to measure how much memory is being taken up by the processes in question?

[Indira Aramandla]

Hi,

You can measurement these using v$sesstat to look up pga and uga memory. This will give you a better estimate of memory increases per session across versions.

Its true that Oracle 8.1.7 needs more memory for the shadow processes.
In the case of an Oracle shadow process or foreground process the text segment will always be shared as it is the same program. The shadow process will have a shared segment, the SGA. This memory should only be counted for the first process.

Using pmap utility (pmap is a Solaris utility only), you can determine the following:

1)How much memory a process is using

2)How much of that memory is shared

You can also use the normal 'ps' utility.
This 'ps' utility will print out information about any process currently active on the system. Using 'ps' with the '-l' will cause 'ps' to print out the SZ field, which contains the virtual size of the process's non-text segments, measured in pages. While this figure is an accurate measure of the virtual memory being used by this process, it is not accurate if the process has attached a shared memory segment. This means that when sizing memory, you must subtract the size of the SGA from the virtual memory used by all of the Oracle background and shadow processes.

By using PMAP you can also see that the SGA is always part of the shared memory of a shadow process. This is normal. To get the real amount of memory always subtract the SGA. Determining the memory for the shadow processes of 8.1.7 this way will give you a good idea of your memory requirement. Otherwise you can use the following query to determine the sum of PGA and UGA memory which will also give you some idea on your total memory requirement:

compute sum of maxmem on report
break on report

select se.sid,n.name, max(se.value) maxmem
from v$sesstat se,v$statname n
where n.statistic# = se.statistic#
and n.name in ('session pga memory','session pga memory max',
'session uga memory','session uga memory max')
group by n.name,se.sid
order by 3
/

Further for reducing the memory required for the shadow processes you can use the init parameters SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE to some extent. These control the size of the UGA and PGA.

If you find your available memory is not sufficient to run a dedicated server you can very well go for MTS but at the cost of reduced performance of the application due to increased wait times.

[Brian Crabtree]

Ian,

Overall, the answer you are looking for is, "Yes, 9i takes up twice as much memory as 8i". This was also true in the change from 8.0 to 8i as well. I wouldn't worry about it to much, as the "ps -elf" command will show you the total amount of memory that the process is taking up, but this aso includes the shared memory that Oracle takes up as well.

Overall, I wouldn't worry about it to much, and would just keep an eye on available memory. Inrdu posted well on how to reduce the pga and sga as needed.

Brian

[Yildiz_1]

Oracle 9i documentation says:

With PGA_AGGREGATE_TARGET, sizing of work areas for all dedicated sessions is automatic and all *_AREA_SIZE parameters are ignored for these sessions.

I have moved from Oracle 7 to 9i and it needs 2-3 times more Memory.

[Nicolas Dumeige]

Look at metalink Note:174555.1
UNIX: Determining the Size of an Oracle Process

As for the PGA_AGGREGATE_TARGET, Oracle will allocate min(5% PGA_AGGREGATE_TARGET, 100MB) to each process.

This rule is not enforced drasticaly, we've seen pga consumption per process far more than 100 Mo.

[Bill Hassell]

Just a note on using ps to examin process memory. ps -efl shows memory usage in pages (4k) which is not the most useful unit of measure for a sysadmin. Try this:

UNIX95= ps -u oracle -o vsz,pid,args | sort -rn

That shows the amount of memory used in Kbytes. To look at everything sorted by memory usage:

UNIX95= ps -eo vsz,pid,args | sort -rn | more

As mentioned, SGA (shared memory) is not counted because it really isn't counted for any single process.