15.2 How Oracle Database Uses Memory

---

Tuning the way that Oracle Database uses memory is a highly complex subject. This section will serve as an introduction to how Oracle Database uses memory with a brief description of tuning Oracle Database memory usage.

Oracle Database uses memory as a number of buffers to cache information, instructions, and data in. These buffers are placed into both RAM and virtual memory and generally exist to help speed up performance. Note that it is best to contain all buffers in RAM and not spread into virtual memory. Also do not neglect memory requirements for the operating system and anything else running on your database server. If something can be accessed from a memory buffer rather than from disk storage then I/O activity will be reduced since accessing memory is much faster than accessing from disk storage.

Tip 

There is a limit to how large memory buffers can be. Managing the multi-user aspects of obtaining information from buffers is much more complex than managing locking of table data stored on disk. There is a point where buffers can become too big and become detrimental to performance.

The different buffers not only have to be tuned in relation to CPU usage and I/O activity but also in relation to each other. Some buffers depend on the proper sizes of other buffers to perform at optimal pace. Here we will briefly introduce tuning the buffer cache areas from a generalized perspective. What are the separate buffers areas?

• The Database Buffer Cache.   Contains database object data such as table and index rows for fast access in RAM, reducing I/O.

  Note 

   Oracle Database 10 Grid   The ALTER SYSTEM command allows flushing of the database buffer cache. This option should only be used for testing purposes.

• The Shared Pool.   Contains highly shareable information such as database metadata and parsed SQL code.

  Tip 

  Flushing the shared pool is not an action which should be used on a production server. The need to flush the shared pool is probably due to oversizing of the shared pool, probably as a result of application issues. Flushing the shared pool will not resolve application issues and it absolutely will hurt performance because anything shareable must be reloaded.

• Session Connection Buffers.   Every database connection creates a session. Shared memory areas are allocated to each session both within the shared pool and the large pool depending on configuration. Some applications continually connect and disconnect from the database for every SQL code command executed. This type of activity is common in Java object applications. Objects tend to oversimplify complexity whereas a relational database can thrive on complexity.

• The Large Pool.   A cache area less structured than the shared pool with similar functions.

• The Redo Log Buffer.   Redo log entries record all database change activity.

• The Java Pool.   Used for the Oracle JVM.

All of the buffers together are known as the System Global Area and can be examined in general using the SHOW SGA command in SQL*Plus. The same information can be found by querying the V$SGA performance view. The V$SGASTAT performance view will provide memory allocations to all parts of the SGA.

SHOW SGA;     Total System Global Area   122755896   bytes Fixed Size                    453432   bytes Variable Size               88080384   bytes Database Buffers            33554432   bytes Redo Buffers                  667648   bytes

Here are some of the more interesting parts of the SGA using the V$SGASTAT performance view.

COL name FORMAT a32; SELECT pool, name, bytes FROM v$sgastat WHERE pool IS NULL OR pool = 'large pool' OR (pool = 'shared pool'       AND (name IN('dictionary cache','enqueue','library cache'       ,'parameters','processes','sessions','free memory'))) ORDER BY pool DESC NULLS FIRST, name;     POOL           NAME                   BYTES ------------   ----------------   ---------                buffer_cache        46137344                fixed_sga             453492                log_buffer            656384 shared pool    dictionary cache     1610880 shared pool    enqueue               223368 shared pool    free memory         58436864 shared pool    library cache        2497744 shared pool    parameters              6264 shared pool    processes             192000 shared pool    sessions              543600 large pool     PX msg pool           122880 large pool     free memory          4071424

It is best to make sure that the entire SGA fits into RAM and does not overflow into temporary disk space or virtual memory. As already stated, space should be reserved in RAM for other functions such as other processes, applications, and the operating system. The LOCK_SGA parameter forces the entire SGA to remain in RAM, excluding the use of virtual memory disk space. Only some platforms support this parameter.

Note 

 Oracle Database 10 Grid   A new performance view called V$SGAINFO gives a better picture of the SGA.

Now let's look at the different pools in a little more detail.

15.2.1 The Database Buffer Cache

The database buffer cache is available in Oracle9i Database in two forms. The first form is the original form using the DB_BLOCK_BUFFERS parameter and various other parameters. The second form is using the DB_CACHE_SIZE parameter, along with its associated parameters. In general the same rules and methods govern the way in which the two database buffer caches are tuned. The DB_BLOCK_BUFFERS parameter will not be covered in this book.

