Skip to Main Content
  • Questions

  • Indexes Maintenance in a Managed Hosting Setup

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, oradbanj.

Asked: November 24, 2014 - 9:19 pm UTC

Last updated: December 02, 2014 - 5:44 pm UTC

Version: 11.2.0.3

Viewed 1000+ times

You Asked

Hi Tom,

We have a Oracle Standard Edition installed in a managed hosting environment.

The various clients access data pertinent to them using CLIENT_ID column added to each table ( when a client issues "SELECT * FROM MYTAB", it actually becomes "SELECT * FROM MYTAB where CLIENT_ID = NNN" )

Think of it as "poor man" version of Partitioning.

Now some information about structure

- several tables having a few million rows
- most of the large tables having 12+ indexes ( yes - I know sounds excessive but more on it later).
- most of large tables having 100+ constraints; some of them 10-level deep
- the parent/child indexing has been taken care of ( so that there is no FTS when searching for a child belonging to a parent)

Now the problem:

We have to regularly reload data for a given client WITHOUT affecting other clients who are using the system.

Given the amount of indexes and constraints on few large tables, it takes a lot of time. Whenever a row needs to be deleted from a table - it has to search several level of child tables.

The conventional wisdom is to do the following :

- drop indexes
- load data
- rebuild indexes

But - given it is managed hosting environment, we don't want other clients whose queries might be running on same tables be affected by index rebuild; because presence/absence of an index at a given time can affect the query plans. Besides - the index rebuild and DML issued by other clients may interfere with each other.

So what we are looking for a is an approach as follows :

- response time of queries being executed by other clients is not affected ( consistency is the key here.

- any maintenance activity for a given client ( where CLIENT_ID = ### ) should not interfere with either queries (or DML) being run by other clients

- Dropping of Indexes has pretty much been ruled out

Looking for your advice here.


and Tom said...

about the only thing I can think of here is - change the client_id.

That is, generate a new client_id.
Load the data into this new client_id.
Change the client_id for the customer in your metadata table that maps the client_id to a specific customer.

You've just skipped the delete part - you only need to load. You can periodically clean up the data in a lazy fashion during periods of less activity - or just constantly be cleaning it up a little at a time - forever.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Individual schema Vs Single schema for hosted environment

Ravikanth Beevanapalli, December 01, 2014 - 11:56 pm UTC

Hi Tom,

What is your opinion over spawning a unique schema for each customer Vs client_id approach? It would be good for data isolation but only thing i could think of is software maintenance. For example, we have to install our product on each of the instances and any change in structure or code should be applied to all the instances.

Thanks.
Tom Kyte
December 02, 2014 - 12:11 pm UTC

In version 12c - multi-tenant will be the way to go.

Prior to that, I would follow the path of single instance, single schema, and a view that incorporates a client_id in the predicate. You can use partitioning for big customers (they get their own list partition) when necessary.

Multiple instances is problematic to manage (upgrade, patch, upgrade your app, making sure instance 1 doesn't consume all of the resources making instances 2-100 dead in the water and so on

Individual schema Vs Single schema for hosted environment

Ravikanth Beevanapalli, December 02, 2014 - 5:37 pm UTC

Thanks Tom for your response. Good to see you back.

I agree 12c would be way to go. I got very good insight in Oracle Open World. But we are still on 11gR2 and might take a while to migrate. The design that has been proposed in our company is individual USER/SCHEMA per client in the same instance as apposed to separate instance for each client. Do you see any issue with this method?

Thanks!
Tom Kyte
December 02, 2014 - 5:44 pm UTC

big time.

Say you have 200 clients. Your application logs in as user1, user2, userN.

Your application issues "select * from t", but that is really "select from userN.t", but in the shared pool all we see is "select * from t"

so, your 200 clients run the application. They all think they are parsing the same sql but they are not. the shared pool will become flooded with cursors that look the same but are different. It'll take longer and longer to parse as we have a longer and longer list to look through. Additionally - we'll only cache about 100 child cursors per sql statement so you'll be hard parsing like mad too (client 1's sql gets aged out and we'll have to parse it again - kicking out client 2's sql - who will have to parse it again and so on)


You'd want to make sure your application issues "select * from USER1.t", not "select * from t" to ensure they are all using distinct sql.

multi-tenant will not make you do that. single instance, one database will.


Individual schema Vs Single schema for hosted environment

Ravikanth Beevanapalli, December 02, 2014 - 5:48 pm UTC

Thanks Tom, very helpful!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.