RE: SQL*Loader performance - PCTFREE

From: Mark W. Farnham <>
Date: Thu, 17 Jan 2008 18:42:47 -0500
Message-ID: <012a01c85962$aafaa840$>

IFF (IF and only iF) the handling of the database blocks is a significant portion of waits and service times. Presuming you don't exhaust some other system resources' ability to keep up with demand, the proportional reduction in your job time should be proportional to the decrease in blocks used times the proportion of the job time consumed by handling blocks.

Let's say for clarity of argument, that 80% of your current time is handling database blocks.

Let's say you currently use 1000 blocks, so at 40% free and ignoring block overhead to keep the illustrative math simple, you've got 600 blocks of content and 400 blocks empty, in aggregate.

If you go to 10% free, then only 60 blocks will be free in aggregate, so you'll only need about 660 blocks or so to fit the 600 blocks of content. So the job time would go down by about .66 *.8, or .528 (which is huge).

Of course if handling the data blocks is only 20 percent of your runtime, then you'll only reduce the runtime by about 13%.

This is the long way around the barn for folks who have trouble imprinting the implications of Ahmdahl's Law.

hope this helps, and please ignore the huge informality.

-----Original Message-----

From: [] On Behalf Of
Sent: Thursday, January 17, 2008 6:12 PM To: oracle-l
Subject: Re: SQL*Loader performance - PCTFREE

I just thought about something else. We currently have pctfree set to 40 in the largest of the tables. This was done a few month ago to deal with Oracle not being able to build bitmap indices on this tables because they were too
fragmented. To reduce the number of records per block I have increased the pctfree to 40. This increased the
number of blocks needed to store the table. Now I don't think we have any more of the bitmap indices and therefore I should be able to reduce the pctfree thus reducing the size of the table and this should reduce the time needed
to load this table.

Does that sound reasonable?

thank you

Gene Gurevich


-- Received on Thu Jan 17 2008 - 17:42:47 CST

Original text of this message