| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: PCTUSED - when is block added to freelist?
So my understanding from this is that
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_at_TDWater To: Multiple recipients of
list ORACLE-L <ORACLE-L_at_fatcity.com>
house.com> cc:
Sent by: Subject: RE: PCTUSED - when
is block added to freelist?
root_at_fatcity.com
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: jkstill_at_cybcon.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: JayMiller_at_TDWaterhouse.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Rick_Cale_at_teamhealth.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: JayMiller_at_TDWaterhouse.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).Received on Thu Sep 05 2002 - 11:33:33 CDT
 |
 |