Oracle-L: Re: PCTFREE and PCTUSED

From: Mladen Gogala <mgogala_at_adelphia.net>
Date: Fri, 07 Nov 2003 16:49:26 -0800
Message-ID: <F001.005D6036.20031107164926@fatcity.com>

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

Received on Fri Nov 07 2003 - 18:49:26 CST