| 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
I once read an article where a dba said he saved his company > $500 000
because he reduced a query time from 2 seconds to < 1 second. I think it was
a help desk query. I guess they multiplied how much people make by the time
saved and said it was a productivity gain. I thought BS because I'm pretty
sure that 1 second saved isn't going to be used elsewhere (but now I do it
all the time at my work :-)). But I think you could set up a 9i instance and
do what you're thinking and measure the time diff and puke out the costs. If
your program is a batch maybe you won't save anything to specific users but
you will benefit by scalability on the server.
Mike
ganstadba_at_hotmail.com
----- Original Message -----
From: "Jesse, Rich" <Rich.Jesse_at_quadtechworld.com>
To: <oracle-l_at_freelists.org>
Sent: Tuesday, March 02, 2004 10:39 AM
Subject: 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?
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
-- 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:48:50 CST
 |
 |