Oracle High Performance Tuning for 9i and 10g

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.

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.

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.

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.

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.

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.

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.