Home » RDBMS Server » Performance Tuning » pctfree and pctused settings
pctfree and pctused settings [message #65076] Thu, 15 April 2004 22:21 Go to next message
dennis
Messages: 31
Registered: June 1998
Member
any suggestions on the proper pctused and pctfree values for the following cases:

1. a very large table (but storage is not a concern / performance is a primary concern) most activity includes complex queries.

2. a very large table (storage is not an issue ... performance is) most activity includes complex queries and inserts. (very minimal or no updates and deletes)

these are regular tables with one unique index (based on the primary key) with 120bytes avg. row length.
Re: pctfree and pctused settings [message #65077 is a reply to message #65076] Thu, 15 April 2004 22:59 Go to previous messageGo to next message
IA
Messages: 91
Registered: March 2004
Member
Hi,

Because you have minimal updates, you can set pctfree to 10. I advise u stick to the oracle default of 40 for pctused.

Cheers ... IA
Re: pctfree and pctused settings [message #65078 is a reply to message #65077] Fri, 16 April 2004 09:07 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
pctfree=0 is often used in a data warehousing environments because row length won't grow due to updates. Because you aren't reserving the default 10% of usable block size for subsequent updates, you can cram more rows into a block and consequently end up doing fewer I/O's to read more rows (faster performance). If you make pctfree too small and end up performing updates which increase a row's length, then you'll end up having row chaining/migration (bad for performance). If you have analyzed your tables (analyze table ABC estimate statistics), you can see the chained row count in user_tables. Not all row chaining is avoidable (v long rows).

pctused determines how empty the block needs to become before it becomes a candidate for inserts again. If you raise it too high, you increase block maintenance overhead. Too low, you waste space which could be freed up for fresh inserts. Same data spread over more spacemeans more I/O's to retrieve it.
Previous Topic: Querying a Partitioned table
Next Topic: Performance Problem with Stored procedure
Goto Forum:
  


Current Time: Sun Sep 20 15:45:58 CDT 2020