Oracle Database Discussions

Please provide the details what kind of storage you are using.

Amardeep Sidhu
http://amardeepsidhu.com/blog

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.

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....

that means while i will create any table i must keep datafile and index file in separate disk or mount point .

is that right ?

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

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.

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

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....

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

Pavan,
What you said was correct :-0.
Aman....

Edited by: Aman.... on Sep 16, 2008 7:08 PM

As fas as my understanding goes: everything is dependent upon the type of storage being used.

Mount point is nothing really. I have a home PC having a single HDD and i create 5 mount points. Now wherever you put the stuff, the IO channel is the same as there is only one HDD.

Now i have an external storage of 500 GB (consisting of mulitple hard disks) where some RAID level is implemented. Here i create different mount points for different HDDs. So here it should matter like where you are putting the data.

Now lets say some other storage strategy is in place where data is evenly spread across all HDDs. Now again mount points should not matter.

Experts...please correct if anything is wrong.

:)

AAH! Yes you are right, my mistake that I took mount point as one physical media connected some how. Surely in this way, the contentions are irrelevant of mount points and are related to storage.
I guess this is what Pavan mentioned too in his reply.
@Pavan,
I guess I am corrected by Sidhu :-). Sorry!
Aman....