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 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 > >>> > > > >> > > >> > > >> > > >> > > > > > > >>> >> 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 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 > >> > > > > > > > >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 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 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
> >
>
>
>
>
>> 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 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
