修改时间 17-AUG-2011 类型 PROBLEM 状态 PUBLISHED
So how is the 3GB of memory divided up by Oracle?
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)) +
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
V$PGA_TARGET_ADVICE under the column ESTD_OVERALLOC_COUNT.
To get an idea of the current PGA memory used we can run a query like :-
This gives a sum of PGA and UGA memory used by all sessions, eg
STATISTIC# NAME VALUE
---------- ------------------------------ -------------
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 :-
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.)
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
...and it will show that 1 MB is currently being used.
To reduce this ensure that oracle.exe is not currently active, then run
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
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.
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
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:
TOTAL MEMORY FOR ALL SESSIONS
TOTAL MAX MEM FOR ALL SESSIONS
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:
This query returns the results of a calculation that show the following:
AVERAGE WAIT TIME PER REQUEST
.090909 HUNDREDTHS OF SECONDS
From the result, you can tell that a request waits an average of 0.09 hundredths of a second in the queue
You can also determine how many shared servers are currently running by issuing the following query:
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 summary to manage memory in a 32 bit Windows environment we should review the following :-* Enable /3GB
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
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/38267/viewspace-713467/，如需转载，请注明出处，否则将追究法律责任。