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" <ORACLE-L_at_fatcity.com> 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" <ORACLE-L_at_fatcity.com>
> > 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: maryann_30_at_yahoo.com
> > > >
> > > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > > > San Diego, California -- Mailing list and web hosting
services
> > >
> ---------------------------------------------------------------------
> > > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > > (or the name of mailing list you want to be removed from). You may
> > > > also send the HELP command for other information (like subscribing).
> > > >
> > >
> > > 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: mladen_at_wangtrading.com
> > >
> > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > > San Diego, California -- Mailing list and web hosting services
> > > ---------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You may
> > > also send the HELP command for other information (like subscribing).
> > >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Richard Foote
> > INET: richard.foote_at_bigpond.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
>
> --
> Mladen Gogala
> Oracle DBA
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mladen Gogala
> INET: mgogala_at_adelphia.net
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Richard Foote
  INET: richard.foote_at_bigpond.com

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