Note 

 Oracle Database 10 Grid   The DB_BLOCK_BUFFERS parameter was deprecated in Oracle9i Database but is still usable. This parameter is no longer available in Oracle Database 10g.

The database buffer cache can be bypassed. When it is bypassed then data is read directly from disk. Full table scans are read directly from disk amongst various other operations such as sorting and parallel processing.

Two further factors warrant discussion:

• Multiple Block Sizes.   Oracle9i Database allows for tablespaces with different block sizes. Large transactions can benefit from larger block size tablespaces and small transactions from smaller block sizes. The database buffer cache is thus split into separate caches involving all but the default, using parameters named DB_[ 2 | 4 | 8 | 16 | 32 ]K_CACHE_SIZE.

  Tip 

  Available block sizes and thus DB_nK_CACHE_SIZE parameters may vary for different operating systems. A 32K block size is not available for Win2K SP3 running Oracle Database Release 2 (9.2).

• Multiple Buffer Pools.   Oracle9i Database handles the keep and recycle pools using the DB_KEEP_POOL_SIZE and DB_RECYCLE_POOL_SIZE parameters. The keep pool should be used to attempt to keep small static or frequently used tables and indexes in memory. The recycle pool should be used to remove large infrequently accessed row sets from memory as quickly as possible. Note that the keep and recycle pools can only contain blocks contained in tablespaces of block size defined by the DB_BLOCK_SIZE parameter; use the DB_nK_CACHE_SIZE buffers otherwise.

15.2.2 The Shared Pool

The shared pool allows for sharing and reuse of information and previously processed executions. The shared pool retains highly shareable data in memory used simultaneously by many database users. Things stored in the shared pool include items such as previously parsed and executed SQL code, latches protecting buffer areas and data dictionary metadata. All of these items are heavily used by all sessions connected to the database. The shared pool is set using the SHARED_POOL_SIZE parameter. Setting the SHARED_POOL_RESERVED_SIZE parameter to a small percentage of the shared pool, normally less than 10%, will reserve a small portion of the shared pool for contiguous large transactions. This can help to avoid large amounts of defragmentation in the shared pool when large transactions are executed. Defragmentation in the shared pool can cause serious wait event and latch contention problems.

The reserved section of the shared pool can be examined with the following query.

SELECT request_misses, request_failures, free_space FROM v$shared_pool_reserved; 

Even when running large queries on my database free space remains constant at 2 Mb, the setting for the SHARED_POOL_ RESERVED_SIZE parameter. My reserved shared pool is never used because the rest of the shared pool is large enough and not suffering from fragmentation as a result of poorly tuned SQL code. Request misses imply that flushing occurred because the shared pool reserved portion is not large enough for large transactions, or it is being used. Request failures are worse. If either of the misses or failures columns have a value greater than zero, perhaps increase the value of the parameter SHARED_POOL_RESERVED_ SIZE. I have seen some success setting this parameter to values sometimes 10% and over of the shared pool for databases executing large amounts of poorly tuned SQL code. The default setting is 5%. The better solution is to tune the SQL code but that is often not possible. If you are going to tell a customer to rewrite an application you might as well be telling them to reinvent the wheel. Using bind variables in SQL code or forcing their use with the CURSOR_SHARING parameter can alleviate these issues substantially but be cautious using cursor sharing in a data warehouse. As always there is no better solution than tuning SQL code. Using bind variables as a quick fix to avoid tuning poorly built SQL code is relative to how scalable the database needs to be in the long term. Additionally data warehouse databases perform best without bind variable use, due to matching of statistics over vast quantities of data and the use of histograms. Statistics are very precise, if they are kept up to date. Using bind variables in SQL code will offset the accuracy of statistics in large data warehouses.

REQUEST_MISSES   REQUEST_FAILURES   FREE_SPACE --------------   ----------------   ----------              0                  0      2014320

The shared pool is quite literally a shared area of memory containing various types of information in various caches used to help Oracle Database perform better. What are these different memory caches?

• The Library Cache.   Parsed SQL and PL/SQL code plus optimized query execution plans for SQL code. Also included is Java class code.

• The Metadata Cache.   Data about the data stored in memory for rapid access.

