Home » RDBMS Server » Performance Tuning » Reduce the number of Datafiles
Reduce the number of Datafiles [message #221535] Tue, 27 February 2007 06:03 Go to next message
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.
Re: Reduce the number of Datafiles [message #221579 is a reply to message #221535] Tue, 27 February 2007 09:23 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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.
Re: Reduce the number of Datafiles [message #221658 is a reply to message #221535] Tue, 27 February 2007 23:38 Go to previous messageGo to next message
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.
Re: Reduce the number of Datafiles [message #221660 is a reply to message #221535] Tue, 27 February 2007 23:51 Go to previous messageGo to next message
BlackSwan
Messages: 25036
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.
Re: Reduce the number of Datafiles [message #221663 is a reply to message #221658] Wed, 28 February 2007 00:03 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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.
Re: Reduce the number of Datafiles [message #221668 is a reply to message #221535] Wed, 28 February 2007 00:18 Go to previous messageGo to next message
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.
Re: Reduce the number of Datafiles [message #221671 is a reply to message #221668] Wed, 28 February 2007 00:37 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Read my first response.
It is to do with FTS (scattered) /index scans (sequential)
Better, start reading documentation.
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm#sthref921
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm#sthref943
Re: Reduce the number of Datafiles [message #221673 is a reply to message #221535] Wed, 28 February 2007 00:44 Go to previous message
frozenam_24
Messages: 9
Registered: April 2006
Junior Member
Appreciated.
Previous Topic: how to measure the Performance improvement
Next Topic: How to Purge tables ??
Goto Forum:
  


Current Time: Sun Dec 04 14:37:34 CST 2016

Total time taken to generate the page: 0.05042 seconds