Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Tablespace Design in Real Application Clusters

Oracle RAC Cluster Tips by Burleson Consulting

This is an excerpt from the bestselling book Oracle Grid & Real Application Clusters.  To get immediate access to the code depot of working RAC scripts, buy it directly from the publisher and save more than 30%.


The goal in tablespace design is to group database objects according to their data access distribution patterns. The dependency analysis and transaction profiles of the database must be considered, and then tablespaces are divided into containers for the following objects:

* Frequently and randomly modified tables and indexes belonging to particular functional areas.

* Frequently and randomly modified tables and indexes with a lower probability of having affinity to any functional area.

* Tables and indexes that are mostly READ or READ-ONLY and infrequently modified.

The following criteria must also be considered for separating database objects into tablespaces:

* Tables should be separated from indexes.

* Assign READ-ONLY tables to READ-ONLY tablespaces.

* Group smaller reference tables in the same tablespace.

Using this strategy to group objects into tablespaces will improve the performance of Oracle's dynamic resource mastering. Oracle?s dynamic resource remastering by datafiles algorithm re-distributes GCS resources to the instance where they are needed most. This remastering strategy is designed to improve resource operation?s efficiency. Oracle remasters cache blocks to the instance with which the cache blocks are most closely associated based on historical access patterns. As a result, resource operations, after remastering, require minimal communication with remote instances through the GES and GCS.

Extent Management and Locally Managed Tablespaces

Allocating and de-allocating extents are expensive operations that should be minimized. Most of these operations in Real Application Clusters require inter-instance coordination. A high rate of extent management operations can adversely affect performance in Real Application Clusters environments more than in single instance environments. This is especially true for dictionary-managed tablespaces.

Identifying Extent Management Issues

If the row cache lock event is a significant contributor to the non-idle wait time in v$system_event, there is contention in the data dictionary cache. Extent allocation and de-allocation operations could cause this. v$rowcache provides data dictionary cache information for dc_used_extents and dc_free_extents. This is particularly true when the values for dlm_conflicts for those parameters increase significantly over time. This means that excessive extent management activity is occurring.

Minimizing Extent Management Operations

Proper storage parameter configuration for tables, indexes, temporary segments, and rollback segments decreases extent allocation and de-allocation frequency. This is accomplished using the initial, next, pctincrease, minextents, and optimal parameters.

Using Locally Managed Tablespaces

Extent allocation and de-allocation overhead can be greatly reduced if locally managed tablespaces are used. For optimal performance and the most efficient use of space, segments in locally managed tablespaces should ideally have similar space allocation characteristics. This enables the tablespace to be created with the proper uniform extent size that corresponds to the ideal extent size increment calculated for the segments.

For example, tables with relatively high insert rates can be placed in a tablespace with a 10MB uniform extent size. On the other hand, small tables with limited DML activity can be placed in a tablespace with a 100K uniform extent size. For an existing system, where tablespaces are not organized by segment size, this type of configuration can require significant reorganization efforts with limited benefits. For that reason, compromise by making most of the tablespaces locally managed, with AUTOALLOCATE instead of UNIFORM extent allocation.

 


This is an excerpt from the bestselling book Oracle Grid & Real Application Clusters, Rampant TechPress, by Mike Ault and Madhu Tumma.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

http://www.rampant-books.com/book_2004_1_10g_grid.htm


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational