An SQL query lasts 60 seconds... 
Author Message
 An SQL query lasts 60 seconds...

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.***.com/ - Talk more ways (sm)



Wed, 18 Jun 1902 08:00:00 GMT
 An SQL query lasts 60 seconds...


Quote:
> 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



Wed, 18 Jun 1902 08:00:00 GMT
 An SQL query lasts 60 seconds...


Quote:

> 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 {*filter*}
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



Wed, 18 Jun 1902 08:00:00 GMT
 An SQL query lasts 60 seconds...
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



Quote:



> > 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 {*filter*}
> 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.***.com/ - Talk more ways (sm)


Wed, 18 Jun 1902 08:00:00 GMT
 An SQL query lasts 60 seconds...

Quote:

> 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





> > > 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 {*filter*}
> > 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.***.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



Wed, 18 Jun 1902 08:00:00 GMT
 An SQL query lasts 60 seconds...

Interesting thought.

Are talking about connections from NT front-ends
to Unix servers ?

I used to run Samba on my HP9000 so that I could
backup my NT to the HP.

When I upgraded NT to SP3 it suddenly took the
NT a minute to find any volumes on the HP. Is
this a coincidence or what ?

P.S.  Does anyone have Samba working on
HPUX 11 yet ?

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Quote:


>> 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!
>> ***
> 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)



Wed, 18 Jun 1902 08:00:00 GMT
 An SQL query lasts 60 seconds...
I'm assuming that the user is running SQL*Plus on NT.
If that's true, do you get the same result running locally using ORACLE_SID
with the same queries?
What happens if you rerun the same query immediately?

Mike Hately


Quote:

> > 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!
> > ***



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. Bug: Multi-Threaded Server and 60 second delay on queries

2. BobMgr::GetBuf: Bob write not complete after 60 seconds

3. BobMgr::GetBuf: Bob write not complete after 60 seconds

4. rst.Delete Takes 30-60 seconds in an OBBCdirect workspace

5. patch for 60 seconds bug

6. Interesting date/time 60 second problem with Postgresql

7. Oracle type 4 driver(s) pause for 60 seconds?

8. ORA-12545 Oracle Listener dieing afterapprox 60 seconds on linux RH 7.1

9. Setting CRONTAB job to run every 60 seconds

10. ORA-12545 Oracle listener dying after approx 60 seconds on Linux

11. OCI app hangs on oclose for 60 seconds or until another db operation

12. ORA-12545 Oracle Listener dieing afterapprox 60 seconds on linux RH 7.1


 
Powered by phpBB® Forum Software