| pctfree and pctused settings [message #65076] |
Thu, 15 April 2004 22:21  |
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 #65078 is a reply to message #65077] |
Fri, 16 April 2004 09:07  |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
