maxssiz_64bit

[Steve Dirschel]

We are attempting to ensure our database server can handle 9000 database connections. At 1 point in the past we ran into a problem where new sessions couldn't be spawned because maxssiz_64bit wasn't sized correctly. Since then it has been increased from 268435456 to 1073741824. I'm a DBA, not a sysadmin- could someone please clarify a few points related to this parameter:

1. Is that parameter per unix process or a combination of all processes an individual user owns? Meaning if all db connections come in under owner oracle does each individual process get to use up to 1073741824 or the combination of all processes owner by oracle are limited to that? Based on the problem in the past I believe it's all processes owned by oracle but a sysadmin has told me differently
2. Is there a relatively easy way to find out how large the stack size is for all my oracle connections? I can go into gpm, select a process, drill down to memory. I've seen some at 192k, some at 256k, and some at 320k. Right now I have 2000 db connections so I'd like to be able to see where stack usage is at under 2k users so I can estimate needs under 4.5 times user volumn.

Assuming this value is a limit for all processes running under Oracle I can see we might have a problem with the current limit
@320k per session = 3200 sessions
@256k per session = 4000 sessions
@192k per session = 5333 sessions

I need more accurate stack usage statistics for all oracle sessions without having to go through 1 by 1 in gpm (imagine doing that to 2k sessions).

Thanks in advance

[Stuart Abramson_2]

Here is how I look up kernel parameter descriptions. There may be a better way:
..login as root
..set you display
..sam
....kernel configuration
......configurable parameters
........highlight the parameter in question
........hit "F1"

This brings up comments which describe the parameter.

"maxssiz_64bit" and his friends are for a SINGLE process. Your SystemAdmin told you the truth...

2. "ps -elf" shows you process size. However, I don't know how to break that down into data segment, stack, etc.

3. On hp-ux 11.0 systems there is a document called:
../usr/share/doc/mem_mgt.txt
that explains memory usage. Try that. It's gone in hpux 11.11.

[A. Clay Stephenson]

As indicated, maxssiz and maxssiz_64bit refer to any single process but I must say that anything 32 or 64-bit that needs a stack larger than about 64MB (and that is very generous) is seriously miscoded. I might make an exception is this is FORTRAN or linking FORTRAN libraries but otherwise rather than needing a large stack memory should have been dynamically allocated and that is limited by maxdsiz.

Error messages sometimes mention the stack when that is really not the problem and instead the real limit is maxdsiz.

[Mike Stroyan]

The maxssiz_64bit limit is per unix process. It is the maximum size that each 64-bit process can grow its first thread's stack to. (Threaded processes have a separate thread for each stack. Those have limits set by calling either pthread_default_stacksize_np or pthread_attr_setstacksize.)

Here is a small program that will report the current stack size of all processes using the pstat_getproc function.

[Steve Dirschel]

Thanks for the replies. What still doesn't make sense to me is if it's the limit for individual processes why Oracle documentation recommends a value of 1,073,741,824. When I look at any given dedicated process or even the oracle background processes through gpm I see values like 192k, 256k, 320k for stack memory used. Would anyone see the possibility that each dedicated db connection is somehow tied together and that limit would be for all the connections together?

[John Palmer]

Maybe the clue is in your original question: 'New sessions couldn't be spawned'.

Could it be that its the Oracle Listener process that was hitting the stack limit? This is the process that normally spawns Oracle sessions.

maxsssiz_64bit definitely relates to each process, there's nothing cumulative system wide about it.

Regards,
John