首页 > Linux操作系统 > Linux操作系统 > How To Avoid ORA-04030/ORA-12500 In 32 Bit Windows Environment [Video]

How To Avoid ORA-04030/ORA-12500 In 32 Bit Windows Environment [Video]

原创 Linux操作系统 作者:spider0283 时间:2011-12-16 22:44:33 0 删除 编辑
 [ID 373602.1]

 17-AUG-2011     类型 PROBLEM     状态 PUBLISHED

In this Document
     Reducing Memory =============== 
     Shared Server ============= 
      Conclusion ========== 

Applies to:

Oracle Server - Enterprise Edition - Version: to - Release: 9.2 to 10.2
z*OBSOLETE: Microsoft Windows 2000
Microsoft Windows (32-bit)
z*OBSOLETE: Microsoft Windows XP
z*OBSOLETE: Microsoft Windows Server 2003 R2 (32-bit)
MS Windows 2000Microsoft Windows XPMicrosoft Windows Server 2003 R2 (32-bit)MS Windows NT


Checked for relevance on 04-MAR-2010

You are encountering ORA-04030 or ORA/TNS-12500 in the 32 bit windows environment.
It is assumed that your server machine has less than 16GB of physical RAM, and that both
the /3GB and /PAE switches have been set in the boot.ini file (see Note 225349.1 to understand
why this is significant).

This document is intended as an adjunct to the excellent information in

Note 225349.1 "Using Address Windowing Extensions (AWE) or VLM on Windows"
Note 46001.1 Oracle Database and the Windows NT memory architecture
Note 46053.1 Windows NT Memory Architecture Overview

and it is assumed that these Notes have been read.

The purpose of this article is to go into further detail with respect to the
spread of memory usage in a 32bit Windows environment, in the hope that errors such
as ORA-4030 and ORA/TNS-12500 may be avoided or reduced.


Just to recap the information in the notes above, a 32-bit Windows machine by default will allow 
a user process to address only 2GB of memory, leaving the other 2GB for the OS.  By setting the 
/3GB switch we can allow this to increase to 3GB for a user process - oracle.exe, in this case. 

By using PERFMON (not Task Manager, which only shows the working set, ie recently-used pages) 
and looking at the Virtual Bytes field we can see at any one time how much memory is being used. 
By using the AWE (PAE) facility and the init.ora parameter USE_INDIRECT_DATA_BUFFERS 
we can allow a much larger data buffer cache to be used, as memory from outside the 3GB 
space can be mapped to the Oracle process. However this indirect memory can ONLY be 
used for data buffers. The other SGA components must still come out of the 3GB. 

But please be aware of Bug 4494543 - affecting 10g and fixed in Oracle 11.0 ...... 

Rediscovery Information: 
1) Using 32-Bit Oracle on a 32-Bit Windows 2003 server running on an AMD Opteron 64-Bit chip. 
2) You have set use_indirect_data_buffers=true in init.ora 

Workaround: Basically disable NUMA feature on 32-Bit platform.:- 
1) Set _ENABLE_NUMA = FALSE in Windows registry for the Oracle Home. 
2) Set _enable_NUMA_optimizations = FALSE (init.ora) 


So how is the 3GB of memory divided up by Oracle? 

3GB = 
a) AWE_WINDOW_MEMORY (used to address/map the indirect data buffers) + 
b) non-buffer_cache part of the SGA (Shared Pool, Large Pool, Java Pool, Streams Pool, Log buffer) + 
c) Oracle Process overhead + 
d) (Thread stack x number of threads(ie Oracle processes)) + 
c) PGA 

So which of these can we alter, and under what circumstances? 