Tuning and sizing the shared pool correctly is critical to performance. Proper "sharing" of high-usage information between many users is more critical than proper tuning of other buffers, other than perhaps a completely inappropriately sized redo log buffer. The database buffer cache is not as important as the shared pool because data in the shared pool is more likely to be shared by more users.

The Library Cache

The library cache contains parsed SQL code and latches. It is important to note that efficiently tuned SQL code will not affect library cache performance directly. Properly tuned SQL code will help database performance in general. Bind variables used in SQL code will help to allow the library cache to share previously parsed SQL code. The problem with sharing using bind variables is that in very large or widely variant data sets bind variables can generalize statistics. This need to use statistics precisely is more important in data warehouses but exact value searches with statistics can potentially perform much faster.

SQL code tuning is covered in Part II of this book. Most of the aspects of SQL code tuning affecting performance of the library cache are based on the way in which SQL code is written and the ways in which applications manage connections to the database. Latches and locks will be covered later in this book. Note that management of latches can become difficult if shared pool space allocated to latch management is too low if the shared pool is either too small or filled with too many different things.

Examine library cache statistics using the V$LIBRARYCACHE performance view. The GETS column applies to locking requests, the PINS column to pins in the shared pool, RELOADS indicates I/O activity, and INVALIDATIONS denotes SQL code re-parsing.

SELECT namespace, gets, pins, reloads, invalidations       FROM v$librarycache;     NAMESPACE          GETS      PINS   RELOADS   INVALIDATIONS ---------------   -----   -------   -------   ------------- SQL AREA          97385   1577407        16               0 TABLE/PROCEDURE   50741    494446         0               0 BODY              24624     24623         0               0 TRIGGER           52336     52336         0               0 INDEX                66        35         0               0 CLUSTER             167       221         0               0 OBJECT                0         0         0               0 PIPE                  0         0         0               0 JAVA SOURCE           0         0         0               0 JAVA RESOURCE         0         0         0               0 JAVA DATA             0         0         0               0

Here are a few pointers:

• Maintain Connections.   Do not continuously connect and disconnect from the database for every SQL code operation. Establishing and breaking a database connection involves starting up and stopping of processes and assignment and releasing of chunks of memory. It is better to share connections, especially with large numbers of users, preferably using a middle-tier server utilizing some type of connection pooling. Oracle shared servers is not as effective as a middle-tier since connection pool management takes processing power away from the database server.

• Generated SQL.   Generated SQL is SQL generated "on the fly" from within an application. This type of application-based SQL code is always unpredictable and is largely dependent on the coding skills of multiple programmers. Additionally programmers always have their own distinctive styles and it is very difficult to enforce standards.

• Copying Schemas to Different Users.   There is some weight to be granted for creating separate schemas for each user. However, this type of application-based database design tends to be top-down and not built from the perspective of database efficiency. See the next section on the metadata (dictionary) cache.

• PL/SQL and DDL.   PL/SQL can help since packages can be pinned into contiguous areas of memory at database startup. However, many developers will balk at the idea of placing too much business logic and application-type processing into the database. Executing DDL commands on highly active objects is detrimental to performance because many DDL commands will invoke table locks amongst other unpleasant things.

• SESSION_CACHED_CURSORS.   The library cache effectively caches cursors at the database level. The SESSION_CACHED_ CURSORS parameter can be used to cache cursors for each session. A cached cursor is a reusable cursor. Reusing a cursor removes the need for re-processing.

  SELECT name, value FROM v$sysstat WHERE name       IN('session cursor cache count','session cursor          cache hits');     NAME                          VALUE ---------------------------   ----- session cursor cache hits         0 session cursor cache count        0 

  Search for unnecessary parse calls using the V$SQLAREA view.

  COL execs FORMAT 9999990; COL parses FORMAT 99990; COL fetches FORMAT 999990; COL loads FORMAT 9990; COL invalids FORMAT 9999990; COL i/o FORMAT 99990; COL hits FORMAT 9999990; COL rows FORMAT 999990; COL sorts FORMAT 9990; COL sql FORMAT a32;     SELECT * FROM( SELECT executions "Execs"        --Executions       ,parse_calls "Parses"         --Parses       ,fetches "Fetches"            --Fetches       ,loads     "Loads"            --Loads and reloads       ,invalidations "Invalids"     --Invalidations       ,disk_reads "I/O"             --I/O       ,buffer_gets "Hits"           --Buffer hits       ,rows_processed "Rows"        --Rows       ,sorts       "Sorts"          --Sorts       ,sql_text "SQL" FROM v$sqlarea ORDER BY executions DESC ) WHERE ROWNUM <= 10;     Execs    Parses   Fetches   Loads     I/O     Rows   SQL ------   ------   -------   -----   -----   ------   --------- 210140    24788         0     1      1477   210139   INSERT in 210140    24788         0     1      2205   210140   INSERT in 210140    24788         0     1      2081   210131   INSERT in 210137    24789         0     1      3022   210142   INSERT in 154342    32775    154342     1       164   154342   SELECT to 101918    32773    101918     1       180   101918   SELECT sy 64544     64544         0     1         0        0   COMMIT 61054     61056         0     1     19950    61054   INSERT in 61050     61050         0     1     14588    61050   INSERT in 45193      4603         0     1       330    45193   INSERT in

