So my understanding from this is that
a) A table has a PCTUSED of 40 which is changed to 75
b) A block is 60% free
c) This block will be added to the freelist if an update or delete affects
it (it was not added when the PCTUSED was changed).
d) So my next question (which I may have to try and test myself):
Would a global update on the table that didn't change anything move the
blocks that fit the new PCTUSED criteria to the freelist?
For example:
update big_table
set column1=column1;
This would touch every block, at the end of the update the block would be
below the new PCTUSED, but no data actually changes. Would this make the
block available for inserts?
Hmm, this is getting interesting!
Jay Miller
-----Original Message-----
Sent: Wednesday, September 04, 2002 12:33 PM
To: Multiple recipients of list ORACLE-L
See Note: 1029850.6 on MetaLink for more details but here is algorithm used
for freelist
A block
is put on free list if
the free
space in the block is
greater
than the
space
reserved by PCTFREE.
Blocks
linked in a free list are
available
for
future
updates or inserts.
A block
is unlinked from a free
list if
the free space in the
block is
not
enough to
allow a new row
insert,
and if the percentage of
the used
space
remains
above PCTUSED.
A block
is relinked to a free
list if
after DELETE or UPDATE
operations, the
percentage of the used space
falls
below PCTUSED.
Each time
a block is added to a
free
list, it is linked at the
head of
the
chain.
Rick
"Miller, Jay"
<JayMiller@TDWater To: Multiple recipients of
list ORACLE-L <[EMAIL PROTECTED]>
house.com> cc:
Sent by: Subject: RE: PCTUSED - when
is block added to freelist?
[EMAIL PROTECTED]
09/04/2002 11:03
AM
Please respond to
ORACLE-L
Yes, that's what I intended to ask :).
Thanks, Jared.
Jay
-----Original Message-----
Sent: Tuesday, September 03, 2002 9:18 PM
To: Multiple recipients of list ORACLE-L
If I understand Jay's question correctly, what he's asking is
not how PCTUSED and PCTFREE work, but "what action or
actions trigger Oracle to put a block back on the freelist after
changing PCTUSED to a higher value?"
Is that correct Jay?
Jared
On Tuesday 03 September 2002 15:38, Miller, Jay wrote:
> I have one huge table (takes up about 30% of the total database storage)
> which has a monthly batch deletion of old data. I had PCTFREE and
PCTUSED
> set to the defaults of 10 and 40 respectively.
>
> I occurred to me that I could probably free up a lot more space by
> increasing the PCTUSED so that more blocks would be available to be
written
> to (since getting more storage for the server is a bureaucratic nightmare
> here).
>
> So my question is, if I just raise the PCTUSED from 40 to, say, 75 would
> all blocks that fall into the 40-75 range become available for inserts?
Or
> is it only after their next update or delete?
>
> Different sections of the docs seem to imply different things. The docs
> say:
>
> A lower PCTUSED reduces processing costs incurred during UPDATE and
DELETE
> statements for moving a block to the free list when the block has fallen
> below that percentage of usage.
>
> This seems to imply that it won't be moved to the freelist until a delete
> or update is done that affects that block.
>
>
> But they also say:
>
> A higher PCTUSED increases processing cost during INSERTs and UPDATEs.
>
> This seems to imply that when it's looking to do the insert it might find
> that it can insert to a block.
>
>
> Anyway, why would a lower PCTUSED reduce processing costs during a DELETE
> but a higher PCTUSED wouldn't increase processing costs during a DELETE.
> That makes no sense. I'm befuddled.
>
>
> TIA,
> Jay Miller
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
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: Miller, Jay
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:
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: Miller, Jay
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).
