Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jan 30, 2003

Oracle9i's Auto Segment Space Management Option

By James Koopmann

Oracle has done it again. Venture with me down what seems like a small option but in fact has major implications on what we, as DBAs no longer have to manage.

The world of database performance and tuning is changing very fast. Every time I look at new features, it convinces me more and more that databases are becoming auto-tunable and self-healing. We could argue for quite awhile that DBAs will or will not become obsolete in the future, but I think our current nitch is the acceptance of new technology and our ability to empower the companies we work for by using it. With Oracle9i, Oracle has given a peek into the future of where it is going when tuning, not only the database but applications as well. The little gem that Oracle has snuck in is Its' new automated segment space management option.

What Is It

If you haven't read the manuals yet, please do. You will quickly realize that Oracle is pushing us to use locally managed tablespaces. Because all the information to manage segments and blocks is kept in bitmaps in locally managed tablespaces, the access to the data dictionary is relieved. Not only does this not generate redo, contention is reduce. Along with the push to locally managed tablespaces, is the push to use automatic segment space management. This option takes total control of the parameters FREELISTS, FREELIST GROUPS, and PCTUSED. That means that Oracle will track and manage the used and free space in datablocks using bitmaps for all objects defined in the tablespace for which it has been defined.

How It Use to Be

In the olden days, everything was dictionary-managed tablespaces. How objects were being used within tablespaces made setting FREELIST, FREELIST GROUPS, and PCTUSED an ordeal. Typically, you would sit down and look at the type of DML that was going to be executed, the number of users executing the DML, the size of rows in tables, and how the data would grow over time. You would then come up with an idea of how to set FREELIST, PCTUSED, and PCTFREE in order to get the best usage of space when weighed against performance of DML. If you didn't know what you were doing or even if you did, you constantly had to monitor contention and space to verify and plan your next attempt. Let's spend a bit of time getting accustomed to these parameters.

FREELIST

This is a list of blocks kept in the segment header that may be used for new rows being inserted into a table. When an insert is being done, Oracle gets the next block on the freelist and uses it for the insert. When multiple inserts are requested from multiple processes, there is the potential for a high level of contention since the multiple processes will be getting the same block from the freelist, until it is full, and inserting into it. Depending on how much contention you can live with, you need to determine how many freelists you need so that the multiple processes can access their own freelist.

PCTUSED

This is a storage parameter that states when a certain percentage of a block begin used falls below PCTUSED, that block should be placed back on the freelist for available inserts. The issue with using a value for PCTUSED was that you had to balance the need for performance, a low PCTUSED to keep blocks off the freelist, against a high PCTUSED to keep space usage under control.

FREELIST GROUPS

Basically used for multiple instances to access an object. This setting can also be used to move the freelists to other blocks beside the segment header and thus give some relief to segment header contention.

Why Is Auto Segment Space Management Good

I have come up with a short list of reasons why you might want to switch to auto segment space management. I truly think you can find something that you will like.

  • No worries
  • No wasted time searching for problems that don't exist.
  • No planning needed for storage parameters
  • Out of the box performance for created objects
  • No need to monitor levels of insert/update/delete rates
  • Improvement in space utilization
  • Better performance than most can tune or plan for with concurrent access to objects
  • Avoidance of data fragmentation
  • Minimal data dictionary access
  • Better indicator of the state of a data block
  • Further more, the method that Oracle uses to keep track of the availability of free space in a block is much more granular than the singular nature of the old, on the freelist or off the freelist scenario.

Create a Tablespace for Auto Segment Space Management

Creating a tablespace for Auto Segment Space Management is quite simple. Include the statement at the end of the CREATE TABLESPACE statement. Here is an example.

CREATE TABLESPACE no_space_worries_ts 
                DATAFILE '/oradata/mysid/datafiles/nospaceworries01.dbf' SIZE 100M
                EXTENT MANAGEMENT LOCAL 
                SEGMENT SPACE MANAGEMENT AUTO;

The AUTO keyword tells Oracle to use bitmaps for managing space for segments.

Check What You Have Defined

To determine your current tablespace definition, query the data dictionary.

select tablespace_name,
          contents,
          extent_management,
          allocation_type,
          segment_space_management 
  from dba_tablespaces;

How Do You Switch To Auto Segment Space Management

Realize that you can't change the method of segment space management by an ALTER statement. You must create a new permanent, locally managed tablespace and state auto segment space management and then migrate the objects.

Optional Procedures

Oracle has a package called DBMS_REPAIR that contains a procedure called SEGMENT_FIX_STATUS that will allow you to fix corruption of the bitmap states. This procedure will recalculate the bitmap states based on either block contents or a specified value.

The package DBMS_SPACE contains a procedure called SPACE_USAGE that gives information about how space is being used within blocks under the segment high water mark.

Let Oracle Take Over

Maybe it's my old age or years of doing the mundane tasks as a DBA that wants to embrace this feature. If there is one thing I have learned from using Oracle databases, it's that Oracle has gotten a ton better at making sure new features work and are geared at truly making database performance better. Here is just one instance where I think we can embrace Oracles' attempt to take over a mundane task that is has been prone to error in the wrong hands. After all, it isn't rocket science when you get down to it and will probably be gone in the next release anyway.

» See All Articles by Columnist James Koopmann



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM