Forum Stats

  • 3,784,147 Users
  • 2,254,897 Discussions
  • 7,880,711 Comments

Discussions

Categories

index and datafile in same mount point

user10240196
user10240196 Member Posts: 37
edited Sep 16, 2008 9:38AM in General Database Discussions
hi

i am using oracle 7.3.4 under AIX 4.3. there are many datafile and indexes . the datafile and index of same table are located in same mount point . my question is is performence will degrade du to store index and datafile in same mount point.

'as for example

/dtemp/oradata/dhklive/data/inv_dtld01.dbf -- datafile
/dtemp/oradata/dhklive/data/inv_dtlx01.dbf -- index file


pls help me

munir

Edited by: user10240196 on Sep 15, 2008 10:19 PM
Tagged:
· Share on TwitterShare on Facebook
«1

Answers

  • amardeep.sidhu
    amardeep.sidhu Member Posts: 2,290
    Please provide the details what kind of storage you are using.

    Amardeep Sidhu
    http://amardeepsidhu.com/blog
    · Share on TwitterShare on Facebook
  • 525507
    525507 Member Posts: 341
    If I/O really issue on your system then definitely keeping the datafile which
    contain the table and whose index is with index tablespace datafile are in same mount point can hamper the performance.

    It is recommended to have table and its index on different disk to get good performance.
    · Share on TwitterShare on Facebook
  • Aman....
    Aman.... Member Posts: 22,913 Gold Crown
    Its a classic question and the answer is "in general no". There is not much effect with putting indexes and tables together. The most common belief is that both are accessed parallely so it creates the contention.In real, both are accessed serially , indexes and then tables. So even though they are on the same mount , there isn't impact over the performance.
    Over here,as Sidhu asked, storage plays an important role. If you are using some new time storage solutions than the arguement of splitting them on different mount points goes away totally.If not than you have to look how you access your indexes and tables,for example most of the queries are small selections or big warehouse queries? Looking at that,difference may come in splitting of both indexes and tables.
    HTH
    Aman....
    · Share on TwitterShare on Facebook
  • user10240196
    user10240196 Member Posts: 37
    that means while i will create any table i must keep datafile and index file in separate disk or mount point .

    is that right ?
    · Share on TwitterShare on Facebook
  • Fco Munoz Alvarez
    Fco Munoz Alvarez Member Posts: 926
    edited Sep 16, 2008 6:50AM
    Hi Munir,

    Basically if you have data and index datafiles in the same mount point will not affect too much you performance. You need to detect the hot spots that are causing contention and Balancing the database I/O in different mount points.

    Cheers,

    Francisco Munoz Alvarez
    http://www.oraclenz.com
    · Share on TwitterShare on Facebook
  • 247514
    247514 Member Posts: 10,875 Bronze Trophy
    The more import is underlying storage system not mount point. You can have same storage system while having multiple mount points on it. In this case having multiple mount points doesn't distribute IO and have no real performance gain.

    You might elaborate a little about your storage and file system used.

    PS. You should seriously consider upgrade your database now.
    · Share on TwitterShare on Facebook
  • Fco Munoz Alvarez
    Fco Munoz Alvarez Member Posts: 926
    Dear Munir,

    Please take a look in the following Metalink note:

    - [OPTIMIZING ORACLE7.1 DATABASE PERFORMANCE - Note:16347.1|https://metalink.oracle.com/metalink/plsql/f?p=130:14:725432850963062762::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,16347.1,1,1,1,helvetica]


    Cheers,

    Francisco Munoz Alvarez
    http://www.oraclenz.com
    · Share on TwitterShare on Facebook
  • Aman....
    Aman.... Member Posts: 22,913 Gold Crown
    OP,
    You need to take care about the contention(if its coming) over the storage in total. Mount point is not the question here. I guess, in my reply I mentioned to you that it doesn't matter that whether you put your tables/indexes together or sperately as they both are accessed serially not parallely. So any question for contetion gets overruled here. Now the next point that whether you have IO contentions and/or hotspots over the disk, this eventually will lead to bad performance whether you put your tables besides indexes or miles apart even.
    The answer of your questions still remains "in generic terms",no only.
    HTH
    Aman....
    · Share on TwitterShare on Facebook
  • Pavan Kumar
    Pavan Kumar Member Posts: 11,904 Gold Crown
    Hi,

    Generally is Speaking it's not needed to specify the mount point for the files in different places. In simple terms I say that when you have a two different text files in same directory and when you open the files with textpad, both will open. There will no Issues. As you said about the contention or peformance problem then in our example, the Contention might happen due to the memory allocated or used by textpad (in case they are not enough to handle at a time more than one file) and performance will effect due to the Application but not due to the Files stored at a single place.

    Simiarly, the ORACLE Application have had the Architecture OFA, by the help of that your mention your mount points as per your Business Requirement seeing to the the storage considerations. It will not effect until you may across the Things like Corruption etc.,

    I Hope you understood the Example and as well as the Concept... !!

    - Pavan Kumar N
    · Share on TwitterShare on Facebook
  • Aman....
    Aman.... Member Posts: 22,913 Gold Crown
    edited Sep 16, 2008 9:38AM
    Pavan,
    What you said was correct :-0.
    Aman....

    Edited by: Aman.... on Sep 16, 2008 7:08 PM
    · Share on TwitterShare on Facebook
This discussion has been closed.