topic Re: Setting Account Quotas for Oracle in Operating System - OpenVMS

Setting Account Quotas for Oracle

Srikanth Arunachalam — Thu, 26 Oct 2006 12:39:04 GMT

Hi,

Can someone let me know if the following quotas can be specified for oracle user on HP OpenVMS 8.1-2. This will be a build system which will not involve much of database activity. The only activity that goes on this system is compilation and validation. May I know what will be the respective quota value on those systems where heavy load of database transactions happen.

Account Quota Minimum Value Quota Name
ASTLM
250 (the default)
Asynchronous system trap limit

BYTLM
750000
Buffered I/O limit

ENQLM
2000 (the default)
Enqueue quota

FILLM
2048
Open file quota

JTQUOTA
8192
Job table quota

MAXDETACH
0 (the default)
Max detached processes

MAXJOBS
0 (the default)
Max active jobs

PGFLQUOTA
1,500,000 (recommended)
Paging file quota

WSDEFAULT
2048
Working set default

WSEXTENT
8192
Working set extent

WSQUOTA
4096
Working set quota

Thanks,
Srikanth

Re: Setting Account Quotas for Oracle

Jan van den Ende — Thu, 26 Oct 2006 14:24:55 GMT

Srikanth,

with no recent experience on VMS+Oracle, I only can give general indications.

ASTLM, ENQLM, FILLM, MAXDETACH, MAXJOB are looking good to generous. Some special cases may want one of m higher, but it would be SPECIAL.
BYTLM looks rather low. The cost of it is trivial, so I would advise 2 4, or (especially on IA64) maybe 10 M
JTQUOTA depends on your app. Should be enough, or ORACLE would have advised more.
PGFLQUOTA might turn out scrappy if you run big databases. I usually use about double a recommended value.
WSQUOTA I would prefer 8 or 16 K on modern systems, maybe evn 32 or 64 K on IA64

The one that really sticks out as plainly WRONG is WSEXTENT. Make this BIG. AFAIK the limit of 64K very much enlarged with VMS V8.x
Modern systems tend to have much memory. Therefor, allow processes to USE it if/when no one else needs it.

hth

Proost.

Have one on me.

jpe

Re: Setting Account Quotas for Oracle

Karl Rohwedder — Thu, 26 Oct 2006 15:29:36 GMT

Perhaps the ENQLM may need some increase.

regards Kalle

Re: Setting Account Quotas for Oracle

Srikanth Arunachalam — Thu, 26 Oct 2006 16:38:18 GMT

Hi Jan,

If Bytlm is suppose to hold 2M of value, should I insert value of 2147483648.

thanks,
Srikanth A

Re: Setting Account Quotas for Oracle

Allan Bowman — Thu, 26 Oct 2006 20:20:27 GMT

It won't matter - you can use 2000000 if it is easier to read. Some of the smaller numbers should be a power of 2, but the larger ones shouldn't matter.

Allan in Atlanta

Re: Setting Account Quotas for Oracle

Galen Tackett — Fri, 27 Oct 2006 06:02:38 GMT

Don't forget that the effective quota is the maximum of the UAF quota and the SYSGEN PQL_Mxxxx sysgen parameter.

If, for example, a user's PGFLQUOTA in SYSUAF is 200000 but PQL_MPGFLQUOTA is 2000000, you'd have to increase the SYSUAF a long way (by at least 1800000) before the user's processes would actually see any difference.

Re: Setting Account Quotas for Oracle

Steven Bearden — Fri, 27 Oct 2006 09:48:32 GMT

We are running Oracle in a heavy production and development environment on Integrity Servers VMS 8.2-1 and these are the quotas we use as a minimum (Be sure to modify modparams.dat as needed). Good Luck.

Maxjobs: 0
Fillm: 2048
Bytlm: 500000
Maxacctjobs: 0
Shrfillm: 0
Pbytlm: 0
Maxdetach: 0
BIOlm: 400
JTquota: 4096
Prclm: 100
DIOlm: 200
WSdef: 8192
Prio: 4
ASTlm: 200
WSquo: 32768
Queprio: 0
TQElm: 200
WSextent: 262144
CPU: (none)
Enqlm: 2000
Pgflquo: 2000000

Re: Setting Account Quotas for Oracle

comarow — Fri, 27 Oct 2006 11:05:59 GMT

One tip.

If you are not using Reserved Memory, make sure your Global Pages are very generous, or you won't be able to restart Oracle without a reboot.

Re: Setting Account Quotas for Oracle

GuentherF — Fri, 27 Oct 2006 11:15:54 GMT

If this is for building the Oracle Server (not Rdb I assume) the quotas look ok. If there is a quota too low the application (compiler, linker) will fail. One change though to lower pagefaults: WSEXTENT=size of physical memory.

re Galen:
"If, for example, a user's PGFLQUOTA in SYSUAF is 200000 but PQL_MPGFLQUOTA is 2000000, you'd have to increase the SYSUAF a long way (by at least 1800000) before the user's processes would actually see any difference."

In this case (PQL_MPGFLQUOTA=2000000) ALL processes would at least get a minimum of 2000000 for their PGFLQUOTA.

/Guenther

Re: Setting Account Quotas for Oracle

Srikanth Arunachalam — Fri, 27 Oct 2006 11:30:51 GMT

Hi Everybody,

thanks for your response. I had good info from all of you. I will close the fir with this note.

Thanks,
Srikanth

Re: Setting Account Quotas for Oracle

Srikanth Arunachalam — Fri, 27 Oct 2006 11:31:50 GMT

Thanks for all the comments.