Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id gBJJua325754 for ; Thu, 19 Dec 2002 13:56:36 -0600 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id gBJJua325747 for ; Thu, 19 Dec 2002 13:56:36 -0600 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id IAA77731; Thu, 19 Dec 2002 08:39:17 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 0051E30B; Thu, 19 Dec 2002 07:09:20 -0800 Message-ID: Date: Thu, 19 Dec 2002 07:09:20 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "VIVEK_SHARMA" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "VIVEK_SHARMA" Subject: RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPartTM-000-413ddfc0-da26-4fdd-bd2f-e516f5856850" ------=_NextPartTM-000-413ddfc0-da26-4fdd-bd2f-e516f5856850 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C2A770.3900602B" ------_=_NextPart_001_01C2A770.3900602B Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable =20 As part of a RAC benchmark with 9.2 we had faced severe LOCKING=20 on setting segment space management AUTO & had to REMOVE it=20 =20 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.=20 ********You cannot specify this clause for the SYSTEM tablespace.=20 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=20 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=20 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.=20 ********AUTO=20 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: * You can specify this clause only for permanent, locally managed = tablespace. * You cannot specify this clause for the SYSTEM tablespace. * You cannot store LOBs in AUTO segment-managed tablespaces. -------------------------------------------------------------------------= ----------------------------------------------- The above is from the 9.0.1 SQL manual. 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. =20 Ian MacGregor Stanford Linear Accelerator Center ian@slac.stanford.edu =20 -----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=3DAUTO in = the 9.2.0.2 PatchSet Release Notes. Hemant K Chitale My web site page is : http://hkchital.tripod.com = =20 --=20 Please see the official ORACLE-L FAQ: http://www.orafaq.com = =20 --=20 Author: Hemant K Chitale INET: hkchital@singnet.com.sg Fat City Network Services -- 858-538-5051 http://www.fatcity.com = =20 San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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). --=20 Please see the official ORACLE-L FAQ: http://www.orafaq.com = =20 --=20 Author: MacGregor, Ian A. INET: ian@SLAC.Stanford.EDU Fat City Network Services -- 858-538-5051 http://www.fatcity.com = =20 San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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 = =20 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- = Author: Hemant K Chitale INET: hkchital@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@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).=20 ------_=_NextPart_001_01C2A770.3900602B Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
 
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-----
From: Hemant K Chitale=20 [mailto:hkchital@singnet.com.sg]
Sent: Sunday, December 15, = 2002=20 8:09 AM
To: Multiple recipients of list = ORACLE-L
Subject:=20 RE: 9i / 9iRAC : Segment_Space_Management AUTO, with=20 LOB


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

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



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

Locally managed=20 tablespaces restriction removed

You can now create LOB = columns in=20 locally managed tablespaces.



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


Specifying = Segment=20 Space Management in Locally Managed Tablespaces

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

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

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

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

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

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

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

Hemant

At = 03:03=20 PM 14-12-02 -0800, you wrote:
Was the prohibitition on = placing=20 LOB's into tablespaces with automatic segment space management only = a=20 bug.  I ask because  the description of  "auto" = segment space=20 management includes the following restrictions

Restrictions = on=20 AUTO:

    * You can specify this clause only = for=20 permanent, locally managed tablespace.

    * = You=20 cannot specify this clause for the SYSTEM tablespace.

* You = cannot=20 store LOBs in AUTO segment-managed=20 = tablespaces.
---------------------------------------------------------= ---------------------------------------------------------------
The=20 above is from the 9.0.1 SQL manual.

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

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

Ian=20 MacGregor
Stanford Linear Accelerator=20 Center
ian@slac.stanford.edu

 

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




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

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

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

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


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


-- =
Please see=20 the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: = Hemant K=20 Chitale
  INET: hkchital@singnet.com.sg

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

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

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

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

------_=_NextPart_001_01C2A770.3900602B-- ------=_NextPartTM-000-413ddfc0-da26-4fdd-bd2f-e516f5856850-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: VIVEK_SHARMA@infosys.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@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).