Oracle Database Tips by Donald Burleson

Allocating Oracle Objects into Multiple RAM Data Buffers

Since very few Oracle databases can afford the cost of full RAM caching, many rules of thumb have been developed for the segregation and isolation of cached objects.  Some of these rules of thumb will yield clues about the best way to utilize SSD in a solid-state Oracle environment:

• Segregate large-table full-table scans: Tables that experience large-table full-table scans will benefit from the largest supported block size and should be placed in a tablespace with the largest block size.

• Use the RECYCLE Pool: If db_cache_size is not being set to the largest supported block size for the server, the db_recycle_cache_size  parameter should not be used. Instead, a db_32k_cache_size, or whatever the user's max is, should be created and all tables that experience frequent large-table full-table scans should be assigned to the largest buffer cache in the database.

• Segregate Indexes:  In many cases, Oracle SQL statements will retrieve index information via an index range scan, scanning the b-tree or bitmap index for ranges of values that match the SQL search criteria. Hence, it is beneficial to have as much of an index residing in RAM as possible. One of the first things the Oracle 9i DBA should do is to migrate all of their Oracle indexes into a large blocksize tablespace. Indexes will always favor the largest supported blocksize.

• Segregate random access reads: For those databases that fetch small rows randomly from the disk, the Oracle DBA can segregate these types of tables into 2K tablespaces. While disk is becoming cheaper every day, it is still not wise to waste any available RAM by reading in more information to RAM than is actually going be used by the query. Hence, many Oracle DBAs will use small block size is in cases of tiny, random access record retrieval.

• Segregate LOB column tables: For those Oracle tables that contain raw, long raw, or in-line LOBs, moving the table rows to large block size will have an extremely beneficial effect on disk I/O. Experienced DBAs will check dba_tables.avg_row_len to make sure that the blocksize is larger than the average size. Row chaining will be reduced while at the same time the entire LOB can be read within a single disk I/O, thereby avoiding the additional overhead of having Oracle to go out of read multiple blocks.

• Segregate large-table full-table scan rows: When the recycle pool was first introduced in Oracle8i, the idea was the full-table scan data blocks, which are not likely to be reread by other transactions, could be quickly flushed through the Oracle SGA thereby reserving critical RAM for those data blocks which are likely to be reread by another transaction. In Oracle9i, the RECYCLE pool can be configured to use a smaller block size.

• Check the average row length: The block size for a table's tablespace should always be greater than the average row length for the table, dba_tables.avg_row_len. Not only is it smaller than the average row length, rows chaining occurs and excessive disk I/O is incurred.

• Use large blocks for data sorting: The TEMP tablespace will benefit from the largest supported blocksize. This allows disk sorting to happen in large blocks with a minimum of disk I/O.

These suggestions are very important to the study of the best way to utilize SSD as an alternative caching mechanism.

However, recent TPC-C benchmarks make it clear that very large RAM regions are a central component in high performance Oracle databases.  The 2004 UNISYS Oracle Windows benchmark exceeded 250,000 transactions per minute using a Windows-based 16-CPU server with 115 gigabytes of Oracle data buffer cache.  The following are the Oracle parameters that were used in the benchmark, and the benefit of large scale RAM caching is clear:

• db_16k_cache_size  = 15010M

• db_8k_cache_size  = 1024M

• db_cache_size  = 8096M

• db_keep_cache_size  = 78000M

At this point, it is very clear that RAM resources are an important factor in maintaining the performance of I/O intensive Oracle systems.

See code depot for complete scripts

This is an excerpt from the book Oracle RAC & Tuning with Solid State Disk.  You can get it for more than 30% by buying it directly from the publisher and get immediate access to working code examples.