What to set pctfree and pctused to for a work table?

[sri_sumit123]

Ok , Thanks julian , got your point .

[julian]

OK but nothing should happen to the design of the application if you truncate an empty table, right?

[sri_sumit123]

yes ys !! u r very true.

[tamilselvan]

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.

Tamilselvan

[Mnemonical]

I read in Oracle 8i DBA HandBook that it's better if the sum of pctfree with pctused results 85, according to pctfree value. For example:

if pctfree is 10%, pctused should be 75%

if pctfree is 5%, pctused should be 80%

if pctfree is 50%, pctused should be 35%

this grants that a block will use most of its space for data storage.

How far is this true?


F.

[tamilselvan]

Oracle DBA HandBook talked about PCTFREE and PCTUSED are in general.
It does not know the nature of table that undergoes changes.

[jmodic]

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...

[pando]

however with this new feature there are already performance problems being reported on metalink....

[Shestakov]

Originally posted by Mnemonical

For example:
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?
F.

Very far!!!

Example :
DB_BLOCK_SIZE=4K
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%)

Conclusion : use LMT

[tamilselvan]

Set PCTFREE to 75 % and PCTUSED 15 %.


[Edited by tamilselvan on 03-14-2002 at 05:02 PM]