Re: Re: Little competition
Hi Jonathan, SQL create tablespace bowie_test 2 datafile 'c:/bowie/bowie_test01.dbf' size 100m 3 extent management local uniform size 1m 4 segment space management auto; Tablespace created. SQL create table bowie_assm (ziggy number) 2 tablespace bowie_test 3 storage (initial 1m next 2m pctincrease 100 minextents 3); Table created. SQL select owner, segment_name, blocks from dba_extents 2 where segment_name = 'BOWIE_ASSM'; OWNER SEGMENT_NAME BLOCKS -- -- BOWIE BOWIE_ASSM 128 BOWIE BOWIE_ASSM 128 BOWIE BOWIE_ASSM 128 BOWIE BOWIE_ASSM 128 BOWIE BOWIE_ASSM 128 BOWIE BOWIE_ASSM 128 BOWIE BOWIE_ASSM 128 7 rows selected. 3 minextents = 1 initial + 2 next + (2 next + (2 * 1.0 pctincrease) = 7 * 1M extents It's actually quite a common misconception that NEXT, PCTINCREASE and MINEXTENTS are ignored for locally managed tablespaces when in fact they're used to determine the initial size of the object and hence the number of extents initially allocated. This was all a bit of fun but I think it did prove my little (mischievous) point. That it's really quite easy to base ones belief and certainty on a fact that turns out to be totally false because the basis on why you believe something also turns out to be false. On the surface it appeared to be quite a reasonable conclusion, that pctfree is not permitted with ASSM objects because the evidence strongly supported such a claim. Unfortunately the evidence was somewhat erroneous in that it stupidly relied on incorrect syntax and so an incorrect conclusion resulted. This incorrect conclusion can then result in inappropriate behaviour and curses from DBAs as they experience all these unavoidable migrated rows. Before you know it, other Oracle myth is born ... Of course everyone makes mistakes but to publish them does come with it's own set of responsibilities. I can't stress enough that one be careful of what you read and be careful of who you read. The truth IS out there ;) Cheers Richard quote This could be a serious issue for the Oracle professional unless they remember that locally-managed tablespaces with automatic space management ignore any specified values for NEXT and FREELISTS. end quote There is another error here. For a bonus 10 points can anyone spot it ? Hint - try the following in a tablespace which is locallally managed, with automatic space management, and either system managed or uniform sized extents of no more than 1 M. create table test2(n1 number) storage (initial 1M next 2M pctincrease 100 minextents 3); Regards Jonathan Lewis -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: Re: Little competition
Hey Richard, One of my desktop treasures is an autographed copy of Jonathan Lewis' book, Practical Oracle8i. The humorous, self-deprecating caption in the author's own hand: 'Never believe all you read.' Scott [EMAIL PROTECTED] 12/12/03 7:24:24 AM Hi Jonathan,SQL create tablespace bowie_test 2 datafile 'c:/bowie/bowie_test01.dbf' size 100m 3 extent management local uniform size 1m 4 segment space management auto;Tablespace created.SQL create table bowie_assm (ziggy number) 2 tablespace bowie_test 3 storage (initial 1m next 2m pctincrease 100 minextents 3);Table created.SQL select owner, segment_name, blocks from dba_extents 2 where segment_name = 'BOWIE_ASSM';OWNER SEGMENT_NAME BLOCKS-- --BOWIE BOWIE_ASSM 128BOWIE BOWIE_ASSM 128BOWIE BOWIE_ASSM 128BOWIE BOWIE_ASSM 128BOWIE BOWIE_ASSM 128BOWIE BOWIE_ASSM 128BOWIE BOWIE_ASSM 1287 rows selected.3 minextents = 1 initial + 2 next + (2 next + (2 * 1.0 pctincrease) = 7 * 1MextentsIt's actually quite a common misconception that NEXT, PCTINCREASE andMINEXTENTS are ignored for locally managed tablespaces when in fact they'reused to determine the initial size of the object and hence the number ofextents initially allocated.This was all a bit of fun but I think it did prove my little (mischievous)point. That it's really quite easy to base ones belief and certainty on a"fact" that turns out to be totally false because the basis on why youbelieve something also turns out to be false. On the surface it appeared tobe quite a reasonable conclusion, that pctfree is not permitted with ASSMobjects because the "evidence" strongly supported such a claim.Unfortunately the evidence was somewhat erroneous in that it stupidly reliedon incorrect syntax and so an incorrect conclusion resulted. This incorrectconclusion can then result in inappropriate behaviour and curses from DBAsas they experience all these "unavoidable" migrated rows. Before you knowit, other Oracle myth is born ...Of course everyone makes mistakes but to publish them does come with it'sown set of responsibilities. I can't stress enough that one be careful of"what" you read and be careful of "who" you read.The truth IS out there ;)CheersRichard quote This could be a serious issue for the Oracle professional unless they remember that locally-managed tablespaces with automatic space management ignore any specified values for NEXT and FREELISTS. end quote There is another error here. For a bonus 10 points can anyone spot it ? Hint - try the following in a tablespace which is locallally managed, with automatic space management, and either system managed or uniform sized extents of no more than 1 M. create table test2(n1 number) storage (initial 1M next 2M pctincrease 100 minextents 3); Regards Jonathan Lewis-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Richard Foote INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: Little competition
Well, Googling this statement, one can easily find the source of this statement :-) Naveen -Original Message-From: Richard Foote [mailto:[EMAIL PROTECTED]Sent: Thursday, December 11, 2003 5:09 PMTo: Multiple recipients of list ORACLE-LSubject: Little competition Little competition for you all :) It's a two part question: What's wrong with the followingpiece of expert analysis? Which well know "Oracle Guru" published this (and continues to display it on his web-page) ? "Sadly, Oracle9i doesn't allow you to specify the value for PCTFREE if you're using automatic space management. This is a serious limitation because Oracle9i can't know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance." SQL create table 2 test_table 3 (c1 number) 4 tablespace 5 asm_test 6 storage 7 ( pctfree 20 pctused 30 ) 8 ; ( pctfree 20 pctused 30 ) *ERROR at line 7:ORA-02143: invalid STORAGE optionHowever, here's an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings" You've gotta love it !! Sorry no clues Cheers ;) Richard
RE: Little competition
Title: Message Don King SQL create table 2 test_table 3 (c1 number) 4 tablespace 5 asm_test 6 pctfree 20 pctused 30; -Original Message-From: Richard Foote [mailto:[EMAIL PROTECTED] Sent: Thursday, December 11, 2003 1:39 PMTo: Multiple recipients of list ORACLE-LSubject: Little competition Little competition for you all :) It's a two part question: What's wrong with the followingpiece of expert analysis? Which well know "Oracle Guru" published this (and continues to display it on his web-page) ? "Sadly, Oracle9i doesnt allow you to specify the value for PCTFREE if youre using automatic space management. This is a serious limitation because Oracle9i cant know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance." SQL create table 2 test_table 3 (c1 number) 4 tablespace 5 asm_test 6 storage 7 ( pctfree 20 pctused 30 ) 8 ; ( pctfree 20 pctused 30 ) *ERROR at line 7:ORA-02143: invalid STORAGE optionHowever, heres an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings" You've gotta love it !! Sorry no clues Cheers ;) Richard
Re: Little competition
Well, I guess it's all about self-tuning, all this parameter stuff is out of date... :) Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - Little competition for you all :) It's a two part question: a.. What's wrong with the following piece of expert analysis ? b.. Which well know Oracle Guru published this (and continues to display it on his web-page) ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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: Little competition
Life would be worse w/o PCT_FREE. create table test_table (c1 varchar2(1000)) pctfree 20 ; select pct_free from user_tables where table_name='TEST_TABLE' PCT_FREE -- 20 rw Little competition for you all :) It's a two part question: What's wrong with the following piece of expert analysis ? Which well know Oracle Guru published this (and continues to display it on his web-page) ? Sadly, Oracle9i doesn't allow you to specify the value for PCTFREE if you're using automatic space management. This is a serious limitation because Oracle9i can't know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance. create table 2 test_table 3 (c1 number) 4 tablespace 5 asm_test 6 storage 7 ( pctfree 20 pctused 30 ) 8 ; ( pctfree 20 pctused 30 ) * ERROR at line 7: ORA-02143: invalid STORAGE option However, here's an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings You've gotta love it !! Sorry no clues Cheers ;) Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wartiak Rastislav 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: Little competition
i meant automatic segment management has fragmentation issues. From: Richard Foote [EMAIL PROTECTED] Date: 2003/12/11 Thu AM 06:39:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Little competition Little competition for you all :) It's a two part question: a.. What's wrong with the following piece of expert analysis ? b.. Which well know Oracle Guru published this (and continues to display it on his web-page) ? Sadly, Oracle9i doesn't allow you to specify the value for PCTFREE if you're using automatic space management. This is a serious limitation because Oracle9i can't know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance. SQL create table 2 test_table 3 (c1 number) 4 tablespace 5 asm_test 6 storage 7 ( pctfree 20 pctused 30 ) 8 ; ( pctfree 20 pctused 30 ) * ERROR at line 7: ORA-02143: invalid STORAGE option However, here's an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings You've gotta love it !! Sorry no clues Cheers ;) Richard Little competition for you all :) It's a two part question: What's wrong with the followingpiece of expert analysis? Which well know "Oracle Guru" published this (and continues to display it on his web-page) ? "Sadly, Oracle9i doesnt allow you to specify the value for PCTFREE if youre using automatic space management. This is a serious limitation because Oracle9i cant know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance." SQL create table 2 test_table 3 (c1 number) 4 tablespace 5 asm_test 6 storage 7 ( pctfree 20 pctused 30 ) 8 ; ( pctfree 20 pctused 30 ) *ERROR at line 7:ORA-02143: invalid STORAGE optionHowever, heres an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings" You've gotta love it !! Sorry no clues Cheers ;) Richard
Re: Little competition
who wrote that? automatic undo management has fragmentation issues. Niall Litchfield posted a test case a couple of months ago. From: Richard Foote [EMAIL PROTECTED] Date: 2003/12/11 Thu AM 06:39:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Little competition Little competition for you all :) It's a two part question: a.. What's wrong with the following piece of expert analysis ? b.. Which well know Oracle Guru published this (and continues to display it on his web-page) ? Sadly, Oracle9i doesn't allow you to specify the value for PCTFREE if you're using automatic space management. This is a serious limitation because Oracle9i can't know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance. SQL create table 2 test_table 3 (c1 number) 4 tablespace 5 asm_test 6 storage 7 ( pctfree 20 pctused 30 ) 8 ; ( pctfree 20 pctused 30 ) * ERROR at line 7: ORA-02143: invalid STORAGE option However, here's an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings You've gotta love it !! Sorry no clues Cheers ;) Richard Little competition for you all :) It's a two part question: What's wrong with the followingpiece of expert analysis? Which well know "Oracle Guru" published this (and continues to display it on his web-page) ? "Sadly, Oracle9i doesnt allow you to specify the value for PCTFREE if youre using automatic space management. This is a serious limitation because Oracle9i cant know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance." SQL create table 2 test_table 3 (c1 number) 4 tablespace 5 asm_test 6 storage 7 ( pctfree 20 pctused 30 ) 8 ; ( pctfree 20 pctused 30 ) *ERROR at line 7:ORA-02143: invalid STORAGE optionHowever, heres an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings" You've gotta love it !! Sorry no clues Cheers ;) Richard
Re: Little competition
hopefully i wont sound like a complete idiot, but what is wrong with wanting to be able to handle your own pctfree and pctused. Ok oracle handles the next and initial extent sizes...(which causes fragmentation). I use transportable tablespaces and in order to increase the time it takes to copy these datafiles, I use pctused 99 and pctfree 1 in order to compact the tables. I can cut 45 minutes off my load times by shrinking the data file? From: Richard Foote [EMAIL PROTECTED] Date: 2003/12/11 Thu AM 06:39:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Little competition Little competition for you all :) It's a two part question: a.. What's wrong with the following piece of expert analysis ? b.. Which well know Oracle Guru published this (and continues to display it on his web-page) ? Sadly, Oracle9i doesn't allow you to specify the value for PCTFREE if you're using automatic space management. This is a serious limitation because Oracle9i can't know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance. SQL create table 2 test_table 3 (c1 number) 4 tablespace 5 asm_test 6 storage 7 ( pctfree 20 pctused 30 ) 8 ; ( pctfree 20 pctused 30 ) * ERROR at line 7: ORA-02143: invalid STORAGE option However, here's an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings You've gotta love it !! Sorry no clues Cheers ;) Richard Little competition for you all :) It's a two part question: What's wrong with the followingpiece of expert analysis? Which well know "Oracle Guru" published this (and continues to display it on his web-page) ? "Sadly, Oracle9i doesnt allow you to specify the value for PCTFREE if youre using automatic space management. This is a serious limitation because Oracle9i cant know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance." SQL create table 2 test_table 3 (c1 number) 4 tablespace 5 asm_test 6 storage 7 ( pctfree 20 pctused 30 ) 8 ; ( pctfree 20 pctused 30 ) *ERROR at line 7:ORA-02143: invalid STORAGE optionHowever, heres an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings" You've gotta love it !! Sorry no clues Cheers ;) Richard
RE: Little competition
Richard, Don't know about the "guru", but sorry to hear that David Bowie is sick with Influenza. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Richard Foote [mailto:[EMAIL PROTECTED]Sent: Thursday, December 11, 2003 6:39 AMTo: Multiple recipients of list ORACLE-LSubject: Little competition Little competition for you all :) **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.**5
Re: Little competition
Title: Re: Little competition I can cut 45 minutes off my load times by shrinking the data file? And how much overhead gets added to DML statements as blocks madly shift on and off the freelists with each operation? Priorities, priorities, priorities... If youre using 9i or above, the table COMPRESS feature might be a more effective mechanism? on 12/11/03 5:44 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: hopefully i wont sound like a complete idiot, but what is wrong with wanting to be able to handle your own pctfree and pctused. Ok oracle handles the next and initial extent sizes...(which causes fragmentation). I use transportable tablespaces and in order to increase the time it takes to copy these datafiles, I use pctused 99 and pctfree 1 in order to compact the tables. I can cut 45 minutes off my load times by shrinking the data file? From: Richard Foote [EMAIL PROTECTED] Date: 2003/12/11 Thu AM 06:39:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Little competition Little competition for you all :) It's a two part question: a.. What's wrong with the following piece of expert analysis ? b.. Which well know Oracle Guru published this (and continues to display it on his web-page) ? Sadly, Oracle9i doesn't allow you to specify the value for PCTFREE if you're using automatic space management. This is a serious limitation because Oracle9i can't know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance. SQL create table 2 test_table 3 (c1 number) 4 tablespace 5 asm_test 6 storage 7 ( pctfree 20 pctused 30 ) 8 ; ( pctfree 20 pctused 30 ) * ERROR at line 7: ORA-02143: invalid STORAGE option However, here's an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings You've gotta love it !! Sorry no clues Cheers ;) Richard Little competition for you all :) It's a two part question: What's wrong with the following piece of expert analysis ? Which well know Oracle Guru published this (and continues to display it on his web-page) ? Sadly, Oracle9i doesnt allow you to specify the value for PCTFREE if youre using automatic space management. This is a serious limitation because Oracle9i cant know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance. SQL create table 2 test_table 3 (c1 number) 4 tablespace 5 asm_test 6 storage 7 ( pctfree 20 pctused 30 ) 8 ; ( pctfree 20 pctused 30 ) * ERROR at line 7: ORA-02143: invalid STORAGE option However, heres an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings You've gotta love it !! Sorry no clues Cheers ;) Richard
Antw: RE: Little competition
Hi Raj, David Bowie? Didn't he publish something regarding 'space' management a long time ago? ;-) I may (as usual) be totally wrong here. Greetings, Guido [EMAIL PROTECTED] 11.12.2003 13.49 Uhr Richard, Don't know about the guru, but sorry to hear that David Bowie is sick with Influenza. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, December 11, 2003 6:39 AM To: Multiple recipients of list ORACLE-L Little competition for you all :) ** 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. **5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guido Konsolke 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: Antw: RE: Little competition
No, you are not but I think Richard from down under is a big David Bowie fan !! Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- [mailto:[EMAIL PROTECTED] Sent: Thursday, December 11, 2003 8:09 AM To: Multiple recipients of list ORACLE-L Hi Raj, David Bowie? Didn't he publish something regarding 'space' management a long time ago? ;-) I may (as usual) be totally wrong here. Greetings, Guido [EMAIL PROTECTED] 11.12.2003 13.49 Uhr Richard, Don't know about the guru, but sorry to hear that David Bowie is sick with Influenza. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! ** 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. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: Little competition
Thursday, December 11, 2003, 6:39:26 AM, Richard Foote ([EMAIL PROTECTED]) wrote: RF a.. What's wrong with the following piece of expert analysis ? I don't know what's wrong with this analysis. There's not much really there. The claim is that it's bad not to be able to specify PCTFREE, but there's no real backup to that claim, no testing to prove the point, etc. Not sure I want to admit this publicly, but I don't recall ever needing to use PCTFREE. I know what it does, and I've played around with it a bit, but in production I always got along just fine with the default setting. I told this to a data warehousing person recently, and he was aghast, as he (apparently) uses PCTFREE often. But I have not worked on such huge databases, and maybe that's why I've never needed it. Bringing this back to automatic space management, it's my opinion that such features are targeted towards the low end (for lack of a better term, sorry) in which defaults work just fine. I'd guess that there's a class of databases for which the default PCTFREE setting is good-enough, and for which the automatic space management feature is good-enough, and for which automatic extent management is good-enough, etc. One of the things I've wondered about lately, is how to characterize the sort of database for which all the automatic features and the defaults are fine. Related to all this, as complicated as Oracle *can* be, I'm close to convinced that it's possible to define a greatly simplified database management regime. Work within a certain box, and you can ignore much of the complexity. I can even envision a user-manual targeted specifically at that box. Such a manual, for example, would show a simplified version of CREATE TABLE that omitted such things as PCTFREE, PCTUSED, etc. I haven't quite figured out yet how to define that box and how to characterize the sort of environment to which it applies. I once worked for a client who had a 5-10 gigabyte database with in the neighborhood of a dozen users. What they needed to know about managing Oracle would have fit into a really small book. Oracle is on to something with all the automatic features, but they need to present that feature set differently. Right now you get a database, you get told it can do all these automatic things (space mgmt, extent mgmt, SGA mgmt), but then you get pointed to this HUGE manual set that you need to wade through before you can begin to understand the automatic features. Maybe I'm wrong here, but I don't believe Oracle has put together the simplified DBA manual yet, and perhaps maybe they should. What do you think? Should Oracle define the box and write a manual for customers who want to live within that box? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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: Little competition
Title: Re: Little competition Neither the PCTFREE and PCTUSED clauses go inside the STORAGE clause. They are independent of it. That is why the error was thrown, not because PCTFREE is invalid with ASSM... Essentially, an erroneous interpretation of the error message. If it was really going to prove his point, the CREATE syntax in the article should have read instead: create table test_table (c1 number) tablespace test_assm pctfree 20 pctused 30; It succeeds, by the way... on 12/11/03 6:14 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: #1. these instances are still on 8i. We are supposed to go to 9i, but its not my call #2. its read only for the users. We do batch loads at night and I did not notice any slow down in the loads. I run statspack regularly. no problem. Just gotta do an alter table move periodically when we get too much row migration, but I can do that over the weekend. Depends on your situation. There are cases for dense blocks and there are cases where you dont want to do this. again, what is so bad with what burleson said about the pctfree and pctused? From: Tim Gorman [EMAIL PROTECTED] Date: 2003/12/11 Thu AM 07:59:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Little competition I can cut 45 minutes off my load times by shrinking the data file? And how much overhead gets added to DML statements as blocks madly shift on and off the freelists with each operation? Priorities, priorities, priorities... If youre using 9i or above, the table COMPRESS feature might be a more effective mechanism? on 12/11/03 5:44 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: hopefully i wont sound like a complete idiot, but what is wrong with wanting to be able to handle your own pctfree and pctused. Ok oracle handles the next and initial extent sizes...(which causes fragmentation). I use transportable tablespaces and in order to increase the time it takes to copy these datafiles, I use pctused 99 and pctfree 1 in order to compact the tables. I can cut 45 minutes off my load times by shrinking the data file? From: Richard Foote [EMAIL PROTECTED] Date: 2003/12/11 Thu AM 06:39:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Little competition Little competition for you all :) It's a two part question: a.. What's wrong with the following piece of expert analysis ? b.. Which well know Oracle Guru published this (and continues to display it on his web-page) ? Sadly, Oracle9i doesn't allow you to specify the value for PCTFREE if you're using automatic space management. This is a serious limitation because Oracle9i can't know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance. SQL create table 2 test_table 3 (c1 number) 4 tablespace 5 asm_test 6 storage 7 ( pctfree 20 pctused 30 ) 8 ; ( pctfree 20 pctused 30 ) * ERROR at line 7: ORA-02143: invalid STORAGE option However, here's an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings You've gotta love it !! Sorry no clues Cheers ;) Richard Little competition for you all :) It's a two part question: * What's wrong with the following piece of expert analysis ? * Which well know Oracle Guru published this (and continues to display it on his web-page) ? Sadly, Oracle9i doesnt allow you to specify the value for PCTFREE if youre using automatic space management. This is a serious limitation because Oracle9i cant know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance. SQL create table 2 test_table 3 (c1 number) 4 tablespace 5 asm_test 6 storage 7 ( pctfree 20 pctused 30 ) 8 ; ( pctfree 20 pctused 30 ) * ERROR at line 7: ORA-02143: invalid STORAGE option However, heres an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings You've gotta love it !! Sorry no clues Cheers ;) Richard I can cut 45 minutes off my load times by shrinking the data file? And how much overhead gets added to DML statements as blocks madly shift on and off the freelists with each operation? Priorities, priorities, priorities... If youre using 9i or above, the table COMPRESS feature might be a more effective mechanism? on 12/11/03 5:44 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: hopefully i wont sound like a complete idiot
RE: Little competition
Jonathan, I've been coming to the same conclusion as you have. In all of my databases that I have ever worked on, there are only a select set of Oracle params that are really required. And with space management the way it is, a lot of the 'create table' params are really not needed for the low-to-mid-end applications. While I personally always use pctfree and pctused, what you said makes sense. Consistency is more important to me (in creating the tables) than the details of each table. If all tables are created the same, then, at worse, you will use more space in storing data than you could if you tuned the parameters (like - pctfree/pctused 10/40 uses more space per table than 10/90). But does this really matter? In a high-end database, it might. But in a smaller application with only a couple of hundred users and, say, less than half-a-gig of total data, I'd say that tuning these params are only playing in the margins of throughput. And I think a condensed documentation set is a great idea. I wonder if there is a market for it? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, December 11, 2003 8:25 AM To: Multiple recipients of list ORACLE-L Thursday, December 11, 2003, 6:39:26 AM, Richard Foote ([EMAIL PROTECTED]) wrote: RF a.. What's wrong with the following piece of expert analysis ? I don't know what's wrong with this analysis. There's not much really there. The claim is that it's bad not to be able to specify PCTFREE, but there's no real backup to that claim, no testing to prove the point, etc. Not sure I want to admit this publicly, but I don't recall ever needing to use PCTFREE. I know what it does, and I've played around with it a bit, but in production I always got along just fine with the default setting. I told this to a data warehousing person recently, and he was aghast, as he (apparently) uses PCTFREE often. But I have not worked on such huge databases, and maybe that's why I've never needed it. Bringing this back to automatic space management, it's my opinion that such features are targeted towards the low end (for lack of a better term, sorry) in which defaults work just fine. I'd guess that there's a class of databases for which the default PCTFREE setting is good-enough, and for which the automatic space management feature is good-enough, and for which automatic extent management is good-enough, etc. One of the things I've wondered about lately, is how to characterize the sort of database for which all the automatic features and the defaults are fine. Related to all this, as complicated as Oracle *can* be, I'm close to convinced that it's possible to define a greatly simplified database management regime. Work within a certain box, and you can ignore much of the complexity. I can even envision a user-manual targeted specifically at that box. Such a manual, for example, would show a simplified version of CREATE TABLE that omitted such things as PCTFREE, PCTUSED, etc. I haven't quite figured out yet how to define that box and how to characterize the sort of environment to which it applies. I once worked for a client who had a 5-10 gigabyte database with in the neighborhood of a dozen users. What they needed to know about managing Oracle would have fit into a really small book. Oracle is on to something with all the automatic features, but they need to present that feature set differently. Right now you get a database, you get told it can do all these automatic things (space mgmt, extent mgmt, SGA mgmt), but then you get pointed to this HUGE manual set that you need to wade through before you can begin to understand the automatic features. Maybe I'm wrong here, but I don't believe Oracle has put together the simplified DBA manual yet, and perhaps maybe they should. What do you think? Should Oracle define the box and write a manual for customers who want to live within that box? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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: Re: Little competition
Ive been working on 'large' distributed databases lately and its not as high as alot of arrogant people make it out to be. Its just different. Im not sure tuning a database with alot of data and fewer transactions like in a datawarehouse necessarily 'harder' than tuning a higher transaction database with less data. You just look for different things. I really dont care that much about LIOs for my batch loads since Im not going to scale it, Im just worried about response time. I routinely let my LIOs go up alot to increase response time. Is that harder than trying to get LIOs down in a database with less data? No its just different. Its not that different... same basic principles. I think defining high end databases should have more to do with what you are doing with them, then how much data is in them. The biggest thing about working on database in the multi-TB range is that its a nice buzzword for your resume. Its not necessarily harder. Besides, when your on a 'lower' end project with less resources and less people, Id argue that alot of times your job is alot harder. You dont have the same hardware and you have to do alot more different things yourself. Though it doesnt look as good on a resume... Maybe Carrie Milsap can chime in since he is the resident tuner expert here? Do you necessarily find it harder to tune large databases over smaller ones? From: Jonathan Gennick [EMAIL PROTECTED] Date: 2003/12/11 Thu AM 08:24:34 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Little competition Thursday, December 11, 2003, 6:39:26 AM, Richard Foote ([EMAIL PROTECTED]) wrote: RF a.. What's wrong with the following piece of expert analysis ? I don't know what's wrong with this analysis. There's not much really there. The claim is that it's bad not to be able to specify PCTFREE, but there's no real backup to that claim, no testing to prove the point, etc. Not sure I want to admit this publicly, but I don't recall ever needing to use PCTFREE. I know what it does, and I've played around with it a bit, but in production I always got along just fine with the default setting. I told this to a data warehousing person recently, and he was aghast, as he (apparently) uses PCTFREE often. But I have not worked on such huge databases, and maybe that's why I've never needed it. Bringing this back to automatic space management, it's my opinion that such features are targeted towards the low end (for lack of a better term, sorry) in which defaults work just fine. I'd guess that there's a class of databases for which the default PCTFREE setting is good-enough, and for which the automatic space management feature is good-enough, and for which automatic extent management is good-enough, etc. One of the things I've wondered about lately, is how to characterize the sort of database for which all the automatic features and the defaults are fine. Related to all this, as complicated as Oracle *can* be, I'm close to convinced that it's possible to define a greatly simplified database management regime. Work within a certain box, and you can ignore much of the complexity. I can even envision a user-manual targeted specifically at that box. Such a manual, for example, would show a simplified version of CREATE TABLE that omitted such things as PCTFREE, PCTUSED, etc. I haven't quite figured out yet how to define that box and how to characterize the sort of environment to which it applies. I once worked for a client who had a 5-10 gigabyte database with in the neighborhood of a dozen users. What they needed to know about managing Oracle would have fit into a really small book. Oracle is on to something with all the automatic features, but they need to present that feature set differently. Right now you get a database, you get told it can do all these automatic things (space mgmt, extent mgmt, SGA mgmt), but then you get pointed to this HUGE manual set that you need to wade through before you can begin to understand the automatic features. Maybe I'm wrong here, but I don't believe Oracle has put together the simplified DBA manual yet, and perhaps maybe they should. What do you think? Should Oracle define the box and write a manual for customers who want to live within that box? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list
Re: Re: Little competition
yeah typical burleson carelessness then. anyone can make that mistake, but if your going to publish you should be more careful. should have known. From: Tim Gorman [EMAIL PROTECTED] Date: 2003/12/11 Thu AM 08:34:32 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Little competition Neither the PCTFREE and PCTUSED clauses go inside the STORAGE clause. They are independent of it. That is why the error was thrown, not because PCTFREE is invalid with ASSM... Essentially, an erroneous interpretation of the error message. If it was really going to prove his point, the CREATE syntax in the article should have read instead: create table test_table (c1 number) tablespace test_assm pctfree 20 pctused 30; It succeeds, by the way... on 12/11/03 6:14 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: #1. these instances are still on 8i. We are supposed to go to 9i, but its not my call #2. its read only for the users. We do batch loads at night and I did not notice any slow down in the loads. I run statspack regularly. no problem. Just gotta do an alter table move periodically when we get too much row migration, but I can do that over the weekend. Depends on your situation. There are cases for dense blocks and there are cases where you dont want to do this. again, what is so bad with what burleson said about the pctfree and pctused? From: Tim Gorman [EMAIL PROTECTED] Date: 2003/12/11 Thu AM 07:59:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Little competition I can cut 45 minutes off my load times by shrinking the data file? And how much overhead gets added to DML statements as blocks madly shift on and off the freelists with each operation? Priorities, priorities, priorities... If you¹re using 9i or above, the table COMPRESS feature might be a more effective mechanism? on 12/11/03 5:44 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: hopefully i wont sound like a complete idiot, but what is wrong with wanting to be able to handle your own pctfree and pctused. Ok oracle handles the next and initial extent sizes...(which causes fragmentation). I use transportable tablespaces and in order to increase the time it takes to copy these datafiles, I use pctused 99 and pctfree 1 in order to compact the tables. I can cut 45 minutes off my load times by shrinking the data file? From: Richard Foote [EMAIL PROTECTED] Date: 2003/12/11 Thu AM 06:39:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Little competition Little competition for you all :) It's a two part question: a.. What's wrong with the following piece of expert analysis ? b.. Which well know Oracle Guru published this (and continues to display it on his web-page) ? Sadly, Oracle9i doesn't allow you to specify the value for PCTFREE if you're using automatic space management. This is a serious limitation because Oracle9i can't know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance. SQL create table 2 test_table 3 (c1 number) 4 tablespace 5 asm_test 6 storage 7 ( pctfree 20 pctused 30 ) 8 ; ( pctfree 20 pctused 30 ) * ERROR at line 7: ORA-02143: invalid STORAGE option However, here's an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings You've gotta love it !! Sorry no clues Cheers ;) Richard Little competition for you all :) It's a two part question: * What's wrong with the following piece of expert analysis ? * Which well know Oracle Guru published this (and continues to display it on his web-page) ? Sadly, Oracle9i doesnt allow you to specify the value for PCTFREE if youre using automatic space management. This is a serious limitation because Oracle9i cant know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance. SQL create table 2 test_table 3 (c1 number) 4 tablespace 5 asm_test 6 storage 7 ( pctfree 20 pctused 30 ) 8 ; ( pctfree 20 pctused 30 ) * ERROR at line 7: ORA-02143: invalid STORAGE option However, heres an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters
Re: Little competition
1) Pctfree and pctused are not storage parameters. 2) Oracle allows using both of those parameters as shown by the next example: SQL select tablespace_name from dba_tablespaces 2 where segment_space_management='AUTO'; TABLESPACE_NAME -- CWMLITE DRSYS EXAMPLE INDX ODM TOOLS USERS XDB COMPLIANCE VIEWS 10 rows selected. SQL set termout on SQL set echo on SQL @/tmp/1 SQL create table 2 test_table 3 (c1 number) 4 tablespace 5 compliance 6 pctfree 20 pctused 30 7 / Table created. SQL 3) I have no idea who the guru is, but he or she hasn't been following this list. There was quite an extensive debate about ASSM not so long ago, resolved by Tanel Poder's detailed presentation of the new bitmap mechanism. On 12/11/2003 06:39:26 AM, Richard Foote wrote: Little competition for you all :) It's a two part question: a.. What's wrong with the following piece of expert analysis ? b.. Which well know Oracle Guru published this (and continues to display it on his web-page) ? Sadly, Oracle9i doesn't allow you to specify the value for PCTFREE if you're using automatic space management. This is a serious limitation because Oracle9i can't know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance. SQL create table 2 test_table 3 (c1 number) 4 tablespace 5 asm_test 6 storage 7 ( pctfree 20 pctused 30 ) 8 ; ( pctfree 20 pctused 30 ) * ERROR at line 7: ORA-02143: invalid STORAGE option However, here's an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings You've gotta love it !! Sorry no clues Cheers ;) Richard Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: Re: Little competition
#1. these instances are still on 8i. We are supposed to go to 9i, but its not my call #2. its read only for the users. We do batch loads at night and I did not notice any slow down in the loads. I run statspack regularly. no problem. Just gotta do an alter table move periodically when we get too much row migration, but I can do that over the weekend. Depends on your situation. There are cases for dense blocks and there are cases where you dont want to do this. again, what is so bad with what burleson said about the pctfree and pctused? From: Tim Gorman [EMAIL PROTECTED] Date: 2003/12/11 Thu AM 07:59:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Little competition I can cut 45 minutes off my load times by shrinking the data file? And how much overhead gets added to DML statements as blocks madly shift on and off the freelists with each operation? Priorities, priorities, priorities... If you¹re using 9i or above, the table COMPRESS feature might be a more effective mechanism? on 12/11/03 5:44 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: hopefully i wont sound like a complete idiot, but what is wrong with wanting to be able to handle your own pctfree and pctused. Ok oracle handles the next and initial extent sizes...(which causes fragmentation). I use transportable tablespaces and in order to increase the time it takes to copy these datafiles, I use pctused 99 and pctfree 1 in order to compact the tables. I can cut 45 minutes off my load times by shrinking the data file? From: Richard Foote [EMAIL PROTECTED] Date: 2003/12/11 Thu AM 06:39:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Little competition Little competition for you all :) It's a two part question: a.. What's wrong with the following piece of expert analysis ? b.. Which well know Oracle Guru published this (and continues to display it on his web-page) ? Sadly, Oracle9i doesn't allow you to specify the value for PCTFREE if you're using automatic space management. This is a serious limitation because Oracle9i can't know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance. SQL create table 2 test_table 3 (c1 number) 4 tablespace 5 asm_test 6 storage 7 ( pctfree 20 pctused 30 ) 8 ; ( pctfree 20 pctused 30 ) * ERROR at line 7: ORA-02143: invalid STORAGE option However, here's an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings You've gotta love it !! Sorry no clues Cheers ;) Richard Little competition for you all :) It's a two part question: * What's wrong with the following piece of expert analysis ? * Which well know Oracle Guru published this (and continues to display it on his web-page) ? Sadly, Oracle9i doesnt allow you to specify the value for PCTFREE if youre using automatic space management. This is a serious limitation because Oracle9i cant know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance. SQL create table 2 test_table 3 (c1 number) 4 tablespace 5 asm_test 6 storage 7 ( pctfree 20 pctused 30 ) 8 ; ( pctfree 20 pctused 30 ) * ERROR at line 7: ORA-02143: invalid STORAGE option However, heres an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings You've gotta love it !! Sorry no clues Cheers ;) Richard Title: Re: Little competition I can cut 45 minutes off my load times by shrinking the data file? And how much overhead gets added to DML statements as blocks madly shift on and off the freelists with each operation? Priorities, priorities, priorities... If youre using 9i or above, the table COMPRESS feature might be a more effective mechanism? on 12/11/03 5:44 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: hopefully i wont sound like a complete idiot, but what is wrong with wanting to be able to handle your own pctfree and pctused. Ok oracle handles the next and initial extent sizes...(which causes fragmentation). I use transportable tablespaces and in order to increase the time it takes to copy these datafiles, I use pctused 99 and pctfree 1 in order to compact the tables. I can cut 45 minutes off my load times by shrinking the data file? From: Richard
RE: Antw: RE: Little competition
Yes, I know. That David Bowie stuff comes up every few weeks / months in several c.d.o. groups. But what are 'fans'? In my opintion they make wind ;-) Anyway, I like this competition thread - mostly because there's nothing but bulls**t coming from me. Greetings to the list, Guido [EMAIL PROTECTED] 11.12.2003 14.24 Uhr No, you are not but I think Richard from down under is a big David Bowie fan !! Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- [mailto:[EMAIL PROTECTED] Sent: Thursday, December 11, 2003 8:09 AM To: Multiple recipients of list ORACLE-L Hi Raj, David Bowie? Didn't he publish something regarding 'space' management a long time ago? ;-) I may (as usual) be totally wrong here. Greetings, Guido [EMAIL PROTECTED] 11.12.2003 13.49 Uhr Richard, Don't know about the guru, but sorry to hear that David Bowie is sick with Influenza. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! ** 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. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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.net -- Author: Guido Konsolke 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: Re: Little competition
I managed to emulate Don Burleson's problem: SQL create table test_table 2 (c1 number) 3 storage (pctfree 20 pctused 30); storage (pctfree 20 pctused 30) * ERROR at line 3: ORA-02143: invalid STORAGE option But when I read the error message, and corrected the error that was reporting, the statement worked: create table test_table(c1 number) pctfree 20 pctused 30; Table created. = Richard was kind enough to refrain from posting the next paragraph in the tip: quote This could be a serious issue for the Oracle professional unless they remember that locally-managed tablespaces with automatic space management ignore any specified values for NEXT and FREELISTS. end quote There is another error here. For a bonus 10 points can anyone spot it ? Hint - try the following in a tablespace which is locallally managed, with automatic space management, and either system managed or uniform sized extents of no more than 1 M. create table test2(n1 number) storage (initial 1M next 2M pctincrease 100 minextents 3); Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November 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] Sent: Thursday, December 11, 2003 1:14 PM again, what is so bad with what burleson said about the pctfree and pctused? From: Richard Foote [EMAIL PROTECTED] Date: 2003/12/11 Thu AM 06:39:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Little competition Little competition for you all :) It's a two part question: a.. What's wrong with the following piece of expert analysis ? b.. Which well know Oracle Guru published this (and continues to display it on his web-page) ? Sadly, Oracle9i doesn't allow you to specify the value for PCTFREE if you're using automatic space management. This is a serious limitation because Oracle9i can't know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance. SQL create table 2 test_table 3 (c1 number) 4 tablespace 5 asm_test 6 storage 7 ( pctfree 20 pctused 30 ) 8 ; ( pctfree 20 pctused 30 ) * ERROR at line 7: ORA-02143: invalid STORAGE option However, here's an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings -- 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: Little competition
Good points Jonathan, The 'box' as far as I'm concerned was accomodated by v7.3.4. That had 95% of the features anyone could want for most environments. After that we've had a succession of 'nice to have' features. Don't get me wrong, some environments absolutely demand these new features and there's a living to be made in understanding all of the new bells and whistles but I agree that most people don't use more than a tiny subset of the available toys. Mike Hately PS Yes, I'm aware that there will follow a list of post-7.3.4 features that people consider absolutely vital. =) -Original Message- Sent: 11 December 2003 13:25 To: Multiple recipients of list ORACLE-L Maybe I'm wrong here, but I don't believe Oracle has put together the simplified DBA manual yet, and perhaps maybe they should. What do you think? Should Oracle define the box and write a manual for customers who want to live within that box? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] E mail Disclaimer You agree that you have read and understood this disclaimer and you agree to be bound by its terms. The information contained in this e-mail and any files transmitted with it (if any) are confidential and intended for the addressee only. If you have received this e-mail in error please notify the originator. This e-mail and any attachments have been scanned for certain viruses prior to sending but CE Electric UK Funding Company nor any of its associated companies from whom this e-mail originates shall be liable for any losses as a result of any viruses being passed on. No warranty of any kind is given in respect of any information contained in this e-mail and you should be aware that that it might be incomplete, out of date or incorrect. It is therefore essential that you verify all such information with us before placing any reliance upon it. CE Electric UK Funding Company Lloyds Court 78 Grey Street Newcastle upon Tyne NE1 6AF Registered in England and Wales: Number 3476201 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (LogicaCMG) 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: Little competition
What's wrong with the analysis? Wrong syntax in the 'CREATE TABLE' PCTFREE and PCTUSEDare not part of the storage clause. SQL create table t1 (c1 number) tablespace lmt1 pctfree 20 pctused 30; Table created. Lot's of potential to go down the wrong path on this one. Assumptions! Nelson -Original Message-From: Richard Foote [mailto:[EMAIL PROTECTED]Sent: Thursday, December 11, 2003 6:39 AMTo: Multiple recipients of list ORACLE-LSubject: Little competition Little competition for you all :) It's a two part question: What's wrong with the followingpiece of expert analysis? Which well know "Oracle Guru" published this (and continues to display it on his web-page) ? "Sadly, Oracle9i doesn't allow you to specify the value for PCTFREE if you're using automatic space management. This is a serious limitation because Oracle9i can't know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance." SQL create table 2 test_table 3 (c1 number) 4 tablespace 5 asm_test 6 storage 7 ( pctfree 20 pctused 30 ) 8 ; ( pctfree 20 pctused 30 ) *ERROR at line 7:ORA-02143: invalid STORAGE optionHowever, here's an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings" You've gotta love it !! Sorry no clues Cheers ;) Richard
Re: Little competition
create table test_table (c1 number) tablespace users pctfree 20 pctused 30 / Works for me. ;) ( USERS is ASSM) Richard Foote [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/11/2003 03:39 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Little competition Little competition for you all :) It's a two part question: What's wrong with the following piece of expert analysis ? Which well know Oracle Guru published this (and continues to display it on his web-page) ? Sadly, Oracle9i doesn't allow you to specify the value for PCTFREE if you're using automatic space management. This is a serious limitation because Oracle9i can't know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance. SQL create table 2 test_table 3 (c1 number) 4 tablespace 5 asm_test 6 storage 7 ( pctfree 20 pctused 30 ) 8 ; ( pctfree 20 pctused 30 ) * ERROR at line 7: ORA-02143: invalid STORAGE option However, here's an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings You've gotta love it !! Sorry no clues Cheers ;) Richard
Re: RE: Little competition
oracle literature is really lacking in entry level docs anyway. The concepts document is way too large to be digestable by someone new to the topic. What we really need is: simple SQL book for newbies simple PL/SQL book for newbies Architecture book automatic features Beginning Oracle Programming by Kyte, et all took a stab at this but they included WAY too much information and some sections are unreadable(the pl/sql chapters are terrible). Any newbie book should be 400 pages maximum. People get intimidated by large books when they are new. From: Hately, Mike (LogicaCMG) [EMAIL PROTECTED] Date: 2003/12/11 Thu AM 08:44:32 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Little competition Good points Jonathan, The 'box' as far as I'm concerned was accomodated by v7.3.4. That had 95% of the features anyone could want for most environments. After that we've had a succession of 'nice to have' features. Don't get me wrong, some environments absolutely demand these new features and there's a living to be made in understanding all of the new bells and whistles but I agree that most people don't use more than a tiny subset of the available toys. Mike Hately PS Yes, I'm aware that there will follow a list of post-7.3.4 features that people consider absolutely vital. =) -Original Message- Sent: 11 December 2003 13:25 To: Multiple recipients of list ORACLE-L Maybe I'm wrong here, but I don't believe Oracle has put together the simplified DBA manual yet, and perhaps maybe they should. What do you think? Should Oracle define the box and write a manual for customers who want to live within that box? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] E mail Disclaimer You agree that you have read and understood this disclaimer and you agree to be bound by its terms. The information contained in this e-mail and any files transmitted with it (if any) are confidential and intended for the addressee only. If you have received this e-mail in error please notify the originator. This e-mail and any attachments have been scanned for certain viruses prior to sending but CE Electric UK Funding Company nor any of its associated companies from whom this e-mail originates shall be liable for any losses as a result of any viruses being passed on. No warranty of any kind is given in respect of any information contained in this e-mail and you should be aware that that it might be incomplete, out of date or incorrect. It is therefore essential that you verify all such information with us before placing any reliance upon it. CE Electric UK Funding Company Lloyds Court 78 Grey Street Newcastle upon Tyne NE1 6AF Registered in England and Wales: Number 3476201 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (LogicaCMG) 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.net -- Author: [EMAIL PROTECTED] 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: RE: Little competition
On Thu, 11 Dec 2003 [EMAIL PROTECTED] wrote: oracle literature is really lacking in entry level docs anyway. The concepts document is way too large to be digestable by someone new to the topic. What we really need is: simple SQL book for newbies - O'Reilly's Mastering Oracle SQL? (It starts out from the basics, and moves at a rapid pace, but it's very well done, IMO. 312 pages) simple PL/SQL book for newbies - O'Reilly's Learning Oracle PL/SQL? (Not quite as imposing as Oracle PL/SQL Programming, and just a bit over 400 pages.) Architecture book - O'Reilly's Oracle Essentials: Oracle9i, Oracle8i Oracle8? (381 pages) automatic features - Not sure...might be covered a bit in the previous title. I guess altogether that's pushing 1000 pages, but between those three books, there's probably a lot of good information. (I'm a major O'Reilly fan, if you couldn't tell...). Once they've digested these, Milsap Holt would be next in the list of course ;-). Beginning Oracle Programming by Kyte, et all took a stab at this but they included WAY too much information and some sections are unreadable(the pl/sql chapters are terrible). Any newbie book should be 400 pages maximum. People get intimidated by large books when they are new. For most of these 'simple' databases though, do people really need to get into PL/SQL? Or would SQL itself be enough for most of these tasks? If so you could drop the PL/SQL book off the list and you're down 400 pages. -- Dan Daniel Hanks - Systems/Database Administrator About Inc., Web Services Division -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Hanks 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: RE: Little competition
PCTFREE and PCTUSED are not part of the storage clause. SQL create table t1 (c1 number) tablespace lmt1 pctfree 20 pctused 30; Table created. Lot's of potential to go down the wrong path on this one. Assumptions! Maybe it has been already mentioned here, but with ASSM, pctused value is ignored. A full block becomes free again when its usage drops below next freeness status. There are 4 freeness statuses for an ASSM table block, representing in 25% increments how full a block (plus two additional statuses for unformatted and logically full blocks). Tanel.