• CURSOR_SPACE_FOR_TIME.   This parameter can be set to TRUE to only remove cursors from the shared pool when they are closed rather than aging them out according to MRU and LRU lists. A parameter like this can cause problems if for instance cursors are left open by applications or ad hoc SQL code power users. What happens if someone goes to lunch?

The Metadata or Dictionary Cache

The metadata cache contains all of the database definitional data such as table and index structural definitions in memory in the shared pool. Metadata is generally accessed much more frequently than anything else in the database and is absolutely critical for efficiency.

Too much metadata in the database can cause serious performance problems. Let's once again examine a top-down database design built from the perspective of an application. Sometimes these types of database designs are characterized by profligate use of schema copies, views, and synonyms, increasing memory requirements for metadata even further. It is always best to access objects directly. Be aware of creating too many logical object layers. Views and synonyms can often be used for security purposes. Be forewarned of the hidden costs of building your database structure from the point of view of the application.

The metadata cache can be examined in great detail using the V$ROWCACHE performance view. My Accounts schema high-activity code using DBMS_JOBS scheduled mass DML activity is apparent in the results of the following query. The Accounts schema uses sequences as primary keys so there is high sequence activity. Manual rollback segments are currently used in my database as is apparent as well, amongst other things.

SELECT COUNT "Entries", GETS "Requests", GETMISSES "Misses"       ,MODIFICATIONS "DML Activity", parameter "Area" FROM v$rowcache;     Entries  Requests  Misses  DML Activity  Area -------  --------  ------  ------------  --------------       0         0       0             0  dc_free_extents       0         0       0             0  dc_used_extents     169       683     169             0  dc_segments       4     33577       4             0  dc_tablespaces       2       193       2           193  dc_tablespace_quotas       0         0       0             0  dc_files      19    204738      19             0  dc_users      29      5202      28            49  dc_rollback_segments     292      1095     237            55  dc_objects       6        12       6             0  dc_global_oids       0         0       0             0  dc_constraints     301   1075530     246            55  dc_object_ids       7     31064       7         31064  dc_sequences       5       332       5             0  dc_usernames       0         0       0             0  dc_database_links      79       226      79             0  dc_histogram_defs       0         0       0             0  dc_table_scns       0         0       0             0  dc_outlines       1     40792       1             0  dc_profiles       0         0       0             0  dc_encrypted_objects       0         0       0             0  dc_encryption_                                             profiles       0         0       0             0  dc_qmc_cache_entries       0         0       0             0  dc_users       0         0       0             0  dc_histogram_data       0         0       0             0  dc_histogram_data_                                              values       0         0       0             0  dc_partition_scns      15       411      15             0  dc_user_grants       0         0       0             0  dc_app_role

Pinning Objects in the Shared Pool

PL/SQL packages can be pinned in memory to place them into a contiguous area to prevent further parsing processing. Packages should be pinned at database startup to ensure that contiguous areas of memory are used and parts of packages are not spread all over the shared pool and thus defragmented. Packages can be pinned into memory using the DBMS_SHARED_POOL package using the KEEP procedure.

DBMS_SHARED_POOL.[UN]KEEP (object, type);

The type parameter is set as P, C, R, or Q for package, cursor, trigger or sequence, respectively.

15.2.3 Session Connection Cache

