Re: PCTUSED??

You might consider Dave Ensor's suggestion to set PCTUSED = 0! As inserts occur, entire blocks are filled, but never put back on the free list until they are completely empty - saving on the on/off/on/off/... freelist shuffle that would mix records from different periods in the blocks and making the purge process handle more blocks. There are other advantages also such as handling fewer blocks on date range scans.

If the data is already "well ordered" in the blocks, this won't make a difference though - as mentioned by Greg. All those other points are good ones also. Perhaps the "ideal" solution (other than cost!) is to use rolling temporal partitions and perform the purge by partition maintenance operations.

-Don Granaman
[Certifiable OraSaurus]

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, June 06, 2001 6:56 PM

> > I have big growing tables has default PCT_USED 40%, But we delete data
> some
> > times one year or 2yrs data. What is optimum value for this parameter??
I
> > may consider my database is oltp. So how can decide a correct value for
> > this???
>
> PCT_USED might not have any effect for you at all.
>
> If you keep inserting rows by date, the blocks will fill with rows where
the
> date is the same for every row in the block, or close. Then if the only
> time you delete is when you delete an entire year's worth of data, entire
> blocks will become empty. That's going to cause them to go on the free
list
> no matter what you set PCT_USED to.
>
> As the other reply implied, your main concern may be tables where, after
the
> mass deletes, there are many blocks below the high water mark that are
> completely empty. This would take up a little disk space until they are
> filled, but the main concern would be that full table scans would take as
> long after the deletes as before, due to the need to read all blocks up to
> the high water mark (empty or full). So if that is a concern you could
> reorg the table.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Greg Moore
> INET: sqlgreg_at_pacbell.net
>
> 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: 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.com
-- 
Author: Don Granaman
  INET: granaman_at_home.com

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