OraFAQ Forum: Performance Tuning » Reduce the number of Datafiles

Home » RDBMS Server » Performance Tuning » Reduce the number of Datafiles

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Reduce the number of Datafiles [message #221535]

Tue, 27 February 2007 06:03

frozenam_24
Messages: 9
Registered: April 2006

Junior Member

Hello,

In one of our instance a Tablespace is having 101 datafiles with size ranging from 1.5 GB to 2 GB. Because the number of datafiles are more, most of the performance is degrading in dbfile scattered read / dbfile sequential read.

Is there any way to reduce the datafiles number by recreating the tablespace and moving all the existing data to new tablespace.

If so please any of you suggest.

Thank You,
Bharath.
Oracle Apps DBA.

Report message to a moderator

Re: Reduce the number of Datafiles [message #221579 is a reply to message #221535]

Tue, 27 February 2007 09:23

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

You have to move the datafiles to different 'physical disks'.
Just merely moving data among different tablespaces has nothing to do with performance.
>> most of the performance is degrading in dbfile scattered read / dbfile sequential read
You will still have that. This is to do with FTS/Index reads.

Report message to a moderator

Re: Reduce the number of Datafiles [message #221658 is a reply to message #221535]

Tue, 27 February 2007 23:38

frozenam_24
Messages: 9
Registered: April 2006

Junior Member

Hi Mahesh,

Thank You for the reply. Already there is LVM, data might not be on one disk (not sure of this).

Actually i wanted is to create a new tablespace with less number of datafiles with 5 GB size each and get all the data imported to the new tablespace. Is this possible?

Thanks for the humble reply
Appreciated
Bharath.

Report message to a moderator

Re: Reduce the number of Datafiles [message #221660 is a reply to message #221535]

Tue, 27 February 2007 23:51

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Because the number of datafiles are more, most of the performance is degrading in dbfile scattered read / dbfile sequential read.

Please explain how the number of datafiles impacts the number of
reads of any flavor for a given SQL statement.

IMO, the top statement is an unwarrented/unsubstansiated leap in logic.

Report message to a moderator

Re: Reduce the number of Datafiles [message #221663 is a reply to message #221658]

Wed, 28 February 2007 00:03

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>Is this possible?
Yes. you just create a new tablespace and import objects or even just move them to new tablespace.
But it is not going to improve any performance (apart from the fact that you are resetting the HWM and updating the stats).
Not sure why you want to do this.
Helps nothing (may be better for ease of administration) as anacedent succinctly puts.

Report message to a moderator

Re: Reduce the number of Datafiles [message #221668 is a reply to message #221535]

Wed, 28 February 2007 00:18

frozenam_24
Messages: 9
Registered: April 2006

Junior Member

Thanks for all you guys.

I have one doubt now does db file scattered / sequential read mean the data has to be fetched from so many datafiles ( i assumed like that) or its on the disk.

Thanks
Bharath.

Report message to a moderator