Umar DBA: Tackling ORA-4030 on WindowsNT

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

Additional Search Words

-----------------------

4030 process memory windows esma 2GB ORA-4030

Umar DBA