Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: An SQL query lasts 60 seconds...

Re: An SQL query lasts 60 seconds...

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 2000/04/03
Message-ID: <954767653.8388.2.pluto.d4ee154e@news.demon.nl>#1/1

pavel_sustr <pXaXvXeXl_sXuXsXtXr_at_geocities.com> wrote in message news:Sq0G4.9577$A4.293020_at_c01read04.service.talkway.com...
> Hello,
>
> I'm going to do some research at our customer's. They have a problem -
> the performance of their Oracle 8.0.5 Server running on RS6000 IBM SP
> is quite poor. It usually takes about 60 seconds (!) for an SQL query
> to complete (e. g. from SQL Plus).
>
> I just want to be sure I haven't ommited anything. I'm going to do
> these things:
>
> ---
>
> 1. Verify that they don't use any unwanted logging (listener logs,
> database logs, session tracing, audits). Check there are no unwanted
> big files on the server that seem to be growing rapidly and
> continuously.
>
> 2. Check whether their server is not overloaded - run performance
> monitor, look at the number of disk I/O, memory status.
>
> 3. Examine their network. Try to tnsping the server, measure the
> response time.
>
> 4. Create a sample SQL command and measure it's performance (SQL Trace
> Facility and TKPROF).
>
> 5. Let Oracle explain the execution plan (EXPLAIN PLAN FOR ...).
>
> ---
>
> Anny comments on what else I should do? Thanks in advance.
>
> PaShu
> --
> Free audio & video emails, greeting cards and forums
> Talkway - http://www.talkway.com - Talk more ways (sm)
>

1 Forget about this issue, 60 secs is really nothing 2 Run utlbstat and utlestat, determine the buffer hit rate, and the library cache hit rate
3 check init.ora parameters
4 check the frequency of checkpoints
5 get more familiar with Oracle tuning. The 5 points you mentioned above should be considered only when the issues I mention don't resolve anything. Usually also it's the application which is badly written and only getting more horsepower can resolve that.

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.

Hth,

Sybrand Bakker, Oracle DBA Received on Mon Apr 03 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US