Re: Tuning Nightmare - Advice Needed

You already had a very good starting point with the literals. Because literals cause each time a different sql to be composed, just continue the idea. For each customer and each type of query store somewhere hints as a string. Then include those hints into the dynamically created sql.

Martin

gdas wrote:
>
> 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 Thu Dec 20 2001 - 10:43:59 CST