| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| 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
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
Rich Jesse System/Database Administrator rich.jesse_at_quadtechworld.com QuadTech, Sussex, WI USA
> -----Original Message-----
> From: Mladen Gogala [mailto:mladen_at_wangtrading.com]
> Sent: Tuesday, March 02, 2004 9:09 AM
> To: oracle-l_at_freelists.org
> 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?
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Mar 02 2004 - 09:36:22 CST
 |
 |