Tuning Nightmare - Advice Needed

I've been trying to resolve a query performance issue in our oracle database (8.1.6) for several days now and I'm very frustrated. I'm hoping someone might be able to provide some advice or perhaps just empathy.

The problem is that we have enormous data skew.

We have a decision support web-based application which our customers use to do analysis. The queries which that application sends are all predefined, there is no adhoc querying done by end users.

Basically, all the queries are the same except for one 'dynamic constraint' in the where clause. (...where account_id =[the account _id of the user who is currently logged in]).

We have about 100 accounts and some accounts are very large in terms of number of rows and others are very small and most are somewhere in between.

The largest table (fact table in a star schema orientation) is about 4 million rows.

The skew exists at the account level but also at all the dimension levels, such as time, geography etc... so a "small" account (by overall size) can at any point have a large proportion of the data for a period or region etc... and vice-versa.

I calculated full statistics on all the indexes and tables. I'm pretty sure I have the proper indexes in place.

I then calculated histograms on the columns in the 'fact' table. I've experimented with various bucket sizes...This is basically where I am at now, just trying different bucket sizes (I've gone as high as 254 as low as 5, 50, 75, 100, 150, etc...)...to no avail. I even used a script from ixora.com that was to help me determine the appropriate number of buckets... didn't help.

I then found that our developers were using bind variables in their queries so I asked them to disable that and use literals instead so that the optimizer would make full use of the histograms.

Query performance is now literally all over the map, depending on which account you are logged into, which region you select, which time frame etc...

With bind variables on things were at least consistent.... consistently slow.

I took one query that was slow for account 628 (which is a large account)... saw that oracle was selecting the wrong driving table... so I had the developers put in a USE_NL hint. Once they did... the query came back in under a second (was previously taking about 50 seconds). All good, until I went and checked account 2 ( a very small account) which used to come back in less than 1 second, but now because of the hint, it takes 20 seconds! Exact same query with a different account_id constraint.

Ahhhhhhhhhhhh!

So even using a hint is not helping me and I thought for certain hints would be my way out of this mess.

Everything I do helps the problem query I am working on. But I am finding that if I help one account, it's at the expense of another account.

We have about 5 tables in the schema.

I'm getting so frustrated, I'm almost tempted to denormalize the entire schema and put everything in one giant table.

I read up on stored outlines. I'm not sure if they would have helped...unfortunately the stored outlines feature is only available in Enterprise Edition and we have Standard Edition.

I would appreciate any advice that anyone thinks might help... I've got 2 oracle performance tuning books and none of them spend much time on issues related to skew (other than a page or two on the importance of histogram collection).

Also, I don't feel comfortable posting all the details of our entire schema here, but if someone would like a more thorough description, I can email it.

Thanks in advance.
Best Regards,

Gavin Received on Wed Dec 12 2001 - 02:36:19 CST