Every connected session is connected directly to a dedicated server process, or indirectly using a dispatcher to a shared server process. A server process provides access to the database server for a client connection. A proportion of shared server process memory requirements are passed to the large pool. The large pool will be discussed later in this chapter. Session connection cache for each connection can be automatically or manually managed for dedicated connections but only manually managed for shared server connections. General connection cache involves sort space, bitmap operation space, and space in memory for hash joins.

The session connection cache is the area of memory assigned to managing connections to the database. There are two methods of handling database connections.

• Dedicated Server Connection.   This type of connection maintains a connection to every client process even when client activity is idle. Dedicated server configuration is generally used for client server implementations or connections to middle-tier application or web servers where the burden of connection pooling and load balancing is placed onto the middle tier.

  Note 

   Oracle Database 10 Grid   Using the two parameters WORKAREA_SIZE_POLICY = AUTO and PGA_AGGREGATE_TARGET for automated session connection memory management has been recommended by Oracle Corporation since Oracle9i Database as better performing. Dedicated server connection configurations can perform well even at 1,000 concurrent connections.

• Shared Server Connection.   Server connection processes are shared between multiple database connections, usually implemented for highly active OLTP databases. Shared server architectures are totally unnecessary when a middle tier is included in the general system architecture. Shared servers connect to client connections through the management of network dispatcher processes.

  Note 

   Oracle Database 10 Grid   Oracle installation software and the Database Configuration Assistant defaults to dedicated server mode from Oracle9i Database onwards.

Various parameters are involved in controlling session-level connection memory. There are also two separate options. The first option is automated memory management and the second is manual memory management. Automated memory management can only be used with dedicated server database connections.

Automated Memory Management

• WORKAREA_SIZE_POLICY = AUTO.

• PGA_AGGREGATE TARGET = n.   Total PGA memory available for dedicated server database connections. Statistics can be obtained from the V$PGASTAT performance view.

  SELECT * FROM v$pgastat;     NAME                                  VALUE    UNIT --------                           --------   ----- aggregate PGA target parameter            0   bytes aggregate PGA auto target                 0   bytes global memory bound                       0   bytes total PGA inuse 8950784 bytes total PGA allocated                85715968   bytes maximum PGA allocated              85715968   bytes total freeable PGA memory                 0   bytes PGA memory freed back to OS               0   bytes total PGA used for auto workarea          0   bytes maximum PGA used for auto workar          0   bytes total PGA used for manual workar          0   bytes maximum PGA used for manual work          0   bytes over allocation count                     0 bytes processed                           0   bytes extra bytes read/written                  0   bytes cache hit percentage                      0   percent

Manual Memory Management

• WORKAREA_SIZE_POLICY = MANUAL.   This option switches off the use of the PGA_AGGREGATE_TARGET parameter.

• SORT_AREA_SIZE = n.   Determines the size of the sort buffer. When the buffer is exceeded for a connection sorting will be passed to temporary sort space on disk. Serious performance problems can occur in some situations. Firstly, if the sort buffer is too small and too many sorts are moved to disk heavy I/O will result. Secondly, too large a sort buffer could potentially use up too much memory since every database connection is allocated the amount of memory specified in the sort buffer as a maximum for sorting. If the SORT_AREA_SIZE is set to 1M then 2,000 users could require up to 2 Gb of RAM, assuming there are enough connections executing concurrent sorting. Thirdly, make sure a temporary sorting tablespace is created in the database and that each user is allocated that temporary tablespace. The default temporary sorting tablespace can be set in the CREATE DATABASE or ALTER DATABASE commands. If not set, the SYSTEM metadata tablespace will be used for sorting.

  Note 

   Oracle Database 10 Grid   On disk temporary space can be allocated as tablespace groups where a tablespace group can contain multiple locally managed temporary tablespaces. A tablespace group will allow spreading of SQL execution sorting across multiple temporary tablespaces, thereby potentially speeding up sorting operations. Processing is distributed. A user can be allocated a tablespace group as a temporary sort space as opposed to just a single temporary tablespace.

• SORT_AREA_RETAINED_SIZE = less than 10% of SORT_ AREA_SIZE.   This parameter helps to retain memory space for multiple sorts in the same query.

  Tip 

  It is possible that the value of the SORT_AREA_RETAINED_ SIZE parameter should be set equal to the SORT_AREA_SIZE parameter, especially where large sorts are very common. Since many OLTP databases are often in reality OLTP/DSS databases this may always apply.

