Path: news.easynews.com!easynews!news-out.cwix.com!newsfeed.cwix.com!news.tele.dk!small.news.tele.dk!212.177.105.133!news.mailgate.org!web2news!d225s244.hotbank.com!not-for-mail From: "John Darrah" Newsgroups: comp.databases.oracle.server Subject: Re: Tuning Nightmare - Advice Needed Date: Wed, 12 Dec 2001 17:02:19 +0000 (UTC) Organization: Mailgate.ORG Server - http://www.Mailgate.ORG Lines: 188 Message-ID: <15e9c449e88b29dc01b5ad4bda120e52.36240@mygate.mailgate.org> References: <7a4ed455.0112120036.aab1115@posting.google.com> NNTP-Posting-Host: d225s244.hotbank.com Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: news.mailgate.org 1008170500 31320 63.83.225.244 (Wed Dec 12 18:02:19 2001) X-Complaints-To: abuse@mailgate.org NNTP-Posting-Date: Wed, 12 Dec 2001 17:02:19 +0000 (UTC) Injector-Info: news.mailgate.org; posting-host=d225s244.hotbank.com; posting-account=36240; posting-date=1008170500 User-Agent: Mailgate Web Server X-URL: http://www.Mailgate.ORG Xref: easynews comp.databases.oracle.server:127965 X-Received-Date: Wed, 12 Dec 2001 10:02:21 MST (news.easynews.com) Mabey you could add a table that breaks up accounts into sizes and select what type of query you run by the size of account. It could be a table something like: ACCOUNT_ID NUMBER SIZE VARCHAR2 Check ('SMALL','MED','BIG') The web servers could suck the rows from this table into a cache and determine which type of query to send to the DB by the size of the account (BIG gets the query with use_hash and small gets the query with use_nl or vice versa). This should allow you more control than histograms and literals. "gdas" wrote in message news:7a4ed455.0112120036.aab1115@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 -- Posted from d225s244.hotbank.com [63.83.225.244] via Mailgate.ORG Server - http://www.Mailgate.ORG