5

I have a doubt about the use of oracle tablespaces. I've decided to define for each schema of the datababase two different tablespaces (one for data, and another one for indexes).

The problem is that some time has passed, and my developers had mixed the tablespaces creating indexes in the data tablespace and creating tables and partitions in the index tablespace.

I would like to know how this could impact on performance.

Thanks in advance

migrated from stackoverflow.com Mar 6 '14 at 9:46

This question came from our site for professional and enthusiast programmers.

  • 1
    You can always move tables and indexes to the correct tablespace. – Rene Mar 6 '14 at 8:17
  • yes, i know but i would like to know the consequences of the bad use – Lucas Mar 6 '14 at 8:44
4

In reality, on the modern devices the impact on performance is likely to be minimal.

The old "tablespace for index, tablespace for data" mantra comes from a time when storage tended to be made up of sets of single devices. I.E. if you said that you wanted to put a tablespace on a certain disk, it went onto a particular physical disk.

Having both indexes and data on the same disk increases disk contention as Oracle can't read both index and data at the same time.

However, in most reasonably specc'd servers these days a 'disk' as far as Oracle is concerned is not necessarily a single disk. Often they are striped disk arrays. In this case there often isn't an increase in disk contention as there are multiple physical disks and IO cards in the server and the indexes and data don't always clash anyway.

So, in short - it depends on your hardware set-up.

Though, to be honest, unless you're really on top of monitoring, responding to your "top SQL" reports, have good data model designers and are building the right indexes you are much more likely to find performance improvements by learning about those things first. I'm not saying you don't do those things, but if you don't you should look to improve there first.

If you think the tablespaces issue is a problem, then (as others have said) you can just move the tables and indexes around to put them back in the right place.

Once done, a good plan would be to implement some kind of standard for how you implement table and index creates, probably using substitution variables for the names of tablespaces, most certainly supported by a scripted test that runs as part of your commit / build process to check that people aren't putting things in the wrong place.

You can then follow that up with a regular process on your demo / test server that checks for things in the wrong place and sends an e-mail to the team when it discovers something. That way you stand a chance of discovering problems before they hit the live environment.

I'd use this approach for any standard that is broken more than once or twice.

As for non-performance related implications, it can have an impact on the maintainability of your tablespaces and ensuring that the datafiles are properly configured. Even that is probably negligible on modern servers.

Is should say though - I'm not a DBA, I've just worked as a developer with Oracle a LONG time - so when it comes to maintainability, I'm no expert...

  • having data on the same disk instead of seperate disks increases disk contention yes. But why would this have anything to do with seperating indexes and tables? You can just as well put different datafiles on different disk for the same tablespace containing both indexes and tables, this would also spread out IO across disks. If you access index to read table data in one query you are accessing index and table data serial and not parallel, so no need to spread IO for that. – Dieter DHoker Mar 6 '14 at 22:42
  • @DieterDHoker OK, in the strictest sense, this isn't contention - but imagine a multiblock index range scan leading to table lookup by ID. This could result in a "read index block - read table block(s) - read index block - read table blocks(s)". If index and table are on the same disk there is a huge amount of time wasted on disk seeks. Since Oracle doesn't do any read-ahead caching, and back in the day disks didn't either, and seek times were high, this would result in sub-optimal performance. This was regarded as disk-contention even though there was never actually a queue. – Rob Baillie Mar 7 '14 at 11:25
  • @DieterDHoker - though in a modern disk set-up the problem pretty much disappears, hence my assertion that it probably has little impact in any reasonably spec'd server. – Rob Baillie Mar 7 '14 at 11:26
  • In the case of your index range scan: with good clustering factoring wouldn't multi-block IO be happening anyway wether we use different disks or not. If we have bad clustering factor the Oracle optimizer can instead of going back and forth collect all rowids first and then reorder them to still do multi-block IO. Maybe it didn't do this yet in older oracle versions but we need to do seek for this single block IO in any case with indexes on the same disk or different disk. (?) – Dieter DHoker Mar 7 '14 at 16:54
  • You may well be right @DieterDHoker, just as long as Oracle doesn't need to read-ahead cache in order to do what you suggest. I don't know what Oracle did in 7.4, but the behaviour I suggest is what was described to me back in the day (about 15 years ago I guess) and it's the only performance reason I've ever heard for keeping the index and data table spaces apart. All in all though you're just adding more weight to the assertion that it probably has little or no effect on performance in a modern setup, aren't you? – Rob Baillie Mar 7 '14 at 17:05
1

We can talk about performance only if:

  1. Your tablespaces are stored on different devices which are not RAID-connected
  2. In the same query the table and index on this table are used

That's how I see this with a real-life example:

Just imagine a big queue (queries/read-write operations) in a supermarket (database) to only one cash point (hard drive/RAID group of hard drives) and there are 5 sellers (tablespace). It simply does not matter what seller you want to contact: they will all use one cash point and they will wait until it is free.

Now you want to increase the performance and you add 4 cash points more. From now on it helps to have 5 sellers: every of them has his own cash point.

Now let's say every seller has store places (tables) and files with description where to find goods (indices). How it works:

  1. customer wants to buy apples and says this to seller
  2. seller searches for the goods in the file (if there is one and the amount of goods is really big) and goes to some point
  3. at this point he picks up the goods and brings them to the customer

So as you can see nearly nothing can be improved. The only thing you can do is asking some other person to read the file and send the seller directly to the place in parallel. And if you return to our topic this is the only advantage of storage separation. You can read from two different devices simultaneously.

As you can see from the example above it will not give you much more performance. Only a little bit. And only if you have separated storage system.

Maybe I am somehow wrong, if so, correct me please.

  • If both index and tables are used in the same query they are never used in parallel. In your example you cannot send a store clerk to fetch apples and get someone else to read the file where to find the apples in parallel as the clerk has no idea where to go before the other person finds the location in the file. – Dieter DHoker Mar 6 '14 at 22:21
0

It depends on storage which is used for this tablespaces (for datafiles used by tablespaces). If tablespaces are using the same device then it shouldn't have impact on performance. But if e.g. one tablespace is on faster disk then it can obviously have impact on performance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.