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

03-14-2002, 09:55 AM

sri_sumit123

yes ys !! u r very true.

03-14-2002, 03:10 PM

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.

4 Mostly update. Estimate row expansion in %, and set it to PCTFREE and set PCTUSED as 40.

Tamilselvan

03-14-2002, 04:22 PM

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.

03-14-2002, 04:53 PM

tamilselvan

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

03-14-2002, 05:29 PM

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

03-14-2002, 05:43 PM

pando

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

03-14-2002, 05:54 PM

Shestakov

Quote:

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.

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