• CREATE_BITMAP_AREA_SIZE = n.   Used to create bitmap indexes in memory.

• BITMAP_MERGE_AREA_SIZE = n.   In memory bitmap merge operations.

• HASH_AREA_SIZE = n.   Used for hash joins in SQL join statements.

15.2.4 The Large Pool

The large pool is not a subset of the shared pool buffer but is used as an area of memory to contain what could possibly be termed shared pool buffer overflow in certain circumstances.

• Some shared server session connection memory.

• Backup processing.

• Parallel execution.

Shared Servers and Virtual Circuits

A virtual circuit is a chunk of memory reserved for a shared database connection through a dispatcher process. A dispatcher process can support multiple concurrent client processes where each client connection is communicated with using a virtual circuit; the virtual circuit is used to pass requests and responses from and to the client.

15.2.5 The Redo Log Buffer

The redo log buffer is a cyclical buffer, which under certain circumstances is either fully or partially flushed to the currently active redo log file. The redo log buffer cache will be written to redo log files when it is 1/3 full; a COMMIT or ROLLBACK command is issued every 3 s or if it reaches 1 Mb.

Tip 

A checkpoint is a process of flushing dirty buffers from database buffer cache to disk storage. The redo log buffer is not flushed to disk when a checkpoint occurs. This is the consensus of opinion from a number of database administrators and contrary to many Oracle software texts.

During a checkpoint the database writer (DBWR) will post the log writer (LGWR) to write redo from the buffer to the disk if the DBWR process has to write dirty blocks for which redo entries have not been written. This is essential for recoverability. Nothing will be written to datafiles without first being written to redo log files. The DBWR process will actually stop and wait for LGWR process to complete writing redo log entries. Redo log entries are always written to disk before database changes to ensure recoverability. Redo log files are best on the fastest disk storage available.

What can cause problems with respect to the redo log buffer?

• The size of the redo log buffer must be appropriate for database activity.

  Tip 

  It is unlikely that the size of the redo log buffer will ever need to be changed from the default value created by the Database Configuration Assistant. Quite often the log buffer is inappropriately sized because it has been tampered with.

• The number and size of redo log files.

• The frequency of log switching can affect the availability of redo log files for buffer flushing.

How can we tell if the redo log buffer is too small? Contrary to popular belief the redo log space requests entry in the V$SYSSTAT view records requests for the buffer to write out to a redo log file, not requests for space in the buffer. The redo entries statistic tells us how often redo entries are copied into the buffer. The redo writes statistic tells us how often buffer entries were written to redo log files. The redo buffer allocation retries event tells us about retries required when a process has attempted to allocate free space in the log buffer. If the log writer (LGWR) falls behind this will occur. However, this event can also occur when a log switch occurs. Therefore, the occurrence of this event can have more than one meaning. If this event occurs because log buffer space is unavailable then the log buffer may be too small. On the contrary, if it occurs because of a log switch then perhaps the log files are too small. Speeding up log writing by using faster disks should always be considered as well.

15.2.6 The Java Pool

The Java pool is set using the JAVA_POOL_SIZE parameter. Unless the Oracle JVM is created for a database this parameter can be set to zero (JAVA_POOL_SIZE = 0). It is a complete waste of memory otherwise. If the Oracle JVM is created in a database the Java pool buffer must be set.

Note 

 Oracle Database 10 Grid   The JAVA_POOL_SIZE parameter is now dynamic and can be changed using the ALTER SYSTEM command and a binary parameter file (SPFILE).

15.2.7 Getting Advice on Buffers

There are various performance views providing advisory projections of potential performance as to sizing for various buffers. Let's start with the database buffer cache advisory view.

Database Buffer Cache Advice

The database buffer cache advisor performance view is called V$DB_CACHE_ADVICE. The following query shows the potential database buffer cache hit ratio decreasing as the cache size increases.

