| PERFS : Is separate data/index into different tablespaces is better ? [message #59133] |
Thu, 30 October 2003 16:54  |
Yhab
Messages: 7
Registered: October 2003
|
Junior Member |
|
|
Hi,
This is a performance question :
Is separate data/Index into different TS is better for performances ? And how can we improve that ?
Some dba's think YES (because it is better for the system calls to work read/write into two different datafiles in parallel)
Some people think NO (It's better to put everything in the same Tablespace, with one datafile, because the system will work better)
Can anybody help ?
Yhab
|
|
|
|
| Re: PERFS : Is separate data/index into different tablespaces is better ? [message #59136 is a reply to message #59133] |
Thu, 30 October 2003 23:04  |
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
|
Senior Member |
|
|
Hi,
Well as u know the controversy will always exists in different DBAs on the issue of performance tuning.
According to me separating data from indexes will definitely increase the performance. The reason is that the table and their associated indexes are having their blocks read while the blocks are also having rows inserted to them. It will definitely balance the I/O on the Datafiles which increase perfomance.
Along with this u can separate the tables according to their size and no. of transactions processed. Moreover locally managed tablespaces can also be used to improve performance.
|
|
|
|
| Re: PERFS : Is separate data/index into different tablespaces is better ? [message #59142 is a reply to message #59133] |
Fri, 31 October 2003 07:49 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
I have heard of some DBAs/SAs going for Data and Index on the same disk spindles(on a striped RAID volume) to actually improve performance and there "might" be some truth in it, but I have not heard of anybody wanting to place both Tables and Indexes on the SAME tablespace.
TABLES and INDEXes SHOULD ALWAYS be on DIFFERENT tablespaces becos they have different characterstics,not just access patterns. They are 'different' kind of segments and you dont want to put differing type of segments in the same tablespace.
Placing the DATA and INDEX tablespaces on the same disk volumes is a different thing(this is at the disk volume level and is a function of their concurrent access patterns)...
-Thiru
|
|
|
|
| Re: PERFS : Is separate data/index into different tablespaces is better ? [message #59155 is a reply to message #59136] |
Fri, 31 October 2003 19:54 |
Yhab
Messages: 7
Registered: October 2003
|
Junior Member |
|
|
Thank you Daljit,
If you request a famous EMP table (1 000 000 records) with data/index in the same TS and try the same request with data/index in separated TS.
(Do you think we will obtain different response time ?)
I understand that associated blocks are read while there are also having rows inserted.
I suppose that operation to fetch and retreive indexed data is sequential no ? In this case what is the Pb if data/index are mixed in the same TS ?
Separate data/index because they are different type of segments is a data organisation, what is the relationship with decrease/increase performance ?
OS tuning is very important for perfs but I want to understand only the logic with Oracle.
Yhab
|
|
|
|
| Re: PERFS : Is separate data/index into different tablespaces is better ? [message #59172 is a reply to message #59142] |
Sun, 02 November 2003 07:41 |
Yhab
Messages: 7
Registered: October 2003
|
Junior Member |
|
|
If you request a famous EMP table (1 000 000 records) with data/index in the same TS and try the same request with data/index in separated TS.
(Do you think we will obtain different response time ?)
I understand that associated blocks are read while there are also having rows inserted.
I suppose that operation to fetch, retreive indexed data is sequential no ? In this case what is the Pb if data/index are mixed in the same TS ?
Separate data/index because they are different type of segments is a data organisation, what is the relationship with decrease/increase performance ?
OS tuning is very important for perfs but I want to understand only the logic with Oracle.
Yhab
|
|
|
|
| Re: PERFS : Is separate data/index into different tablespaces is better ? [message #59173 is a reply to message #59172] |
Sun, 02 November 2003 12:36 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
There may not be 'much' or 'any' performance difference if you were to place both the tables and indexes in the same tablespace or separate them. I had done some tests earlier(years), and I didnt find anything that will push me definitely into separating them for pure performance reasons.
Just to clarify on the access patterns,
Unlike table scans,index reads are predominantly single block reads ( not always. Fast full Index scan where multiblock reads occur).. But usually they are accessed serially(ie an index lookup to get the rowid,which then is used to access the table data in a btree index) and hence the term 'concurrent access' is not always true.
The idea was to spread the I/O across many disk spindles to reduce head thrash,but if they are separated,in most cases you are not even keeping all the disks busy becos of the serial nature.these days when disks are cheap and there are so many layers(Raid,Volume manager etc) its becoming less and less true, and as I mentioned earlier, physically placing them together in a striped config have shown to actually improve performance .
I separate them for easier management mostly and not for any performance reasons. For eg, I might want to create a separate Index tablespace and make it NOLOGGING ,so that if I loose them , I just recreate them. Again this is not a universal case, just an example where I want to separate them.
Another reason could be creating a separate Index tablespace with a bigger block size than the tables becos of the benefits(ie lesser number of leaf blocks need to be scanned becos of the larger block size) thus reducing the logical reads during an index scan.
I have seen some people separating it out just for the purpose of backups , in that they totally skip the Index tablespace becos they can afford to recreate them in parallel,nologging.
So my earler statement "TABLES and INDEXes SHOULD ALWAYS be on DIFFERENT tablespaces" ..I shouldnt have said 'ALWAYS' ..it depends on your environment..I do it for some management reasons , but not for perfomance.
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
