PCTUSED Value?

[sudip]

Hi All,
One of my tables has a BLOB column, in which I will store Excel Files. Now this particular Column will be updated very frequenty. I have taken the precaution of putting the BLOB column on a eparate tablespace.
What should be the optimal value of PCTUSED parameter?

Regards
Sudip

[rohitsn]

Hi,
By default the value of PCTUSED is 40 and PCTFREE is 10 but I usually keep the value of PCTUSED to 60 and PCTFREE to 25 to reduce the contention on FREELISTS.

Regards,
Rohit Nirkhe,Oracle DBA,OCP 8i
oracle-support@indiatimes.com

[julian]

Rohit is right in the direction meaning that if PCTUSED and PCTFREE are not set properly, you can waste time placing blocks onto and taking blocks off the freelist.

Run

select table_name, NUM_FREELIST_BLOCKS from user_tables;

to see the number of blocks being placed on and taken off the freelists.

This is my opinion: if you NUM_FREELIST_BLOCKS is more than 1% of the blocks of the table, then you may have a problem. May have!

[sudip]

Will increasing the value of freelist help?
Second for normal columns using Pctfree and PCTUSED is ok, here I am concerned with a column which is BLOB in nature and is updated frequently.

[rohitsn]

Hi,
Yes increasing the value of freelists will help .Give this parameter at the object level creation.

Regards,
Rohit Nirkhe,Oracle DBA,OCP 8i
oracle-support@indiatimes.com

[julian]

Originally posted by rohitsn
Hi,
Yes increasing the value of freelists will help .Give this parameter at the object level creation.

Regards,
Rohit Nirkhe,Oracle DBA,OCP 8i
oracle-support@indiatimes.com

Actually Rohit, it does not matter at what time you define the number of freelists. You can always increase the number of freelists:

alter table XXX storage(FREELISTS 6);

On the other hand FREELIST GROUPS can be defined only at creation time.

[vinit]

Julian, i think setting freelists without recreating the table works only on versions 8.1.7 and above.

[sudip]

One can change Freelists with version 8.1.6 and above.