The general guidelines for setting up PCTFREE and PCTUSED are:
If the table is of
1 Mostly inserts, Set a small PCTFREE and PCTUSED as 40
2 Queue like - rows are inserted and subsequently deleted soon after- Set a small PCTFREE (5) and very small PCTUSED (10).
Example, an audit table (log table). Rows older than a month get deleted every day.
3 Randon Insert/Delete, estimate row size. Then apply the formula given below:
PCTUSED = (100 - PCTFREE - (MAX(10,(MAX_ROW_SIZE/BLOCK_SIZE)*100))
If the PCTUSED ends up -n negative, set it 1.
4 Mostly update. Estimate row expansion in %, and set it to PCTFREE and set PCTUSED as 40.
When you switch to 9i you can forget about PCTUSED (and FREELIST stuff, which PCTUSED is all about). You can simply specify your localy managed tablespace as "SEGMENT SPACE MANAGEMENT AUTO" and let Oracle doo all the decisions about which blocks should be available for additional inserts. And no more freelist-contention problems...
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Originally posted by Mnemonical
if pctfree is 10%, pctused should be 75%
if pctfree is 5%, pctused should be 80%
if pctfree is 50%, pctused should be 35%
How far is this true?
Table TTTT has avg_row_len=400 byte
Row_per_block ~9 max
Every day insert ~ 1000 new records.
Avg delete ~5% rows
Business procedure has 3 steps:
1) insert row (with initial information) ~ 100 bytes avg len
2) 1 week later each row should be upbate and add +50 bytes to avg len
3) 2 weeks later each row should be upbate and add last +250 bytes to avg len
How we should calculate PCT_FREE, PCT_USED
If we set :
if pctfree is 10%, pctused should be 75% ~= 90% rows will be chained (and near 100% of db_blocks)
if pctfree is 50%, pctused should be 35% ~= 25% rows will be chained
base on information about 1step ~ 35% alg len of record (then PCT_FREE ~ 30-35%)
base on information about 5% deleted rows:
PCT_USED ~ 35-( 9*0.05*400)/4000 ~= 25(or 30%)