COL pool FORMAT a10; SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameter       WHERE name = 'db_cache_size') "Current Cache(Mb)" ,name "Pool", size_for_estimate "Projected Cache(Mb)" ,ROUND(100-estd_physical_read_factor,0) "Cache Hit Ratio%" FROM v$db_cache_advice WHERE block_size = (SELECT value FROM v$parameter       WHERE name = 'db_block_size') ORDER BY 1, 2;     Current     Pool       Projected   Cache Hit Cache(Mb)              Cache(Mb)      Ratio% ---------   --------   ---------   ---------        32   DEFAULT            4          92        32   DEFAULT            8          93        32   DEFAULT           12          96        32   DEFAULT           16          99        32   DEFAULT           20          99        32   DEFAULT           24          99        32   DEFAULT           28          99        32   DEFAULT           32          99        32   DEFAULT           36          99        32   DEFAULT           40          99        32   DEFAULT           44          99        32   DEFAULT           48          99        32   DEFAULT           52          99        32   DEFAULT           56          99        32   DEFAULT           60          99        32   DEFAULT           64          99        32   DEFAULT           68          99        32   DEFAULT           72          99        32   DEFAULT           76          99        32   DEFAULT           80          99 

For my Accounts schema I could decide to move some static and some very large tables into the keep and recycle buffer pools, respectively. Firstly, I change my parameters file on my database and bounce that database. I am not currently using an SPFILE binary parameter file for this database.

Tip 

Many Oracle9i Database parameters can be changed online using the ALTER SYSTEM command and the binary SPFILE parameter file. Not all parameters can be changed online; check the ISSYS_MODIFIABLE column in the V$PARAMETER view. The binary parameter file allows for configuration changes without forcing a database restart. Be sure to make backup copies of parameter files.

Note 

 Oracle Database 10 Grid   More parameters can be changed online.

My database parameter file has been changed and I have added the two keep and recycle pool parameters.

COL name FORMAT a24; SELECT name, value FROM v$parameter WHERE name LIKE    'db%cache%size%'; NAME                       VALUE ------------------------  ------- db_keep_cache_size        4194304 db_recycle_cache_size     8388608 db_2k_cache_size                0 db_4k_cache_size                0 db_8k_cache_size                0 db_16k_cache_size               0 db_32k_cache_size               0 db_cache_size            33554432

In Oracle8i Database the keep and recycle pools were subset MRU and LRU listings placed within the database buffer cache using the BUFFER_POOL storage parameter in the ALTER TABLE and ALTER INDEX commands. In Oracle9i Database keep and recycle pools are separate pools, as shown in the following by the change from 32M to around 44M for the entire database buffer cache.

SHOW SGA;     Total System Global Area   135338868   bytes Fixed Size                 453492      bytes Variable Size              88080384    bytes Database Buffers           46137344    bytes Redo Buffers               667648      bytes

In my Accounts schema I could distribute tables to different pools as shown in the following examples, placing the smallest tables in the keep pool and the largest tables in the recycle pool. Note that there are many other factors to consider. Assuming that static data sizes are known, the keep pool could be sized more precisely to actual data sizes. The Customer and Supplier tables are semi-static and relatively large, thus depending on current activity they could possibly benefit from being part of the keep pool.

ALTER TABLE type STORAGE(BUFFER_POOL KEEP); ALTER TABLE subtype STORAGE(BUFFER_POOL KEEP); ALTER TABLE period STORAGE(BUFFER_POOL KEEP); ALTER TABLE posting STORAGE(BUFFER_POOL KEEP); ALTER TABLE category STORAGE(BUFFER_POOL KEEP); ALTER TABLE coa STORAGE(BUFFER_POOL KEEP); ALTER TABLE generalledger STORAGE(BUFFER_POOL RECYCLE); ALTER TABLE stockmovement STORAGE(BUFFER_POOL RECYCLE);

Since the tables shown previously are pushed into keep and recycle pools it would make sense to examine their respective indexes. Indexes can be shifted to different pools regardless of where their parent tables are placed. Since I have placed small static tables into the keep pool to account for Optimizer full table scans there is no point in placing their indexes in the keep pool. It would however be sensible to place Customer and Supplier indexes into the keep pool on that basis.

ALTER INDEX xak_cust_name STORAGE(BUFFER_POOL KEEP); ALTER INDEX xak_cust_ticker STORAGE(BUFFER_POOL KEEP); ALTER INDEX xak_supp_name STORAGE(BUFFER_POOL KEEP); ALTER INDEX xak_supp_ticker STORAGE(BUFFER_POOL KEEP); ALTER INDEX xpk_customer STORAGE(BUFFER_POOL KEEP); ALTER INDEX xpk_supplier STORAGE(BUFFER_POOL KEEP); 

