Re: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB
It would appear we're looking into yet another hit ratio, namely the ASS Hit Ratio. Used to be rather high in my younger days. Mogens Jonathan Lewis wrote: Depending on your circumstances, ASS Management can eliminate severe contention on the freelists / freelist groups area. However, because Oracle is overgenerous with its allocation of bitmap blocks (which may turn out to be in excess of 1% of your database), you may end up thrashing your system because most of your buffer space is flooded with hot BMBs and the data has to keep thrashing on and off disk. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 19 December 2002 17:51 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
Fwd: RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB
Ian, Could you explain how ASSM could be detrimental in a single instance environment ? Vivek, Could you provide details on the issues [BugNos, description of the problem, your findings] you had with ASSM in 9iRAC ? Hemant At 07:09 AM 19-12-02 -0800, you wrote: 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 [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 15, 2002 8:09 AM To: Multiple recipients of list ORACLE-L Subject: RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB quotes from Oracle Manuals deleted 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 quote from 9.0.1 manual deleted 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 [EMAIL PROTECTED] -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 Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB
Title: RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB in our 9202 RAC we have everything except SYSTEM,UNDO in AUTO and have NO problems at all. We have 7 such instances (all RAC, all 9202 except for on which is 9012). 4 Instances use CLOBs and intermedia extensively, one of these uses XML CLOB combination never faced any problems. Vivek, what problem you faced (or still facing), are those bugs? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: VIVEK_SHARMA [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 10:09 AM To: Multiple recipients of list ORACLE-L Subject: RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB 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 *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB
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-From: VIVEK_SHARMA [mailto:[EMAIL PROTECTED]]Sent: Thursday, December 19, 2002 10:09 AMTo: Multiple recipients of list ORACLE-LSubject: RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB 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 [mailto:[EMAIL PROTECTED]]Sent: Sunday, December 15, 2002 8:09 AMTo: Multiple recipients of list ORACLE-LSubject: RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOBIn 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 LOBsfor Segment_Space_Management AUTO. [here, too, there's a lineindicating Segment_Space_Management AUTO is preferred for RACenvironments]Specifying Segment Space Management in Locally Managed TablespacesWhen 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 --- definitelyworks in 9.2.0.2Why 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. " ?HemantAt 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 restrictionsRestrictions on AUTO: * You can specify this clause only for permanent, locally managed tablespace
Re: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB
Depending on your circumstances, ASS Management can eliminate severe contention on the freelists / freelist groups area. However, because Oracle is overgenerous with its allocation of bitmap blocks (which may turn out to be in excess of 1% of your database), you may end up thrashing your system because most of your buffer space is flooded with hot BMBs and the data has to keep thrashing on and off disk. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 19 December 2002 17:51 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB
Title: RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB ASS Management? I'm snickering, I know it's juvenile humor, but... LOL! Let me tell you, my ASS is not on the Free List. And thrashing? I'll leave that one alone! Sorry everyone. Couldn't resist. Is it Friday yet... -Original Message- From: Jonathan Lewis [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 1:56 PM To: Multiple recipients of list ORACLE-L Subject: Re: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB Depending on your circumstances, ASS Management can eliminate severe contention on the freelists / freelist groups area. However, because Oracle is overgenerous with its allocation of bitmap blocks (which may turn out to be in excess of 1% of your database), you may end up thrashing your system because most of your buffer space is flooded with hot BMBs and the data has to keep thrashing on and off disk. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 19 December 2002 17:51 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB
Title: RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB :-0 Our ASS managment does a lot of thrashing lately... It's close enuf to Friday and a 2 week vacation as well. -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Thursday, December 19, 2002 2:30 PMTo: Multiple recipients of list ORACLE-LSubject: RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB ASS Management? I'm snickering, I know it's juvenile humor, but... LOL! Let me tell you, my ASS is not on the Free List. And thrashing? I'll leave that one alone! Sorry everyone. Couldn't resist. Is it Friday yet... -Original Message- From: Jonathan Lewis [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 1:56 PM To: Multiple recipients of list ORACLE-L Subject: Re: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB Depending on your circumstances, ASS Management can eliminate severe contention on the freelists / freelist groups area. However, because Oracle is overgenerous with its allocation of bitmap blocks (which may turn out to be in excess of 1% of your database), you may end up thrashing your system because most of your buffer space is flooded with hot BMBs and the data has to keep thrashing on and off disk. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 19 December 2002 17:51 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB
Title: RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB Does this 'Ass Management' apply to duhvelopers as much as it applies to tablespaces ?? Thrashing will be more useful. ps: Before you say something I have been a developer for about 9 years (and still consider my self a part developer) Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message-From: Loughmiller, Greg [mailto:[EMAIL PROTECTED]]Sent: Thursday, December 19, 2002 3:08 PMTo: Multiple recipients of list ORACLE-LSubject: RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB :-0 Our ASS managment does a lot of thrashing lately... It's close enuf to Friday and a 2 week vacation as well. -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Thursday, December 19, 2002 2:30 PMTo: Multiple recipients of list ORACLE-LSubject: RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB ASS Management? I'm snickering, I know it's juvenile humor, but... LOL! Let me tell you, my ASS is not on the Free List. And thrashing? I'll leave that one alone! Sorry everyone. Couldn't resist. Is it Friday yet... -Original Message- From: Jonathan Lewis [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 1:56 PM To: Multiple recipients of list ORACLE-L Subject: Re: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB Depending on your circumstances, ASS Management can eliminate severe contention on the freelists / freelist groups area. However, because Oracle is overgenerous with its allocation of bitmap blocks (which may turn out to be in excess of 1% of your database), you may end up thrashing your system because most of your buffer space is flooded with hot BMBs and the data has to keep thrashing on and off disk. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 19 December 2002 17:51 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB
Title: RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB Oh the good old days when at the state government they decided to call us Bureau of Application Development And System Software Yep, until the initials became apparent.. I think it lasted about 2-4 weeks, and they changed it. Maks. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Loughmiller, GregSent: Thursday, December 19, 2002 3:08 PMTo: Multiple recipients of list ORACLE-LSubject: RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB :-0 Our ASS managment does a lot of thrashing lately... It's close enuf to Friday and a 2 week vacation as well. -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Thursday, December 19, 2002 2:30 PMTo: Multiple recipients of list ORACLE-LSubject: RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB ASS Management? I'm snickering, I know it's juvenile humor, but... LOL! Let me tell you, my ASS is not on the Free List. And thrashing? I'll leave that one alone! Sorry everyone. Couldn't resist. Is it Friday yet... -Original Message- From: Jonathan Lewis [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 1:56 PM To: Multiple recipients of list ORACLE-L Subject: Re: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB Depending on your circumstances, ASS Management can eliminate severe contention on the freelists / freelist groups area. However, because Oracle is overgenerous with its allocation of bitmap blocks (which may turn out to be in excess of 1% of your database), you may end up thrashing your system because most of your buffer space is flooded with hot BMBs and the data has to keep thrashing on and off disk. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 19 December 2002 17:51 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB Segments
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. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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 -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: 9i / 9iRAC : Segment_Space_Management AUTO, with LOB
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: * 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. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -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
