Re: Question about spreading I/O accross multiple datafiles for a tablespace

"G Dahler" <gordon.dalher_at_spamex.com> a écrit dans le message de news:GgF2d.26111$0h7.1804446_at_news20.bellglobal.com...
> Hi,
>
> I searched the internet, and I wanted to confirm somthing with the experts.
>
> Suppose you create a tablespace using 'x' datafiles instaed of a single one.
> Will oracle allocate extents in a round robin manner accross all datafiles ?
>
> According to what I've read:
>
> - If you use a locally managed tablespace with an UNIFORM extent size, it
> will allocate extents in a round robin manner
>
> - If you use a locally managed tablespace with AUTO allocation, it will NOT
> allocate extents in a round robin manner, that is, it will fill the first
> datafile and then proceed to the next
>
> - I have absolutely no idea what will happen in the case of dictionnary
> managed tablespaces.
>
> The reason I'm asking this, is that I have a big application here (An ERP
> system) that has numerous tablespaces. I have many disks. When the ERP was
> implemented, it was decided to separate the tables for GL, AR, AP etc... in
> different tablespace. Index tablespaces were also separated.
>
> The problem is that the ERP vendor sometims send us scripts to change the
> data model (add tables, index, etc...) but the scripts are generic and do
> not take in consideration the tablespace or (worst) the storage parameters.
>
> To alleviate the problems, I rebuilt the database and used locally managed
> tablespacs with auto allocation to fix the storage parameter problems. I
> still have to move things around and rebuild index after running the vendors
> scripts, but it's less painful than before.
>
> I was thinking of creating just a couple of tablespaces (LMT, auto
> allocation) now (one data, one index) and spread the load accross many
> disks. I do not have hardware raid, the disks are mirrored with sun volume
> manager (aka disksuite, I know there is a write penalty but I can live with
> that)
>
> But according to what I read, if I try to to this, and use LMT with auto
> allocation, oracle will not spread the load. I could use uniform extent
> size, but I would then have to segregate tables by size, which will be as
> worst has before when come the time to run one of the vendors scripts.
>
> Povided someone understand my bad english and my situation, does anyone here
> have a good suggestion for dealing with this ?
>
> Thanks a lot
>
>

v920>create tablespace test
  2 datafile 'D:\ORACLE\BASES\MIK9\TEST01.DBF' size 10m,   3 'D:\ORACLE\BASES\MIK9\TEST02.DBF' size 10m   4 extent management local autoallocate;

Tablespace created.

v920>create table t (col number) tablespace test;

Table created.

v920>alter table t allocate extent;

Table altered.

v920>alter table t allocate extent;

Table altered.

v920>select extent_id, file_id from dba_extents where owner='TEST' and segment_name='T' order by 1;  EXTENT_ID FILE_ID
---------- ----------

         0          8
         1          8
         2          8

3 rows selected.

v920>drop tablespace test including contents and datafiles;

Tablespace dropped.

v920>create tablespace test
  2 datafile 'D:\ORACLE\BASES\MIK9\TEST01.DBF' size 10m,   3 'D:\ORACLE\BASES\MIK9\TEST02.DBF' size 10m   4 extent management local uniform;

Tablespace created.

v920>create table t (col number) tablespace test;

Table created.

v920>alter table t allocate extent;

Table altered.

v920>alter table t allocate extent;

Table altered.

v920>select extent_id, file_id from dba_extents where owner='TEST' and segment_name='T' order by 1;  EXTENT_ID FILE_ID
---------- ----------

         0          8
         1          7
         2          8

3 rows selected.

v817>create tablespace test
  2 datafile 'D:\ORACLE\BASES\MIKE\TEST01.DBF' size 10m,   3 'D:\ORACLE\BASES\MIKE\TEST02.DBF' size 10m   4 extent management dictionary;

Tablespace created.

v817>create table t (col number) tablespace test;

Table created.

v817>alter table t allocate extent;

Table altered.

v817>alter table t allocate extent;

Table altered.

v817>select extent_id, file_id from dba_extents where owner='TEST' and segment_name='T' order by 1;  EXTENT_ID FILE_ID
---------- ----------

         0          8
         1          6
         2          8

3 rows selected.

v817>drop tablespace test including contents;

Tablespace dropped.

v817>create tablespace test
  2 datafile 'D:\ORACLE\BASES\MIKE\TEST01.DBF' size 10m reuse,   3 'D:\ORACLE\BASES\MIKE\TEST02.DBF' size 10m reuse   4 extent management local autoallocate;

Tablespace created.

v817>create table t (col number) tablespace test;

Table created.

v817>alter table t allocate extent;

Table altered.

v817>alter table t allocate extent;

Table altered.

v817>select extent_id, file_id from dba_extents where owner='TEST' and segment_name='T' order by 1;  EXTENT_ID FILE_ID
---------- ----------

         0          8
         1          8
         2          8

3 rows selected.

v817>drop tablespace test including contents;

Tablespace dropped.

v817>create tablespace test
  2 datafile 'D:\ORACLE\BASES\MIKE\TEST01.DBF' size 10m reuse,   3 'D:\ORACLE\BASES\MIKE\TEST02.DBF' size 10m reuse   4 extent management local uniform;

Tablespace created.

v817>create table t (col number) tablespace test;

Table created.

v817>alter table t allocate extent;

Table altered.

v817>alter table t allocate extent;

Table altered.

v817>select extent_id, file_id from dba_extents where owner='TEST' and segment_name='T' order by 1;  EXTENT_ID FILE_ID
---------- ----------

         0          8
         1          6
         2          8

3 rows selected.

-- 
Regards
Michel Cadot