The default value for AWE_WINDOW_MEMORY is 1 GB, although we can reduce this by changing 
its value in the Windows registry.  However, it is the area where we must map the data buffers 
obtained from the indirect buffer cache, and there are some restrictions (see Note 225349.1
so let's assume we leave it at 1 GB. 

If you are not using Java or Streams then you can set these init.ora params to zero. 

The Log buffer is small in comparison to the other areas and usually a value of 1 to 5 MB 
is adequate, so let's assume we set it to 5 MB. 

The Large Pool can also be set to zero if not being used (see the section on Shared Server below for more details). 

The Shared Pool size is dependent on a number of factors to do with your application, and how much 
of your SQL is shared, and if we make it too small we could end up with a number of problems. 
Let's assume for the moment it is 250MB. 

The Oracle process overhead is fixed, dependent on what options you have, so let's assume it is 100MB 

Thread stack size is by default 1 MB, and the number of processes/threads is dependent on 
your application requirements, and whether you are using Shared Server (Multi-Threaded Server). 
For the purposes of illustration let's assume for the moment we have 500 concurrent users and we 
are not using Shared Server. 

So in our example, with the default and assumed values, and 500 users we have 

3GB = 1000MB + 5MB + 250MB + 100MB + 500MB + PGA = 1855MB + PGA 

So we could have approximately 1.1GB of PGA. To understand more about the PGA see 
Note: 223730.1 Automatic PGA Memory Management in 9i (applies to 10g as well). 

Within the PGA we have "tunable" and "non-tunable" areas. The tunable part is memory 
allocated for intensive SQL operations such as sorts, hash-joins, bitmap merge, 
and bitmap index create. This memory can be shrunk and expanded in response to system load. 
However, the non-tunable part cannot be managed in the same way. Importantly the non-tunable 
part includes cursors. We can control the number of cursors by the init.ora OPEN_CURSORS 
parameter, but if this is exceeded we get an ORA-1000 error, which is clearly undesirable. 
See Note.1012266.6 "Overview of ORA-1000 Maximum Number of Cursors Exceeded" for more info. 
More importantly, however, we have no control over the size of a cursor, and users may 
open very large cursors dependent on their SQL or PLSQL. 

Also note that if we set PGA_AGGREGATE_TARGET too small to accommodate the non-tunable 
part of the PGA plus the minimum memory required to execute the tunable part then Oracle 
cannot honour the PGA_AGGREGATE_TARGET value, and will attempt to allocate extra memory. 
This is known as overallocation, and an estimation of this can be seen in the view 

To get an idea of the current PGA memory used we can run a query like :-

col statistic# form. 999 
col name form. a30 
col value form. 999999999999 

SELECT statistic#, name, value 
FROM v$sysstat 
WHERE name like '%memory%'; 

This gives a sum of PGA and UGA memory used by all sessions, eg 

---------- ------------------------------ ------------- 
15 session uga memory 847164 
16 session uga memory max 1108988 
20 session pga memory 9377704 
21 session pga memory max 9565800 
229 workarea memory allocated 0 
245 sorts (memory) 570 

6 rows selected. 

However, it may be more useful to find out what memory individual sessions are using :-

FROM v$sesstat 
WHERE statistic# in (15,16,20,21) 
ORDER BY value DESC ; 

        SID  STATISTIC#       VALUE 
----------     ----------   ------------- 
             3                20         5412516 
             3                21         5412516 
             2                20         1870624 
             2                21         1870624 
             8                21           528860 
             5                20           438904 
             5                21           438904 
             8                20           406300 
             4                20           322068 
             4                21           322068 
             9                21           318572 


The SID from the above query will allow you to find the session details from V$SESSION, 
and investigate those sessions using large amounts of PGA memory. 

(N.B. The statistic number associated with the names may change from release to release 
so you should always run the first piece of SQL to get the statistic number.) 

Reducing Memory 

Let's now take a look at ways to reduce some of the above components which 
may allow us to have more PGA memory available. 

Each thread within Oracle uses a default 1 MB stack space. However, we can 
reduce this to 500KB by using ORASTACK. See Note 46001.1  for more details wrt ORASTACK. 
From the bin directory run

orastack oracle.exe 

...and it will show that 1 MB is currently being used.

To reduce this ensure that oracle.exe is not currently active, then run

orastack oracle.exe 500000 

You can do this for all relevant executables, eg tnslsnr.exe, sqlplus.exe, dbsnmp.exe 

Remembering our example of 500 users we can "reclaim" 500 x 500KB of memory (250MB) just by 
doing this. 

Shared Server 

Another way to reduce the memory used by many users is to use Shared Server (formerly 
Multi-Threaded Server). When Shared Server is used instead of having a shadow thread 
for each connection we utilize relatively few shared servers, and a number of dispatcher threads. 
For each session there is data that must be available to other shared servers, and data that 
must remain private. 

UGA is the User Global Area, which is within the PGA in dedicated server mode. 
In shared server mode the memory for the UGA comes out of the Shared Pool, or, if it is set, out 
of the Large Pool.  In this way we can service our 500 concurrent users with perhaps fewer 
than 50 server threads (shared server threads & dispatcher threads).

The PGA of a shared server process does not contain user-related data (which needs to be 
accessible to all shared server processes).  The PGA of a shared server process contains 
stack space and process-specific variables.

All session-related information is contained in the SGA.  Each shared server process needs 
to be able to access all sessions' data spaces so that any server can handle requests from 
any session.  Space is allocated in the SGA for each session's data space.  You can limit the 
amount of space that a session can allocate by setting the resource limit PRIVATE_SGA to 
the desired amount of space in the user's profile.

Note that sort_area_size and hash_area_size reside in the PGA, but sort_area_retained_size resides 
in the UGA (as it must be available to other shared servers). 

N.B. Certain administrative activities cannot be performed while connected to a dispatcher process, 
including shutting down or starting an instance, and media recovery.  An error message is 
issued if you attempt to perform. these activities while connected to a dispatcher process. 
These activities are typically performed when connected with administrator privileges.  When 
you want to connect with administrator privileges in a system configured with shared servers, 
you must state in your connect string that you want to use a dedicated server process 
(SERVER=DEDICATED) instead of a dispatcher process.  Therefore we still have a small overhead 
for dedicated servers. 

Oracle recommends using the large pool to allocate the shared server-related User Global Area (UGA), 
rather that using the shared pool.  This is because Oracle uses the shared pool to allocate 
System Global Area (SGA) memory for other purposes, such as shared SQL and PL/SQL procedures. 
Using the large pool instead of the shared pool decreases fragmentation of the shared pool. 
To store shared server-related UGA in the large pool, specify a value for the initialization 
parameter LARGE_POOL_SIZE. To see which pool (shared pool or large pool) the memory for an object 
resides in, check the column POOL in V$SGASTAT. The large pool is not configured by default; its 
minimum value is 300K.  If you do not configure the large pool, then Oracle uses the shared pool 
for shared server user session memory. 

If a shared server architecture is used, then Oracle allocates some fixed amount of memory (about 10K) 
for each configured session from the shared pool, even if you have configured the large pool. 
The CIRCUITS initialization parameter specifies the maximum number of concurrent shared server 
connections that the database allows. 

The exact amount of UGA Oracle uses depends on each application.  To determine an effective setting 
for the large or shared pools, observe UGA use for a typical user and multiply this amount by the 
estimated number of user sessions.  Even though use of shared memory increases with shared servers, 
the total amount of memory use decreases.  This is because there are fewer processes; therefore, Oracle 
uses less PGA memory with shared servers when compared to dedicated server environments. 

Statistic                                Description 
------------------                --------------------------- 
session UGA memory          The value of this statistic is the amount of memory in bytes allocated to the session. 
Session UGA memory max  The value of this statistic is the maximum amount of memory in bytes ever allocated
                                            to the session. 

To find the value, query V$STATNAME.  If you are using a shared server, you can use the following query 
to decide how much larger to make the shared pool.  Issue the following queries while your application 
is running:

WHERE NAME = 'session uga memory' 

WHERE NAME = 'session uga memory max' 

These queries also select from the dynamic performance view V$STATNAME to obtain internal identifiers for 
session memory and max session memory. The results of these queries could look like the following: 

157125 BYTES 

417381 BYTES 

The result of the first query indicates that the memory currently allocated to all sessions is 157,125 bytes. 
This value is the total memory with a location that depends on how the sessions are connected to Oracle. 
If the sessions are connected to dedicated server processes, then this memory is part of the memories of 
the user processes.  If the sessions are connected to shared server processes, then this memory is part of 
the shared pool. 

The result of the second query indicates that the sum of the maximum size of the memory for all sessions 
is 417,381 bytes. The second result is greater than the first because some sessions have deallocated memory 
since allocating their maximum amounts. 

If you use a shared server architecture, you can use the result of either of these queries to determine 
how much larger to make the shared pool. The first value is likely to be a better estimate than the second 
unless nearly all sessions are likely to reach their maximum allocations at the same time. 

You can modify the dispatchers and shared servers with the following parameters :- 


To assess dispatcher performance, query the V$DISPATCHER_RATE view and compare the current values with the 
maximums.  If your present system throughput provides adequate response time and current values from this view are 
near the average and less than the maximum, then you likely have an optimally tuned shared server environment. 

Steadily increasing wait times in the requests queue indicate contention for shared servers. To examine wait 
time data, use the dynamic performance view V$QUEUE. This view contains statistics showing request queue activity 
for shared servers.  By default, this view is available only to the user SYS and to other users with SELECT ANY TABLE system privilege, such as SYSTEM. 

Monitor these statistics occasionally while your application is running by issuing the following SQL statement: 

SELECT DECODE(TOTALQ, 0, 'No Requests', 

This query returns the results of a calculation that show the following: 


From the result, you can tell that a request waits an average of 0.09 hundredths of a second in the queue 
before processing. 

You can also determine how many shared servers are currently running by issuing the following query:

SELECT COUNT(*) "Shared Server Processes" 

The result of this query could look like the following: 

Shared Server Processes 

For more information on Shared Server see the following Oracle Manuals :- 

Oracle� Database Performance Tuning Guide 
Oracle� Database Database Administrator's Guide 
Oracle� Database Concepts 

...and the following Note :-

Note 274130.1 Basic Shared Server Configuration

So now, if we assume 20 shared servers, our calculation looks more like :- 

3GB = 1000MB (AWE_WINDOW_MEMORY) + 5MB (Log Buffer) + 100MB (Oracle Process overhead) + 
25MB (500KB x 50 shared server/dispatcher threads) + 
250MB (Shared Pool) + xMB (extra Shared Pool for UGA) + yMB (Large Pool for UGA) + 
zMB (20 shared servers x PGA size) 

Let's assume the PGA size for the shared servers is 5 MB. Not all shared servers will need 5MB 
at the same time, but let's assume 20 x 5MB anyway, which gives 100MB. 

..which makes 3GB = 1480MB + (x+y)MB space for UGA memory. So x+y = approx 1500MB.

This gives us around an extra 400MB or so compared with the 1.1GB we had in the previous example. 

In addition we also need to check for excessive INACTIVE sessions consuming memory.
This can be done with the following SQL :-
select sum(PGA_ALLOC_MEM)
from v$process p, v$session s
where p.addr = s.paddr and s.status = 'INACTIVE'

To overcome these sessions, we need to implement Dead Connection Detection (DCD). 
Please review :-
Note 151972.1 Dead Connection Detection (DCD) Explained
Note 206007.1 How to Automate Cleanup of Dead Connections and INACTIVE Sessions


In summary to manage memory in a 32 bit Windows environment we should review the following :-

* Enable /3GB
* Reduce unused SGA memory allocations OR implement ASMM (set SGA_TARGET)
* Shrink the default memory allocation of the ORACLE.EXE (ORASTACK)
* Implement Shared Servers/MTS
* Check for excessive INACTIVE sessions


 Video - How To Deal With ORA-4030 Errors On 32-bit Windows Systems (8:00) 


By better understanding, and judicious use of Oracle features and utilities, we can 
increase the amount of memory available to Oracle for PGA/UGA memory on 32bit Windows, 
and thus reduce the likelihood of ORA-4030 or ORA-12500, ORA-12560, TNS-12500, TNS-12560 errors. 
However, it must be pointed out that this is at best a short-term band-aid, and the 
real long-term solution is to move to a 64bit operating system version. With this we can 
address 8 terabytes, which should be a solution until well past my retirement. 

Another alternative to consider is the use of Oracle Real Application Clusters (RAC), 
as the memory limitations described above apply to a single instance, and in a 
RAC configuration you have multiple instances spread across multiple nodes. In this 
way you can increase the number of concurrent users and the amount of PGA/UGA they 
will utilize by virtue of multiple 3GB address spaces. 


NOTE:151972.1 - Dead Connection Detection (DCD) Explained
NOTE:206007.1 - How To Automate Cleanup Of Dead Connections And INACTIVE Sessions
NOTE:225349.1 - Implementing Address Windowing Extensions (AWE) or VLM on 32-bit Windows Platforms
NOTE:274130.1 - Basic Shared Server Configuration
NOTE:46001.1 - Oracle Database and the Windows NT memory architecture, Technical Bulletin
NOTE:46053.1 - Windows NT Memory Architecture Overview

显示附件 附件

显示相关信息 相关内容

  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
TNS-12500; TNS-12560; ORA-1000; ORA-7445; ORA-12500; ORA-12560; ORA-4030; 4030 ERROR; 1000 ERROR; 12560 ERROR


来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录


  • 博文量
  • 访问量