6 Replies Latest reply: Dec 13, 2011 4:50 AM by 803116 RSS

    tablespace with multiple files , how is space consumed?

    803116
      i have one tablespace tspace1 that has 2 data files, and there are 2 physical disks /data1 /data2 each of those mount points are a partition on a separate disk

      disks1 /data1
      disk2 /data2

      /data1 is 80% utilized (both data files are on /data1)
      /data2 is not used yet


      am i corect saying i shouldh ave created one of the data files in /data2 rom day one ? for improved performance?

      now im in need of creating 3rd and fourth data files.. should i create those new ones in /data2 and leave it as it stands?

      or shall i move one of the large ones to /data2 so data is distributed across both disks? and then create datafile3 in /data1 and datafile4 in /data2?

      thanks
        • 1. Re: tablespace with multiple files , how is space consumed?
          Vishalp-Oracle
          if most of the data you select is within a single datafile then no performance improvement even if you place files in different disks. But if your data is evenly placed across your datafiles in terms of selectivity then having those datafiles places across multiple disks would help.

          Place your mostly accessed datafiles on separate disks.
          • 2. Re: tablespace with multiple files , how is space consumed?
            Aman....
            What is the database version? If you are on 10g and above, I would recommend that you deploy ASM which would handle the space management challenges like the one you have mentioned on its own to a lot of extent.

            Aman....
            • 3. Re: tablespace with multiple files , how is space consumed?
              803116
              Aman.... wrote:
              What is the database version? If you are on 10g and above, I would recommend that you deploy ASM which would handle the space management challenges like the one you have mentioned on its own to a lot of extent.

              Aman....
              its 11g no asm at the moment,
              how does oracle consume data files? if a tablespace has 2 data files does oracle write to both? or writes to one and then once the max limit is reached it starts writing to next one ?

              is it the same with temp table space?
              • 4. Re: tablespace with multiple files , how is space consumed?
                EdStevens
                800113 wrote:
                Aman.... wrote:
                What is the database version? If you are on 10g and above, I would recommend that you deploy ASM which would handle the space management challenges like the one you have mentioned on its own to a lot of extent.

                Aman....
                its 11g no asm at the moment,
                how does oracle consume data files? if a tablespace has 2 data files does oracle write to both? or writes to one and then once the max limit is reached it starts writing to next one ?

                is it the same with temp table space?
                Several years ago I ran some tests that showed the files being written to in round-robin fashion. (Actually, it was extents being allocated in round robin. A segment is only written to in previously allocated extents). When I posted that on this forum a few years ago, someone responded with their own test results showing a file being filled before the other is used. So the answer is . . . as almost always . . . "it depends".

                And at the moment I don't recall what the controlling factor was .
                • 5. Re: tablespace with multiple files , how is space consumed?
                  Jonathan Lewis
                  EdStevens wrote:

                  Several years ago I ran some tests that showed the files being written to in round-robin fashion. (Actually, it was extents being allocated in round robin. A segment is only written to in previously allocated extents). When I posted that on this forum a few years ago, someone responded with their own test results showing a file being filled before the other is used. So the answer is . . . as almost always . . . "it depends".

                  And at the moment I don't recall what the controlling factor was .
                  One of the factors would have been version, of course.

                  Round robin is the basic strategy for allocating extents to a segment, but automatic extent allocation (which start with 64KB extents before moving to 1MB and so on) introduced a special case where Oracle would stick with one file for the first megabyte of the segment.

                  Until fairly recently, though, the first extent of a segment was always put into the first file of the tablespace, so if you have a lot of object which need to have just one extent allocated then you could end up with most of the space from the first file in use and very little space allocated from the rest. I don't recall when this changed, but I do remember seeing a note on MOS describing this anomaly and explaining how the imbalance had been fixed by keeping track of which file should be used next when a new segment was to be created.

                  Regards
                  Jonathan Lewis
                  http://jonathanlewis.wordpress.com
                  Author: <b><em>Oracle Core</em></b>
                  • 6. Re: tablespace with multiple files , how is space consumed?
                    803116
                    thank you all, i will disable auto extend on exisitgn fiels and should trigger oracle to write to new data files