Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Tuning Nightmare - Advice Needed

Re: Tuning Nightmare - Advice Needed

From: Joseph Kane <SunTzu_Samurai_at_Yahoo.Com>
Date: 28 Dec 2001 15:36:00 -0800
Message-ID: <6b292cbb.0112281536.35b8ff47@posting.google.com>


One of the best sources for efficent performance optimization can be found on the site that Cary Millsap put up. He was the former VP of the System Performance Group at Oracle and is an expert on this particular topic. I've read a lot of his work and seen him help other folks on the various oaug message boards.

www.Hotsos.Com

Good Luck....

JK

gdas1_at_yahoo.com (gdas) wrote in message news:<7a4ed455.0112120036.aab1115_at_posting.google.com>...
> 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 Fri Dec 28 2001 - 17:36:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US