[askdba] Re: ORA - 04030
- From: PV Narayanan <PNarayanan@xxxxxxxxxxxxxxxxxx>
- To: askdba@xxxxxxxxxxxxx
- Date: Wed, 29 Sep 2004 15:05:27 +0400
Hi vinod,
Also refer to the below metalink note 116076.1 for 04030 issues with
windows environment.
PURPOSE:
========
Solutions to alleviate the problem when encountering an ORA-4030 on an NT
system.
SCOPE & APPLICATION:
====================
This document intends to provide information on how to circumvent the
ORA-4030
error on WindowsNT systems and also suggests workarounds for the same.
RELATED DOCUMENTS:
==================
[NOTE:28299.1] Windows NT Architecture Guide
[NOTE:46001.1] Oracle and the Windows NT memory architecture
[NOTE:46053.1] Windows NT Memory Architecture Overview
PROBLEM STATEMENT:
=================
An ORA-4030 when executing reports or when connecting to the NT instance.
PROBLEM EXPLANATION:
===================
The primary reasons for an ORA-4030 to be presented is that the virtual
memory
model on which NT operates has reached its limit. NT has a memory
architecture
wherein 2G is allocated for the user processes and 2G for the system hence
the
addressability is 2^31-1. Now when the virtual memory counter in
performance
manager has reached 2G ,the maximum memory that is addressable has been
attained
and ORA-4030 will start surfacing. Also a TNS internal limit has been
reached
may also appear in certain cases.
There are 3 problem points which can be checked in this scenario.
( More to be added are welcome )
a)The system SGA (SGA's in case of multiple instances) occupy memory which
is
nearing 2G. The aggregate of the SGA should be lesser than 2G to allow
the
buffer for the process memory i.e the shadow processes which will be
forked
on connections to have their share of space. Hence reduction of the SGA
components would provide a workaround . However this may not be a
feasible
component as the performance would go down.
b)The sort area size parameter of the instances could be reduced to reduce
the
per process committal memory allocated when the connection is made .
This will
give the chance for more connections as more memory will be available.
Note
that committal memory is that memory that is allocated and backed by a
data
store ( pagefile / physical memory pages ) and reserved memory is that
which
is just allocated and not backed up. These memory components could be
checked
using the orastack utility which is provided.
The defaults are as follows
G:\oidhome\bin>orastack oracle.exe
Dump of file oracle.exe
Current Reserved Memory per Thread = 1048576
Current Committed Memory per Thread = 4096
For connections that come from the listener the stacks on the tnslsnr.exe
executable can be reduced.
However care should be taken when adjusting these settings .
Refer to notes:
[NOTE:28299.1] Windows NT Architecture Guide
[NOTE:46053.1] Windows NT Memory Architecture Overview
[NOTE:46001.1] Oracle and the Windows NT memory architecture
for information about NT architecture and memory allocation.
c)This is most favourable method
Increase the addressability of the process from 2G to 3G without changing
the
API of the application.The details of the same are present in
[NOTE:46001.1]
however here is the essence
1)The customer should have Winnt Enterprise Edition with Service pack 3
at
least
2)You need to modify the executables by using the imagecfg utility to
set a
flag in the header of the executables so that they take advantage of
the
3G allocated. The system in turn will occupy only 1G of RAM.
3)Exploiting the Intel pentium II and III architectures (32 bit)
support for
the Extended Server Memory Architecture (ESMA) which is also
supported by
8i.
4) The oracle8i parameters which help in exploiting ESMA are
VLM_BUFFER_MEMORY
USE_INDIRECT_DATA_BUFFERS
Details for this could be found in the Oracle Server Reference
Guide.
SOLUTION:
========
1) Reduce system SGA
2) Reduce PGA/UGA contents (Sort_area_size)
3) Increase memory addressability from 2G to 3G (this requires WindowsNT
Enterprise edition plus SP3) by doing the following :
Here is the boot.ini on the system drive
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
[operating systems] multi(0)disk(0)rdisk(0)partition(2)\WINNT="Windows NT
Server Version 4.00" /3GB
multi(0)disk(0)rdisk(0)partition(2)\WINNT="Windows NT Server Version 4.00
[VGA mode]" /basevideo /sos
Note the /3GB setting added to the 4th line above
Also copy the imagecfg utility (Present on the Windows NT cdrom in the
support
folder) and use the following command
Imagecfg -l oracle.exe
and if connections are via a listener then also
Imagecfg -l tnslsnr.exe
Best regards
P.V.Narayanan
Other related posts:
