Proper sizing and effective use of the Oracle memory
caches greatly improves database performance. This chapter explains how
to allocate memory to Oracle memory caches, and how to use those caches.
This chapter contains the following sections:
Oracle stores information in memory caches and on disk. A performance goal is to reduce the physical I/O overhead as much as possible, either by making it more likely that the required data is in memory or by making the process of retrieving the required data more efficient.
ALTER
SYSTEM
statement (except for the log buffer and java pool,
which are static after startup).
You can dynamically reconfigure the sizes of the shared
pool, the large pool, the buffer cache, and the process-private memory.
Memory for the shared pool, large pool, java pool, and
buffer cache is allocated in units of granules. Generally speaking, on
most platforms, the size of a granule is 4 MB if the total SGA size is
less than 128 MB, and it is 16 MB for larger SGAs. There may be some
platform dependency; for example, on 32-bit Windows NT, the granule
size is 8 MB for SGAs larger than 128 MB. The granule size that is
currently being used for SGA can
be viewed in the view V$SGA_DYNAMIC_COMPONENTS
. The same
granule size is used for all dynamic components in the SGA.
You can, if necessary, decrease the size of one cache and
reallocate that memory to another cache. You can expand the total SGA
size to a value equal to the SGA_MAX_SIZE
parameter.
SGA_MAX_SIZE
.
You can specify SGA_MAX_SIZE
to be larger than the sum of
all of the memory components (such as buffer cache and shared pool);
otherwise, SGA_MAX_SIZE
defaults to the actual size used
by those components. Setting SGA_MAX_SIZE
larger than the
sum of memory used by all of the components lets you dynamically
increase a cache size without needing to decrease the size of another
cache.
Because the purpose of the SGA is to store data in memory for fast access, the SGA should be within main memory. If pages of the SGA are swapped to disk, then the data is no longer quickly accessible. On most operating systems, the disadvantage of paging significantly outweighs the advantage of a large SGA.
Note: The |
SHOW SGA
The output of this statement will look similar to the following:
Total System Global Area 840205000 bytes
Fixed Size 279240 bytes
Variable Size 520093696 bytes
Database Buffers 318767104 bytes
Redo Buffers 1064960 bytes
For many types of operations, Oracle uses the buffer cache to store blocks read from disk. Oracle bypasses the buffer cache for particular operations, such as sorting and parallel reads. For operations that use the buffer cache, this section explains the following:
When configuring a new instance, it is impossible to know the correct size for the buffer cache. Typically, a database administrator makes a first estimate for the cache size, then runs a representative workload on the instance and examines the relevant statistics to see whether the cache is under or over configured.
A number of statistics can be used to examine buffer cache activity. These include the following:
This view is populated when the DB_CACHE_ADVICE
parameter is set to ON
. This view shows the simulated
miss rates for a range of potential buffer cache sizes.
Each cache size simulated has its own row in this view,
with the predicted physical I/O activity that would take place for that
size. The DB_CACHE_ADVICE
parameter is dynamic, so the
advisory can be enabled and disabled dynamically to allow you to
collect advisory data for a specific workload.
Two minor overheads are associated with this advisory:
The following SQL statement returns the predicted I/O requirement for the default buffer pool for various cache sizes:
COLUMN size_for_estimate FORMAT 999,999,999,999 heading 'Cache Size (MB)'Estd Phys Estd PhysThis view assists in cache sizing by providing information that predicts the number of physical reads for each potential cache size. The data also includes a physical read factor, which is a factor by which the current number of physical reads is estimated to change if the buffer cache is resized to a given value.
Cache Size (MB) Buffers Read Factor Reads
---------------- ------------ ----------- ------------
30 3,802 18.70 192,317,943 10% of Current Size
60 7,604 12.83 131,949,536
91 11,406 7.38 75,865,861
121 15,208 4.97 51,111,658
152 19,010 3.64 37,460,786
182 22,812 2.50 25,668,196
212 26,614 1.74 17,850,847
243 30,416 1.33 13,720,149
273 34,218 1.13 11,583,180
304 38,020 1.00 10,282,475 Current Size
334 41,822 .93 9,515,878
364 45,624 .87 8,909,026
395 49,426 .83 8,495,039
424 53,228 .79 8,116,496
456 57,030 .76 7,824,764
486 60,832 .74 7,563,180
517 64,634 .71 7,311,729
547 68,436 .69 7,104,280
577 72,238 .67 6,895,122
608 76,040 .66 6,739,731 200% of Current Size
Note: With Oracle, physical reads do not necessarily indicate disk reads; physical reads may well be satisfied from the file system cache. |
The buffer cache hit ratio calculates how often a
requested block has been found in the buffer cache without requiring
disk access. This ratio is computed using data selected from the
dynamic performance view V$SYSSTAT
. The buffer cache hit
ratio can be used to verify the physical I/O as predicted by V$DB_CACHE_ADVICE
.
Example 14-1
has been simplified by using values selected directly from the V$SYSSTAT
table, rather than over an interval. It is best to calculate the delta
of these statistics over an interval while your application is running,
then use them to determine the hit ratio.
NAME VALUECalculate the hit ratio for the buffer cache with the following formula:
---------------------------------------------------------------- ----------
session logical reads 464905358
physical reads 10380487
physical reads direct 86850
physical reads direct (lob) 0
Hit Ratio = 1 - ((physical reads - physical reads direct - physical reads direct (lob)) /
(db block gets + consistent gets - physical reads direct - physical reads direct (lob))
Based on the sample statistics in the example, the buffer cache hit ratio is equal to .978 or 97.8%.
There are many factors to examine before considering
whether to increase or decrease the buffer cache size. For example, you
should examine V$DB_CACHE_ADVICE
data and the buffer
cache hit ratio.
Note: Short table scans are scans performed on tables under a certain size threshold. The definition of a small table is the maximum of 2% of the buffer cache and 20, whichever is bigger. |
As a general rule, investigate increasing the size of the cache if the cache hit ratio is low and your application has been tuned to avoid performing full table scans.
To increase cache size, first set theDB_CACHE_ADVICE
parameter to ON
, and let the cache statistics stabilize.
Examine the advisory data in the V$DB_CACHE_ADVICE
view
to determine the next increment required to significantly decrease the
amount of physical I/O performed. If it is possible to allocate the
required extra memory to the buffer cache without causing the host
operating system to page, then allocate this memory. To increase the
amount of memory allocated to the buffer cache, increase the value of
the parameter DB_CACHE_SIZE
.
If required, resize the buffer pools dynamically, rather than shutting down the instance to perform this change.
DB_CACHE_SIZE
parameter specifies the
size of the default cache for the database's standard block size. To
create and use tablespaces with block sizes different than the
database's standard block sizes (such as to support transportable
tablespaces), you must configure a separate cache for each block size
used. The DB_
n
K_CACHE_SIZE
parameter can be used to configure the nonstandard block size needed
(where n
is 2, 4, 8, 16 or 32 and n
is not the standard block size).
Note: The process of choosing a cache size is the same,
regardless of whether the cache is the default standard block size
cache, the |
If the cache hit ratio is high, then the cache is
probably large enough to hold the most frequently accessed data. Check V$DB_CACHE_ADVICE
data to see whether decreasing the cache size significantly causes the
number of physical I/Os to increase. If not, and if you require memory
for another memory structure, then you might be able to reduce the
cache size and still maintain good performance. To make the buffer
cache smaller, reduce the size of the cache by changing the value for
the parameter DB_CACHE_SIZE
.
A single default buffer pool is generally adequate for
most systems. However, users with detailed knowledge of an
application's buffer pool might benefit from configuring multiple
buffer pools.
With segments that have atypical access patterns, store
blocks from those segments in two different buffer pools: the KEEP
pool and the RECYCLE
pool. A segment's access pattern may
be atypical if it is constantly accessed (that is, hot) or infrequently
accessed (for example, a large segment accessed by a batch job only
once a day).
Multiple buffer pools let you address these differences.
You can use a KEEP
buffer pool to maintain frequently
accessed segments in the buffer cache, and a RECYCLE
buffer pool to prevent objects from consuming unnecessary space in the
cache. When an object is associated with a cache, all blocks from that
object are placed in that cache. Oracle maintains a DEFAULT
buffer pool for objects that have not been assigned to a specific
buffer pool. The default buffer pool is of size DB_CACHE_SIZE
.
Each buffer pool uses the same LRU replacement policy (for example, if
the KEEP
pool is not large enough to store all of the
segments allocated to it, then the oldest blocks age out of the cache).
By allocating objects to appropriate buffer pools, you
can:
Very frequently accessed segments are not affected by large segment reads because their buffers are warmed frequently enough that they do not age out of the cache. However, the problem affects warm segments that are not accessed frequently enough to survive the buffer aging caused by the large segment reads. There are three options for solving this problem:
RECYCLE
cache so that it does not affect the other segments. The RECYCLE
cache should be smaller than the DEFAULT
buffer pool, and
it should reuse buffers more quickly than the DEFAULT
buffer pool. KEEP
cache that is not used at all for large segments. The KEEP
cache can be sized to minimize misses in the cache. You can make the
response times for specific queries more predictable by putting the
segments accessed by the queries in the KEEP
cache to
ensure that they do not age out. To define a default buffer pool for an object, use the BUFFER_POOL
keyword of the STORAGE
clause. This clause is valid for CREATE
and ALTER
TABLE
, CLUSTER
, and
INDEX
SQL statements. After a buffer pool has been
specified, all subsequent blocks read for the object are placed in that
pool.
If a buffer pool is defined for a partitioned table or
index, then each partition of the object inherits the buffer pool from
the table or index definition, unless you override it with a specific
buffer pool.
When the buffer pool of an object is changed using the ALTER
statement, all buffers currently containing blocks of the altered
segment remain in the buffer pool they were in before the ALTER
statement. Newly loaded blocks and any blocks that have aged out and
are reloaded go into the new buffer pool.
See Also:
Oracle9i
SQL Reference for information
on specifying |
The V$BH
view shows the data object ID of
all blocks that currently reside in the SGA. To determine which
segments have many buffers in the pool, you can use one of the two
methods described in this section. You can either look at the buffer
cache usage pattern for all segments (Method
1) or examine the usage pattern of a specific segment, (Method
2).
The following query counts the number of blocks for all segments that reside in the buffer cache at that point in time. Depending on buffer cache size, this might require a lot of sort space.
COLUMN object_name FORMAT a40
COLUMN number_of_blocks FORMAT 999,999,999,999
SELECT o.object_name, COUNT(1) number_of_blocks
FROM DBA_OBJECTS o, V$BH bh
WHERE o.object_id = bh.objd
AND o.owner != 'SYS'
GROUP BY o.object_name
ORDER BY count(1);
OBJECT_NAME NUMBER_OF_BLOCKS
---------------------------------------- ----------------
OA_PREF_UNIQ_KEY 1
SYS_C002651 1
..
DS_PERSON 78
OM_EXT_HEADER 701
OM_SHELL 1,765
OM_HEADER 5,826
OM_INSTANCE 12,644
Use the following steps to determine the percentage of the cache used by an individual object at a given point in time:
SELECT DATA_OBJECT_ID, OBJECT_TYPE
FROM DBA_OBJECTS
WHERE OBJECT_NAME = UPPER('SEGMENT_NAME');
Because two objects can have the same name (if they are different types of objects), use the OBJECT_TYPE column to identify the object of interest.
SELECT COUNT(*) BUFFERS
FROM V$BH
WHERE objd =data_object_id_value
;
where data_object_id_value
is
from step 1.
SELECT NAME, BLOCK_SIZE, SUM(BUFFERS)
FROM V$BUFFER_POOL
GROUP BY NAME, BLOCK_SIZE
HAVING SUM(BUFFERS) > 0;
% cache used by segment_name = [buffers(Step2)/total buffers(Step3)]
If there are certain segments in your application that
are referenced frequently, then store the blocks from those segments in
a separate cache called the KEEP
buffer pool. Memory is
allocated to the KEEP
buffer pool by setting the
parameter DB_KEEP_CACHE_SIZE
to the required size. The
memory for the KEEP
pool is not a subset of the default
pool. Typical segments that can be kept are small reference tables that
are used frequently. Application developers and DBAs can determine
which tables are candidates.
You can check the number of blocks from candidate tables by querying V$BH, as described in "Determining Which Segments Have Many Buffers in the Pool".
The goal of the KEEP
buffer pool is to
retain objects in memory, thus avoiding I/O operations. The size of the
KEEP
buffer pool, therefore, depends on the objects that
you want to keep in the buffer cache. You can compute an approximate
size for the KEEP
buffer pool by adding together the
blocks used by all objects assigned to this pool. If you gather
statistics on the segments, you can query DBA_TABLES.BLOCKS
and DBA_TABLES
.EMPTY_BLOCKS
to determine the
number of blocks used.
Calculate the hit ratio by taking two snapshots of system
performance at different times, using the previous query. Subtract the
more recent values for physical
reads
, block
gets
, and consistent
gets
from
the older values, and use the results to compute the hit ratio.
A buffer pool hit ratio of 100% might not be optimal.
Often, you can decrease the size of your KEEP
buffer pool
and still maintain a sufficiently high hit ratio. Allocate blocks
removed from the KEEP
buffer pool to other buffer pools.
Note: If an object grows in size, then it might no longer
fit in the |
Each object kept in memory results in a trade-off. It is beneficial to keep frequently-accessed blocks in the cache, but retaining infrequently-used blocks results in less space for other, more active blocks.
It is possible to configure a RECYCLE
buffer pool for blocks belonging to those segments that you do not want
to remain in memory. The RECYCLE
pool is good for
segments that are scanned rarely or are not referenced frequently. If
an application accesses the blocks of a very large object in a random
fashion, then there is little chance of reusing a block stored in the
buffer pool before it is aged out. This is true regardless of the size
of the buffer pool (given the constraint of the amount of available
physical memory). Consequently, the object's blocks need not be cached;
those cache buffers can be allocated to other objects.
Memory is allocated to the RECYCLE
buffer
pool by setting the parameter DB_RECYCLE_CACHE_SIZE
to
the required size. This memory for the RECYCLE
buffer
pool is not a subset of the default pool.
Do not discard blocks from memory too quickly. If the
buffer pool is too small, then blocks can age out of the cache before
the transaction or SQL statement has completed execution. For example,
an application might select a value from a table, use the value to
process some data, and then update the record. If the block is removed
from the cache after the SELECT
statement, then it must
be read from disk again to perform the update. The block should be
retained for the duration of the user transaction.
Oracle uses the shared pool to cache many different types of data. Cached data includes the textual and executable forms of PL/SQL blocks and SQL statements, dictionary cache data, and other data.
Proper use and sizing of the shared pool can reduce resource consumption in at least four ways:
This section covers the following:
The main components of the shared pool are the library cache and the dictionary cache. The library cache stores the executable (parsed or compiled) form of recently referenced SQL and PL/SQL code. The dictionary cache stores data referenced from the data dictionary. Many of the caches in the shared pool automatically increase or decrease in size, as needed, including the library cache and the dictionary cache. Old entries are aged out of these caches to accommodate new entries when the shared pool does not have free space.
A cache miss on the data dictionary cache or library cache is more expensive than a miss on the buffer cache. For this reason, the shared pool should be sized to ensure that frequently used data is cached.
A number of features make large memory allocations in the shared pool: for example, the shared server, parallel query, or Recovery Manager. Oracle recommends segregating the SGA memory used by these features by configuring a distinct memory area, called the large pool.
Information stored in the data dictionary cache includes usernames, segment information, profile data, tablespace information, and sequence numbers. The dictionary cache also stores descriptive information, or metadata, about schema objects. Oracle uses this metadata when parsing SQL cursors or during the compilation of PL/SQL programs.
The library cache holds executable forms of SQL cursors, PL/SQL programs, and Java classes. This section focuses on tuning as it relates to cursors, PL/SQL programs, and Java classes. These are collectively referred to as application code.
When application code is run, Oracle attempts to reuse existing code if it has been executed previously and can be shared. If the parsed representation of the statement does exist in the library cache and it can be shared, then Oracle reuses the existing code. This is known as a soft parse, or a library cache hit.
If Oracle is unable to use existing code, then a new executable version of the application code must be built. This is known as a hard parse, or a library cache miss.
Library cache misses can occur on either the parse step or the execute step when processing a SQL statement.
When an application makes a parse call for a SQL statement, if the parsed representation of the statement does not already exist in the library cache, then Oracle parses the statement and stores the parsed form in the shared pool. This is a hard parse. You might be able to reduce library cache misses on parse calls by ensuring that all shareable SQL statements are in the shared pool whenever possible.
If an application makes an execute call for a SQL statement, and if the executable portion of the previously built SQL statement has been aged out (that is, deallocated) from the library cache to make room for another statement, then Oracle implicitly reparses the statement, creating a new shared SQL area for it, and executes it. This also results in a hard parse. Usually, you can reduce library cache misses on execution calls by allocating more memory to the library cache.
In order to perform a hard parse, Oracle uses more resources than during a soft parse. Resources used for a soft parse include CPU and library cache latch gets. Resources required for a hard parse include additional CPU, library cache latch gets, and shared pool latch gets.
An important purpose of the shared pool is to cache the executable versions of SQL and PL/SQL statements. This allows multiple executions of the same SQL or PL/SQL code to be performed without the resources required for a hard parse, which results in significant reductions in CPU, memory, and latch usage.
The shared pool is also able to support unshared SQL in data warehousing applications, which execute low-concurrency, high-resource SQL statements. In this situation, using unshared SQL with literal values is recommended. Using literal values rather than bind variables allows the optimizer to make good column selectivity estimates, thus providing an optimal data access plan.
In an OLTP system, there are a number of ways to ensure efficient use of the shared pool and related resources. Discuss the following items with application developers and agree on strategies to ensure that the shared pool is used effectively:
Efficient use of the shared pool in high-concurrency OLTP systems significantly reduces the probability of parse-related application scalability issues.
Reuse of shared SQL for multiple users running the same application, avoids hard parsing. Soft parses provide a significant reduction in the use of resources such as the shared pool and library cache latches. To share cursors, do the following:
SELECT employee_id FROM employees WHERE department_id = 10;
SELECT employee_id FROM employees WHERE department_id = 20;
By replacing the literals with a bind variable, only one SQL statement would result, which could be executed twice:
SELECT employee_id FROM employees WHERE department_id = :dept_id;
Large OLTP applications with middle tiers should maintain connections, rather than connecting and disconnecting for each database request. Maintaining connections saves CPU resources and database resources, such as latches.
Large OLTP systems where users log in to the database as their own user ID can benefit from explicitly qualifying the segment owner, rather than using public synonyms. This significantly reduces the number of entries in the dictionary cache. For example:
SELECT employee_id FROM hr.employees WHERE department_id = :dept_id;
An alternative to qualifying table names is to connect to the database through a single user ID, rather than individual user IDs. User-level validation can take place locally on the middle tier. Reducing the number of distinct userIDs also reduces the load on the dictionary cache.
Using stored PL/SQL packages can overcome many of the scalability issues for systems with thousands of users, each with individual user sign-on and public synonyms. This is because a package is executed as the owner, rather than the caller, which reduces the dictionary cache load considerably.
Allocating sufficient cache space for frequently updated
sequence numbers significantly reduces the frequency of dictionary
cache locks, which improves scalability. The CACHE
keyword on the CREATE
SEQUENCE
or ALTER
SEQUENCE
statement lets you configure the number of cached
entries for each sequence.
Depending on the Oracle application tool you are using, it is possible to control how frequently your application performs parse calls.
The frequency with which your application either closes cursors or reuses existing cursors for new SQL statements affects the amount of memory used by a session and often the amount of parsing performed by that session.
An application that closes cursors or reuses cursors (for a different SQL statement), does not need as much session memory as an application that keeps cursors open. Conversely, that same application may need to perform more parse calls, using extra CPU and Oracle resources.
Cursors associated with SQL statements that are not executed frequently can be closed or reused for other statements, because the likelihood of reexecuting (and reparsing) that statement is low.
Extra parse calls are required when a cursor containing a SQL statement that will be reexecuted is closed or reused for another statement. Had the cursor remained open, it could have been reused without the overhead of issuing a parse call.
For most OLTP applications, shared pool size is an important factor in application performance. Shared pool size is less important for applications that issue a very limited number of discrete SQL statements, such as data support systems (DSS).
If the shared pool is too small, then extra resources are used to manage the limited amount of available space. This consumes CPU and latching resources, and causes contention.
Optimally, the shared pool should be just large enough to cache frequently accessed objects. Having a significant amount of free memory in the shared pool is a waste of memory.
When sizing the shared pool, the goal is to ensure that SQL statements that will be executed multiple times are cached in the library cache, without allocating too much memory.
The statistic that shows the amount of reloading (that
is, reparsing) of a previously cached SQL statement that was aged out
of the cache is the RELOADS
column in the V$LIBRARYCACHE
view. In an application that reuses SQL effectively, on a system with
an optimal shared pool size, the RELOADS
statistic will
have a value near zero.
The INVALIDATIONS
column in V$LIBRARYCACHE
view shows the number of times library cache data was invalidated and
had to be reparsed. INVALIDATIONS
should be near zero.
This means SQL statements that could have been shared were invalidated
by some operation (for example, a DDL). This statistic should be near
zero on OLTP systems during peak loads.
Another key statistic is the amount of free memory in the
shared pool at peak times. The amount of free memory can be queried
from V$SGASTAT
, looking at the free memory for the shared
pool. Optimally, free memory should be as low as possible, without
causing any reloads on the system.
Lastly, a broad indicator of library cache health is the library cache hit ratio. This value should be considered along with the other statistics discussed in this section and other data, such as the rate of hard parsing and whether there is any shared pool or library cache latch contention.
These statistics are discussed in more detail in the following section.
You can monitor statistics reflecting library cache
activity by examining the dynamic performance view V$LIBRARYCACHE
.
These statistics reflect all library cache activity since the most
recent instance startup.
Each row in this view contains statistics for one type of
item kept in the library cache. The item described by each row is
identified by the value of the NAMESPACE
column. Rows
with the following NAMESPACE
values reflect library cache
activity for SQL statements and PL/SQL blocks:
Rows with other NAMESPACE
values reflect
library cache activity for object definitions that Oracle uses for
dependency maintenance.
To examine each namespace individually, use the following query:
SELECT namespace
, pins
, pinhits
, reloads
, invalidations
FROM V$LIBRARYCACHE
ORDER BY namespace;
The output of this query could look like the following:
NAMESPACE PINS PINHITS RELOADS INVALIDATIONS
--------------- ---------- ---------- ---------- -------------
BODY 8870 8819 0 0
CLUSTER 393 380 0 0
INDEX 29 0 0 0
OBJECT 0 0 0 0
PIPE 55265 55263 0 0
SQL AREA 21536413 21520516 11204 2
TABLE/PROCEDURE 10775684 10774401 0 0
TRIGGER 1852 1844 0 0
To calculate the library cache hit ratio, use the following formula:
Library Cache Hit Ratio = sum(pinhits) / sum(pins)
Using the library cache hit ratio formula, the cache hit ratio is the following:
SUM(PINHITS)/SUM(PINS)
----------------------
.999466248
Examining the returned data leads to the following observations:
SQL
AREA
namespace, there were 21,536,413 executions. RELOAD
).
The amount of free memory in the shared pool is reported
in V$SGASTAT
. Report the current value from this view
using the following query:
SELECT * FROM V$SGASTAT
WHERE NAME = 'free memory'
AND POOL = 'shared pool';
The output will be similar to the following:
POOL NAME BYTES
----------- -------------------------- ----------
shared pool free memory 4928280
If free memory is always available in the shared pool, then increasing the size of the pool offers little or no benefit. However, just because the shared pool is full does not necessarily mean there is a problem. It may be indicative of a well-configured system.
The amount of memory available for the library cache can drastically affect the parse rate of an Oracle instance. With Oracle9i, Release 2 (9.2) or higher, the shared pool advisory statistics provide a database administrator with information about library cache memory and predict how changes in the size of the shared pool can affect the parse rate.
Use the following query to monitor the statistics in the V$ROWCACHE
view over a period of time while your application is running. The
derived column PCT_SUCC_GETS
can be considered the
item-specific hit ratio:
column parameter format a21
column pct_succ_gets format 999.9
column updates format 999,999,999
SELECT parameter
, sum(gets)
, sum(getmisses)
, 100*sum(gets - getmisses) / sum(gets) pct_succ_gets
, sum(modifications) updates
FROM V$ROWCACHE
WHERE gets > 0
GROUP BY parameter;
The output of this query will be similar to the following:
PARAMETER SUM(GETS) SUM(GETMISSES) PCT_SUCC_GETS UPDATES
--------------------- ---------- -------------- ------------- ------------
dc_database_links 81 1 98.8 0
dc_free_extents 44876 20301 54.8 40,453
dc_global_oids 42 9 78.6 0
dc_histogram_defs 9419 651 93.1 0
dc_object_ids 29854 239 99.2 52
dc_objects 33600 590 98.2 53
dc_profiles 19001 1 100.0 0
dc_rollback_segments 47244 16 100.0 19
dc_segments 100467 19042 81.0 40,272
dc_sequence_grants 119 16 86.6 0
dc_sequences 26973 16 99.9 26,811
dc_synonyms 6617 168 97.5 0
dc_tablespace_quotas 120 7 94.2 51
dc_tablespaces 581248 10 100.0 0
dc_used_extents 51418 20249 60.6 42,811
dc_user_grants 76082 18 100.0 0
dc_usernames 216860 12 100.0 0
dc_users 376895 22 100.0 0
Examining the data returned by the sample query leads to these observations:
It is also possible to calculate an overall dictionary cache hit ratio using the following formula; however, summing up the data over all the caches will lose the finer granularity of data:
SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
Shared pool statistics indicate adjustments that can be made. The following sections describe some of your choices.
Increasing the amount of memory for the shared pool increases the amount of memory available to both the library cache and the dictionary cache.
To ensure that shared SQL areas remain in the cache after
their SQL statements are parsed, increase the amount of memory
available to the library cache until the V$LIBRARYCACHE
.RELOADS
value is near zero. To increase the amount of memory available to the
library cache, increase the value of the initialization parameter SHARED_POOL_SIZE
.
The maximum value for this parameter depends on your operating system.
This measure reduces implicit reparsing of SQL statements and PL/SQL
blocks on execution.
To take advantage of additional memory available for
shared SQL areas, you might also need to increase the number of cursors
permitted for a session. You can do this by increasing the value of the
initialization parameter OPEN_CURSORS
.
Examine cache activity by monitoring the GETS
and GETMISSES
columns. For frequently accessed dictionary
caches, the ratio of total GETMISSES
to total GETS
should be less than 10% or 15%, depending on the application.
Consider increasing the amount of memory available to the cache if all of the following are true:
Increase the amount of memory available to the data
dictionary cache by increasing the value of the initialization
parameter SHARED_POOL_SIZE
.
If your RELOADS
are near zero, and if you
have a small amount of free memory in the shared pool, then the shared
pool is probably large enough to hold the most frequently accessed data.
If you always have significant amounts of memory free in the shared pool, and if you would like to allocate this memory elsewhere, then you might be able to reduce the shared pool size and still maintain good performance.
To make the shared pool smaller, reduce the size of the
cache by changing the value for the parameter SHARED_POOL_SIZE
.
Unlike the shared pool, the large pool does not have an LRU list. Oracle does not attempt to age objects out of the large pool.
You should consider configuring a large pool if your instance uses any of the following:
As Oracle allocates shared pool memory for shared server session memory, the amount of shared pool memory available for the library cache and dictionary cache decreases. If you allocate this session memory from a different pool, then Oracle can use the shared pool primarily for caching shared SQL and not incur the performance overhead from shrinking the shared SQL cache.
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 Shared 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.
Configure the size of the large pool based on the number of simultaneously active sessions. Each application requires a different amount of memory for session information, and your configuration of the large pool or SGA should reflect the memory requirement. For example, assuming that the shared server requires 200K to 300K to store session information for each active session. If you anticipate 100 active sessions simultaneously, then configure the large pool to be 30M, or increase the shared pool accordingly if the large pool is not configured.
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.
Oracle collects statistics on total memory used by a
session and stores them in the dynamic performance view V$SESSTAT
.
Table 14-3
lists these statistics.
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:
SELECT SUM(VALUE) || ' BYTES' "TOTAL MEMORY FOR ALL SESSIONS"
FROM V$SESSTAT, V$STATNAME
WHERE NAME = 'session uga memory'
AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;
SELECT SUM(VALUE) || ' BYTES' "TOTAL MAX MEM FOR ALL SESSIONS"
FROM V$SESSTAT, V$STATNAME
WHERE NAME = 'session uga memory max'
AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;
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
-----------------------------
157125 BYTES
TOTAL MAX MEM FOR ALL SESSIONS
------------------------------
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 sizes of the memories 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 set the PRIVATE_SGA
resource limit
to restrict the memory used by each client session from the SGA. PRIVATE_SGA
defines the number of bytes of memory used from the SGA by a session.
However, this parameter is used rarely, because most DBAs do not limit
SGA consumption on a user-by-user basis.
If you have a high number of connected users, then you can reduce memory usage by implementing three-tier connections. This by-product of using a transaction process (TP) monitor is feasible only with pure transactional models, because locks and uncommitted DMLs cannot be held between calls. A shared server environment offers the following advantages:
If you have no library cache misses, then you might be
able to accelerate execution calls by setting the value of the
initialization parameter CURSOR_SPACE_FOR_TIME
to true
.
This parameter specifies whether a cursor can be deallocated from the
library cache to make room for a new SQL statement. CURSOR_SPACE_FOR_TIME
has the following values meanings:
CURSOR_SPACE_FOR_TIME
is set to false
(the default), then a cursor can be
deallocated from the library cache regardless of whether application
cursors associated with its SQL statement are open. In this case,
Oracle must verify that the cursor containing the SQL statement is in
the library cache. CURSOR_SPACE_FOR_TIME
is set to true
, then a cursor can be deallocated only
when all application cursors associated with its statement are closed.
In this case, Oracle need not verify that a cursor is in the cache,
because it cannot be deallocated while an application cursor associated
with it is open. Setting the value of the parameter to true
saves Oracle a small amount of time and can slightly improve the
performance of execution calls. This value also prevents the
deallocation of cursors until associated application cursors are closed.
Do not set the value of CURSOR_SPACE_FOR_TIME
to true
if you have found library cache misses on
execution calls. Such library cache misses indicate that the shared
pool is not large enough to hold the shared SQL areas of all
concurrently open cursors. If the value is true
, and if
the shared pool has no space for a new SQL statement, then the
statement cannot be parsed, and Oracle returns an error saying that
there is no more shared memory. If the value is false
,
and if there is no space for a new statement, then Oracle deallocates
an existing cursor. Although deallocating a cursor could result in a
library cache miss later (only if the cursor is reexecuted), it is
preferable to an error halting your application because a SQL statement
cannot be parsed.
Do not set the value of CURSOR_SPACE_FOR_TIME
to true
if the amount of memory available to each user
for private SQL areas is scarce. This value also prevents the
deallocation of private SQL areas associated with open cursors. If the
private SQL areas for all concurrently open cursors fills your
available memory so that there is no space for a new SQL statement,
then the statement cannot be parsed. Oracle returns an error indicating
that there is not enough memory.
If an application repeatedly issues parse calls on the same set of SQL statements, then the reopening of the session cursors can affect system performance. Session cursors can be stored in a session cursor cache. This feature can be particularly useful for applications that use Oracle Forms, because switching from one form to another closes all session cursors associated with the first form.
Oracle checks the library cache to determine whether more than three parse requests have been issued on a given statement. If so, then Oracle assumes that the session cursor associated with the statement should be cached and moves the cursor into the session cursor cache. Subsequent requests to parse that SQL statement by the same session then find the cursor in the session cursor cache.
To enable caching of session cursors, you must set the
initialization parameter SESSION_CACHED_CURSORS
. The
value of this parameter is a positive integer specifying the maximum
number of session cursors kept in the cache. An LRU algorithm removes
entries in the session cursor cache to make room for new entries when
needed.
You can also enable the session cursor cache dynamically with the statement:
ALTER
SESSION
SET
SESSION_CACHED_CURSORS
=value
;
To determine whether the session cursor cache is
sufficiently large for your instance, you can examine the session
statistic session cursor cache hits
in the V$SYSSTAT
view. This statistic counts the number of times a parse call found a
cursor in the session cursor cache. If this statistic is a relatively
low percentage of the total parse call count for the session, then
consider setting SESSION_CACHED_CURSORS
to a larger value.
Although Oracle breaks down very large requests for memory into smaller chunks, on some systems there might still be a requirement to find a contiguous chunk (for example, over 5 KB) of memory. (The default minimum reserved pool allocation is 4,400 bytes.)
If there is not enough free space in the shared pool, then Oracle must search for and free enough memory to satisfy this request. This operation could conceivably hold the latch resource for detectable periods of time, causing minor disruption to other concurrent attempts at memory allocation.
Hence, Oracle internally reserves a small memory area in the shared pool that can be used if the shared pool does not have enough space. This reserved pool makes allocation of large chunks more efficient.
By default, Oracle configures a small reserved pool. This memory can be used for operations such as PL/SQL and trigger compilation or for temporary space while loading Java objects. After the memory allocated from the reserved pool is freed, it returns to the reserved pool.
You probably will not need to change the default amount
of space Oracle reserves. However, if necessary, the reserved pool size
can be changed by setting the SHARED_POOL_RESERVED_SIZE
initialization parameter. This parameter sets aside space in the shared
pool for unusually large allocations.
For large allocations, Oracle attempts to allocate space in the shared pool in the following order:
The default value for SHARED_POOL_RESERVED_SIZE
is 5% of the SHARED_POOL_SIZE
. This means that, by
default, the reserved list is configured.
If you set SHARED_POOL_RESERVED_SIZE
to
more than half of SHARED_POOL_SIZE
, then Oracle signals
an error. Oracle does not let you reserve too much memory for the
reserved pool. The amount of operating system memory, however, might
constrain the size of the shared pool. In general, set SHARED_POOL_RESERVED_SIZE
to 10% of SHARED_POOL_SIZE
. For most systems, this value
is sufficient if you have already tuned the shared pool. If you
increase this value, then the database takes memory from the shared
pool. (This reduces the amount of unreserved shared pool memory
available for smaller allocations.)
Statistics from the V$SHARED_POOL_RESERVED
view help you tune these parameters. On a system with ample free memory
to increase the size of the SGA, the goal is to have the value of REQUEST_MISSES
equal zero. If the system is constrained for operating system memory,
then the goal is to not have REQUEST_FAILURES
or at least
prevent this value from increasing.
If you cannot achieve these target values, then increase
the value for SHARED_POOL_RESERVED_SIZE
. Also, increase
the value for SHARED_POOL_SIZE
by the same amount,
because the reserved list is taken from the shared pool.
The reserved pool is too small when the value for REQUEST_FAILURES
is more than zero and increasing. To resolve this, increase the value
for the SHARED_POOL_RESERVED_SIZE
and SHARED_POOL_SIZE
accordingly. The settings you select for these parameters depend on
your system's SGA size constraints.
Increasing the value of SHARED_POOL_RESERVED_SIZE
increases the amount of memory available on the reserved list without
having an effect on users who do not allocate memory from the reserved
list.
Too much memory might have been allocated to the reserved list if:
REQUEST_MISSES
is zero or not increasing FREE_MEMORY
is greater than or equal to 50% of SHARED_POOL_RESERVED_SIZE
minimum If either of these conditions is true, then decrease the
value for SHARED_POOL_RESERVED_SIZE
.
The V$SHARED_POOL_RESERVED
fixed view can
also indicate when the value for SHARED_POOL_SIZE
is too
small. This can be the case if REQUEST_FAILURES
is
greater than zero and increasing.
If you have enabled the reserved list, then decrease the
value for SHARED_POOL_RESERVED_SIZE
. If you have not
enabled the reserved list, then you could increase SHARED_POOL_SIZE
.
After an entry has been loaded into the shared pool, it cannot be moved. Sometimes, as entries are loaded and aged, the free memory can become fragmented.
Use the PL/SQL package DBMS_SHARED_POOL
to
manage the shared pool. Shared SQL and PL/SQL areas age out of the
shared pool according to a least recently used (LRU) algorithm, similar
to database buffers. To improve performance and prevent reparsing, you
might want to prevent large SQL or PL/SQL areas from aging out of the
shared pool.
The DBMS_SHARED_POOL
package lets you keep
objects in shared memory, so that they do not age out with the normal
LRU mechanism. By using the DBMS_SHARED_POOL
package and
by loading the SQL and PL/SQL areas before memory fragmentation occurs,
the objects can be kept in memory.
One of the first stages of parsing is to compare the text of the statement with existing statements in the shared pool to see if the statement can be shared. If the statement differs textually in any way, then Oracle does not share the statement.
Exceptions to this are possible when the parameter CURSOR_SHARING
has been set to SIMILAR
or FORCE
. When this
parameter is used, Oracle first checks the shared pool to see if there
is an identical statement in the shared pool. If an identical statement
is not found, then Oracle searches for a similar statement in the
shared pool. If the similar statement is there, then the parse checks
continue to verify the executable form of the cursor can be used. If
the statement is not there, then a hard parse is necessary to generate
the executable form of the statement.
Statements that are identical, except for the values of
some literals, are called similar statements. Similar statements pass
the textual check in the parse phase when the CURSOR_SHARING
parameter is set to SIMILAR
or FORCE
.
Textual similarity does not guarantee sharing. The new form of the SQL
statement still needs to go through the remaining steps of the parse
phase to ensure that the execution plan of the preexisting statement is
equally applicable to the new statement.
Setting CURSOR_SHARING
to EXACT
allows SQL statements to share the SQL area only when their texts match
exactly. This is the default behavior. Using this setting, similar
statements cannot shared; only textually exact statements can be shared.
Setting CURSOR_SHARING
to either SIMILAR
or FORCE
allows similar statements to share SQL. The
difference between SIMILAR
and FORCE
is
that SIMILAR
forces similar statements to share the SQL
area without deteriorating execution plans. Setting CURSOR_SHARING
to FORCE
forces similar statements to share the
executable SQL area, potentially deteriorating execution plans. Hence, FORCE
should be used as a last resort, when the risk of suboptimal plans is
outweighed by the improvements in cursor sharing.
The CURSOR_SHARING
initialization parameter
can solve some performance problems. It has the following values: FORCE
,
SIMILAR
, and EXACT
(default). Using this
parameter provides benefit to existing applications that have many
similar SQL statements.
The optimal solution is to write sharable SQL, rather
than rely on the CURSOR_SHARING
parameter. This is
because although CURSOR_SHARING
does significantly reduce
the amount of resources used by eliminating hard parses, it requires
some extra work as a part of the soft parse to find a similar statement
in the shared pool.
Consider setting CURSOR_SHARING
to SIMILAR
or FORCE
if you can answer 'yes' to both of the following
questions:
Using CURSOR_SHARING
= SIMILAR
(or FORCE
) can significantly improve cursor sharing on
some applications that have many similar statements, resulting in
reduced memory usage, faster parses, and reduced latch contention.
Server processes making changes to data blocks in the buffer cache generate redo data into the log buffer. LGWR begins writing to copy entries from the redo log buffer to the online redo log if any of the following are true:
COMMIT
or ROLLBACK.
When LGWR writes redo entries from the redo log buffer to a redo log file or disk, user processes can then copy new entries over the entries in memory that have been written to disk. LGWR usually writes fast enough to ensure that space is available in the buffer for new entries, even when access to the redo log is heavy.
A larger buffer makes it more likely that there is space for new entries, and also gives LGWR the opportunity to efficiently write out redo records (too small a log buffer on a system with large updates means that LGWR is continuously flushing redo to disk so that the log buffer remains 2/3 empty).
On machines with fast processors and relatively slow disks, the processors might be filling the rest of the buffer in the time it takes the redo log writer to move a portion of the buffer to disk. A larger log buffer can temporarily mask the effect of slower disks in this situation. Alternatively, you can do one of the following:
Good usage of the redo log buffer is a simple matter of:
NOLOGGING
operations when you are loading large quantities of data The size of the redo log buffer is determined by the
initialization parameter LOG_BUFFER
. The log buffer size
cannot be modified after instance startup.
Applications that insert, modify, or delete large volumes of data usually need to change the default log buffer size. The log buffer is small compared with the total SGA size, and a modestly sized log buffer can significantly enhance throughput on systems that perform many updates.
A reasonable first estimate for such systems is to make the log buffer 1 MB. On most systems, sizing the log buffer larger than 1m does not provide any performance benefit. Increasing the log buffer size does not have any negative implications on performance or recoverability. It merely uses extra memory.
The statistic REDO
BUFFER
ALLOCATION
RETRIES
reflects the number of times a user process waits
for space in the redo log buffer. This statistic can be queried through
the dynamic performance view V$SYSSTAT
.
Use the following query to monitor these statistics over a period of time while your application is running:
SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME = 'redo buffer allocation retries';
The value of redo buffer allocation retries
should be near zero over an interval. If this value increments
consistently, then processes have had to wait for space in the redo log
buffer. The wait can be caused by the log buffer being too small or by
checkpointing. Increase the size of the redo log buffer, if necessary,
by changing the value of the initialization parameter LOG_BUFFER
.
The value of this parameter is expressed in bytes. Alternatively,
improve the checkpointing or archiving process.
Another data source is to check whether the log
buffer
space
wait event is not a significant
factor in the wait time for the instance; if not, the log buffer size
is most likely adequate.
The Program Global Area (PGA) is a private memory region containing data and control information for a server process. Access to it is exclusive to that server process and is read and written only by the Oracle code acting on behalf of it. An example of such information is the runtime area of a cursor. Each time a cursor is executed, a new runtime area is created for that cursor in the PGA memory region of the server process executing that cursor.
For complex queries (for example, decision support queries), a big portion of the runtime area is dedicated to work areas allocated by memory intensive operators, such as the following:
ORDER
BY
, GROUP
BY
, ROLLUP
, window functions) A sort operator uses a work area (the sort area) to perform the in-memory sort of a set of rows. Similarly, a hash-join operator uses a work area (the hash area) to build a hash table from its left input.
The size of a work area can be controlled and tuned. Generally, bigger work areas can significantly improve the performance of a particular operator at the cost of higher memory consumption. Ideally, the size of a work area is big enough that it can accommodate the input data and auxiliary memory structures allocated by its associated SQL operator. This is known as the optimal size of a work area. When the size of the work area is smaller than optimal, the response time increases, because an extra pass is performed over part of the input data. This is known as the one-pass size of the work area. Under the one-pass threshold, when the size of a work area is far too small compared to the input data size, multiple passes over the input data are needed. This could dramatically increase the response time of the operator. This is known as the multi-pass size of the work area. For example, a serial sort operation that needs to sort 10GB of data needs a little more than 10GB to run optimal and at least 40MB to run one-pass. If this sort gets less that 40MB, then it must perform several passes over the input data.
The goal is to have most work areas running with an optimal size (for example, more than 90% or even 100% for pure OLTP systems), while a smaller fraction of them are running with a one-pass size (for example, less than 10%). Multi-pass execution should be avoided. Even for DSS systems running large sorts and hash-joins, the memory requirement for the one-pass executions is relatively small. A system configured with a reasonable amount of PGA memory should not need to perform multiple passes over the input data.
Prior to Oracle9i, the maximum
size of these working areas was controlled using the SORT_AREA_SIZE
,
HASH_AREA_SIZE
, BITMAP_MERGE_AREA_SIZE
and CREATE_BITMAP_AREA_SIZE
parameters. Setting these parameters is difficult, because the maximum
work area size is ideally selected based on the data input size and the
total number of work areas active in the system. These two factors vary
a lot from one work area to another and from one point in time to
another. Thus, the various *_AREA_SIZE
parameters are hard
to tune under the best of circumstances.
With Oracle9i, you can simplify
and improve the way PGA memory is allocated, by enabling automatic PGA
memory management. In this mode, Oracle dynamically adjusts the size of
the portion of the PGA memory dedicated to work areas, based on an
overall PGA memory target explicitly set by the DBA. To enable
automatic PGA memory management, you have to set the initialization
parameter PGA_AGGREGATE_TARGET
, as described in the
following section.
When running under the automatic PGA memory management
mode, sizing of work areas for all dedicated sessions becomes
automatic. Thus, the *_AREA_SIZE
parameters are ignored by
all sessions running in that mode. At any given time, the total amount
of PGA memory available to active work areas in the instance is
automatically derived from the PGA_AGGREGATE_TARGET
initialization parameter. This amount is set to the value of PGA_AGGREGATE_TARGET
minus the amount of PGA memory allocated by other components of the
system (for example, PGA memory allocated by sessions). The resulting
PGA memory is then assigned to individual active work areas, based on
their specific memory requirements.
Under automatic PGA memory management mode, the main goal
of Oracle is to honor the PGA_AGGREGATE_TARGET
limit set
by the DBA, by controlling dynamically the amount of PGA memory
allotted to SQL work areas. At the same time, Oracle tries to maximize
the performance of all the memory-intensive SQL operators, by
maximizing the number of work areas that are using an optimal amount of
PGA memory (cache memory). The rest of the work areas are executed in
one-pass mode, unless the PGA memory limit set by the DBA with the
parameter PGA_AGGREGATE_TARGET
is so low that multi-pass
execution is required to reduce even more the consumption of PGA memory
and honor the PGA target limit.
When configuring a brand new instance, it is hard to know
precisely the appropriate setting for PGA_AGGREGATE_TARGET
.
You can determine this setting in three stages:
PGA_AGGREGATE_TARGET
, based on a rule of
thumb. PGA_AGGREGATE_TARGET
,
using Oracle's PGA advice statistics. The following sections explain this in detail:
The value of the PGA_AGGREGATE_TARGET
initialization parameter (for example 100000 KB, 2500 MB, or 50 GB)
should be set based on the total amount of memory available for the
Oracle instance. This value can then be tuned and dynamically modified
at the instance level. Example 14-2
illustrates a typical situation.
Assume that an Oracle instance is configured to run on a system with 4 GB of physical memory. Part of that memory should be left for the operating system and other non-Oracle applications running on the same hardware system. You might decide to dedicate only 80% of the available memory to the Oracle instance, or 3.2 GB.
You must then divide the resulting memory between the SGA and the PGA.
Good initial values for the parameter PGA_AGGREGATE_TARGET
might be:
:
PGA_AGGREGATE_TARGET = (total_mem
* 80%) * 20% :
PGA_AGGREGATE_TARGET = (total_mem
* 80%) * 50%
where total_mem
is the total
amount of physical memory available on the system.
In this example, with a value of total_mem
equal to 4 GB, you can initially set PGA_AGGREGATE_TARGET
to 1600 MB for the DSS system and to 655 MB for the OLTP system.
Before starting the tuning process, you need to know how to monitor and interpret the key statistics collected by Oracle to help in assessing the performance of the automatic PGA memory management component. Several dynamic performance views are available for this purpose:
This view gives instance-level statistics on the PGA memory usage and the automatic PGA memory manager. For example:
SELECT * FROM V$PGASTAT;
The output of this query might look like the following:
NAME VALUE UNIT
---------------------------------------------------------- -------
aggregate PGA target parameter 524288000 bytes
aggregate PGA auto target 463435776 bytes
global memory bound 25600 bytes
total PGA inuse 9353216 bytes
total PGA allocated 73516032 bytes
maximum PGA allocated 698371072 bytes
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 560744448 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 0 bytes
over allocation count 0
total bytes processed 4.0072E+10 bytes
total extra bytes read/written 3.1517E+10 bytes
cache hit percentage 55.97 percent
The main statistics displayed in V$PGASTAT
are as follows:
aggregate PGA
target parameter
: This is the current value of the
initialization parameter PGA_AGGREGATE_TARGET
, here set
to 500 MB. If you do not set this parameter, its value is 0 and
automatic management of the PGA memory is disabled. aggregate PGA
auto target
: This gives the amount of PGA memory Oracle can use
for work areas running in automatic mode. This amount is dynamically
derived from the value of the parameter PGA_AGGREGATE_TARGET
and the current work area workload. Hence, it is continuously adjusted
by Oracle. If this value is small compared to the value of PGA_AGGREGATE_TARGET
,
then a lot of PGA memory is used by other components of the system (for
example, PL/SQL or Java memory) and little is left for sort work areas.
You must ensure that enough PGA memory is left for work areas running
in automatic mode. global memory
bound
: This gives the maximum size of a work area executed in AUTO
mode. This value is continuously adjusted by Oracle to reflect the
current state of the work area workload. The global memory bound
generally decreases when the number of active work areas is increasing
in the system. As a rule of thumb, the value of the global bound should
not decrease to less than one megabyte. If it does, then the value of PGA_AGGREGATE_TARGET
should probably be increased. total PGA
allocated
: This gives the current amount of PGA memory allocated
by the instance. Oracle tries to keep this number less than the value
of PGA_AGGREGATE_TARGET
. However, it is possible for the
PGA allocated to exceed that value by a small percentage and for a
short period of time, when the work area workload is increasing very
rapidly or when the initialization parameter PGA_AGGREGATE_TARGET
is set to a too small value. total PGA used
for auto workareas
: This indicates how much PGA memory is
currently consumed by work areas running under automatic memory
management mode. This number can be used to determine how much memory
is consumed by other consumers of the PGA memory (for example, PL/SQL
or Java):
PGA other = total PGA allocated - total PGA used for auto workareas
over allocation
count
: This statistic is cumulative from instance start-up.
Over-allocating PGA memory can happen if the value of PGA_AGGREGATE_TARGET
is too small to accommodate the PGA other
component in
the previous equation plus the minimum memory required to execute the
work area workload. When this happens, Oracle cannot honor the
initialization parameter PGA_AGGREGATE_TARGET
, and extra
PGA memory needs to be allocated. If over-allocation occurs, you should
increase the value of PGA_AGGREGATE_TARGET
using the
information provided by the advice view V$PGA_TARGET_ADVICE
.
total bytes
processed
: This is the number of bytes processed by
memory-intensive SQL operators since instance start-up. For example,
the number of byte processed is the input size for a sort operation.
This number is used to compute the cache
hit
percentage
metric. extra bytes
read/written
: When a work area cannot run optimally, one or more
extra passes is performed over the input data. extra
bytes
read/written
represents the number of bytes processed
during these extra passes since instance start-up. This number is also
used to compute the cache
hit
percentage
.
cache hit
percentage
: This metric is computed by Oracle to reflect the
performance of the PGA memory component. It is cumulative from instance
start-up. A value of 100% means that all work areas executed by the
system since instance start-up have used an optimal amount of PGA
memory. This is, of course, ideal but rarely happens except maybe for
pure OLTP systems. In reality, some work areas run one-pass or even
multi-pass, depending on the overall size of the PGA memory. When a
work area cannot run optimally, one or more extra passes is performed
over the input data. This reduces the cache
hit
percentage
in proportion to the size of the input data
and the number of extra passes performed. Example 14-3
shows how cache
hit
percentage
is affected by extra passes. Consider a simple example: Four sort operations have been
executed, three were small (1 MB of input data) and one was bigger (100
MB of input data). The total number of bytes processed (BP
)
by the four operations is 103 MB. If one of the small sorts runs
one-pass, an extra pass over 1 MB of input data is performed. This 1 MB
value is the number of extra
bytes
read/written
,
or EBP
. The cache
hit
percentage
is calculated by the following formula:
BP x 100 / (BP + EBP)
The cache
hit
percentage
in this case is 99.03%, almost 100%. This value reflects the fact that
only one of the small sorts had to perform an extra pass while all
other sorts were able to run optimally. Hence, the cache
hit
percentage
is almost 100%, because this extra pass over 1
MB represents a tiny overhead. On the other hand, if the big sort is
the one to run one-pass, then EBP is 100 MB instead of 1 MB, and the cache
hit
percentage
falls to 50.73%, because the
extra pass has a much bigger impact.
This view has one row for each Oracle process connected
to the instance. The columns PGA_USED_MEM
, PGA_ALLOC_MEM
and PGA_MAX_MEM
can be used to monitor the PGA memory
usage of these processes. For example:
SELECT PROGRAM, PGA_USED_MEM, PGA_ALLOC_MEM, PGA_MAX_MEM
FROM V$PROCESS;
The output of this query might look like the following:
PROGRAM PGA_USED_MEM PGA_ALLOC_MEM PGA_MAX_MEM
------------------------------ ------------ ------------- -----------
PSEUDO 0 0 0
oracle@miflo (PMON) 120463 234291 234291
oracle@miflo (DBW0) 1307179 1817295 1817295
oracle@miflo (LGWR) 4343655 4849203 4849203
oracle@miflo (CKPT) 194999 332583 332583
oracle@miflo (SMON) 179923 775311 775323
oracle@miflo (RECO) 129719 242803 242803
oracle@miflo (TNS V1-V3) 1400543 1540627 1540915
oracle@miflo (P000) 299599 373791 635959
oracle@miflo (P001) 299599 373791 636007
oracle@miflo (P002) 299599 373791 570471
oracle@miflo (P003) 303899 373791 636007
oracle@miflo (P004) 299599 373791 635959
This view shows the number of work areas executed with
optimal memory size, one-pass memory size, and multi-pass memory size
since instance start-up. Statistics in this view are subdivided into
buckets that are defined by the optimal memory requirement of the work
area. Each bucket is identified by a range of optimal memory
requirements specified by the values of the columns LOW_OPTIMAL_SIZE
and HIGH_OPTIMAL_SIZE
.
Examples 14-4
and 14-5
show two ways of using V$SQL_WORKAREA_HISTOGRAM
.
Consider a sort operation that requires 3 MB of memory to
run optimally (cached). Statistics about the work area used by this
sort are placed in the bucket defined by LOW_OPTIMAL_SIZE =
2097152
(2 MB) and HIGH_OPTIMAL_SIZE = 4194303
(4
MB minus 1 byte), because 3 MB falls within that range of optimal
sizes. Statistics are segmented by work area size, because the
performance impact of running a work area in optimal, one-pass or
multi-pass mode depends mainly on the size of that work area.
The following query shows statistics for all nonempty
buckets. Empty buckets are removed with the predicate where
total_execution != 0
.
SELECT LOW_OPTIMAL_SIZE/1024 low_kb,
(HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS
FROM V$SQL_WORKAREA_HISTOGRAM
WHERE TOTAL_EXECUTIONS != 0;
The result of the query might look like the following:
LOW_KB HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS
------ ------- ------------------ ------------------ ----------------------
8 16 156255 0 0
16 32 150 0 0
32 64 89 0 0
64 128 13 0 0
128 256 60 0 0
256 512 8 0 0
512 1024 657 0 0
1024 2048 551 16 0
2048 4096 538 26 0
4096 8192 243 28 0
8192 16384 137 35 0
16384 32768 45 107 0
32768 65536 0 153 0
65536 131072 0 73 0
131072 262144 0 44 0
262144 524288 0 22 0
The query result shows that, in the 1024 KB to 2048 KB bucket, 551 work areas used an optimal amount of memory, while 16 ran in one-pass mode and none ran in multi-pass mode. It also shows that all work areas under 1 MB were able to run in optimal mode.
You can also use V$SQL_WORKAREA_HISTOGRAM
to find the percentage of times work areas were executed in optimal,
one-pass, or multi-pass mode since start-up. This query only considers
work areas of a certain size, with an optimal memory requirement of at
least 64 KB.
SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc,
onepass_count, round(onepass_count*100/total, 2) onepass_perc,
multipass_count, round(multipass_count*100/total, 2) multipass_perc
FROM
(SELECT decode(sum(total_executions), 0, 1, sum(total_executions)) total,
sum(OPTIMAL_EXECUTIONS) optimal_count,
sum(ONEPASS_EXECUTIONS) onepass_count,
sum(MULTIPASSES_EXECUTIONS) multipass_count
FROM v$sql_workarea_histogram
WHERE low_optimal_size > 64*1024);
The output of this query might look like the following:
OPTIMAL_COUNT OPTIMAL_PERC ONEPASS_COUNT ONEPASS_PERC MULTIPASS_COUNT MULTIPASS_PERC
------------- ------------ ------------- ------------ --------------- --------------
2239 81.63 504 18.37 0 0
This result shows that 81.63% of these work areas have been able to run using an optimal amount of memory. The rest (18.37%) ran one-pass. None of them ran multi-pass. Such behavior is preferable, for the following reasons:
This view can be used to display the work areas that are active (or executing) in the instance. Small active sorts (under 64 KB) are excluded from the view. Use this view to precisely monitor the size of all active work areas and to determine if these active work areas spill to a temporary segment. Example 14-6 shows a typical query of this view:
SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
operation_type OPERATION,
trunc(EXPECTED_SIZE/1024) ESIZE,
trunc(ACTUAL_MEM_USED/1024) MEM,
trunc(MAX_MEM_USED/1024) "MAX MEM",
NUMBER_PASSES PASS,
trunc(TEMPSEG_SIZE/1024) TSIZE
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2;
The output of this query might look like the following:
SID OPERATION ESIZE MEM MAX MEM PASS TSIZE
--- ----------------- --------- --------- --------- ----- -------
8 GROUP BY (SORT) 315 280 904 0
8 HASH-JOIN 2995 2377 2430 1 20000
9 GROUP BY (SORT) 34300 22688 22688 0
11 HASH-JOIN 18044 54482 54482 0
12 HASH-JOIN 18044 11406 21406 1 120000
This output shows that session 12 (column SID
)
is running a hash-join having its work area running in one-pass mode (PASS
column). This work area is currently using 11406 KB of memory (MEM
column) and has used, in the past, up to 21406 KB of PGA memory (MAX
MEM
column). It has also spilled to a temporary segment of
size 120000 KB. Finally, the column ESIZE
indicates the
maximum amount of memory that the PGA memory manager expects this
hash-join to use. This maximum is dynamically computed by the PGA
memory manager according to workload.
When a work area is deallocated--that is, when the
execution of its associated SQL operator is complete--the work area is
automatically removed from the V$SQL_WORKAREA_ACTIVE
view.
Oracle maintains cumulative work area statistics for each
loaded cursor whose execution plan uses one or more work areas. Every
time a work area is deallocated, the V$SQL_WORKAREA
table
is updated with execution statistics for that work area.
V$SQL_WORKAREA
can be joined with V$SQL
to relate a work area to a cursor. It can even be joined to V$SQL_PLAN
to precisely determine which operator in the plan uses a work area.
Example 14-7
shows three typical queries on the V$SQL_WORKAREA
dynamic
view:
The following query finds the top 10 work areas requiring most cache memory:
SELECT *
FROM
( SELECT workarea_address, operation_type, policy, estimated_optimal_size
FROM V$SQL_WORKAREA
ORDER BY estimated_optimal_size )
WHERE ROWNUM <= 10;
The following query finds the cursors with one or more work areas that have been executed in one or even multiple passes:
col sql_text format A80 wrap
SELECT sql_text, sum(ONEPASS_EXECUTIONS) onepass_cnt,
sum(MULTIPASSES_EXECUTIONS) mpass_cnt
FROM V$SQL s, V$SQL_WORKAREA wa
WHERE s.address = wa.address
GROUP BY sql_text
HAVING sum(ONEPASS_EXECUTIONS+MULTIPASSES_EXECUTIONS)>0;
Using the hash value and address of a particular cursor, the following query displays the cursor execution plan, including information about the associated work areas.
col "O/1/M" format a10
col name format a20
SELECT operation, options, object_name name,
trunc(bytes/1024/1024) "input(MB)",
trunc(last_memory_used/1024) last_mem,
trunc(estimated_optimal_size/1024) optimal_mem,
trunc(estimated_onepass_size/1024) onepass_mem,
decode(optimal_executions, null, null,
optimal_executions||'/'||onepass_executions||'/'||
multipasses_executions) "O/1/M"
FROM V$SQL_PLAN p, V$SQL_WORKAREA w
WHERE p.address=w.address(+)
AND p.hash_value=w.hash_value(+)
AND p.id=w.operation_id(+)
AND p.address='88BB460C'
AND p.hash_value=3738161960;
OPERATION OPTIONS NAME input(MB) LAST_MEM OPTIMAL_ME ONEPASS_ME O/1/M
------------ -------- -------- --------- -------- ---------- ---------- ------
SELECT STATE
SORT GROUP BY 4582 8 16 16 16/0/0
HASH JOIN SEMI 4582 5976 5194 2187 16/0/0
TABLE ACCESS FULL ORDERS 51
TABLE ACCESS FUL LINEITEM 1000
You can get the address and hash value from the V$SQL
view by specifying a pattern in the query. For example:
SELECT address, hash_value
FROM V$SQL
WHERE sql_text LIKE '%my_pattern
%';
To help you tune the initialization parameter PGA_AGGREGATE_TARGET
,
Oracle provides two PGA advice performance views:
By examining these two views, you no longer need to use
an empirical approach to tune the value of PGA_AGGREGATE_TARGET
.
Instead, you can use the content of these views to determine how key
PGA statistics will be impacted if you change the value of PGA_AGGREGATE_TARGET
.
In both views, values of PGA_AGGREGATE_TARGET
used for the prediction are derived from fractions and multiples of the
current value of that parameter, to assess possible higher and lower
values. Values used for the prediction range from 10 MB to a maximum of
256 GB.
Oracle generates PGA advice performance views by
recording the workload history and then simulating this history for
different values of PGA_AGGREGATE_TARGET
. The simulation
process happens in the background and continuously updates the workload
history to produce the simulation result. You can view the result at
any time by querying V$PGA_TARGET_ADVICE
or V$PGA_TARGET_ADVICE_HISTOGRAM
.
To enable automatic generation of PGA advice performance views, make sure the following parameters are set:
PGA_AGGREGATE_TARGET
,
to enable automatic PGA memory management. Set the initial value as
described in "Setting
PGA_AGGREGATE_TARGET Initially". STATISTICS_LEVEL
.
Set this to TYPICAL
(the default) or ALL
;
setting this parameter to BASIC
turns off generation of
PGA performance advice views. The content of these PGA advice performance views is
reset at instance start-up or when PGA_AGGREGATE_TARGET
is altered.
This view predicts how the statistics cache
hit
percentage
and over
allocation
count
in V$PGASTAT
will be impacted if you
change the value of the initialization parameter PGA_AGGREGATE_TARGET
.
Example 14-8
shows a typical query of this view:
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM V$PGA_TARGET_ADVICE;
The output of this query might look like the following:
TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
63 23 367
125 24 30
250 30 3
375 39 0
500 58 0
600 59 0
700 59 0
800 60 0
900 60 0
1000 61 0
1500 67 0
2000 76 0
3000 83 0
4000 85 0
If you use OEM, looking at the graphic, the curve shows
how the PGA cache
hit
percentage
improves as the value of PGA_AGGREGATE_TARGET
increases. The shaded zone in the graph is the over
allocation
zone, where the value of the column ESTD_OVERALLOCATION_COUNT
is nonzero. It indicates that PGA_AGGREGATE_TARGET
is too
small to even meet the minimum PGA memory needs. If PGA_AGGREGATE_TARGET
is set within the over
allocation
zone, the
memory manager will over-allocate memory and actual PGA memory consumed
will be more than the limit you set. It is therefore meaningless to set
a value of PGA_AGGREGATE_TARGET
in that zone. In this
particular example PGA_AGGREGATE_TARGET
should be set to
at least 375 MB.
Beyond the over
allocation
zone, the value of the PGA cache
hit
percentage
increases rapidly. This is due to an increase in the number of work
areas which run optimally or one-pass and a decrease in the number of
multi-pass executions. At some point, somewhere around 500 MB in this
example, there is an inflection in the curve that corresponds to the
point where most (probably all) work areas can run optimally or at
least one-pass. After this inflection, the cache
hit
percentage
keeps increasing, though at a lower pace, up to
the point where it starts to taper off and shows only slight
improvement with increase in PGA_AGGREGATE_TARGET
.
Ideally, PGA_AGGREGATE_TARGET
should be set
at the optimal value, or at least to the maximum value possible in the
region beyond the over
allocation
zone. As
a rule of thumb, the PGA cache
hit
percentage
should be higher than 60%, since at 60% the system is almost processing
double the number of bytes it actually needs to process in an ideal
situation. Using this particular example, it makes sense to set PGA_AGGREGATE_TARGET
to at least 500 MB and as close as possible to 3 GB. But the right
setting for the parameter PGA_AGGREGATE_TARGET
really
depends on how much memory can be dedicated to the PGA component.
Generally, adding PGA memory requires reducing memory for some of the
SGA components, like the shared pool or the buffer cache. This is
because the overall memory dedicated to the Oracle instance is often
bound by the amount of physical memory available on the system. As a
result, any decisions to increase PGA memory must be taken in the
larger context of the available memory in the system and the
performance of the various SGA components (which you monitor with
shared pool advisory and buffer cache advisory statistics). If memory
cannot be taken away from the SGA, you might consider adding more
physical memory to the system.
You can use the following steps as a tuning guideline in
tuning PGA_AGGREGATE_TARGET
:
PGA_AGGREGATE_TARGET
so there is no memory over-allocation; avoid setting it in the
over-allocation zone. In Example 14-8,
PGA_AGGREGATE_TARGET
should be set to at least 375 MB.
cache
hit
percentage
, based on your
response-time requirement and memory constraints. In Example 14-8,
assume you have a limit X on memory you can
allocate to PGA.
PGA_AGGREGATE_TARGET
to the optimal value. After this point, the incremental benefit with
higher memory allocation to PGA_AGGREGATE_TARGET
is very
small. In Example 14-8,
if you have 10 GB to dedicate to PGA, set PGA_AGGREGATE_TARGET
to 3 GB, the optimal value. The remaining 7 GB is dedicated to the SGA.
PGA_AGGREGATE_TARGET
to X.
In Example 14-8,
if you have only 2 GB to dedicate to PGA, set PGA_AGGREGATE_TARGET
to 2 GB and accept a cache
hit
percentage
of 75%. Finally, like most statistics collected by Oracle that are cumulative since instance start-up, you can take a snapshot of the view at the beginning and at the end of a time interval. You can then derive the predicted statistics for that time interval as follows:
estd_overalloc_count = (difference in estd_overalloc_count between the two snapshots)
(difference in bytes_processed between the two snapshots)
estd_pga_cache_hit_percentage = -----------------------------------------------------------------
(difference in bytes_processed + extra_bytes_rw between the two snapshots )
This view predicts how the statistics displayed by the
performance view V$SQL_WORKAREA_HISTOGRAM
will be
impacted if you change the value of the initialization parameter PGA_AGGREGATE_TARGET
.
You can use the dynamic view V$PGA_TARGET_ADVICE_HISTOGRAM
to view detailed information on the predicted number of optimal,
one-pass and multi-pass work area executions for the set of PGA_AGGREGATE_TARGET
values you use for the prediction.
The V$PGA_TARGET_ADVICE_HISTOGRAM
view is
identical to the V$SQL_WORKAREA_HISTOGRAM
view, with two
additional columns to represent the PGA_AGGREGATE_TARGET
values used for the prediction. Therefore, any query executed against
the V$SQL_WORKAREA_HISTOGRAM
view can be used on this
view, with an additional predicate to select the desired value of PGA_AGGREGATE_TARGET
.
The following query displays the predicted content of V$SQL_WORKAREA_HISTOGRAM
for a value of the initialization parameter PGA_AGGREGATE_TARGET
set to twice its current value.
SELECT LOW_OPTIMAL_SIZE/1024 low_kb, (HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
estd_optimal_executions estd_opt_cnt,
estd_onepass_executions estd_onepass_cnt,
estd_multipasses_executions estd_mpass_cnt
FROM v$pga_target_advice_histogram
WHERE pga_target_factor = 2
AND estd_total_executions != 0
ORDER BY 1;
The output of this query might look like the following.
LOW_KB HIGH_KB ESTD_OPTIMAL_CNT ESTD_ONEPASS_CNT ESTD_MPASS_CNT
------ ------- ---------------- ---------------- --------------
8 16 156107 0 0
16 32 148 0 0
32 64 89 0 0
64 128 13 0 0
128 256 58 0 0
256 512 10 0 0
512 1024 653 0 0
1024 2048 530 0 0
2048 4096 509 0 0
4096 8192 227 0 0
8192 16384 176 0 0
16384 32768 133 16 0
32768 65536 66 103 0
65536 131072 15 47 0
131072 262144 0 48 0
262144 524288 0 23 0
The output shows that increasing PGA_AGGREGATE_TARGET
by a factor of 2 will allow all work areas under 16 MB to execute in
optimal mode.
Statistics in the V$SYSSTAT
and V$SESSTAT
views show the total number of work areas executed with optimal memory
size, one-pass memory size, and multi-pass memory size. These
statistics are cumulative since the instance or the session was started.
The following query gives the total number and the percentage of times work areas were executed in these three modes since the instance was started:
SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage
FROM (SELECT name, value cnt, (sum(value) over ()) total
FROM V$SYSSTAT
WHERE name like 'workarea exec%');
The output of this query might look like the following:
PROFILE CNT PERCENTAGE
----------------------------------- ---------- ----------
workarea executions - optimal 5395 95
workarea executions - onepass 284 5
workarea executions - multipass 0 0
Tuning sort operations using SORT_AREA_SIZE
is only relevant for configurations running the Oracle shared server
option or for configurations not running under the automatic memory
management mode. In the later case, Oracle Corporation strongly
recommends switching to the automatic memory management mode, because
it is easier to manage and often outperforms a manually-tuned system.
This section describes the following:
A sort is an operation that orders data according to certain criteria before the data is returned to the requestor.
Operations that perform sorts include the following:
CREATE
INDEX
SELECT
.... ORDER
BY
SELECT
DISTINCT
SELECT
.... GROUP
BY
SELECT
... CONNECT
BY
SELECT
... CONNECT
BY
ROLLUP
See Also:
Oracle9i Database Concepts for a list of SQL statements that perform sorts |
When the WORKAREA_SIZE_POLICY
parameter is
set to MANUAL
, the maximum amount of memory allocated for
a sort is defined by the parameter SORT_AREA_SIZE
. If the
sort operation is not able to completely fit into SORT_AREA_SIZE
memory, then the sort is separated into phases. The temporary output of
each phase is stored in temporary segments on disk. The tablespace in
which these sort segments are created is the user's temporary
tablespace.
When Oracle writes sort operations to disk, it writes out partially sorted data in sorted runs. After all the data has been received by the sort, Oracle merges the runs to produce the final sorted output. If the sort area is not large enough to merge all the runs at once, then subsets of the runs are merged in several merge passes. If the sort area is larger, then there are fewer, longer runs produced. A larger sort area also means that the sort can merge more runs in one merge pass.
Oracle collects statistics that reflect sort activity and
stores them in dynamic performance views, such as V$SQLAREA
and V$SYSSTAT
.
Table 14-4
lists the statistics from V$SYSSTAT
that reflect sort
behavior.
For example, the following query monitors these statistics:
SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME IN ('sorts (memory)', 'sorts (disk)');
The output of this query might look like the following:
NAME VALUE
---------------------------------------------------------------- ----------
sorts (memory) 430418566
sorts (disk) 33255
In addition, to find individual SQL statements that are
performing sorts, query the V$SQLAREA
view. Order the
rows by SORTS
to identify the SQL statements performing
the most sorts. For example:
SELECT HASH_VALUE, SQL_TEXT, SORTS, EXECUTIONS
FROM V$SQLAREA
ORDER BY SORTS;
In an OLTP environment, the best solution is to investigate whether the SQL statements can be tuned to avoid the sort activity.
For best performance in OLTP systems, most sorts should
occur solely within memory. Sorts written to disk can adversely affect
performance. If your OLTP application frequently performs sorts that do
not fit into sort area size, and if the application has been tuned to
avoid unnecessary sorting, then consider increasing the SORT_AREA_SIZE
parameter for the whole instance.
If there are only a few programs that perform larger than
average sorts that sort to disk, then it is possible to modify SORT_AREA_SIZE
at the session level only for that workload or application (for
example, building an index).
DSS applications typically access large volumes of data.
These types of applications are expected to perform sorts to disk,
purely because of the nature of the application and the data volumes
involved. In DSS applications, it is important to identify the optimal SORT_AREA_SIZE
to allow the disk sorts to perform most efficiently. Allocating more
memory to sorts does not necessarily mean that the sort will be faster.
The main consideration when choosing SORT_AREA_SIZE
is balancing memory usage with sort performance.
Since Oracle8 release 8.0, sorts do not allocate the
whole of SORT_AREA_SIZE
in one memory allocation at the
beginning of the sort. The memory is allocated in DB_BLOCK_SIZE
chunks when required, up to SORT_AREA_SIZE
.
This means that increasing SORT_AREA_SIZE
memory is a concern when the majority of processes on the system
perform sorts and use the maximum allocation. In this situation,
increasing SORT_AREA_SIZE
for the instance as a whole
results in more memory being allocated from the operating system (for
dedicated connections; that is, if a shared server environment is not
used). This is not necessarily a problem if the system has free memory
available. However, if there is not enough free memory, then this
causes paging or swapping.
If a shared server environment is used, then the
additional memory is allocated out of the shared pool or the large pool
when it is configured (that is, when the LARGE_POOL_SIZE
initialization parameter is specified). The actual amount of memory
used in the shared pool is the lesser of SORT_AREA_SIZE
, SORT_AREA_RETAINED_SIZE
,
and the actual allocation used by the sort.
If the SORT_AREA_SIZE
is too small, then
the sort is not performed as efficiently as possible. This means that
sorts that could have been memory-only sorts will be disk sorts, or,
alternatively, that the number of sort runs required to process the
sort could be larger than necessary. Both of these situations can
severely degrade performance.
Remember that there is a point after which increasing the
SORT_AREA_SIZE
no longer provides a performance benefit.
SORT_AREA_SIZE
is a dynamically modifiable
initialization parameter that specifies the maximum amount of memory to
use for each sort. If a significant number of sorts require disk I/O to
temporary segments, then your application's performance might benefit
from increasing the value of SORT_AREA_SIZE
.
Alternatively in a DSS environment, increasing SORT_AREA_SIZE
is not likely to make the sort a memory-only sort; however, depending
on the current value and the new value chosen, it could make the sort
faster.
The maximum value of this parameter depends on your
operating system. You need to determine what size SORT_AREA_SIZE
makes sense for your system.
The SORT_AREA_RETAINED_SIZE
parameter
determines the lower memory limit to which Oracle reduces the size of
the sort area after the sort has started sending the sorted data to the
user or to the next part of the query.
With dedicated connections, the freed memory is not released to the operating system, rather the freed memory is made available to the session for reuse.
However, if the connection is through shared server, then
there could be a memory benefit to setting SORT_AREA_RETAINED_SIZE
.
If this parameter is set after the sort has completed, then the sorted
data is stored in the SGA. The amount of memory used in the SGA is the
lesser of the actual usage or SORT_AREA_RETAINED_SIZE
if
it is set; otherwise, it is SORT_AREA_SIZE
. This is why
setting SORT_AREA_RETAINED_SIZE
could be of use with a
shared server environment.
Note: Connections made to the database through shared servers usually should not perform large sorts. |
Although there might be a memory saving with shared
server, setting SORT_AREA_RETAINED_SIZE
causes additional
I/O to write and read data to and from temporary segments on disk (if
the sort requires more than SORT_AREA_RETAINED_SIZE
bytes).
One cause of sorting is the creation of indexes. Creating an index for a table involves sorting all rows in the table based on the values of the indexed columns. However, Oracle lets you create indexes without sorting. If the rows in the table are loaded in ascending order, then you can create the index faster without sorting.
To create an index without sorting, load the rows into
the table in ascending order of the indexed column values. Your
operating system might provide a sorting utility to sort the rows
before you load them. When you create the index, use the NOSORT
clause on the CREATE
INDEX
statement. For
example, the following CREATE
INDEX
statement creates the index my_emp_name_ix
on the last_name
column of the employees
table without sorting the rows in
the employees
table:
CREATE INDEX my_emp_name_ix
ON employees(last_name)
NOSORT;
In this SQL example, it is assumed that the rows in the table are loaded in ascending order of the indexed column values.
Note: Specifying |
Presorting your data and loading it in order might not be the fastest way to load a table.
NOSORT
clause. NOSORT
clause. Sorting can be avoided when performing a GROUP
BY
operation when you know that the input data is already
ordered, so that all rows in each group are clumped together. This can
be the case if the rows are being retrieved from an index that matches
the grouped columns, or if a sort merge join produces the rows in the
right order. ORDER
BY
sorts can be avoided
in the same circumstances. When no sort takes place, the EXPLAIN
PLAN
output indicates GROUP
BY
NOSORT
.