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 


 

 

 


 

 

 

Linda Webb

Got breaking Oracle news?    Burleson Consulting Oracle News

Click here for more Oracle News Headlines

 


Bitmap vs. link list freelists

There are two kinds of freelists, one-way linked lists and bitmap freelists (ASSM).

Bitmap freelists vs. traditional space management

Before I discuss the differences between bitmap freelists and traditional space management, let?s examine how bitmap freelists are implemented. I'll begin by creating a tablespace with the segment space management auto parameter:

create tablespace
asm_lmt_ts
datafile
'c:\oracle\oradata\diogenes\asm_lmt.dbf'
size
5m
EXTENT MANAGEMENT LOCAL -- Turn on LMT
SEGMENT SPACE MANAGEMENT AUTO -- Turn on ASSM
;


Once you've defined the tablespace, tables and indexes can easily be moved into the new tablespace with a variety of methods. Here I've used create:

create table
new_cust
tablespace
assm_lmt_ts
as
select * from customer;

alter index cust_name_idx rebuild tablespace assm_lmt_ts;


Note that after a table or index is allocated in this tablespace, the values for PCTUSED for individual objects will be ignored and Oracle will automatically manage the freelists for the tables and indexes inside the tablespace using bitmap arrays. For tables and indexes created inside an LMT tablespace, the NEXT extent clause is obsolete because the locally managed tablespace manages them. However, the INITIAL parameter is still required because Oracle cannot know in advance the size of the initial table load. For ASSM, the minimum INITIAL value is three blocks.

There is some debate about whether a one-size-fits-all approach is best for Oracle. In large databases, individual object settings can make a huge difference in performance and storage.


The issue of PCTFREE

The PCTFREE parameter is used to specify the amount of free space on a data block to reserve for future row expansion. If PCTFREE is set improperly, SQL update statements can cause a huge amount of row fragmentation and chaining.

The setting for PCTFREE is especially important where a row is initially stored small and expanded at a later time. In such systems, it is not uncommon to set PCTFREE equal to 95, telling Oracle to reserve 95 percent of the data block space for subsequent row expansion.

The issue of PCTUSED

Improper settings for PCTUSED (e.g., set too small) can cause huge degradations in the performance of SQL insert statements. If a data block is not largely empty, excessive I/O will happen during SQL inserts because the reused Oracle data blocks will become full quickly. Taken to the extreme, improper settings for PCTUSED can create a situation where the free space on the data block is smaller than the average row length for the table. In these cases, Oracle will try five times to fetch a block from the freelist chain. After five attempts, Oracle will raise the high-water mark for the table and grab five fresh data blocks for the insert.

In Oracle with ASSM, the PCTUSED parameter no longer governs the relink threshold for a table data block, and you must rely on the judgment of Oracle to determine when a block is empty enough to be placed onto the freelist.

While Oracle ignores the PCTUSED, FREELISTS, and FREELIST GROUPS parameters with locally managed tablespaces and ASSM, Oracle does not give an error message when they are used in a table definition:

SQL> create table
2 test_table
3 (c1 number)
4 tablespace
5 asm_test
6 pctfree 20 pctused 30
7 storage
8 ( freelists 23 next 5m ) ;
Table created.



This could be a serious issue unless you remember that locally managed tablespaces with ASSM ignore any specified values for PCTUSED, NEXT, and FREELISTS.

One huge benefit of ASSM is that bitmap freelists are guaranteed to reduce buffer busy waits, which were a serious issue prior to Oracle9i. Let?s take a close look at this feature.

No more buffer busy waits

Without multiple freelists, every Oracle table and index had a single data block at the head of the table to manage free blocks for the object and provide data blocks for new rows created by any SQL insert statements. A buffer busy wait occurs when a data block is inside the data buffer cache but is unavailable because it is locked by another DML transaction. When you want to insert multiple tasks into the same table, the tasks are forced to wait while Oracle assigned free blocks, one at a time.

With ASSM, Oracle claims to improve the performance of concurrent DML operations significantly since different parts of the bitmap can be used simultaneously, eliminating serialization for free space lookups. According to Oracle benchmarks, using bitmap freelists removes all segment header contention and allows for super-fast concurrent insert operations (Figure A).


Oracle corporation benchmark on SQL insert speed with bitmap freelists

.
 


 

 
��  
 
 
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