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 


 

 

 


 

 

 

 

 

Manual Space Management

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%.


In the manual method, free space within a segment is tracked by structures such as free lists and free list groups.

Within the first block of each segment, there is a segment header. The segment header contains a list of blocks with free space, called the master free list. Blocks are removed from the master free list when the free space in those blocks becomes less than that specified by the pctfree storage parameter. Blocks are added back to the master free list when their used space falls below the threshold specified by the pctused storage parameter.

The master free list is accessed and used by all the processes that insert or update an object. These processes remove and add blocks from free lists based on the space remaining after DML operations on those blocks. Simultaneous update to the free list involves writes to the segment header, and also may result in contention as shown in the Figure 8.3.

Figure 8.3: Free List in the Segment Header

To handle such contention situations, many free lists can be specified at the segment level. These are sometimes called process free lists. The freelist storage parameter indicates the number of free list sets. Each user process is assigned to one process free list. When the user process needs a block with free space, it scans its assigned process free lists. Existence of multiple process free lists improves performance for concurrent OLTP activities on the same segment and avoids segment header contention. As shown in Figure 8.4, multiple free lists help reduce contention.

Figure 8.4: Multiple Process Free Lists in the Segment Header

The process free lists are created when the FREELIST clause is used in a create TABLE/ALTER TABLE statement.

For example:

CREATE TABLE salesman
( name varchar2(4), salesid number(6) )
STORAGE ( FREELISTS 6) ;

There is a possibility of additional overhead and contention in the RAC system since multiple instances concurrently access the segment for DML operations. To reduce such contention, create groups of free lists and assign them to the individual instances. This way, users from a particular instance are confined to free lists from a group. This is shown in Figure 8.5. The free list group is a combination of a group specific master free lists and group specific process free lists. Each free list group is stored in its own block. Instances are assigned to different free list groups, thereby reducing contention between instances.

Figure 8.5: Multiple Free List Groups in a segment header

The process free lists are created when the freelist group clause is used in a CREATE TABLE/ALTER TABLE statement.

For example:

CREATE TABLE sales
( . . . )
STORAGE (INITIAL 10M NEXT 10M
MINEXTENTS 2 MAXEXTENTS 512
PCTFREE 20 PCTUSED 60 PCTINCREASE 0
FREELIST GROUP 2 FREELISTS 6 );

The freelist and freelist group parameters can also be specified for other objects such as indexes and clusters.  

For example:

CREATE INDEX sales_index ( . . . )
STORAGE ( FREELIST GROUP 3 FREELISTS 4) ;

Storage Parameters

freelists: Specifies the number of free lists for each of the free list  groups of the schema object.

freelist groups: The number of groups of free lists for the database objects being created. Oracle uses the instance number of Oracle Real Application Cluster instances to map each instance to one free list group.

Associating Instances with Free List Groups

Once the multiple freelist groups for an object in the RAC system have been assigned, the instances and user sessions can be associated with free list groups as follows:

* instance_number parameter: Various SQL clauses with the instance_number initialization parameter can be used to associate extents of data blocks with instances.

* SET INSTANCE clause: The SET INSTANCE clause of the ALTER SESSION statement can be used to ensure that a session uses the free list group associated with a particular instance, regardless of the instance to which the session is connected.

For example:

ALTER SESSION SET INSTANCE = inst_no

The ALLOCATE EXTENT clause of the ALTER TABLE or ALTER CLUSTER statement enables the pre-allocation of an extent to a table, index, or cluster using parameters that specify the extent size, data file, and group of free lists with which to associate the object. Pre-allocating and associating extents to different instances helps reduce contention during the concurrent inserts. For example:

ALTER TABLE SALES
ALLOCATE EXTENT (SIZE 5M INSTANCE 2);

 


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