| Oracle 8i DB fragmentation [message #164076] |
Tue, 21 March 2006 12:27  |
Vamshee
Messages: 32
Registered: February 2005
|
Member |
|
|
Hi
We have Oracle 8i/Win NT
Currently we came accross some fragmentation issues.
And this are the details of our tables
PCT_INCREASE INITIAL_EXTENT TABLESPACE_NAME
------------ -------------- ------------------------------
50 65536 SYSTEM
50 524288 RBS
0 131072 USERS
0 65536 TEMP
0 32768 TOOLS
0 131072 INDX
0 10485760 CPPP
0 33554432 CPPP_NDX
50 40960 SCRATCH
50 40960 STATSPACK
I have refered to faq's under this site
and found the topic "How does one prevent tablespace fragmentation?"
under http://www.orafaq.com/faqdba.htm#FILERESIZE
where it was suggested to have a PCT increase to either 0 or 100.
Question:
----------------
In our current scenario, would make the PCT values of all tables to 0 will help ?
If yes, what is the ideal way of doing it ?
Please suggest if there is any other way of dealing with fragmentation.
Thanks in Advance
Vamshee
|
|
|
|
| Re: Oracle 8i DB fragmentation [message #164082 is a reply to message #164076] |
Tue, 21 March 2006 12:58  |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
I am not sure what 'fragmentation' means. Is it that there are a lot of extents that are not sequentially placed on the disk?
As long as the extents are not too small, I was told that Oracle does not mind having extents in different parts of the physical disk. If you are doing lots of full table scans, then perhaps you want all the extents to be sequentially placed, and have a large multi block read count. If you are doing lots of single row updates, then having extents on multiple parts of a disk is okay.
*I think*
Neil.
[Updated on: Tue, 21 March 2006 12:59] Report message to a moderator |
|
|
|
|
|
| Re: Oracle 8i DB fragmentation [message #164088 is a reply to message #164083] |
Tue, 21 March 2006 14:30 |
Vamshee
Messages: 32
Registered: February 2005
|
Member |
|
|
Thanx for ur immediate replies
All our Tablespaces are with DMT with INITIAl=NEXT.
As i placed earlier , 4 TS's are having 50% PCT values (SYSTEM, RBS, SCRATCH, STATSPACK)
I would like to bring them to a PCT increase of 0.
Could you please let me know as how to accomplish that(any syntax or command).
and do i have to shutdown or bounce the DB for that.
Thnx in Advance
Vamshee.
|
|
|
|
| Re: Oracle 8i DB fragmentation [message #164096 is a reply to message #164088] |
Tue, 21 March 2006 16:37 |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Before you do anything by yourself or by the advice / hint on any forum I strongly suggest you to look into documentation and understand what you are about to do and its ramifications.
>>As i placed earlier , 4 TS's are having 50% PCT values (SYSTEM, RBS, SCRATCH, STATSPACK)
50% default PCTINCREASE may have been inherited from sql.bsq.
In usual cases, i would not worry about 50% PCTINCREASE in SYSTEM tablespace ( unless you have custom objects inside system tablespace, which is very very bad practice). Why would a tablespace get "fragmented"? If are "deleting" data or "dropping and creating objects" frequently and if extents are not evenly allocated, you may get "holes" in tablespace.
So Unless you are rebuilding your dictionary very frequently or dropping the system objects or have custom objects in system tablespace your dictionary tablespace (system tablespace) stays good (well, in most cases). Anyhow, a PCTINCREASE 0 is good.
I have no idea what SCRATCH tablespace is used for.
The syntax,code examples are as usual available here in documentation
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/tspaces.htm#9994
For an RBS you cannot specify pctincrease and it will always default to 0.
quoting the same docset
| Quote: |
After determining the desired total initial size of a rollback segment and the number of initial extents for the segment, use the following formula to calculate the size of each extent of the rollback segment:
T / n = s
where:
T = total initial rollback segment size, in bytes
n = number of extents initially allocate
s = calculated size, in bytes, of each extent initially allocated
After s is calculated, create the rollback segment and specify the storage parameters INITIAL and NEXT as s, and MINEXTENTS to n. PCTINCREASE cannot be specified for rollback segments and therefore defaults to 0. Also, if the size s of an extent is not an exact multiple of the data block size, it is rounded up to the next multiple.
|
[Updated on: Tue, 21 March 2006 17:02] Report message to a moderator |
|
|
|
| Re: Oracle 8i DB fragmentation [message #164101 is a reply to message #164096] |
Tue, 21 March 2006 17:01 |
|
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
and to answer your question
Alter tablespace tablespace_name default storage(pctincrease 0);
and before doing this, i would
*read the docset and understand what is happening.
*take backup. every other kind of possible backup.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
