Re: PCTFREE and PCTUSED
Thanks, Richard. My PC died this weekend (it's getting CPR right now)
and I couldn't respond. I believe you now, but I still want to know
what Metalink has to say about it. The documentation didn't do a
very good job of explaining this thing.
On 11/10/2003 04:19:29 AM, Richard Foote wrote:
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Monday, November 10, 2003 9:54 AM
>
> > Note that ASSM bitmaps track "freeness" not "fullness", to be correct in
> > terminology.
>
> Hi Tanel,
>
> At restaurants, I always say to the waiter that my glass of wine is only
> 1/10 full and can I please have a top-up ;)
>
> Weird eh !!
>
> I posted this little demo on metalink to hopefully prove to Mladen that
> PCTFREE is not ignored :
>
> Let's check which tablespaces to use for our test.
>
> SQL> select tablespace_name, segment_space_management from dba_tablespaces
> where tablespace_name in ('USERS', 'BOWIE_STUFF');
>
> TABLESPACE_NAMESEGMEN
> -- --
> BOWIE_STUFFMANUAL
> USERS AUTO
>
> First lets create two non ASSM tables, one with a low pctfree, the other
> with a high pctfree.
>
> SQL> create table test_non_assm_1 tablespace bowie_stuff pctfree 5 as select
> * from dba_tables;
>
> Table created.
>
> SQL> insert into test_non_assm_1 select * from test_non_assm_1;
>
> 1103 rows created.
>
> SQL> /
>
> 2206 rows created.
>
> SQL> /
>
> 4412 rows created.
>
> SQL> /
>
> 8824 rows created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> create table test_non_assm_2 tablespace bowie_stuff pctfree 90 pctused
> 10 as select * from dba_tables;
>
> Table created.
>
> SQL> insert into test_non_assm_2 select * from test_non_assm_2;
>
> 1104 rows created.
>
> SQL> /
>
> 2208 rows created.
>
> SQL> /
>
> 4416 rows created.
>
> SQL> /
>
> 8832 rows created.
>
> SQL> commit;
>
> Commit complete.
>
> Let's now analyze these tables (I chose analyze because I want to see the
> avg space for each table).
>
> SQL> analyze table TEST_NON_ASSM_1 compute statistics;
>
> Table analyzed.
>
> SQL> analyze table TEST_NON_ASSM_2 compute statistics;
>
> Table analyzed.
>
> SQL> select table_name, blocks, avg_space from dba_tables where table_name
> in ('TEST_NON_ASSM_1', 'TEST_NON_ASSM_2');
>
> TABLE_NAME BLOCKS AVG_SPACE
> -- -- --
> TEST_NON_ASSM_1 452569
> TEST_NON_ASSM_2 4982 7395
>
> As expected, the table with a high pctfree uses dramatically more space and
> has a higher avg space value.
>
> Let's repeat the test with ASSM tables. This should *prove* whether pctfree
> is ignored or not.
>
> SQL> create table test_assm_1 tablespace users pctfree 5 as select * from
> dba_tables;
>
> Table created.
>
> SQL> insert into test_assm_1 select * from test_assm_1;
>
> 1105 rows created.
>
> SQL> /
>
> 2210 rows created.
>
> SQL> /
>
> 4420 rows created.
>
> SQL> /
>
> 8840 rows created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> create table test_assm_2 tablespace users pctfree 90 pctused 10 as
> select * from dba_tables;
>
> Table created.
>
> SQL> insert into test_assm_2 select * from test_assm_2;
>
> 1106 rows created.
>
> SQL> /
>
> 2212 rows created.
>
> SQL> /
>
> 4424 rows created.
>
> SQL> /
>
> 8848 rows created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> analyze table TEST_ASSM_1 compute statistics;
>
> Table analyzed.
>
> SQL> analyze table TEST_ASSM_2 compute statistics;
>
> Table analyzed.
>
> SQL> select table_name, blocks, avg_space from dba_tables where table_name
> in ('TEST_ASSM_1', 'TEST_ASSM_2');
>
> TABLE_NAMEBLOCKS AVG_SPACE
> -- -- --
> TEST_ASSM_247307347
> TEST_ASSM_1 5011213
>
> As we can see, the results are very similar. The table with a high pctfree
> has a massive number of blocks and (potential) wasted space relative to the
> table with a low pctfree.
>
> PCTFREE is most definitely *NOT* ignored with ASSM !!
>
> Cheers
>
> Richard Foote
>
>
> --
> 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).
>
Mladen Gogala
Oracle DBA
Note:
This mess
Re: PCTFREE and PCTUSED
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, November 10, 2003 9:54 AM
> Note that ASSM bitmaps track "freeness" not "fullness", to be correct in
> terminology.
Hi Tanel,
At restaurants, I always say to the waiter that my glass of wine is only
1/10 full and can I please have a top-up ;)
Weird eh !!
I posted this little demo on metalink to hopefully prove to Mladen that
PCTFREE is not ignored :
Let's check which tablespaces to use for our test.
SQL> select tablespace_name, segment_space_management from dba_tablespaces
where tablespace_name in ('USERS', 'BOWIE_STUFF');
TABLESPACE_NAMESEGMEN
-- --
BOWIE_STUFFMANUAL
USERS AUTO
First lets create two non ASSM tables, one with a low pctfree, the other
with a high pctfree.
SQL> create table test_non_assm_1 tablespace bowie_stuff pctfree 5 as select
* from dba_tables;
Table created.
SQL> insert into test_non_assm_1 select * from test_non_assm_1;
1103 rows created.
SQL> /
2206 rows created.
SQL> /
4412 rows created.
SQL> /
8824 rows created.
SQL> commit;
Commit complete.
SQL> create table test_non_assm_2 tablespace bowie_stuff pctfree 90 pctused
10 as select * from dba_tables;
Table created.
SQL> insert into test_non_assm_2 select * from test_non_assm_2;
1104 rows created.
SQL> /
2208 rows created.
SQL> /
4416 rows created.
SQL> /
8832 rows created.
SQL> commit;
Commit complete.
Let's now analyze these tables (I chose analyze because I want to see the
avg space for each table).
SQL> analyze table TEST_NON_ASSM_1 compute statistics;
Table analyzed.
SQL> analyze table TEST_NON_ASSM_2 compute statistics;
Table analyzed.
SQL> select table_name, blocks, avg_space from dba_tables where table_name
in ('TEST_NON_ASSM_1', 'TEST_NON_ASSM_2');
TABLE_NAME BLOCKS AVG_SPACE
-- -- --
TEST_NON_ASSM_1 452569
TEST_NON_ASSM_2 4982 7395
As expected, the table with a high pctfree uses dramatically more space and
has a higher avg space value.
Let's repeat the test with ASSM tables. This should *prove* whether pctfree
is ignored or not.
SQL> create table test_assm_1 tablespace users pctfree 5 as select * from
dba_tables;
Table created.
SQL> insert into test_assm_1 select * from test_assm_1;
1105 rows created.
SQL> /
2210 rows created.
SQL> /
4420 rows created.
SQL> /
8840 rows created.
SQL> commit;
Commit complete.
SQL> create table test_assm_2 tablespace users pctfree 90 pctused 10 as
select * from dba_tables;
Table created.
SQL> insert into test_assm_2 select * from test_assm_2;
1106 rows created.
SQL> /
2212 rows created.
SQL> /
4424 rows created.
SQL> /
8848 rows created.
SQL> commit;
Commit complete.
SQL> analyze table TEST_ASSM_1 compute statistics;
Table analyzed.
SQL> analyze table TEST_ASSM_2 compute statistics;
Table analyzed.
SQL> select table_name, blocks, avg_space from dba_tables where table_name
in ('TEST_ASSM_1', 'TEST_ASSM_2');
TABLE_NAMEBLOCKS AVG_SPACE
-- -- --
TEST_ASSM_247307347
TEST_ASSM_1 5011213
As we can see, the results are very similar. The table with a high pctfree
has a massive number of blocks and (potential) wasted space relative to the
table with a low pctfree.
PCTFREE is most definitely *NOT* ignored with ASSM !!
Cheers
Richard Foote
--
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: PCTFREE and PCTUSED
Hi! > I'm not entirely in agreement with the quote (that's a different story) but > notice there is no mention of PCTFREE. Yes, this is a deficiency of documentation. PCTFREE is still used and needed in ASSM segments. As you said, value for it can not be tuned automatically, since Oracle doesn't have slighest idea about nature of future data (there's still no time machine in Oracle ;) > > ASSM is designed to automatically determine whether or not a block should be > considered for inserts. It does this by using a sequence of bitmaps to > describe the "fullness" of a block. There are different levels of fullness > empty 0-25, 25-50, 50-75 and 75-*full*. However what does *full* actually > mean or at what point does Oracle no longer consider the block suitable for > inserts. Note that ASSM bitmaps track "freeness" not "fullness", to be correct in terminology. Thus the freeness statuses for a table are full, 0-25% free, 25-50% free, 50-75% free, 75-100% free and unformatted as well. (it's probably more like 0-24% and 25-49% etc, but this is not so important) There are 4 freeness bits per block in ASSM table, but for LOBs and indexes there are less. For an index, for example, you only have to state whether a block is "insertable" or not, there's no need for multiple different freeness bits. > Hope this makes some sense :) > > Cheers > > Richard You probably don't need this information but you can download my presentation about Freelists vs. ASSM internals, which I presented at OracleWorld Paris, from my homepage http://integrid.info Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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: PCTFREE and PCTUSED
> We haven't even touched the subject of ITL entries which is also unaffected > by ASSM. By the way, in 9i there is one interesting issue with ITLs, that every table datablock gets 2 ITL slots by default, even if INITRANS and MAXTRANS are set to 1. And blocks formatted due direct path inserts will have 3 ITL entries by default. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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: PCTFREE and PCTUSED (and ASSM)
Hi Mladen, Oh, I have no doubts ;) Hurt you still don't trust me but considering our little wager (2 tickets to the Bowie world tour) I guess it's only fair you go to a neutral referee. Bet when they confirm what I say they don't give as detailed an explanation ;) Cheers Richard - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Sunday, November 09, 2003 12:19 AM > Richard, I asked the question on the Metalink. The mighty Metalink will, > hopefully, resolve the doubt once and for all. Tom Kyte is busy so > he doesn't accept new questions right now. > -- > Mladen Gogala > Oracle DBA > -- > 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). > -- 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: PCTFREE and PCTUSED (and ASSM)
Richard, I asked the question on the Metalink. The mighty Metalink will, hopefully, resolve the doubt once and for all. Tom Kyte is busy so he doesn't accept new questions right now. -- Mladen Gogala Oracle DBA -- 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: PCTFREE and PCTUSED
Hi Mladen, Yes, I can offer some additional information. Firstly, let me extend your quote from the Concepts manual where immediately afterwards it says (quote) : "Free lists have been the traditional method of managing free space within segments. Bitmaps, however, provide a simpler and more efficient way of managing segment space. They provide better space utilization and completely eliminate any need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS attributes for segments created in the tablespace. If such attributes should be specified, they are ignored." I'm not entirely in agreement with the quote (that's a different story) but notice there is no mention of PCTFREE. The reason for that is quite easy to explain. One needs to remember the purpose for PCTFREE, it's there to determine how much of a block should be reserved in order for existing rows within the block to grow. Simplistically, the "correct" value for PCTFREE should be the average expected growth of a row. How can Oracle "automatically" determine when to stop inserting rows into a block such that these rows have sufficient space to grow ? The answer is that Oracle simply can't, it's entirely dependent on the growth characteristics of the tables which differs from table to table. ASSM is designed to automatically determine whether or not a block should be considered for inserts. It does this by using a sequence of bitmaps to describe the "fullness" of a block. There are different levels of fullness empty 0-25, 25-50, 50-75 and 75-*full*. However what does *full* actually mean or at what point does Oracle no longer consider the block suitable for inserts. That is determined by PCTFREE and as Oracle has no idea by how much existing rows could grow, PCTFREE is still a crucial and configurable attribute of a segment, even in a ASSM tablespace. And as we still need to set PCTFREE, we can still stuff it up (or more commonly, totally ignore it). Set it too high and Oracle prematurely considers the block full and no longer considers it for inserts, resulting in wasted space below the (now various) HWMs. Set it too low and we stuff our blocks up too full resulting in row migration due to subsequent updates. We haven't even touched the subject of ITL entries which is also unaffected by ASSM. Therefore, ASSM does little to resolve the issues you've listed because you still need to manually set the PCTFREE. Honest ;) Hope this makes some sense :) Cheers Richard - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Saturday, November 08, 2003 10:49 AM > Richard, here is what the concepts manual says (quoted): > "Segment Space Management in Locally Managed Tablespaces > > When you create a locally managed tablespace using the CREATE TABLESPACE > statement, the SEGMENT SPACE MANAGEMENT clause lets you specify how free and > used space within a segment is to be managed. Your choices are: > > * AUTO > > This keyword tells Oracle that you want to use bitmaps to manage the > free space within segments. A bitmap, in this case, is a map that describes > the status of each data block within a segment with respect to the amount of > space in the block available for inserting rows. As more or less space becomes > available in a data block, its new state is reflected in the bitmap. Bitmaps > enable Oracle to manage free space more automatically; thus, this form of > space management is called automatic segment-space management." > > That looks to me like automating the functionality of PCTFREE/PCTUSED. Do you > hae some other information? > > > On 2003.11.07 17:59, Richard Foote wrote: > > Hi (again) Mladen, > > > > I'm sure I mentioned this previously but ASSM only deals with FREELISTS, > > FREELIST GROUPS and PCTUSED (with possibly significant overheads). > > > > You still need to set *PCTFREE*, which means you can still have over > > allocation of space if you set it too high, you can still have row migration > > if you set it too low, you still have row chaining, you can still have waits > > on ITL entries and other lovely things ... > > > > ASSM is most certainly *not* some magic fix. > > > > And it's only available since 9i. > > > > Cheers > > > > Richard > > > > - Original Message - > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > Sent: Saturday, November 08, 2003 4:59 AM > > > > > > > On the other hand, you might have overallocated the space, which would > > > leave plenty of blocks on the free list, thus minimizing the impact. > > > These things are best seen on "almost full" tables with things like > > > row chaining, row migration, waits on ITL entries and other lovely > > > things. Looks like you've benn lucky so far. As I've told you before, > > > having tablespaces created with "SEGMENT SPACE MANAGEMENT AUTO" option > > > takes care of that. > > > if your tablespace is created with a command like > > > > > > "CREATE TABLESPACE DATA01 > >
Re: PCTFREE and PCTUSED
Richard, here is what the concepts manual says (quoted): "Segment Space Management in Locally Managed Tablespaces When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause lets you specify how free and used space within a segment is to be managed. Your choices are: * AUTO This keyword tells Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps enable Oracle to manage free space more automatically; thus, this form of space management is called automatic segment-space management." That looks to me like automating the functionality of PCTFREE/PCTUSED. Do you hae some other information? On 2003.11.07 17:59, Richard Foote wrote: Hi (again) Mladen, I'm sure I mentioned this previously but ASSM only deals with FREELISTS, FREELIST GROUPS and PCTUSED (with possibly significant overheads). You still need to set *PCTFREE*, which means you can still have over allocation of space if you set it too high, you can still have row migration if you set it too low, you still have row chaining, you can still have waits on ITL entries and other lovely things ... ASSM is most certainly *not* some magic fix. And it's only available since 9i. Cheers Richard - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Saturday, November 08, 2003 4:59 AM > On the other hand, you might have overallocated the space, which would > leave plenty of blocks on the free list, thus minimizing the impact. > These things are best seen on "almost full" tables with things like > row chaining, row migration, waits on ITL entries and other lovely > things. Looks like you've benn lucky so far. As I've told you before, > having tablespaces created with "SEGMENT SPACE MANAGEMENT AUTO" option > takes care of that. > if your tablespace is created with a command like > > "CREATE TABLESPACE DATA01 > DATAFILE '/data01/SID1/data01.dbf' size 8192M reuse > AUTOEXTEND ON NEXT 4096M MAXSIZE 32769M > EXTENT MANAGEMENT LOCAL AUTOALLOCATE > SEGMENT SPACE MANAGEMENT AUTO" > > then you can create tables without any additional parameters and everything will be kosher. > The tablespace above requires something called "large files support" from the file system > and cannot be used on FAT file systems or ISO9660-RR/Joliet file system. Practically anyhing > else (UFS,VxFS,ReiserFS,Ext(2|3),XFS,JFS,OCFS,NTFS (scheduled to go away when the new virus > propagataion engine enters production)). > BTW, what OS and database version do you use? > > > On 11/07/2003 01:24:25 PM, Maryann Atkinson wrote: > > > > >The answer is that > > >free list handling is overhead, which means that the database is working on > > >its own structures and not working on the user data. It's easy to conceive a > > >busy transaction table to which records are frequently added and from which > > >they're frequently removed. Having only one parameter would significantly > > >increase the amount of time spent in moving blocks to and from the free > > >list, > > >and significantly increase the overhead. You can test it by setting up a > > >table > > >with PCTFREE+PCTUSED=100. In other word, the answer to your question is that > > >two parameters are needed to reduce the overhead of the free list maintenance. > > > > By the way, I just wanted to let you know I tried that PCTFREE+PCTUSED=100 > > I used PCTFREE 40 and PCTUSED 60 on a specific table space, and created > > a table there which I updated/deleted records quite a bit, but I didnt see > > much > > of a difference. It might have been because there werent many people > > on the system at the time. > > > > thx > > maa > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Maryann Atkinson > > 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). > > > > 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 imm
Re: PCTFREE and PCTUSED
Hi (again) Mladen, I'm sure I mentioned this previously but ASSM only deals with FREELISTS, FREELIST GROUPS and PCTUSED (with possibly significant overheads). You still need to set *PCTFREE*, which means you can still have over allocation of space if you set it too high, you can still have row migration if you set it too low, you still have row chaining, you can still have waits on ITL entries and other lovely things ... ASSM is most certainly *not* some magic fix. And it's only available since 9i. Cheers Richard - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Saturday, November 08, 2003 4:59 AM > On the other hand, you might have overallocated the space, which would > leave plenty of blocks on the free list, thus minimizing the impact. > These things are best seen on "almost full" tables with things like > row chaining, row migration, waits on ITL entries and other lovely > things. Looks like you've benn lucky so far. As I've told you before, > having tablespaces created with "SEGMENT SPACE MANAGEMENT AUTO" option > takes care of that. > if your tablespace is created with a command like > > "CREATE TABLESPACE DATA01 > DATAFILE '/data01/SID1/data01.dbf' size 8192M reuse > AUTOEXTEND ON NEXT 4096M MAXSIZE 32769M > EXTENT MANAGEMENT LOCAL AUTOALLOCATE > SEGMENT SPACE MANAGEMENT AUTO" > > then you can create tables without any additional parameters and everything will be kosher. > The tablespace above requires something called "large files support" from the file system > and cannot be used on FAT file systems or ISO9660-RR/Joliet file system. Practically anyhing > else (UFS,VxFS,ReiserFS,Ext(2|3),XFS,JFS,OCFS,NTFS (scheduled to go away when the new virus > propagataion engine enters production)). > BTW, what OS and database version do you use? > > > On 11/07/2003 01:24:25 PM, Maryann Atkinson wrote: > > > > >The answer is that > > >free list handling is overhead, which means that the database is working on > > >its own structures and not working on the user data. It's easy to conceive a > > >busy transaction table to which records are frequently added and from which > > >they're frequently removed. Having only one parameter would significantly > > >increase the amount of time spent in moving blocks to and from the free > > >list, > > >and significantly increase the overhead. You can test it by setting up a > > >table > > >with PCTFREE+PCTUSED=100. In other word, the answer to your question is that > > >two parameters are needed to reduce the overhead of the free list maintenance. > > > > By the way, I just wanted to let you know I tried that PCTFREE+PCTUSED=100 > > I used PCTFREE 40 and PCTUSED 60 on a specific table space, and created > > a table there which I updated/deleted records quite a bit, but I didnt see > > much > > of a difference. It might have been because there werent many people > > on the system at the time. > > > > thx > > maa > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Maryann Atkinson > > 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). > > > > 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 s
Re: PCTFREE and PCTUSED
I have a hunch that it will fail on Oracle 8i and with the message like "SQL command not properly terminated", with the asterisk below the word "SEGMENT". It's just my intuition. On 11/07/2003 03:04:31 PM, Maryann Atkinson wrote: > > >CREATE TABLESPACE DATA01 > >DATAFILE '\data01.dbf' size 8M reuse > >AUTOEXTEND ON NEXT 4096M MAXSIZE 32M > >EXTENT MANAGEMENT LOCAL AUTOALLOCATE > >SEGMENT SPACE MANAGEMENT AUTO > > Our prod sys is using 8i on solaris, but I quickly tried that on 9i > on my own pc running on XP, before I go try it anywhere else, > and it worked. > > thx > maa > > At 01:59 PM 11/7/2003, you wrote: > >On the other hand, you might have overallocated the space, which would > >leave plenty of blocks on the free list, thus minimizing the impact. > >These things are best seen on "almost full" tables with things like > >row chaining, row migration, waits on ITL entries and other lovely > >things. Looks like you've benn lucky so far. As I've told you before, > >having tablespaces created with "SEGMENT SPACE MANAGEMENT AUTO" option > >takes care of that. > >if your tablespace is created with a command like > > > >"CREATE TABLESPACE DATA01 > > DATAFILE '/data01/SID1/data01.dbf' size 8192M reuse > > AUTOEXTEND ON NEXT 4096M MAXSIZE 32769M > > EXTENT MANAGEMENT LOCAL AUTOALLOCATE > > SEGMENT SPACE MANAGEMENT AUTO" > > > >then you can create tables without any additional parameters and > >everything will be kosher. > >The tablespace above requires something called "large files support" from > >the file system > >and cannot be used on FAT file systems or ISO9660-RR/Joliet file system. > >Practically anyhing > >else (UFS,VxFS,ReiserFS,Ext(2|3),XFS,JFS,OCFS,NTFS (scheduled to go away > >when the new virus > >propagataion engine enters production)). > >BTW, what OS and database version do you use? > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Maryann Atkinson > 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). > 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: PCTFREE and PCTUSED
CREATE TABLESPACE DATA01 DATAFILE '\data01.dbf' size 8M reuse AUTOEXTEND ON NEXT 4096M MAXSIZE 32M EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO Our prod sys is using 8i on solaris, but I quickly tried that on 9i on my own pc running on XP, before I go try it anywhere else, and it worked. thx maa At 01:59 PM 11/7/2003, you wrote: On the other hand, you might have overallocated the space, which would leave plenty of blocks on the free list, thus minimizing the impact. These things are best seen on "almost full" tables with things like row chaining, row migration, waits on ITL entries and other lovely things. Looks like you've benn lucky so far. As I've told you before, having tablespaces created with "SEGMENT SPACE MANAGEMENT AUTO" option takes care of that. if your tablespace is created with a command like "CREATE TABLESPACE DATA01 DATAFILE '/data01/SID1/data01.dbf' size 8192M reuse AUTOEXTEND ON NEXT 4096M MAXSIZE 32769M EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO" then you can create tables without any additional parameters and everything will be kosher. The tablespace above requires something called "large files support" from the file system and cannot be used on FAT file systems or ISO9660-RR/Joliet file system. Practically anyhing else (UFS,VxFS,ReiserFS,Ext(2|3),XFS,JFS,OCFS,NTFS (scheduled to go away when the new virus propagataion engine enters production)). BTW, what OS and database version do you use? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson 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: PCTFREE and PCTUSED
On the other hand, you might have overallocated the space, which would leave plenty of blocks on the free list, thus minimizing the impact. These things are best seen on "almost full" tables with things like row chaining, row migration, waits on ITL entries and other lovely things. Looks like you've benn lucky so far. As I've told you before, having tablespaces created with "SEGMENT SPACE MANAGEMENT AUTO" option takes care of that. if your tablespace is created with a command like "CREATE TABLESPACE DATA01 DATAFILE '/data01/SID1/data01.dbf' size 8192M reuse AUTOEXTEND ON NEXT 4096M MAXSIZE 32769M EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO" then you can create tables without any additional parameters and everything will be kosher. The tablespace above requires something called "large files support" from the file system and cannot be used on FAT file systems or ISO9660-RR/Joliet file system. Practically anyhing else (UFS,VxFS,ReiserFS,Ext(2|3),XFS,JFS,OCFS,NTFS (scheduled to go away when the new virus propagataion engine enters production)). BTW, what OS and database version do you use? On 11/07/2003 01:24:25 PM, Maryann Atkinson wrote: > > >The answer is that > >free list handling is overhead, which means that the database is working on > >its own structures and not working on the user data. It's easy to conceive a > >busy transaction table to which records are frequently added and from which > >they're frequently removed. Having only one parameter would significantly > >increase the amount of time spent in moving blocks to and from the free > >list, > >and significantly increase the overhead. You can test it by setting up a > >table > >with PCTFREE+PCTUSED=100. In other word, the answer to your question is that > >two parameters are needed to reduce the overhead of the free list maintenance. > > By the way, I just wanted to let you know I tried that PCTFREE+PCTUSED=100 > I used PCTFREE 40 and PCTUSED 60 on a specific table space, and created > a table there which I updated/deleted records quite a bit, but I didnt see > much > of a difference. It might have been because there werent many people > on the system at the time. > > thx > maa > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Maryann Atkinson > 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). > 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: PCTFREE and PCTUSED
The answer is that free list handling is overhead, which means that the database is working on its own structures and not working on the user data. It's easy to conceive a busy transaction table to which records are frequently added and from which they're frequently removed. Having only one parameter would significantly increase the amount of time spent in moving blocks to and from the free list, and significantly increase the overhead. You can test it by setting up a table with PCTFREE+PCTUSED=100. In other word, the answer to your question is that two parameters are needed to reduce the overhead of the free list maintenance. By the way, I just wanted to let you know I tried that PCTFREE+PCTUSED=100 I used PCTFREE 40 and PCTUSED 60 on a specific table space, and created a table there which I updated/deleted records quite a bit, but I didnt see much of a difference. It might have been because there werent many people on the system at the time. thx maa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson 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: PCTFREE and PCTUSED
On Tue, 2003-11-04 at 15:34, Mladen Gogala wrote: > You can test it by setting up a table > with PCTFREE+PCTUSED=100. In other word, the answer to your question is that > two parameters are needed to reduce the overhead of the free list maintenance. Well, geez Mladen, I was trying to make her work for that part. :) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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: PCTFREE and PCTUSED
Uh, oh. No wonder I confused you with what I wrote Mladen. The below should read if the PCTFREE value is 10, NOT 90. See what happens when you compose emails when taking antihistamines. Don't worry. I'm not doing any storage tuning at the present moment ... :) Melanie --- Melanie Caffrey <[EMAIL PROTECTED]> wrote: > Ummm ... Mladen? > > MG> 1) If the free space percentage in the block > falls > MG> below PCTFREE, , the block is > MG> taken off the free list. > > Don't you mean if the free space percentage goes > *above* the value in PCTFREE? > > Unless I'm reading you incorrectly, don't you mean > that, say, if the PCTFREE value is 90, and the block > becomes 91% full, then the block is taken off the > free > list? > > (Maybe this is what you mean and I'm not reading you > correctly ) > > --- Mladen Gogala <[EMAIL PROTECTED]> wrote: > > OK, I used to teach DBA courses and that was one > of > > my favorite topics so let > > me give it a shot here: > > 1) If the free space percentage in the block falls > > below PCTFREE, the block is > > taken off the free list. Heuristically speaking, > we > > can say that oracle does > > its best to keep the block PCTFREE free. > > > > 2) When the block is taken off the free list and > > records are deleted, the > > block is not returned to the free list until the > > percentage of used space > > doesn't fall below PCTUSED. Again, heuristically > > speaking, oracle tries to > > keep blocks at least PCTUSED used. > > > > I believe that your question was about the need > for > > two parameters, in other > > words, why do we need both of them, why don't we > > return block to free list > > after the percentage of free space grows above > > PCTFREE? The answer is that > > free list handling is overhead, which means that > the > > database is working on > > its own structures and not working on the user > data. > > It's easy to conceive a > > busy transaction table to which records are > > frequently added and from which > > they're frequently removed. Having only one > > parameter would significantly > > increase the amount of time spent in moving blocks > > to and from the free list, > > and significantly increase the overhead. You can > > test it by setting up a table > > with PCTFREE+PCTUSED=100. In other word, the > answer > > to your question is that > > two parameters are needed to reduce the overhead > of > > the free list maintenance. > > > > Fortunately, if you are on Oracle v9.2 and above, > > you can avoid the whole > > thing by creating your tablespaces in such a way > > that the objects in them have > > free lists managed by oracle (SEGMENT SPACE > > MANAGEMENT AUTO clause). > > > > > > > > On 2003.11.04 18:09, Maryann Atkinson wrote: > > > Suppose I have the following settings which > happen > > to be > > > the defaults as well: > > > > > > PCTFREE 10 > > > PCTUSED 40 > > > > > > > > > I am trying to figure out what PCTUSED is really > > used for. > > > My book is telling me that is used so that > Oracle > > knows > > > whether to keep a block in the "free-list". > > > > > > My point is this: If PCTFREE is 10%, that means > > the block can be > > > up to 90% full, right? > > > > > > Well, if the block happens to be 60% full at the > > moment, then Oracle > > > knows that this block is not full enough because > > 60 is less than 90, > > > so it can keep it in the free list. I dont see > > what PCTUSED is needed, > > > it kind of seems I can accomplish the same with > > just one parm, > > > that being PCTFREE. > > > > > > But Oracle wouldnt have just put a parm there > > without any usage, > > > so I guess there's something I dont see... > > > > > > Any ideas/examples? Any good reasoning anywhere? > > > > > > Thanks, > > > maa > > > > > >-- > > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.net > > >-- > > > Author: Maryann Atkinson > > > 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). > > -- > > Mladen Gogala > > Oracle DBA > > -- > > 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 > > > --
Re: PCTFREE and PCTUSED
OK. I think we're essentially saying the same thing. But in two different ways. --- Mladen Gogala <[EMAIL PROTECTED]> wrote: > No, I don't mean that. If the free space percentage > goes above PCTFREE, that > means that there is more then PCTFREE % free space > in the block. Block is > thus eligible for free list. If the percentage of > free space falls below > PCTFREE, that means that there is less then PCTFREE > % of free space. Block is > taken off the free list. > > On 2003.11.04 20:19, Melanie Caffrey wrote: > > Ummm ... Mladen? > > > > MG> 1) If the free space percentage in the block > falls > > MG> below PCTFREE, , the block is > > MG> taken off the free list. > > > > Don't you mean if the free space percentage goes > > *above* the value in PCTFREE? > > > > Unless I'm reading you incorrectly, don't you mean > > that, say, if the PCTFREE value is 90, and the > block > > becomes 91% full, then the block is taken off the > free > > list? > > > > (Maybe this is what you mean and I'm not reading > you > > correctly ) > > > > --- Mladen Gogala <[EMAIL PROTECTED]> wrote: > > > OK, I used to teach DBA courses and that was one > of > > > my favorite topics so let > > > me give it a shot here: > > > 1) If the free space percentage in the block > falls > > > below PCTFREE, the block is > > > taken off the free list. Heuristically speaking, > we > > > can say that oracle does > > > its best to keep the block PCTFREE free. > > > > > > 2) When the block is taken off the free list and > > > records are deleted, the > > > block is not returned to the free list until the > > > percentage of used space > > > doesn't fall below PCTUSED. Again, heuristically > > > speaking, oracle tries to > > > keep blocks at least PCTUSED used. > > > > > > I believe that your question was about the need > for > > > two parameters, in other > > > words, why do we need both of them, why don't we > > > return block to free list > > > after the percentage of free space grows above > > > PCTFREE? The answer is that > > > free list handling is overhead, which means that > the > > > database is working on > > > its own structures and not working on the user > data. > > > It's easy to conceive a > > > busy transaction table to which records are > > > frequently added and from which > > > they're frequently removed. Having only one > > > parameter would significantly > > > increase the amount of time spent in moving > blocks > > > to and from the free list, > > > and significantly increase the overhead. You can > > > test it by setting up a table > > > with PCTFREE+PCTUSED=100. In other word, the > answer > > > to your question is that > > > two parameters are needed to reduce the overhead > of > > > the free list maintenance. > > > > > > Fortunately, if you are on Oracle v9.2 and > above, > > > you can avoid the whole > > > thing by creating your tablespaces in such a way > > > that the objects in them have > > > free lists managed by oracle (SEGMENT SPACE > > > MANAGEMENT AUTO clause). > > > > > > > > > > > > On 2003.11.04 18:09, Maryann Atkinson wrote: > > > > Suppose I have the following settings which > happen > > > to be > > > > the defaults as well: > > > > > > > > PCTFREE 10 > > > > PCTUSED 40 > > > > > > > > > > > > I am trying to figure out what PCTUSED is > really > > > used for. > > > > My book is telling me that is used so that > Oracle > > > knows > > > > whether to keep a block in the "free-list". > > > > > > > > My point is this: If PCTFREE is 10%, that > means > > > the block can be > > > > up to 90% full, right? > > > > > > > > Well, if the block happens to be 60% full at > the > > > moment, then Oracle > > > > knows that this block is not full enough > because > > > 60 is less than 90, > > > > so it can keep it in the free list. I dont see > > > what PCTUSED is needed, > > > > it kind of seems I can accomplish the same > with > > > just one parm, > > > > that being PCTFREE. > > > > > > > > But Oracle wouldnt have just put a parm there > > > without any usage, > > > > so I guess there's something I dont see... > > > > > > > > Any ideas/examples? Any good reasoning > anywhere? > > > > > > > > Thanks, > > > > maa > > > > > > > >-- > > > > Please see the official ORACLE-L FAQ: > > > http://www.orafaq.net > > > >-- > > > > Author: Maryann Atkinson > > > > 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 > infor
Re: PCTFREE and PCTUSED
Or due to lack of free ITL slots during inserts -- Denny Koovakattu Quoting Tanel Poder <[EMAIL PROTECTED]>: > Hi! > > Note that a block can come off freelist also when it's usage is under > PCTFREE but above PCTUSED *and* an insert is attempted, but rejected for > this block because it would have filled the block above PCTFREE. > > Tanel. > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Wednesday, November 05, 2003 1:34 AM > > > > To use your numbers, the block can fill to 90% (100-PCTFREE) at which > > time it comes off the freelist. > > > > If you delete rows until the block falls below 40% used (PCTUSED), the > > block will go back on the freelist. > > > > -Original Message- > > Sent: Tuesday, November 04, 2003 3:09 PM > > To: Multiple recipients of list ORACLE-L > > > > > > Suppose I have the following settings which happen to be > > the defaults as well: > > > > PCTFREE 10 > > PCTUSED 40 > > > > > > I am trying to figure out what PCTUSED is really used for. > > My book is telling me that is used so that Oracle knows > > whether to keep a block in the "free-list". > > > > My point is this: If PCTFREE is 10%, that means the block can be up to > > 90% full, right? > > > > Well, if the block happens to be 60% full at the moment, then Oracle > > knows that this block is not full enough because 60 is less than 90, so > > it can keep it in the free list. I dont see what PCTUSED is needed, it > > kind of seems I can accomplish the same with just one parm, that being > > PCTFREE. > > > > But Oracle wouldnt have just put a parm there without any usage, so I > > guess there's something I dont see... > > > > Any ideas/examples? Any good reasoning anywhere? > > > > Thanks, > > maa > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Maryann Atkinson > > 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: Tim Fleury > > 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: Tanel Poder > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > This message was sent using IMP, the Internet Messaging Program. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu 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: PCTFREE and PCTUSED
On 2003.11.04 19:54, Maryann Atkinson wrote: At 06:34 PM 11/4/2003, you wrote: > 1) If the free space percentage in the block falls below PCTFREE, the block is > taken off the free list. Heuristically speaking, we can say that oracle does > its best to keep the block PCTFREE free. So, if pctfree is 10%, oracle does its best to keep it full 90%. Fine. No. That is not what I said. Oracle tries its best to keep it at least 10% free. > 2) When the block is taken off the free list and records are deleted, the > block is not returned to the free list until the percentage of used space > doesn't fall below PCTUSED. Again, heuristically speaking, oracle tries to > keep blocks at least PCTUSED used. So, Oracle lets the block get emptier and eptier, but does it best to stop this downfall of emptiness(!) at 40%, right? > I believe that your question was about the need for two parameters, in other > words, why do we need both of them, why don't we return block to free list > after the percentage of free space grows above PCTFREE? Yes, oh YES!!! :-) The answer is that free list handling is overhead, which means that the database is working on its own structures and not working on the user data. It's easy to conceive a busy transaction table to which records are frequently added and from which they're frequently removed. Having only one parameter would significantly increase the amount of time spent in moving blocks to and from the free list, and significantly increase the overhead. You can test it by setting up a table with PCTFREE+PCTUSED=100. In other word, the answer to your question is that two parameters are needed to reduce the overhead of the free list maintenance. GOT IT! I see, its beginning to make sense... Let me see... If the block were to become 85% full(ie 15% empty), then if Oracle were to put it on the free list again, that would cause overhead because it would fill it pretty soon, then would have to move it off the free list, therefore NOT doing work for me but itself, etc... right? So, PCTFREE and PCTUSED are kind of like boundary values, or kind-of-like FREE SPACE IS BETWEEN PCTUSED and PCTFREE values, right? That is correct. You've got it. May the force be with you. Many, many thanks! maa -- Mladen Gogala Oracle DBA -- 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: PCTFREE and PCTUSED
I did. Thanks. On 2003.11.04 19:29, Paul Baumgartel wrote: --- Mladen Gogala <[EMAIL PROTECTED]> wrote: [snip] > > 2) When the block is taken off the free list and records are deleted, > the block is not returned to the free list until the percentage of used > space doesn't fall below PCTUSED. Mladen-- You did mean "percentage of used space falls below PCTUSED", didn't you? = Paul Baumgartel Transcentive, Inc. www.transcentive.com __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel 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). -- Mladen Gogala Oracle DBA -- 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: PCTFREE and PCTUSED
No, I don't mean that. If the free space percentage goes above PCTFREE, that means that there is more then PCTFREE % free space in the block. Block is thus eligible for free list. If the percentage of free space falls below PCTFREE, that means that there is less then PCTFREE % of free space. Block is taken off the free list. On 2003.11.04 20:19, Melanie Caffrey wrote: Ummm ... Mladen? MG> 1) If the free space percentage in the block falls MG> below PCTFREE, , the block is MG> taken off the free list. Don't you mean if the free space percentage goes *above* the value in PCTFREE? Unless I'm reading you incorrectly, don't you mean that, say, if the PCTFREE value is 90, and the block becomes 91% full, then the block is taken off the free list? (Maybe this is what you mean and I'm not reading you correctly ) --- Mladen Gogala <[EMAIL PROTECTED]> wrote: > OK, I used to teach DBA courses and that was one of > my favorite topics so let > me give it a shot here: > 1) If the free space percentage in the block falls > below PCTFREE, the block is > taken off the free list. Heuristically speaking, we > can say that oracle does > its best to keep the block PCTFREE free. > > 2) When the block is taken off the free list and > records are deleted, the > block is not returned to the free list until the > percentage of used space > doesn't fall below PCTUSED. Again, heuristically > speaking, oracle tries to > keep blocks at least PCTUSED used. > > I believe that your question was about the need for > two parameters, in other > words, why do we need both of them, why don't we > return block to free list > after the percentage of free space grows above > PCTFREE? The answer is that > free list handling is overhead, which means that the > database is working on > its own structures and not working on the user data. > It's easy to conceive a > busy transaction table to which records are > frequently added and from which > they're frequently removed. Having only one > parameter would significantly > increase the amount of time spent in moving blocks > to and from the free list, > and significantly increase the overhead. You can > test it by setting up a table > with PCTFREE+PCTUSED=100. In other word, the answer > to your question is that > two parameters are needed to reduce the overhead of > the free list maintenance. > > Fortunately, if you are on Oracle v9.2 and above, > you can avoid the whole > thing by creating your tablespaces in such a way > that the objects in them have > free lists managed by oracle (SEGMENT SPACE > MANAGEMENT AUTO clause). > > > > On 2003.11.04 18:09, Maryann Atkinson wrote: > > Suppose I have the following settings which happen > to be > > the defaults as well: > > > > PCTFREE 10 > > PCTUSED 40 > > > > > > I am trying to figure out what PCTUSED is really > used for. > > My book is telling me that is used so that Oracle > knows > > whether to keep a block in the "free-list". > > > > My point is this: If PCTFREE is 10%, that means > the block can be > > up to 90% full, right? > > > > Well, if the block happens to be 60% full at the > moment, then Oracle > > knows that this block is not full enough because > 60 is less than 90, > > so it can keep it in the free list. I dont see > what PCTUSED is needed, > > it kind of seems I can accomplish the same with > just one parm, > > that being PCTFREE. > > > > But Oracle wouldnt have just put a parm there > without any usage, > > so I guess there's something I dont see... > > > > Any ideas/examples? Any good reasoning anywhere? > > > > Thanks, > > maa > > > >-- > > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > >-- > > Author: Maryann Atkinson > > 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). > -- > Mladen Gogala > Oracle DBA > -- > 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
Re: PCTFREE and PCTUSED
Ummm ... Mladen? MG> 1) If the free space percentage in the block falls MG> below PCTFREE, , the block is MG> taken off the free list. Don't you mean if the free space percentage goes *above* the value in PCTFREE? Unless I'm reading you incorrectly, don't you mean that, say, if the PCTFREE value is 90, and the block becomes 91% full, then the block is taken off the free list? (Maybe this is what you mean and I'm not reading you correctly ) --- Mladen Gogala <[EMAIL PROTECTED]> wrote: > OK, I used to teach DBA courses and that was one of > my favorite topics so let > me give it a shot here: > 1) If the free space percentage in the block falls > below PCTFREE, the block is > taken off the free list. Heuristically speaking, we > can say that oracle does > its best to keep the block PCTFREE free. > > 2) When the block is taken off the free list and > records are deleted, the > block is not returned to the free list until the > percentage of used space > doesn't fall below PCTUSED. Again, heuristically > speaking, oracle tries to > keep blocks at least PCTUSED used. > > I believe that your question was about the need for > two parameters, in other > words, why do we need both of them, why don't we > return block to free list > after the percentage of free space grows above > PCTFREE? The answer is that > free list handling is overhead, which means that the > database is working on > its own structures and not working on the user data. > It's easy to conceive a > busy transaction table to which records are > frequently added and from which > they're frequently removed. Having only one > parameter would significantly > increase the amount of time spent in moving blocks > to and from the free list, > and significantly increase the overhead. You can > test it by setting up a table > with PCTFREE+PCTUSED=100. In other word, the answer > to your question is that > two parameters are needed to reduce the overhead of > the free list maintenance. > > Fortunately, if you are on Oracle v9.2 and above, > you can avoid the whole > thing by creating your tablespaces in such a way > that the objects in them have > free lists managed by oracle (SEGMENT SPACE > MANAGEMENT AUTO clause). > > > > On 2003.11.04 18:09, Maryann Atkinson wrote: > > Suppose I have the following settings which happen > to be > > the defaults as well: > > > > PCTFREE 10 > > PCTUSED 40 > > > > > > I am trying to figure out what PCTUSED is really > used for. > > My book is telling me that is used so that Oracle > knows > > whether to keep a block in the "free-list". > > > > My point is this: If PCTFREE is 10%, that means > the block can be > > up to 90% full, right? > > > > Well, if the block happens to be 60% full at the > moment, then Oracle > > knows that this block is not full enough because > 60 is less than 90, > > so it can keep it in the free list. I dont see > what PCTUSED is needed, > > it kind of seems I can accomplish the same with > just one parm, > > that being PCTFREE. > > > > But Oracle wouldnt have just put a parm there > without any usage, > > so I guess there's something I dont see... > > > > Any ideas/examples? Any good reasoning anywhere? > > > > Thanks, > > maa > > > >-- > > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > >-- > > Author: Maryann Atkinson > > 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). > -- > Mladen Gogala > Oracle DBA > -- > 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). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Melanie Caffrey INET: [EMAIL PROTECTED] Fat City Network Se
Re: PCTFREE and PCTUSED
At 06:34 PM 11/4/2003, you wrote: > So, PCTFREE and PCTUSED are kind of like boundary values, or kind-of-like > FREE SPACE IS BETWEEN PCTUSED and PCTFREE values, right? I think I should have said : > USED SPACE IS BETWEEN PCTUSED and PCTFREE values, right? maa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson 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: PCTFREE and PCTUSED
At 06:34 PM 11/4/2003, you wrote: > 1) If the free space percentage in the block falls below PCTFREE, the block is > taken off the free list. Heuristically speaking, we can say that oracle does > its best to keep the block PCTFREE free. So, if pctfree is 10%, oracle does its best to keep it full 90%. Fine. > 2) When the block is taken off the free list and records are deleted, the > block is not returned to the free list until the percentage of used space > doesn't fall below PCTUSED. Again, heuristically speaking, oracle tries to > keep blocks at least PCTUSED used. So, Oracle lets the block get emptier and eptier, but does it best to stop this downfall of emptiness(!) at 40%, right? > I believe that your question was about the need for two parameters, in other > words, why do we need both of them, why don't we return block to free list > after the percentage of free space grows above PCTFREE? Yes, oh YES!!! :-) The answer is that free list handling is overhead, which means that the database is working on its own structures and not working on the user data. It's easy to conceive a busy transaction table to which records are frequently added and from which they're frequently removed. Having only one parameter would significantly increase the amount of time spent in moving blocks to and from the free list, and significantly increase the overhead. You can test it by setting up a table with PCTFREE+PCTUSED=100. In other word, the answer to your question is that two parameters are needed to reduce the overhead of the free list maintenance. GOT IT! I see, its beginning to make sense... Let me see... If the block were to become 85% full(ie 15% empty), then if Oracle were to put it on the free list again, that would cause overhead because it would fill it pretty soon, then would have to move it off the free list, therefore NOT doing work for me but itself, etc... right? So, PCTFREE and PCTUSED are kind of like boundary values, or kind-of-like FREE SPACE IS BETWEEN PCTUSED and PCTFREE values, right? Many, many thanks! maa On 2003.11.04 18:09, Maryann Atkinson wrote: Suppose I have the following settings which happen to be the defaults as well: PCTFREE 10 PCTUSED 40 I am trying to figure out what PCTUSED is really used for. My book is telling me that is used so that Oracle knows whether to keep a block in the "free-list". My point is this: If PCTFREE is 10%, that means the block can be up to 90% full, right? Well, if the block happens to be 60% full at the moment, then Oracle knows that this block is not full enough because 60 is less than 90, so it can keep it in the free list. I dont see what PCTUSED is needed, it kind of seems I can accomplish the same with just one parm, that being PCTFREE. But Oracle wouldnt have just put a parm there without any usage, so I guess there's something I dont see... Any ideas/examples? Any good reasoning anywhere? Thanks, maa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson 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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson 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: PCTFREE and PCTUSED
> Your example block can be 60% full and not be on the free list. > Once your block reaches 90% full, it is removed from the free list. So, you are implying that it can be removed from the free list based on the PCTFREE value too, right? So far I was under the impression that it can be removed from the free list based on the PCTUSED value. > It will not be put on the free list again until used space in the block > falls below PCTUSED, which is 40% in your example. Well, why cant it be placed back on the list based on the fact that it dropped below 90%? > So, a block fills up, it is removed from the free list, then a couple of > rows are deleted and used space falls to 60%. The block is still > not on the free list, as the space used has remained above 40%. and > PCTUSED comes into play when rows are deleted from the block. If enough > data is deleted from a block to cause the block to fall below 60% used > (PCTUSED), the block goes back on the freelist for subsequent > inserts/updates. Nice explanation, thanks. It could have been setup so that if it falls below 90%, then its placed on the free list. This would not require the second paramater PCTUSED. But thats hypothetical and not the real case. Oracle will not put it back on the free list, unless its full-percentage drops below 40%. Why is that important, and why has Oracle set it up that way? Why couldnt they say something like, we'll put it on the free list again, since its now less than 90% full? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson 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: PCTFREE and PCTUSED
--- Mladen Gogala <[EMAIL PROTECTED]> wrote: [snip] > > 2) When the block is taken off the free list and records are deleted, > the block is not returned to the free list until the percentage of used > space doesn't fall below PCTUSED. Mladen-- You did mean "percentage of used space falls below PCTUSED", didn't you? = Paul Baumgartel Transcentive, Inc. www.transcentive.com __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel 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: PCTFREE and PCTUSED
Hi! Note that a block can come off freelist also when it's usage is under PCTFREE but above PCTUSED *and* an insert is attempted, but rejected for this block because it would have filled the block above PCTFREE. Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, November 05, 2003 1:34 AM > To use your numbers, the block can fill to 90% (100-PCTFREE) at which > time it comes off the freelist. > > If you delete rows until the block falls below 40% used (PCTUSED), the > block will go back on the freelist. > > -Original Message- > Sent: Tuesday, November 04, 2003 3:09 PM > To: Multiple recipients of list ORACLE-L > > > Suppose I have the following settings which happen to be > the defaults as well: > > PCTFREE 10 > PCTUSED 40 > > > I am trying to figure out what PCTUSED is really used for. > My book is telling me that is used so that Oracle knows > whether to keep a block in the "free-list". > > My point is this: If PCTFREE is 10%, that means the block can be up to > 90% full, right? > > Well, if the block happens to be 60% full at the moment, then Oracle > knows that this block is not full enough because 60 is less than 90, so > it can keep it in the free list. I dont see what PCTUSED is needed, it > kind of seems I can accomplish the same with just one parm, that being > PCTFREE. > > But Oracle wouldnt have just put a parm there without any usage, so I > guess there's something I dont see... > > Any ideas/examples? Any good reasoning anywhere? > > Thanks, > maa > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Maryann Atkinson > 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: Tim Fleury > 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: Tanel Poder 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: PCTFREE and PCTUSED
OK, I used to teach DBA courses and that was one of my favorite topics so let me give it a shot here: 1) If the free space percentage in the block falls below PCTFREE, the block is taken off the free list. Heuristically speaking, we can say that oracle does its best to keep the block PCTFREE free. 2) When the block is taken off the free list and records are deleted, the block is not returned to the free list until the percentage of used space doesn't fall below PCTUSED. Again, heuristically speaking, oracle tries to keep blocks at least PCTUSED used. I believe that your question was about the need for two parameters, in other words, why do we need both of them, why don't we return block to free list after the percentage of free space grows above PCTFREE? The answer is that free list handling is overhead, which means that the database is working on its own structures and not working on the user data. It's easy to conceive a busy transaction table to which records are frequently added and from which they're frequently removed. Having only one parameter would significantly increase the amount of time spent in moving blocks to and from the free list, and significantly increase the overhead. You can test it by setting up a table with PCTFREE+PCTUSED=100. In other word, the answer to your question is that two parameters are needed to reduce the overhead of the free list maintenance. Fortunately, if you are on Oracle v9.2 and above, you can avoid the whole thing by creating your tablespaces in such a way that the objects in them have free lists managed by oracle (SEGMENT SPACE MANAGEMENT AUTO clause). On 2003.11.04 18:09, Maryann Atkinson wrote: Suppose I have the following settings which happen to be the defaults as well: PCTFREE 10 PCTUSED 40 I am trying to figure out what PCTUSED is really used for. My book is telling me that is used so that Oracle knows whether to keep a block in the "free-list". My point is this: If PCTFREE is 10%, that means the block can be up to 90% full, right? Well, if the block happens to be 60% full at the moment, then Oracle knows that this block is not full enough because 60 is less than 90, so it can keep it in the free list. I dont see what PCTUSED is needed, it kind of seems I can accomplish the same with just one parm, that being PCTFREE. But Oracle wouldnt have just put a parm there without any usage, so I guess there's something I dont see... Any ideas/examples? Any good reasoning anywhere? Thanks, maa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson 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). -- Mladen Gogala Oracle DBA -- 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: PCTFREE and PCTUSED
To use your numbers, the block can fill to 90% (100-PCTFREE) at which time it comes off the freelist. If you delete rows until the block falls below 40% used (PCTUSED), the block will go back on the freelist. -Original Message- Sent: Tuesday, November 04, 2003 3:09 PM To: Multiple recipients of list ORACLE-L Suppose I have the following settings which happen to be the defaults as well: PCTFREE 10 PCTUSED 40 I am trying to figure out what PCTUSED is really used for. My book is telling me that is used so that Oracle knows whether to keep a block in the "free-list". My point is this: If PCTFREE is 10%, that means the block can be up to 90% full, right? Well, if the block happens to be 60% full at the moment, then Oracle knows that this block is not full enough because 60 is less than 90, so it can keep it in the free list. I dont see what PCTUSED is needed, it kind of seems I can accomplish the same with just one parm, that being PCTFREE. But Oracle wouldnt have just put a parm there without any usage, so I guess there's something I dont see... Any ideas/examples? Any good reasoning anywhere? Thanks, maa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson 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: Tim Fleury 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: PCTFREE and PCTUSED
Your example block can be 60% full and not be on the free list. Once your block reaches 90% full, it is removed from the free list. It will not be put on the free list again until used space in the block falls below PCTUSED, which is 40% in your example. So, a block fills up, it is removed from the free list, then a couple of rows are deleted and used space falls to 60%. The block is still not on the free list, as the space used has remained above 40%. Guess what can happen when PCTFREE + PCTUSED = 100? Not sure what you're reading, but you should be reading the concepts manual, as it explains this all rather well. HTH Jared Maryann Atkinson <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 11/04/2003 03:09 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: PCTFREE and PCTUSED Suppose I have the following settings which happen to be the defaults as well: PCTFREE 10 PCTUSED 40 I am trying to figure out what PCTUSED is really used for. My book is telling me that is used so that Oracle knows whether to keep a block in the "free-list". My point is this: If PCTFREE is 10%, that means the block can be up to 90% full, right? Well, if the block happens to be 60% full at the moment, then Oracle knows that this block is not full enough because 60 is less than 90, so it can keep it in the free list. I dont see what PCTUSED is needed, it kind of seems I can accomplish the same with just one parm, that being PCTFREE. But Oracle wouldnt have just put a parm there without any usage, so I guess there's something I dont see... Any ideas/examples? Any good reasoning anywhere? Thanks, maa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson 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: PCTFREE and PCTUSED
PCTUSED comes into play when rows are deleted from the block. If enough data is deleted from a block to cause the block to fall below 60% used (PCTUSED), the block goes back on the freelist for subsequent inserts/updates. -Original Message- Sent: Tuesday, November 04, 2003 3:09 PM To: Multiple recipients of list ORACLE-L Suppose I have the following settings which happen to be the defaults as well: PCTFREE 10 PCTUSED 40 I am trying to figure out what PCTUSED is really used for. My book is telling me that is used so that Oracle knows whether to keep a block in the "free-list". My point is this: If PCTFREE is 10%, that means the block can be up to 90% full, right? Well, if the block happens to be 60% full at the moment, then Oracle knows that this block is not full enough because 60 is less than 90, so it can keep it in the free list. I dont see what PCTUSED is needed, it kind of seems I can accomplish the same with just one parm, that being PCTFREE. But Oracle wouldnt have just put a parm there without any usage, so I guess there's something I dont see... Any ideas/examples? Any good reasoning anywhere? Thanks, maa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson 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: Tim Fleury 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).
PCTFREE and PCTUSED
Suppose I have the following settings which happen to be the defaults as well: PCTFREE 10 PCTUSED 40 I am trying to figure out what PCTUSED is really used for. My book is telling me that is used so that Oracle knows whether to keep a block in the "free-list". My point is this: If PCTFREE is 10%, that means the block can be up to 90% full, right? Well, if the block happens to be 60% full at the moment, then Oracle knows that this block is not full enough because 60 is less than 90, so it can keep it in the free list. I dont see what PCTUSED is needed, it kind of seems I can accomplish the same with just one parm, that being PCTFREE. But Oracle wouldnt have just put a parm there without any usage, so I guess there's something I dont see... Any ideas/examples? Any good reasoning anywhere? Thanks, maa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson 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: Calculating new PCTFREE and PCTUSED !!!!!1
I should have explained it the first time, because now I don't
recall exactly what I had in mind.
Probably something along the line of 'if waits on "table fetch continued
row"
were very low, I probably wouldn't bother with unchaining'. But that's
just
me.
I'm by nature lazy and avoid 'unnecessary' work. :)
Jared
"Reddy, Madhusudana" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/21/2003 08:24 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:RE: Calculating new PCTFREE and PCTUSED !1
Thank You all for your replies !!!
Jared, if you dont mind would you please explain " HOW ITS BEING "
ACCESSED"
" .
Thanks again
Madhu
-Original Message-
Sent: Monday, January 20, 2003 8:54 PM
To: Multiple recipients of list ORACLE-L
I might not change either. Chaining can happen
regardless of PCTFREE and PCTUSED.
The answer is 'it depends'. The dependency being
the data and how it's being accessed.
Jared
On Saturday 18 January 2003 15:28, Reddy, Madhusudana wrote:
> Charlie,
>
> Would you please post your PL/SQL code here.I might wanted to
use/tweak
> it .
>
> Also your formula for PCTFREE is pctfree = pctfree + 5 , if table
exceeds
> 5% of chained rows and some DBAs advocate to average row length.
>
> Jared, whats u r idea on this , what would you do to alter PCTFREE or
> PCTUSED ..
>
> Anybody ??
>
> Thanks in advance
> Madhu
>
>
>
>
> -Original Message-
> [mailto:[EMAIL PROTECTED]]
> Sent: Friday, January 17, 2003 3:56 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> What I've done, is I have some PL/SQL code which looks for chained rows.
> When the number of chained rows exceeds 5% it proceeds to unchain the
rows.
> Upon completion it increases the PCTFREE by 5% & decrease PCTUSED by 5%.
> This process continues until they reach values which don't induce
chaining.
> This process runs once a month after our month-end processing completes.
>
>
>
>
>
> DENNIS WILLIAMS
>
>recipients of list ORACLE-L <[EMAIL PROTECTED]>
> UCH.COM> cc:
>
> Sent by: Subject: RE: Calculating
> new PCTFREE and PCTUSED !1
> [EMAIL PROTECTED]
>
>
>
>
>
> 01/17/2003 01:04
>
> PM
>
> Please respond to
>
> ORACLE-L
>
>
>
>
>
>
>
>
>
> Reddy - No I have not used that script. But most of Don's stuff is quite
> good. I thought it might illuminate some issues for you. Sorry if it
didn't
> help.
>The PCTFREE and PCTUSED parameters mainly need tweaked when your data
is
> volatile, when existing rows are updated with additional data. Is your
data
> very volatile?
>
> Dennis Williams
> DBA, 40%OCP
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
>
> -Original Message-
> Sent: Friday, January 17, 2003 12:50 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Dennis,
>
> Have you used the script ??
>
> I have gone thru the material u have pointed , also executed the script
in
> it, but it doesn't make any sense to me . Performance is the issue for
me
> not the SPACE .
>
> here is the script:
>
> - pctused.sql
> - © 1999 by Donald Keith Burleson
> set heading off;
> set pages ;
> set feedback off;
>
> spool pctused.lst;
>
> define spare_rows = 2;
>
> define blksz = 4096; ( I used 8192 )
>
> select
> ' alter table '||owner||'.'||table_name||
> ' pctused
'||least(round(100-((&spare_rows*avg_row_len)/(&blksz/10))),95)||
> ' '||
> ' pctfree '||greatest(round((&spare_rows*avg_row_len)/(&blksz/10)),5)||
> ';'
> from
> dba_tables
> where
> avg_row_len > 1
> and
> avg_row_len < 2000
> and
> table_name not in
> (select table_name from dba_tab_columns b
> where
> data_type in ('RAW','LONG RAW')
> )
> order by owner, table_name
> ;
>
> spool off;
>
> Sample o/p: alter table .TSFDETAIL pctused 95 pctfree 5;
>
> And previous value for PCTFREE is 20 and the chained rows are 1054757
in
> that table..
>
>
>
> Does anyone have good idea to calculate PCTFREE would like to share with
me
> ???
>
>
> I need help !!!
>
> Thanks
> Madhu
>
>
>
> -Original Message-
> Sent: Wednesday, January 15,
RE: Calculating new PCTFREE and PCTUSED !!!!!1
Thank You all for your replies !!!
Jared, if you dont mind would you please explain " HOW ITS BEING " ACCESSED"
" .
Thanks again
Madhu
-Original Message-
Sent: Monday, January 20, 2003 8:54 PM
To: Multiple recipients of list ORACLE-L
I might not change either. Chaining can happen
regardless of PCTFREE and PCTUSED.
The answer is 'it depends'. The dependency being
the data and how it's being accessed.
Jared
On Saturday 18 January 2003 15:28, Reddy, Madhusudana wrote:
> Charlie,
>
> Would you please post your PL/SQL code here.I might wanted to
use/tweak
> it .
>
> Also your formula for PCTFREE is pctfree = pctfree + 5 , if table exceeds
> 5% of chained rows and some DBAs advocate to average row length.
>
> Jared, whats u r idea on this , what would you do to alter PCTFREE or
> PCTUSED ..
>
> Anybody ??
>
> Thanks in advance
> Madhu
>
>
>
>
> -Original Message-
> [mailto:[EMAIL PROTECTED]]
> Sent: Friday, January 17, 2003 3:56 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> What I've done, is I have some PL/SQL code which looks for chained rows.
> When the number of chained rows exceeds 5% it proceeds to unchain the
rows.
> Upon completion it increases the PCTFREE by 5% & decrease PCTUSED by 5%.
> This process continues until they reach values which don't induce
chaining.
> This process runs once a month after our month-end processing completes.
>
>
>
>
>
> DENNIS WILLIAMS
>
>recipients of list ORACLE-L <[EMAIL PROTECTED]>
> UCH.COM> cc:
>
> Sent by: Subject: RE: Calculating
> new PCTFREE and PCTUSED !1
> [EMAIL PROTECTED]
>
>
>
>
>
> 01/17/2003 01:04
>
> PM
>
> Please respond to
>
> ORACLE-L
>
>
>
>
>
>
>
>
>
> Reddy - No I have not used that script. But most of Don's stuff is quite
> good. I thought it might illuminate some issues for you. Sorry if it
didn't
> help.
>The PCTFREE and PCTUSED parameters mainly need tweaked when your data
is
> volatile, when existing rows are updated with additional data. Is your
data
> very volatile?
>
> Dennis Williams
> DBA, 40%OCP
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
>
> -Original Message-
> Sent: Friday, January 17, 2003 12:50 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Dennis,
>
> Have you used the script ??
>
> I have gone thru the material u have pointed , also executed the script in
> it, but it doesn't make any sense to me . Performance is the issue for me
> not the SPACE .
>
> here is the script:
>
> - pctused.sql
> - © 1999 by Donald Keith Burleson
> set heading off;
> set pages ;
> set feedback off;
>
> spool pctused.lst;
>
> define spare_rows = 2;
>
> define blksz = 4096; ( I used 8192 )
>
> select
> ' alter table '||owner||'.'||table_name||
> ' pctused
'||least(round(100-((&spare_rows*avg_row_len)/(&blksz/10))),95)||
> ' '||
> ' pctfree '||greatest(round((&spare_rows*avg_row_len)/(&blksz/10)),5)||
> ';'
> from
> dba_tables
> where
> avg_row_len > 1
> and
> avg_row_len < 2000
> and
> table_name not in
> (select table_name from dba_tab_columns b
> where
> data_type in ('RAW','LONG RAW')
> )
> order by owner, table_name
> ;
>
> spool off;
>
> Sample o/p: alter table .TSFDETAIL pctused 95 pctfree 5;
>
> And previous value for PCTFREE is 20 and the chained rows are 1054757 in
> that table..
>
>
>
> Does anyone have good idea to calculate PCTFREE would like to share with
me
> ???
>
>
> I need help !!!
>
> Thanks
> Madhu
>
>
>
> -Original Message-
> Sent: Wednesday, January 15, 2003 3:26 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Madhu
>Here is a good article that discusses the various aspects:
> http://www.dba-oracle.com/art_pctfree.htm
>
> Dennis Williams
> DBA, 40%OCP
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
>
> -Original Message-
> Sent: Wednesday, January 15, 2003 3:06 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hello All,
> I have found some of the tables are heavily chained in one of the database
> .
> I want to fix them by exp and imp, but before that I would like to have a
> formula or some better method to identify the new PCTFREE and PCTUSE
Re: Calculating new PCTFREE and PCTUSED !!!!!1
I might not change either. Chaining can happen
regardless of PCTFREE and PCTUSED.
The answer is 'it depends'. The dependency being
the data and how it's being accessed.
Jared
On Saturday 18 January 2003 15:28, Reddy, Madhusudana wrote:
> Charlie,
>
> Would you please post your PL/SQL code here.I might wanted to use/tweak
> it .
>
> Also your formula for PCTFREE is pctfree = pctfree + 5 , if table exceeds
> 5% of chained rows and some DBAs advocate to average row length.
>
> Jared, whats u r idea on this , what would you do to alter PCTFREE or
> PCTUSED ..
>
> Anybody ??
>
> Thanks in advance
> Madhu
>
>
>
>
> -Original Message-
> [mailto:[EMAIL PROTECTED]]
> Sent: Friday, January 17, 2003 3:56 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> What I've done, is I have some PL/SQL code which looks for chained rows.
> When the number of chained rows exceeds 5% it proceeds to unchain the rows.
> Upon completion it increases the PCTFREE by 5% & decrease PCTUSED by 5%.
> This process continues until they reach values which don't induce chaining.
> This process runs once a month after our month-end processing completes.
>
>
>
>
>
> DENNIS WILLIAMS
>
>recipients of list ORACLE-L <[EMAIL PROTECTED]>
> UCH.COM> cc:
>
> Sent by: Subject: RE: Calculating
> new PCTFREE and PCTUSED !1
> [EMAIL PROTECTED]
>
>
>
>
>
> 01/17/2003 01:04
>
> PM
>
> Please respond to
>
> ORACLE-L
>
>
>
>
>
>
>
>
>
> Reddy - No I have not used that script. But most of Don's stuff is quite
> good. I thought it might illuminate some issues for you. Sorry if it didn't
> help.
>The PCTFREE and PCTUSED parameters mainly need tweaked when your data is
> volatile, when existing rows are updated with additional data. Is your data
> very volatile?
>
> Dennis Williams
> DBA, 40%OCP
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
>
> -Original Message-
> Sent: Friday, January 17, 2003 12:50 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Dennis,
>
> Have you used the script ??
>
> I have gone thru the material u have pointed , also executed the script in
> it, but it doesn't make any sense to me . Performance is the issue for me
> not the SPACE .
>
> here is the script:
>
> - pctused.sql
> - © 1999 by Donald Keith Burleson
> set heading off;
> set pages ;
> set feedback off;
>
> spool pctused.lst;
>
> define spare_rows = 2;
>
> define blksz = 4096; ( I used 8192 )
>
> select
> ' alter table '||owner||'.'||table_name||
> ' pctused '||least(round(100-((&spare_rows*avg_row_len)/(&blksz/10))),95)||
> ' '||
> ' pctfree '||greatest(round((&spare_rows*avg_row_len)/(&blksz/10)),5)||
> ';'
> from
> dba_tables
> where
> avg_row_len > 1
> and
> avg_row_len < 2000
> and
> table_name not in
> (select table_name from dba_tab_columns b
> where
> data_type in ('RAW','LONG RAW')
> )
> order by owner, table_name
> ;
>
> spool off;
>
> Sample o/p: alter table .TSFDETAIL pctused 95 pctfree 5;
>
> And previous value for PCTFREE is 20 and the chained rows are 1054757 in
> that table..
>
>
>
> Does anyone have good idea to calculate PCTFREE would like to share with me
> ???
>
>
> I need help !!!
>
> Thanks
> Madhu
>
>
>
> -Original Message-
> Sent: Wednesday, January 15, 2003 3:26 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Madhu
>Here is a good article that discusses the various aspects:
> http://www.dba-oracle.com/art_pctfree.htm
>
> Dennis Williams
> DBA, 40%OCP
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
>
> -Original Message-
> Sent: Wednesday, January 15, 2003 3:06 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hello All,
> I have found some of the tables are heavily chained in one of the database
> .
> I want to fix them by exp and imp, but before that I would like to have a
> formula or some better method to identify the new PCTFREE and PCTUSED for
> each individual table.
>
> Many of you have might have done this in the past , would you pl share your
> ideas on this ??
>
> Thanks in advance,
> Madhu
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Au
RE: Calculating new PCTFREE and PCTUSED !!!!!1
No, I won't post it because I downloaded it from another list member's
website (??) years ago.
Ask me again on Monday, when I'm back in the office & will be able to give
a better (more detailed) answer.
"Reddy, Madhusudana"
estbuy.com> cc:
Sent by: Subject: RE: Calculating new
PCTFREE and PCTUSED !1
[EMAIL PROTECTED]
01/18/2003 03:28 PM
Please respond to
ORACLE-L
Charlie,
Would you please post your PL/SQL code here.I might wanted to use/tweak
it .
Also your formula for PCTFREE is pctfree = pctfree + 5 , if table exceeds
5%
of chained rows and some DBAs advocate to average row length.
Jared, whats u r idea on this , what would you do to alter PCTFREE or
PCTUSED ..
Anybody ??
Thanks in advance
Madhu
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Friday, January 17, 2003 3:56 PM
To: Multiple recipients of list ORACLE-L
What I've done, is I have some PL/SQL code which looks for chained rows.
When the number of chained rows exceeds 5% it proceeds to unchain the rows.
Upon completion it increases the PCTFREE by 5% & decrease PCTUSED by 5%.
This process continues until they reach values which don't induce chaining.
This process runs once a month after our month-end processing completes.
DENNIS WILLIAMS
UCH.COM> cc:
Sent by: Subject: RE: Calculating
new
PCTFREE and PCTUSED !1
[EMAIL PROTECTED]
01/17/2003 01:04
PM
Please respond to
ORACLE-L
Reddy - No I have not used that script. But most of Don's stuff is quite
good. I thought it might illuminate some issues for you. Sorry if it didn't
help.
The PCTFREE and PCTUSED parameters mainly need tweaked when your data is
volatile, when existing rows are updated with additional data. Is your data
very volatile?
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Friday, January 17, 2003 12:50 PM
To: Multiple recipients of list ORACLE-L
Dennis,
Have you used the script ??
I have gone thru the material u have pointed , also executed the script in
it, but it doesn't make any sense to me . Performance is the issue for me
not the SPACE .
here is the script:
- pctused.sql
- © 1999 by Donald Keith Burleson
set heading off;
set pages ;
set feedback off;
spool pctused.lst;
define spare_rows = 2;
define blksz = 4096; ( I used 8192 )
select
' alter table '||owner||'.'||table_name||
' pctused '||least(round(100-((&spare_rows*avg_row_len)/(&blksz/10))),95)||
' '||
' pctfree '||greatest(round((&spare_rows*avg_row_len)/(&blksz/10)),5)||
';'
from
dba_tables
where
avg_row_len > 1
and
avg_row_len < 2000
and
table_name not in
(select table_name from dba_tab_columns b
where
data_type in ('RAW','LONG RAW')
)
order by owner, table_name
;
spool off;
Sample o/p: alter table .TSFDETAIL pctused 95 pctfree 5;
And previous value for PCTFREE is 20 and the chained rows are 1054757 in
that table..
Does anyone have good idea to calculate PCTFREE would like to share with me
???
I need help !!!
Thanks
Madhu
-Original Message-
Sent: Wednesday, January 15, 2003 3:26 PM
To: Multiple recipients of list ORACLE
RE: Calculating new PCTFREE and PCTUSED !!!!!1
Charlie,
Would you please post your PL/SQL code here.I might wanted to use/tweak
it .
Also your formula for PCTFREE is pctfree = pctfree + 5 , if table exceeds 5%
of chained rows and some DBAs advocate to average row length.
Jared, whats u r idea on this , what would you do to alter PCTFREE or
PCTUSED ..
Anybody ??
Thanks in advance
Madhu
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Friday, January 17, 2003 3:56 PM
To: Multiple recipients of list ORACLE-L
What I've done, is I have some PL/SQL code which looks for chained rows.
When the number of chained rows exceeds 5% it proceeds to unchain the rows.
Upon completion it increases the PCTFREE by 5% & decrease PCTUSED by 5%.
This process continues until they reach values which don't induce chaining.
This process runs once a month after our month-end processing completes.
DENNIS WILLIAMS
UCH.COM> cc:
Sent by: Subject: RE: Calculating new
PCTFREE and PCTUSED !1
[EMAIL PROTECTED]
01/17/2003 01:04
PM
Please respond to
ORACLE-L
Reddy - No I have not used that script. But most of Don's stuff is quite
good. I thought it might illuminate some issues for you. Sorry if it didn't
help.
The PCTFREE and PCTUSED parameters mainly need tweaked when your data is
volatile, when existing rows are updated with additional data. Is your data
very volatile?
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Friday, January 17, 2003 12:50 PM
To: Multiple recipients of list ORACLE-L
Dennis,
Have you used the script ??
I have gone thru the material u have pointed , also executed the script in
it, but it doesn't make any sense to me . Performance is the issue for me
not the SPACE .
here is the script:
- pctused.sql
- © 1999 by Donald Keith Burleson
set heading off;
set pages ;
set feedback off;
spool pctused.lst;
define spare_rows = 2;
define blksz = 4096; ( I used 8192 )
select
' alter table '||owner||'.'||table_name||
' pctused '||least(round(100-((&spare_rows*avg_row_len)/(&blksz/10))),95)||
' '||
' pctfree '||greatest(round((&spare_rows*avg_row_len)/(&blksz/10)),5)||
';'
from
dba_tables
where
avg_row_len > 1
and
avg_row_len < 2000
and
table_name not in
(select table_name from dba_tab_columns b
where
data_type in ('RAW','LONG RAW')
)
order by owner, table_name
;
spool off;
Sample o/p: alter table .TSFDETAIL pctused 95 pctfree 5;
And previous value for PCTFREE is 20 and the chained rows are 1054757 in
that table..
Does anyone have good idea to calculate PCTFREE would like to share with me
???
I need help !!!
Thanks
Madhu
-Original Message-
Sent: Wednesday, January 15, 2003 3:26 PM
To: Multiple recipients of list ORACLE-L
Madhu
Here is a good article that discusses the various aspects:
http://www.dba-oracle.com/art_pctfree.htm
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, January 15, 2003 3:06 PM
To: Multiple recipients of list ORACLE-L
Hello All,
I have found some of the tables are heavily chained in one of the database
.
I want to fix them by exp and imp, but before that I would like to have a
formula or some better method to identify the new PCTFREE and PCTUSED for
each individual table.
Many of you have might have done this in the past , would you pl share your
ideas on this ??
Thanks in advance,
Madhu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Reddy, Madhusudana
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: DENNIS WILLIAMS
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 contain
Re: Calculating new PCTFREE and PCTUSED !!!!!1
Charlie,
Do you check 'table fetch continued row' stats before doing so?
If you're not waiting on chained rows, or the waits are
negligible, why bother?
Jared
On Friday 17 January 2003 13:56, [EMAIL PROTECTED] wrote:
> What I've done, is I have some PL/SQL code which looks for chained rows.
> When the number of chained rows exceeds 5% it proceeds to unchain the rows.
> Upon completion it increases the PCTFREE by 5% & decrease PCTUSED by 5%.
> This process continues until they reach values which don't induce chaining.
> This process runs once a month after our month-end processing completes.
>
>
>
>
> DENNIS WILLIAMS
>recipients of list ORACLE-L <[EMAIL PROTECTED]> UCH.COM>
> cc:
> Sent by: Subject: RE: Calculating
> new PCTFREE and PCTUSED !1 [EMAIL PROTECTED]
>
>
> 01/17/2003 01:04
> PM
> Please respond to
> ORACLE-L
>
>
>
>
>
>
> Reddy - No I have not used that script. But most of Don's stuff is quite
> good. I thought it might illuminate some issues for you. Sorry if it didn't
> help.
>The PCTFREE and PCTUSED parameters mainly need tweaked when your data is
> volatile, when existing rows are updated with additional data. Is your data
> very volatile?
>
> Dennis Williams
> DBA, 40%OCP
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
>
> -Original Message-
> Sent: Friday, January 17, 2003 12:50 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Dennis,
>
> Have you used the script ??
>
> I have gone thru the material u have pointed , also executed the script in
> it, but it doesn't make any sense to me . Performance is the issue for me
> not the SPACE .
>
> here is the script:
>
> - pctused.sql
> - © 1999 by Donald Keith Burleson
> set heading off;
> set pages ;
> set feedback off;
>
> spool pctused.lst;
>
> define spare_rows = 2;
>
> define blksz = 4096; ( I used 8192 )
>
> select
> ' alter table '||owner||'.'||table_name||
> ' pctused '||least(round(100-((&spare_rows*avg_row_len)/(&blksz/10))),95)||
> ' '||
> ' pctfree '||greatest(round((&spare_rows*avg_row_len)/(&blksz/10)),5)||
> ';'
> from
> dba_tables
> where
> avg_row_len > 1
> and
> avg_row_len < 2000
> and
> table_name not in
> (select table_name from dba_tab_columns b
> where
> data_type in ('RAW','LONG RAW')
> )
> order by owner, table_name
> ;
>
> spool off;
>
> Sample o/p: alter table .TSFDETAIL pctused 95 pctfree 5;
>
> And previous value for PCTFREE is 20 and the chained rows are 1054757 in
> that table..
>
>
>
> Does anyone have good idea to calculate PCTFREE would like to share with me
> ???
>
>
> I need help !!!
>
> Thanks
> Madhu
>
>
>
> -Original Message-
> Sent: Wednesday, January 15, 2003 3:26 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Madhu
>Here is a good article that discusses the various aspects:
> http://www.dba-oracle.com/art_pctfree.htm
>
> Dennis Williams
> DBA, 40%OCP
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
>
> -Original Message-
> Sent: Wednesday, January 15, 2003 3:06 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hello All,
> I have found some of the tables are heavily chained in one of the database
> .
> I want to fix them by exp and imp, but before that I would like to have a
> formula or some better method to identify the new PCTFREE and PCTUSED for
> each individual table.
>
> Many of you have might have done this in the past , would you pl share your
> ideas on this ??
>
> Thanks in advance,
> Madhu
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Reddy, Madhusudana
> 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
> --
> A
RE: Calculating new PCTFREE and PCTUSED !!!!!1
Wow Charlie - I do somewhat the same thing, but do it manually, when the
mood strikes me. But most of my applications don't update the data,
increasing the size, hence my question to Reddy about how much of that his
application does.
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Friday, January 17, 2003 3:56 PM
To: Multiple recipients of list ORACLE-L
What I've done, is I have some PL/SQL code which looks for chained rows.
When the number of chained rows exceeds 5% it proceeds to unchain the rows.
Upon completion it increases the PCTFREE by 5% & decrease PCTUSED by 5%.
This process continues until they reach values which don't induce chaining.
This process runs once a month after our month-end processing completes.
DENNIS WILLIAMS
UCH.COM> cc:
Sent by: Subject: RE: Calculating new
PCTFREE and PCTUSED !1
[EMAIL PROTECTED]
01/17/2003 01:04
PM
Please respond to
ORACLE-L
Reddy - No I have not used that script. But most of Don's stuff is quite
good. I thought it might illuminate some issues for you. Sorry if it didn't
help.
The PCTFREE and PCTUSED parameters mainly need tweaked when your data is
volatile, when existing rows are updated with additional data. Is your data
very volatile?
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Friday, January 17, 2003 12:50 PM
To: Multiple recipients of list ORACLE-L
Dennis,
Have you used the script ??
I have gone thru the material u have pointed , also executed the script in
it, but it doesn't make any sense to me . Performance is the issue for me
not the SPACE .
here is the script:
- pctused.sql
- © 1999 by Donald Keith Burleson
set heading off;
set pages ;
set feedback off;
spool pctused.lst;
define spare_rows = 2;
define blksz = 4096; ( I used 8192 )
select
' alter table '||owner||'.'||table_name||
' pctused '||least(round(100-((&spare_rows*avg_row_len)/(&blksz/10))),95)||
' '||
' pctfree '||greatest(round((&spare_rows*avg_row_len)/(&blksz/10)),5)||
';'
from
dba_tables
where
avg_row_len > 1
and
avg_row_len < 2000
and
table_name not in
(select table_name from dba_tab_columns b
where
data_type in ('RAW','LONG RAW')
)
order by owner, table_name
;
spool off;
Sample o/p: alter table .TSFDETAIL pctused 95 pctfree 5;
And previous value for PCTFREE is 20 and the chained rows are 1054757 in
that table..
Does anyone have good idea to calculate PCTFREE would like to share with me
???
I need help !!!
Thanks
Madhu
-Original Message-
Sent: Wednesday, January 15, 2003 3:26 PM
To: Multiple recipients of list ORACLE-L
Madhu
Here is a good article that discusses the various aspects:
http://www.dba-oracle.com/art_pctfree.htm
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, January 15, 2003 3:06 PM
To: Multiple recipients of list ORACLE-L
Hello All,
I have found some of the tables are heavily chained in one of the database
.
I want to fix them by exp and imp, but before that I would like to have a
formula or some better method to identify the new PCTFREE and PCTUSED for
each individual table.
Many of you have might have done this in the past , would you pl share your
ideas on this ??
Thanks in advance,
Madhu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Reddy, Madhusudana
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: DENNIS WILLIAMS
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 l
RE: Calculating new PCTFREE and PCTUSED !!!!!1
What I've done, is I have some PL/SQL code which looks for chained rows.
When the number of chained rows exceeds 5% it proceeds to unchain the rows.
Upon completion it increases the PCTFREE by 5% & decrease PCTUSED by 5%.
This process continues until they reach values which don't induce chaining.
This process runs once a month after our month-end processing completes.
DENNIS WILLIAMS
UCH.COM> cc:
Sent by: Subject: RE: Calculating new PCTFREE
and PCTUSED !1
[EMAIL PROTECTED]
01/17/2003 01:04
PM
Please respond to
ORACLE-L
Reddy - No I have not used that script. But most of Don's stuff is quite
good. I thought it might illuminate some issues for you. Sorry if it didn't
help.
The PCTFREE and PCTUSED parameters mainly need tweaked when your data is
volatile, when existing rows are updated with additional data. Is your data
very volatile?
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Friday, January 17, 2003 12:50 PM
To: Multiple recipients of list ORACLE-L
Dennis,
Have you used the script ??
I have gone thru the material u have pointed , also executed the script in
it, but it doesn't make any sense to me . Performance is the issue for me
not the SPACE .
here is the script:
- pctused.sql
- © 1999 by Donald Keith Burleson
set heading off;
set pages ;
set feedback off;
spool pctused.lst;
define spare_rows = 2;
define blksz = 4096; ( I used 8192 )
select
' alter table '||owner||'.'||table_name||
' pctused '||least(round(100-((&spare_rows*avg_row_len)/(&blksz/10))),95)||
' '||
' pctfree '||greatest(round((&spare_rows*avg_row_len)/(&blksz/10)),5)||
';'
from
dba_tables
where
avg_row_len > 1
and
avg_row_len < 2000
and
table_name not in
(select table_name from dba_tab_columns b
where
data_type in ('RAW','LONG RAW')
)
order by owner, table_name
;
spool off;
Sample o/p: alter table .TSFDETAIL pctused 95 pctfree 5;
And previous value for PCTFREE is 20 and the chained rows are 1054757 in
that table..
Does anyone have good idea to calculate PCTFREE would like to share with me
???
I need help !!!
Thanks
Madhu
-Original Message-
Sent: Wednesday, January 15, 2003 3:26 PM
To: Multiple recipients of list ORACLE-L
Madhu
Here is a good article that discusses the various aspects:
http://www.dba-oracle.com/art_pctfree.htm
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, January 15, 2003 3:06 PM
To: Multiple recipients of list ORACLE-L
Hello All,
I have found some of the tables are heavily chained in one of the database
.
I want to fix them by exp and imp, but before that I would like to have a
formula or some better method to identify the new PCTFREE and PCTUSED for
each individual table.
Many of you have might have done this in the past , would you pl share your
ideas on this ??
Thanks in advance,
Madhu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Reddy, Madhusudana
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:
RE: Calculating new PCTFREE and PCTUSED !!!!!1
Dennis,
My database is of size 960 Gig for now and its a heavy OLTP with high DML
activity on tables , we are observing some ORA-00600 errors these days due
to chained rows in the tables . Also we all know chained rows cause
performance issues . I wanted to fix this ASAP and also would like to alter
the PCTFREE , so I want some ideas from all of you to find out a proper
value ..
Thanks
Madhu
-Original Message-
Sent: Friday, January 17, 2003 3:04 PM
To: Multiple recipients of list ORACLE-L
Reddy - No I have not used that script. But most of Don's stuff is quite
good. I thought it might illuminate some issues for you. Sorry if it didn't
help.
The PCTFREE and PCTUSED parameters mainly need tweaked when your data is
volatile, when existing rows are updated with additional data. Is your data
very volatile?
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Friday, January 17, 2003 12:50 PM
To: Multiple recipients of list ORACLE-L
Dennis,
Have you used the script ??
I have gone thru the material u have pointed , also executed the script in
it, but it doesn't make any sense to me . Performance is the issue for me
not the SPACE .
here is the script:
- pctused.sql
- © 1999 by Donald Keith Burleson
set heading off;
set pages ;
set feedback off;
spool pctused.lst;
define spare_rows = 2;
define blksz = 4096; ( I used 8192 )
select
' alter table '||owner||'.'||table_name||
' pctused '||least(round(100-((&spare_rows*avg_row_len)/(&blksz/10))),95)||
' '||
' pctfree '||greatest(round((&spare_rows*avg_row_len)/(&blksz/10)),5)||
';'
from
dba_tables
where
avg_row_len > 1
and
avg_row_len < 2000
and
table_name not in
(select table_name from dba_tab_columns b
where
data_type in ('RAW','LONG RAW')
)
order by owner, table_name
;
spool off;
Sample o/p: alter table .TSFDETAIL pctused 95 pctfree 5;
And previous value for PCTFREE is 20 and the chained rows are 1054757 in
that table..
Does anyone have good idea to calculate PCTFREE would like to share with me
???
I need help !!!
Thanks
Madhu
-Original Message-
Sent: Wednesday, January 15, 2003 3:26 PM
To: Multiple recipients of list ORACLE-L
Madhu
Here is a good article that discusses the various aspects:
http://www.dba-oracle.com/art_pctfree.htm
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, January 15, 2003 3:06 PM
To: Multiple recipients of list ORACLE-L
Hello All,
I have found some of the tables are heavily chained in one of the database .
I want to fix them by exp and imp, but before that I would like to have a
formula or some better method to identify the new PCTFREE and PCTUSED for
each individual table.
Many of you have might have done this in the past , would you pl share your
ideas on this ??
Thanks in advance,
Madhu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Reddy, Madhusudana
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: DENNIS WILLIAMS
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: Reddy, Madhusudana
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: Calculating new PCTFREE and PCTUSED !!!!!1
Reddy - No I have not used that script. But most of Don's stuff is quite
good. I thought it might illuminate some issues for you. Sorry if it didn't
help.
The PCTFREE and PCTUSED parameters mainly need tweaked when your data is
volatile, when existing rows are updated with additional data. Is your data
very volatile?
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Friday, January 17, 2003 12:50 PM
To: Multiple recipients of list ORACLE-L
Dennis,
Have you used the script ??
I have gone thru the material u have pointed , also executed the script in
it, but it doesn't make any sense to me . Performance is the issue for me
not the SPACE .
here is the script:
- pctused.sql
- © 1999 by Donald Keith Burleson
set heading off;
set pages ;
set feedback off;
spool pctused.lst;
define spare_rows = 2;
define blksz = 4096; ( I used 8192 )
select
' alter table '||owner||'.'||table_name||
' pctused '||least(round(100-((&spare_rows*avg_row_len)/(&blksz/10))),95)||
' '||
' pctfree '||greatest(round((&spare_rows*avg_row_len)/(&blksz/10)),5)||
';'
from
dba_tables
where
avg_row_len > 1
and
avg_row_len < 2000
and
table_name not in
(select table_name from dba_tab_columns b
where
data_type in ('RAW','LONG RAW')
)
order by owner, table_name
;
spool off;
Sample o/p: alter table .TSFDETAIL pctused 95 pctfree 5;
And previous value for PCTFREE is 20 and the chained rows are 1054757 in
that table..
Does anyone have good idea to calculate PCTFREE would like to share with me
???
I need help !!!
Thanks
Madhu
-Original Message-
Sent: Wednesday, January 15, 2003 3:26 PM
To: Multiple recipients of list ORACLE-L
Madhu
Here is a good article that discusses the various aspects:
http://www.dba-oracle.com/art_pctfree.htm
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, January 15, 2003 3:06 PM
To: Multiple recipients of list ORACLE-L
Hello All,
I have found some of the tables are heavily chained in one of the database .
I want to fix them by exp and imp, but before that I would like to have a
formula or some better method to identify the new PCTFREE and PCTUSED for
each individual table.
Many of you have might have done this in the past , would you pl share your
ideas on this ??
Thanks in advance,
Madhu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Reddy, Madhusudana
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: DENNIS WILLIAMS
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: Reddy, Madhusudana
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: DENNIS WILLIAMS
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: Calculating new PCTFREE and PCTUSED !!!!!1
Dennis,
Have you used the script ??
I have gone thru the material u have pointed , also executed the script in
it, but it doesn't make any sense to me . Performance is the issue for me
not the SPACE .
here is the script:
- pctused.sql
- © 1999 by Donald Keith Burleson
set heading off;
set pages ;
set feedback off;
spool pctused.lst;
define spare_rows = 2;
define blksz = 4096; ( I used 8192 )
select
' alter table '||owner||'.'||table_name||
' pctused '||least(round(100-((&spare_rows*avg_row_len)/(&blksz/10))),95)||
' '||
' pctfree '||greatest(round((&spare_rows*avg_row_len)/(&blksz/10)),5)||
';'
from
dba_tables
where
avg_row_len > 1
and
avg_row_len < 2000
and
table_name not in
(select table_name from dba_tab_columns b
where
data_type in ('RAW','LONG RAW')
)
order by owner, table_name
;
spool off;
Sample o/p: alter table .TSFDETAIL pctused 95 pctfree 5;
And previous value for PCTFREE is 20 and the chained rows are 1054757 in
that table..
Does anyone have good idea to calculate PCTFREE would like to share with me
???
I need help !!!
Thanks
Madhu
-Original Message-
Sent: Wednesday, January 15, 2003 3:26 PM
To: Multiple recipients of list ORACLE-L
Madhu
Here is a good article that discusses the various aspects:
http://www.dba-oracle.com/art_pctfree.htm
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, January 15, 2003 3:06 PM
To: Multiple recipients of list ORACLE-L
Hello All,
I have found some of the tables are heavily chained in one of the database .
I want to fix them by exp and imp, but before that I would like to have a
formula or some better method to identify the new PCTFREE and PCTUSED for
each individual table.
Many of you have might have done this in the past , would you pl share your
ideas on this ??
Thanks in advance,
Madhu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Reddy, Madhusudana
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: DENNIS WILLIAMS
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: Reddy, Madhusudana
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: Calculating new PCTFREE and PCTUSED !!!!!1
You could also try these tables with my unchainer product. Tell me what you think. See details at www.solv-it.net/unchainer.asp Regards : Ferenc - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, January 16, 2003 8:05 AM > Hello All, > I have found some of the tables are heavily chained in one of the database . > I want to fix them by exp and imp, but before that I would like to have a > formula or some better method to identify the new PCTFREE and PCTUSED for > each individual table. > > Many of you have might have done this in the past , would you pl share your > ideas on this ?? > > Thanks in advance, > Madhu > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Reddy, Madhusudana > 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: Ferenc Home 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: Calculating new PCTFREE and PCTUSED !!!!!1
Madhu Here is a good article that discusses the various aspects: http://www.dba-oracle.com/art_pctfree.htm Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, January 15, 2003 3:06 PM To: Multiple recipients of list ORACLE-L Hello All, I have found some of the tables are heavily chained in one of the database . I want to fix them by exp and imp, but before that I would like to have a formula or some better method to identify the new PCTFREE and PCTUSED for each individual table. Many of you have might have done this in the past , would you pl share your ideas on this ?? Thanks in advance, Madhu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reddy, Madhusudana 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: DENNIS WILLIAMS 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).
Calculating new PCTFREE and PCTUSED !!!!!1
Hello All, I have found some of the tables are heavily chained in one of the database . I want to fix them by exp and imp, but before that I would like to have a formula or some better method to identify the new PCTFREE and PCTUSED for each individual table. Many of you have might have done this in the past , would you pl share your ideas on this ?? Thanks in advance, Madhu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reddy, Madhusudana 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: PCTFREE and PCTUSED
Thanks for the answer ANJO, "Besides that pcfree/pctused will determine when blocks are put on the freelist and when not. So probably what you want is multiple freelists to take care of single freelist contention." Let me ask you something about this. The problem of having a small gap between PCTFREE/PCTUSED is that when a DML hits a block, there's a good chance that this block has to be moved in or out from one of the freelists. This would generate contention on the HEADER BLOCK of the table (where ALL the freelists reside). So I think that by increasing the number of freelists I wouldn't be able to eliminate the contention (BBW waits) over the segment header. Am I right about this? I assume that there must be some DML on this table, I really cant tell as the audit is not on. How can I find out the concurrent number of updates in a table? db_block_size=8K thank you Pablo ___ Do You Yahoo!? Yahoo! Messenger Comunicación instantánea gratis con tu gente. http://messenger.yahoo.es -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Pablo=20ksksksk?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: PCTFREE and PCTUSED
Bad things will happen, it just a question of how expensive these waits are. I have a feeling that they don't contribute much to the overall performance problem. Besides that pcfree/pctused will determine when blocks are put on the freelist and when not. So probably what you want is multiple freelists to take care of single freelist contention. Are there any inserts happening on this table ? How many concurrent updates are going on ? And what is the blocksize. You might be running out of transaction freelists . Anjo. Pablo ksksksk wrote: > I see, Charlie. > Thanks for answering. > > I'm not trying to avoid chained rows, I think that > Buffer busy waits may be ocurring in the header of > this segment because the gap between the PCTUSED and > PCTFREE might be too small. > What I want is to check this and all tables as well > and set this GAP correctly (above the average row size > of the table). > > what do you think? > > BTW > would you mind sharing your PL/SQL code with me? > > Thanks > > ___ > Do You Yahoo!? > Yahoo! Messenger > Comunicación instantánea gratis con tu gente. > http://messenger.yahoo.es > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: =?iso-8859-1?q?Pablo=20ksksksk?= > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: PCTFREE and PCTUSED
I see, Charlie. Thanks for answering. I'm not trying to avoid chained rows, I think that Buffer busy waits may be ocurring in the header of this segment because the gap between the PCTUSED and PCTFREE might be too small. What I want is to check this and all tables as well and set this GAP correctly (above the average row size of the table). what do you think? BTW would you mind sharing your PL/SQL code with me? Thanks ___ Do You Yahoo!? Yahoo! Messenger Comunicación instantánea gratis con tu gente. http://messenger.yahoo.es -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Pablo=20ksksksk?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: PCTFREE and PCTUSED
I have some PL/SQL code which looks for chained rows once a week and unchains them when the chained rows count exceeds 5% of the total number of rows. Inside this code for each table where the unchaining occurs I increase PCTFREE by 5 and decrease PCT used by 5. The goal is to achieve equilibrium where chained rows no longer occur within this table. Pablo ksksksk wrote: > > How can I calculate the values I should set PCTFREE > and PCTUSED for a table that has already been created. > > can I use the statistics for that? > > thank you. > Pablo > > ___ > Do You Yahoo!? > Yahoo! Messenger > Comunicación instantánea gratis con tu gente. > http://messenger.yahoo.es > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: =?iso-8859-1?q?Pablo=20ksksksk?= > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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). -- Charlie Mengler Maintenance Warehouse [EMAIL PROTECTED] 10641 Scripps Summit Ct. 858-831-2229 San Diego, CA 92131 Lead, follow, or at least have the courtesy to get out of my way! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
PCTFREE and PCTUSED
How can I calculate the values I should set PCTFREE and PCTUSED for a table that has already been created. can I use the statistics for that? thank you. Pablo ___ Do You Yahoo!? Yahoo! Messenger Comunicación instantánea gratis con tu gente. http://messenger.yahoo.es -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Pablo=20ksksksk?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Problem in setting PCTFREE and PCTUSED
You can not make an intelligent guess at what these values should be without knowing: 1. Average Row Length 2. Blocks Size 3. Average Rows/Block 4. How active the table is for insert, deletes, updates. 5. Average size of updates (1,2,3 columns, 1 byte, 10 bytes) Without these pieces of information, you just as well keep the defaults, it is just mere guessing to set them to anything else. "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Tuesday, October 02, 2001 7:40 AM To: Multiple recipients of list ORACLE-L Hello ! After having defined all the tables, now I have to parametrize each table. I have some tables that every day must be deleted and reloaded through sql loader. I think to set PCTFREE = 0, but I don't know if I have to specify a particular value for PCTUSED. Anybody can give an advice about which value set PCTUSED ? Thanks in advance, Andrea -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Quaglio Andrea INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Problem in setting PCTFREE and PCTUSED
Andrea, If it were me and I had tables that were reloaded every day, I would set the following: PCTFREE=5 : Since you are not doing deletes & inserts, keep this small PCTUSED=95 : Since you are not doing updates, pack the data right in tight. PCTINCREASE=0 : I ALWAYS set this to 0. and, make sure that the INITIAL extent size was large enough to hold all of the data. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, October 02, 2001 7:40 AM To: Multiple recipients of list ORACLE-L Hello ! After having defined all the tables, now I have to parametrize each table. I have some tables that every day must be deleted and reloaded through sql loader. I think to set PCTFREE = 0, but I don't know if I have to specify a particular value for PCTUSED. Anybody can give an advice about which value set PCTUSED ? Thanks in advance, Andrea -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Quaglio Andrea INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Problem in setting PCTFREE and PCTUSED
There are no strict rules, but its all about trying to reduce the amount of wasted space under the high water mark for the table. That wasted space is when you have free'd up some space in a block but the block is still deemed sufficiently "full" for it not be considered for new data. This is where PCTUSED comes in. hth connor --- Quaglio Andrea <[EMAIL PROTECTED]> wrote: > Hello ! > After having defined all the tables, now I have to > parametrize each table. > I have some tables that every day must be deleted > and reloaded through sql > loader. > I think to set PCTFREE = 0, but I don't know if I > have to specify a > particular value for PCTUSED. > Anybody can give an advice about which value set > PCTUSED ? > > Thanks in advance, > Andrea > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Quaglio Andrea > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > 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). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) "Some days you're the pigeon, some days you're the statue" Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Problem in setting PCTFREE and PCTUSED
Are you deleting ALL records before the load? If so, why not truncate instead? Are you deleting, inserting, or updating these tables other than with the load? If there are no updates that increase row size, pctfree=0 makes sense. If there are not a lot of updates that dramatically reduce row size and no deletes except prior to the load, pctused is moot since the blocks will never go back on the freelist anyway. If the relative volume of deletes or inserts is small, then pctused=0 might be preferable. That would reduce block shuffling on and off the freelist(s) and simply allocate new blocks for any inserts. The tradeoff is space and data density. The table would grow as inserts are performed and the density of data in the existing blocks would decrease as deletes are performed. Extent sizing may matter - to reduce or eliminate dynamic extent allocation as inserts are performed. -Don Granaman [OraSaurus - Honk if you remember UFI!] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, October 02, 2001 6:40 AM > Hello ! > After having defined all the tables, now I have to parametrize each table. > I have some tables that every day must be deleted and reloaded through sql > loader. > I think to set PCTFREE = 0, but I don't know if I have to specify a > particular value for PCTUSED. > Anybody can give an advice about which value set PCTUSED ? > > Thanks in advance, > Andrea -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don Granaman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Problem in setting PCTFREE and PCTUSED
Hello ! After having defined all the tables, now I have to parametrize each table. I have some tables that every day must be deleted and reloaded through sql loader. I think to set PCTFREE = 0, but I don't know if I have to specify a particular value for PCTUSED. Anybody can give an advice about which value set PCTUSED ? Thanks in advance, Andrea -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Quaglio Andrea INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
