Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: PCTFREE and data warehousing
Comments inline.
Regards,
Graeme.
-----Original Message-----
From: Ben [mailto:poelsb_at_post.queensu.ca]
Sent: Tuesday, 22 June 2004 4:22 AM
To: Oracle-L_at_Freelists. Org
Subject: PCTFREE and data warehousing
Hi
I understand that PCTFREE should be set very high for data warehouse tables
that are mostly just inserted (loaded) and never updated.
<gf>
Not high but low.
Not just DW tables, any tables really. The reason for the existence PCTFREE
is to allow free space in a data block for future updates in order to avoid
(or at least mitigate) the chance of a row migrating to another block hence
increasing I/O required to read that row. If there are no updates that would
increase the length of a row (or no updates at all) then you can decrease
the PCTFREE value.
</gf>
But what happens
if the PCTFREE is set to zero. I have inherited a data warehouse where all
the tables were being re-created nightly and had PCTFREE=0 and PCTUSED=40.
<gf>
Having PCTFREE of zero really depends on subsequent activity and/or other
table settings. If you expect concurrent DML (and by that I mean at the
block level) then you could experience enqueue waits if there are no free
slots left in the ITL (Interested Transaction List) which is governed by the
INITRANS (and MAXTRANS) setting.
If there is free space in the block (24 bytes) and a transaction requires
write access to data in the block and there is no free transaction slot then
a new one is dynamically created. If there is no free space then the session
will wait on the enqueue wait until it gains access to the block.
In short, you should work out some stats on number of concurrent block
modification accesses, set INITRANS to an appropriate value based on your
findings and then I believe you can set PCTFREE to 0. Otherwise, you can
play safe and set PCTFREE to 1 which will give you sufficient space for a
number of IT entries.
Concurrent (write) access is probably not a problem in a DW environment so
this may be largely irrelevant.
</gf>
The application has changed and now several tables have about 10% of their
data deleted and re-loaded nightly. I am finding that it is not reusing the
freed data blocks - it just keeps grabbing more. I can set the PCTUSED much
higher to try and get the blocks in use.
<gf>
Good plan, PCTUSED of 40 is pretty low considering the low value of PCTFREE.
</gf>
What I am wondering is if a block
has a PCTFREE of zero and is filled by inserts, does it never make it back
onto the free list?
<gf>
It will make it back on the freelist if/when you delete enough data to make
it fall below PCTUSED. If this never happens, the block will remain off the
freelist.
Is partitioning an option? For example, If the data being deleted/added is
date-based then you may be able to alter the deletion strategy to truncate a
partition rather than delete the data prior to reloading. This can really
improve your milage when it comes to storage management.
BTW, indexes require consideration independent of the table configuration.
Monotonically incrementing indexes are good candidates to have low PCTFREE
settings but standard (!) B-Tree indexes require a PCTFREE setting large
enough to reduce the occurrences of block splits due to insertions within
blocks with data already present. (eg insert 'B' into a block that has 0%
free and is full of AAAACCCCC then you need to split the block to perform
the insert and too much of this kind of activity may degrade performance).
</gf>
Thanks,
Ben
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- -- This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please notify the sender and delete the transmission. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Limited unless expressly stated otherwise. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Mon Jun 21 2004 - 19:01:58 CDT
![]() |
![]() |