Date: Mon, 31 Jan 2000 09:41:43 -0600 (CST)
To: Souresh Saha 

On Mon, 31 Jan 2000, Souresh Saha wrote:

> 
> Hi Ari,
>            I am Madhusri Bhattacharya. I am currently preparing for the
> OCP exam.
>            I had the following question: If there are  frequent inserts
> and deletes in a table, what value of PCTUSED should be used and why? I am
> using the book "Exam Cram" and in the book in one place it suggests a low
> value for PCTUSED while in another place it suggests a high value of
> PCTUSED. I was looking for the answer on the web and came across your
> e-mail address in a mailing group.  Could you please clarify?
>             Thanking you in advance for your time.
>             Yours sincerely,
>             Madhusri.

Typically, the PCTUSED should be low and the PCTFREE should be high.
The true question would be if when you delete and then insert a record,
are the sizes of the records different?

For example, if a record has columns whose size doesn't change (DATE,
NUMBER, etc.) then it does not matter as much what PCTUSED/PCTFREE is, so
it is more efficient to have PCTUSED high (90-ish).

If a record's size changes alot (it has a text-field with variable-length
text, or a LONG column) then PCTUSED should be lower, maybe even 40.
PCTFREE should be higher, maybe 60. This will allow Oracle to keep space
within the block for when a small record is replaced with a larger record.

Best regards,

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 345+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             http://www.arikaplan.com                          <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->




Back to Ari Kaplan's Home Page