Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: query slow in 9i, but not slow in 8i

RE: query slow in 9i, but not slow in 8i

From: Jesse, Rich <>
Date: Tue, 2 Mar 2004 09:39:12 -0600
Message-ID: <>

My opinion is that I'm not at all good at coming up with tangible costs associated with poor design. So now we have a program that checks a 2M-row table every 15 minutes to see if there are any new 'N' values in a Y/N column. And since we're on 8i using CS=F to compensate for a lack of binds (my ignorance and lack of emphasis in Oracle classes are to blame for that one), we get an FTS on that table every 15 minutes. You can imagine what's happening to our buffer cache. I'm hoping to compensate once again in 9i by allowing the optimizer to peek at the binds while providing a histogram on that indexed column, so that it'll use it to get the 5-10 rows out of the 2M.


Rich Jesse                        System/Database Administrator      QuadTech, Sussex, WI USA

> -----Original Message-----
> From: Mladen Gogala []
> Sent: Tuesday, March 02, 2004 9:09 AM
> To:
> Subject: Re: query slow in 9i, but not slow in 8i
> On 03/02/2004 09:31:03 AM, "Jesse, Rich" wrote:
> > We have a few DSS-like queries such as yours below that
> users don't think
> > perform very well. But if you look at it, there's not a
> whole lot for
> > Oracle to filter on other than speciesid. Perhaps extreme,
> but for the
> > long-term, maybe a redesign is in order?
> Actually Rich, you opened a very interesting question: when
> is an application eligible for the total re-design? I've seen
> applications that are essentially patched up from Oracle V6 to 9i
> and from SQL*Forms 2.3 -> Developer 6i and that are still
> working in an optimal fashion,
> because they were written well. In my opinion, adding
> entities, objects and functionality
> is what kills the applications. Typical example is HIPAA:
> most of the HMO's used to have
> their own keys for providers, members and groups. Also,
> they've had millions of claims already
> in their system and HIPAA changed things in a very
> significant way. Yet most of the HMO's
> decided that they'll patch up their existing applications and
> not re-design them from ground up.
> That meant adding translation layers, new referential
> integrity, adding new columns to the
> existing tables, frequently breaking the 3rd normal form and
> writing monstrous queries which
> took many hours to optimize to the barely acceptable levels
> of performance. In the long run,
> I'm convinced that it would have been cheaper to redesign the
> application systems from scratch.
> What's your opinion?

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Tue Mar 02 2004 - 09:36:22 CST

Original text of this message