Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB

RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB

From: Loughmiller, Greg <Greg.Loughmiller_at_cingular.com>
Date: Thu, 19 Dec 2002 08:34:32 -0800
Message-ID: <F001.0051E63B.20021219083432@fatcity.com>


We were the other way around in our testing lately:-)  

We turned on auto space management to remove the contention. Afterwards-we removed quite a bit of header block/free list contention..  

anyway, more tests to follow

-----Original Message-----
Sent: Thursday, December 19, 2002 10:09 AM To: Multiple recipients of list ORACLE-L  

As part of a RAC benchmark with 9.2 we had faced severe LOCKING on setting segment space management AUTO & had to REMOVE it  

HTH -----Original Message-----
Sent: Sunday, December 15, 2002 8:09 AM
To: Multiple recipients of list ORACLE-L

In the 9i SQL Reference Guide Release 2 9.2, under "CREATE TABLESPACE", "segment_management_clause"

Restrictions on the AUTO clause:
********You can specify this clause only for permanent, locally managed
tablespace.
********You cannot specify this clause for the SYSTEM tablespace.

In the 9i Application Developer's Guide - Large Objects (LOBs) Release 2 9.2,
the chapter "What's New with Large Objects (LOBs) ?", under "LOB Features Introduced with Oracle 9i, Release 2 (9.2)" , under "Removed Restrictions" :

Locally managed tablespaces restriction removed

You can now create LOB columns in locally managed tablespaces.

Also, in the 9iRelease2 9.2.0 Administrator's Guide, Chapter 11 "Managing Tablespaces", there is no restriction on LOBs for Segment_Space_Management AUTO. [here, too, there's a line indicating Segment_Space_Management AUTO is preferred for RAC environments]

Specifying Segment Space Management in Locally Managed Tablespaces

When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause allows you to specify how free and used space within a segment is to be managed. Your choices are:
********MANUAL
Specifying MANUAL tells Oracle that you want to use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows. This form of managing space within segments is called manual segment-space management because of the need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace.

MANUAL is the default.

********AUTO
This keyword tells Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.

Automatic segment-space management is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace. If such attributes should be specified, they are ignored.

Automatic segment-space management delivers better space utilization than manual segment-space management, and it is self tuning in that it scales with increasing the number of users, as well as instances. For a Real Application Clusters environment, automatic segment-space management allows for a dynamic affinity of space to instances, thus avoiding the hard partitioning of space inherent with using free list groups.

For many standard workloads, application performance when using automatic segment space management is better than the performance of a well tuned application using manual segment-space management.

Bottomline : Either it was a bug or a restriction, it was not possible in 9.0.1 and supposed to be possible in 9.2.0 --- definitely works in 9.2.0.2

Why do you say "In a single instance environment unless you are doing something out of the ordinary, use of the clause will probably be detrimental. " ?

Hemant

At 03:03 PM 14-12-02 -0800, you wrote:

Was the prohibitition on placing LOB's into tablespaces with automatic segment space management only a bug. I ask because the description of "auto" segment space management includes the following restrictions

Restrictions on AUTO:

One usually associates a bug with something the manual says one can do, but one cannot. I've not seen the 9.0.2 manual. Are the restrictions lifted?

In a single instance environment unless you are doing something out of the ordinary, use of the clause will probably be detrimental.

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu  

-----Original Message-----
Sent: Friday, December 13, 2002 7:19 PM
To: Multiple recipients of list ORACLE-L

The first time we tried importing our 8.1.5 [OPS] database into 9.0.1 and 9.2.0.1 with
EXTENT_MANAGEMENT Local for ALL tablespaces SEGMENT_SPACE_MANAGEMENT Auto
we found that tables with LOB segments could not be created. This was a 9.0.1 bug which was expected to have been fixed in 9.2 [I believe it was Bug 1626182, referenced in Note 159078.1]

However, after going to 9.2.0.2, I WAS able to create such tables in the database.
[the Patchset Notes include a reference to Bug 2326066. Unfortunately, I can't see either bug on MetaLink to verify if both are the same issue]

9iRAC strongly recommends SEGMENT_SPACE_MANAGEMENT Auto instead of using Free_Lists and Free_List_Groups. [Part 3, Appendix B of the 9iRAC Deployment and Performance manual].

Are there any real-life experiences / gotchas with LOB Segments in Locally Managed Tablespaces and Segment_Space_Management Auto ? There seem to be a number of bug-fixes for Segment_Space_Management=AUTO in the 9.2.0.2 PatchSet Release Notes.

Hemant K Chitale
My web site page is : http://hkchital.tripod.com <http://hkchital.tripod.com/>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
<http://www.orafaq.com/> 
-- 
Author: Hemant K Chitale
  INET: hkchital_at_singnet.com.sg

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
<http://www.fatcity.com/> 
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from).  You may also send the HELP
command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
<http://www.orafaq.com/> 
-- 
Author: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
<http://www.fatcity.com/> 
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Hemant K Chitale
My web site page is :  http://hkchital.tripod.com
<http://hkchital.tripod.com/> 

-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author:
Hemant K Chitale INET: hkchital_at_singnet.com.sg Fat City Network Services --
858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list
and web hosting services
--------------------------------------------------------------------- To
REMOVE yourself from this mailing list, send an E-Mail message to:
ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message
BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list
you want to be removed from). You may also send the HELP command for other
information (like subscribing). 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Loughmiller, Greg
  INET: Greg.Loughmiller_at_cingular.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Dec 19 2002 - 10:34:32 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US