Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy


  Oracle Tips by 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.

The above book excerpt is from:

Oracle RAC & Grid Tuning with Solid State Disk
Expert Secrets for High Performance Clustered Grid Computing

ISBN: 0-9761573-5-7
Mike Ault, Donald K. Burleson  

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks

Hit Counter