Re: An SQL query lasts 60 seconds...

pavel_sustr wrote:
>
> Sorry guys, but you don't seem to understand the problem. It's not your
> fault, because I don't have enough information either (you know how it
> is when a customer starts complaining about something ;) but the
> problem is that
>
> ***
> _ANY_ _QUERY_ lasts more than a minute, even a simple query in SQL Plus!
> ***
>
> I should have emphasized the word "any" in my original mail. This is
> not normal and it doesn't have anything in common with poorly written
> SQL queries.
>
> As for the query processing time. No, I'm not that stupid to think that
> every query comes back in less than a minute. I used to work with a 20
> GB DB on Informix and our longest batches used to take more than 5 or 6
> days to finish and they were optimized at the maximum. I agree that
> application programmers often create a solution which, from the
> standpoint of database design, is simply terrible and uneffecient.
>
> But this is not the case. Yes, I can check init.ora, and more
> importantly ask their DB admin what they have done with the database
> and when the problem started. I suppose that it didn't use to be that
> slow before. But as I've said, I just don't have enough information.
> They only want me to come and see what's wrong. Perhaps they just want
> to offer a job to me :)
>
> Anyway, thanks for your previous answers :)
>
> PaShu
>
> On Mon, 3 Apr 2000 10:23:22 -0500 "Jason Kratz" <JasonKratz_at_excite.com>
> wrote:
> >
> > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> > news:954767653.8388.2.pluto.d4ee154e_at_news.demon.nl...
> > >
> > > Sorry to be a bit blunt, to me it looks like ridiculous someone is
> > > complaining about a query lasting 60 seconds. In one of my previous jobs
 90
> > > mins was normal. OK, I didn't write it, and we got user revolt, I admit
> > > that, but the cause was all grouping and sorting was executed on the
 client.
> > > In that case you can tune a network whatever you want, but you need to get
> > > rid of sorting at the client side.
> > >
> >
> > Thankfully someone said this ;) I get so tired of hearing people bitch
> > about query processing times. Yes...sometimes queries are written poorly
> > and can really be optimized but to expect every query to come back in less
> > than a minute every time is just stupid. Not blaming the guy who posted
> > here. However developers and others should be trying to get across to users
> > that depending on the database not everything is going to be fast all the
> > time.
> >
> > Jason
> >
> >
> >
>
> --
> Free audio & video emails, greeting cards and forums
> Talkway - http://www.talkway.com - Talk more ways (sm)

        SET TIMING ON and get the timing data for Oracle. I suspect you will find that the bulk of the time is either disk I/O time or network time. If you are on Unix you can check disk I/O activity using monitor or top or iostat. Networks are harder to evaluate since, if the ones I have had to deal with are typical, most Network Managers are extremely defensive and unwilling to cooperate. (I think the NT guys are afraid you will recommend Novell and vice versa)

-- 
Jerry Gitomer 
Once I learned how to spell DBA, I became one