Home » RDBMS Server » Performance Tuning » Intial Extent parameter for Indexes
Intial Extent parameter for Indexes [message #65637] Thu, 18 November 2004 17:15 Go to next message
Milind Deshpande
Messages: 93
Registered: May 2004
Member
Hi All,

Presently I have been assigned a task of assigning storage parameters for Indexes in Oracle 9.2.0.

As there is an option for using Locally managed tablespaces for which we dont have to specify storage parameters but on certain recommendations I have been told that DMT will be used and storage parameters are required for Indexes.

My question to you Experts:- Is there any way by which I can get optimal storage parameters for Indexes like Initial Extent,Next Extent,Pctfree,Pctused,Inittrans,Maxtrans.

Can anybody provide me with the guidelines that for an Index how the optimal storage parameters are to be specified.

Any help is appreciated.

Thanks in Advance

Milind.
Re: Intial Extent parameter for Indexes [message #65640 is a reply to message #65637] Fri, 19 November 2004 03:15 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> but on certain recommendations I have been told that DMT will be used
Ask them to prove their recomendations.
LMT is always better than DMT in every aspect.
If you are using a DMT, oracle recomends to set all storage parameters in Tablespace level
(and NOT in table/index level becuase table level settings will overwrite tablespace settings).

To avoid fragmentation always have a pctincrease=0 and initial=next=SomeConstantValue.

Please go through this MOST WIDELY READ document ( which you may have already done) titled
HOW TO STOP DEFRAGMENTING AND START LIVING
Re: Intial Extent parameter for Indexes [message #65643 is a reply to message #65637] Sat, 20 November 2004 06:04 Go to previous message
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
Senior Member
From the 10g documentation:
[b]Note:[/b]
Oracle strongly recommends that you create only locally managed tablespaces. Locally managed tablespaces are much more efficiently managed than dictionary-managed tablespaces. The creation of new dictionary-managed tablespaces is scheduled for desupport.
Previous Topic: Reg. ROW Chaining
Next Topic: Protecting a Table from Truncated or Delete
Goto Forum:
  


Current Time: Sun Sep 20 15:16:08 CDT 2020