RE: SQL*Loader performance - PCTFREE
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <genegurevich@xxxxxxxxxxxx>, "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 17 Jan 2008 18:42:47 -0500
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: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of genegurevich@xxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
