Path: news.easynews.com!easynews!sjc-peer.news.verio.net!news.verio.net!sn-xit-01!sn-post-01!supernews.com!corp.supernews.com!not-for-mail From: Ron Reidy Newsgroups: comp.databases.oracle.server Subject: Re: Tuning Nightmare - Advice Needed Date: Wed, 12 Dec 2001 04:20:30 -0700 Organization: Posted via Supernews, http://www.supernews.com Message-ID: <3C173D7E.134DC64F@indra.com> X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.4.2-2 i686) X-Accept-Language: en MIME-Version: 1.0 References: <7a4ed455.0112120036.aab1115@posting.google.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: newsabuse@supernews.com Lines: 96 Xref: easynews comp.databases.oracle.server:127934 X-Received-Date: Wed, 12 Dec 2001 04:22:58 MST (news.easynews.com) 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 We need to see the offending SQL and the output from tkprof in order to offer assistance. -- Ron Reidy Oracle DBA Reidy Consulting, L.L.C.