PCTUSED Value?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: PCTUSED Value?

  1. #1
    Join Date
    Feb 2001
    Location
    Kolkata- India
    Posts
    356

    Question

    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
    There Nothing You cannot Do, The problem is HOW.

  2. #2
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    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
    Thanks and Regards
    Rohit S Nirkhe
    rohitsnirkhe@rediffmail.com

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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

    Code:
    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!



    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  4. #4
    Join Date
    Feb 2001
    Location
    Kolkata- India
    Posts
    356
    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.
    There Nothing You cannot Do, The problem is HOW.

  5. #5
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    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
    Thanks and Regards
    Rohit S Nirkhe
    rohitsnirkhe@rediffmail.com

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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:

    Code:
    alter table XXX storage(FREELISTS 6);
    On the other hand FREELIST GROUPS can be defined only at creation time.


    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  7. #7
    Join Date
    Oct 2000
    Posts
    467
    Julian, i think setting freelists without recreating the table works only on versions 8.1.7 and above.
    Vinit

  8. #8
    Join Date
    Feb 2001
    Location
    Kolkata- India
    Posts
    356
    One can change Freelists with version 8.1.6 and above.
    There Nothing You cannot Do, The problem is HOW.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width