is the value of pctfree correct for oracle table?

[Dave Chamberlin]

I have large tables that were created with the default pctfree of 10. Is there a way to see the average free space at the block level in used blocks- ie was the 10% used for updates or is it still there? I am rebuilding these tables and don't want to reserve 10% of each block for updates if it is not needed.
thanks

[TwoProc]

Are there any chained rows? If you have a lot of them, then you need to increase this number. If you have NONE, then you can consider decreasing them. HOWEVER, I really can't see how 10% free is going to upset you (I know, I don't know how much data you've got - sorry), but, in general, I keep 10% free for everything at a minimum. I've looked around, and the best gauge I can come up with is chained rows. If you're chaining rows, then you need to increase this value, otherwise you can consider dropping it lower.

[Indira Aramandla]

Hi Dave,

The setting of PCTFREE and PCTUSED depends on a variety of factors, including performance requirements, rowlengths and on the row insertion and update pattern. It only half the data exist on initial insertion then more room may be needed in pctfree while less could be needed for rows that are never updated.

You can query pct_free, pct_used, freelists, freelist_groups from dba_tables and will see that the pct_free set to the default of 10 or another value if specified. PCTFREE is valid for automatic/bitmapped free space management. It can still be used to reserve extra space in a block to prevent row migration.

It is best, once you have decided on the broad principles of what performance you want, and analyze the table several times over a period to see if you have migration taking place and to correct it, either by recreating the rows or by adjusting the parameters further. When a row is chained or migrated, performance associated with this row decreases because Oracle must scan more than one data block to retrieve the information for that row.

You don't want to have a situation where a block is taken off the freelist (ie. an insert would violate the pctfree value) whilst *under* the pctused value.

A high value for PCTFREE may improve performance because blocks have to be reorganized less frequently and chaining is also reduced. There is more space for growth of existing rows. A low value for PCTFREE may reduce performance since reorganization becomes more often and chaining would be increased.

Attached is a document of storage parameters PCTFREE and PCTUSED derived from metalink.

Indira A

[Yogeeraj_1]

hi dave,

Have a look at the attached procedure.

This will show you the allocated space for youe tables.

The output will be as follows:
exec show_space( 'T' )
Free Blocks.............................19
Total Blocks............................32
Total Bytes.............................262144
Unused Blocks...........................12
Unused Bytes............................98304
Last Used Ext FileId....................1
Last Used Ext BlockId...................64681
Last Used Block.........................5

hope this helps!

happy new year 2005!
regards
Yogeeraj

[Yogeeraj_1 · Accepted solution]

attachment

[Sanjay Kumar Suri]

A smaller PCTFREE has the following effects:

1. Reserves less room for updates to expand existing table rows
2. Allows inserts to fill the block more completely
3. May save space, because the total data for a table or index is stored in fewer blocks (more rows or entries for each block)
4. A small PCTFREE might be suitable, for example, for a segment that is rarely changed.

A larger PCTFREE has the following effects:

1. Reserves more room for future updates to existing table rows
2. May require more blocks for the same amount of inserted data (inserting fewer rows for each block)
3. May improve update performance, because Oracle does not need to chain row pieces as frequently, if ever
4. A large PCTFREE is suitable, for example, for segments that are frequently updated.

Check the views ALL_OBJECT_TABLES, DBA_OBJECT_TABLES, USER_OBJECT_TABLES for lot of valuabe information.

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch289.htm#1297181

sks

[Eric Antunes]

Hi Dave,

As Indira said, to know that you must analyze if you have chaining on that table. To do that:

Check if you already have the SYSTEM.CHAINED_ROWS created. If not, run @$ORACLE_HOME/rdbms/admin/utlchain.sql

Then, analyze it:

SQL> analyze table . list chained rows;

Finaly, check in chained_rows table for chaining:

SQL> select count(*), table_name from chained_rows group by table_name order by 1 desc

Note that you must consider the number of chained rows as a problem only if it is a high percentage of the table total rows...

If you have a high percentage, then you should increase pctfree from 10 to 15 for exemple. Note that this will only prevent future chaining: if you want me to tell you how to correct the present chained rows, tell me!

Regards,

Eric Antunes