Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: PCTFREE and data warehousing

RE: PCTFREE and data warehousing

From: Graeme Farmer <graeme.farmer_at_mincom.com>
Date: Tue, 22 Jun 2004 10:04:56 +1000
Message-ID: <DA5B390A2CBAA64AB2367C2EDA3210F60CA656D0@tqxbneclu03.root.tequinox.com>


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



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


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US