fragmentation

[rnv]

pctincrease is set to 50
what are good values for data and index tablespace
for a app with high insert /update
my tablespace is 10 gig , index is 10 g
please post example

select tablespace_name , sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) pct
from dba_free_space
group by tablespace_name

following query is giving value of 20 for data ts

[anandkl]

its very good to set storage parameters at the table level as you can avoid fragmentation.If you do so you will be storing all your table data with minimum blocks thus increasing the performance of your sql and also reducing framentation

Since u have a apps with high insert and update
create table with pctfree=60 and pctused=40.

Even if you specify storage parameter at tablespace level and if your table data spreads multiple blocks then your sql will have to read more block and performance will degrade and fragmentation will take place.

cheese
anandkl

[rnv]

how about initial and next extent

[rnv]

some please post a sample of create tablespace ...
initial .... pctused......

for a db with 10 G data
with high insert/update

here someone has
pctincrease50
imitial to 35000
next to 35000

thanks

[gvvenkat]

Check out this document titled 'How to stop defragmenting and start living', available at http://www.hotsos.com/catalog/?sort=S-level .
It will answer many of your storage spec. related questions

VJ

[SANJAY_G]

Originally posted by anandkl
Since u have a apps with high insert and update
create table with PCTFREE=60 and PCTUSED=40

The value of pctfree=60 and pctused=40 is not recommended because it will push the block into and out from the FREELIST very frequently, causing overheads.
PCTFREE+PCTUSED should not reach 100.

Sanjay