In contradiction to what was previously mentioned, full table scans on small static tables bypass the database buffer cache altogether, making it pointless to place those tables in the keep buffer pool. So perhaps static table indexes should be placed in the keep pool, not the tables. However, large joins including these static tables are likely to require fast access to static tables and their indexes. The first assumption was correct.

Let's look at the V$DB_CACHE_ADVICE performance view again, restricting the output somewhat.

COL pool FORMAT a10; SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameter       WHERE name = 'db_cache_size') "Current Cache(Mb)" ,name "Pool", size_for_estimate "Projected Cache(Mb)" ,ROUND(100-estd_physical_read_factor,0) "Cache Hit Ratio%" FROM v$db_cache_advice WHERE block_size = (SELECT value FROM v$parameter       WHERE name = 'db_block_size') AND size_for_estimate IN (4,8,32) ORDER BY 1, 2;

The keep pool is set at 5% of the default pool, which is 1.6 Mb. The V$DB_CACHE_ADVICE view does not offer an estimate less than 4M, thus there is no response for the keep pool.

Current               Projected        Cache Cache(Mb)   Pool      Cache(Mb)   Hit Ratio% ---------   -------   ---------   ----------        32   DEFAULT           4           97        32   DEFAULT           8           98        32   DEFAULT          32           99        32   KEEP              4           99        32   KEEP              8           99        32   KEEP             32           99        32   RECYCLE           4           99        32   RECYCLE           8           99        32   RECYCLE          32           99

Shared Pool Advice

The V$SHARED_POOL_ADVICE performance view provides potential information for varying shared pool sizes. Unfortunately as can be seen in the following query result there are no differences between estimates in the various columns. Perhaps this performance view will work a little more usefully in the next version of Oracle Database.

SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameter       WHERE name = 'shared_pool_size') "Current Mb" , shared_pool_size_for_estimate "Projected Mb" , ROUND(shared_pool_size_factor*100) "%" , ESTD_LC_SIZE "Library Mb" , ESTD_LC_TIME_SAVED "Parse Savings" ,ESTD_LC_MEMORY_OBJECT_HITS "Hits" FROM v$shared_pool_advice ORDER BY 1;     Current   Projected    %    Library    Parse Mb               Mb              Mb   Savings      Hits -------   ---------   ---   -------   -------   -------      64          32    50         7    121352   2824153      64          40    63         7    121352   2824153      64          48    75         7    121352   2824153      64          56    88         7    121352   2824153      64          64   100         7    121352   2824153      64          72   113         7    121352   2824153      64          80   125         7    121352   2824153      64          88   138         7    121352   2824153      64          96   150         7    121352   2824153      64         104   163         7    121352   2824153      64         112   175         7    121352   2824153      64         120   188         7    121352   2824153      64         128   200         7    121352   2824153

The V$LIBRARY_CACHE_MEMORY performance view provides a window into the Library cache part of the shared pool.

SELECT lc_namespace "Library" ,LC_INUSE_MEMORY_OBJECTS "Objects" ,LC_INUSE_MEMORY_SIZE "Objects Mb" ,LC_FREEABLE_MEMORY_OBJECTS "Freeable Objects" ,LC_FREEABLE_MEMORY_SIZE "Freeable Mb" FROM v$library_cache_memory;                               Objects   Freeable   Freeable Library         Objects        Mb    Objects         Mb -------------   -------   -------   --------   -------- BODY                  0         0         18          0 CLUSTER              13         0          4          0 INDEX                 8         0         31          0 JAVA DATA             0                    0          0 JAVA RESOURCE         0         0          0          0 JAVA SOURCE           0         0          0          0 OBJECT                0         0          0          0 OTHER/SYSTEM          0         0          6          0 PIPE                  0         0          0          0 SQL AREA             75         0        757          5 TABLE/PROCEDURE      61         0        521          1 TRIGGER               6         0         27          0 

PGA Advice

The V$PGA_TARGET_ADVICE and V$PGA_TARGET_ADVICE_ HISTOGRAM performance views are predictive or advisory views when using automated workarea policy for database connection cache.

Java Pool Advice

 Oracle Database 10 Grid   Two news views are provided for assistance with and analysis of the Java pool: V$JAVA_POOL_ADVICE and V$JAVA_LIBRARY_ CACHE.

---