Home » RDBMS Server » Performance Tuning » Query running slow (19c)
Query running slow [message #684070] Wed, 31 March 2021 18:02 Go to next message
JackBauer
Messages: 19
Registered: February 2021
Junior Member
Hello,

I execute a query on a DB and it runs in under 1 second.

A developer runs the same query from another client and the query takes 45 seconds to give a result.

What could be making such a difference? I suppose this is due to network problems, right?

But how to prove that?

Thanks
Re: Query running slow [message #684072 is a reply to message #684070] Thu, 01 April 2021 07:00 Go to previous messageGo to next message
BlackSwan
Messages: 26763
Registered: January 2009
Location: SoCal
Senior Member
[quote title=JackBauer wrote on Wed, 31 March 2021 16:02]Hello,

I execute a query on a DB and it runs in under 1 second.

A developer runs the same query from another client and the query takes 45 seconds to give a result.

What could be making such a difference? I suppose this is due to network problems, right?

But how to prove that?

Thanks[

/quote]

Please post COPY & PASTE proof that SQL are identical & running against the same instance.
Re: Query running slow [message #684073 is a reply to message #684072] Thu, 01 April 2021 08:19 Go to previous messageGo to next message
JackBauer
Messages: 19
Registered: February 2021
Junior Member
It is a select * from table;
Re: Query running slow [message #684074 is a reply to message #684073] Thu, 01 April 2021 09:14 Go to previous messageGo to next message
John Watson
Messages: 8547
Registered: January 2010
Location: Global Village
Senior Member
You are going to have to provide more information. At the very least, in your SQL*Plus session:

set timing on
set autotrace traceonly
run the statement

and post the results here. Results from you running it and your colleague.
Re: Query running slow [message #684076 is a reply to message #684073] Thu, 01 April 2021 14:53 Go to previous messageGo to next message
BlackSwan
Messages: 26763
Registered: January 2009
Location: SoCal
Senior Member
JackBauer wrote on Thu, 01 April 2021 06:19
It is a select * from table;

How many rows in the table?
Does table contain any LOBs?

Post proof SQLs are running against the same instance.
Re: Query running slow [message #684077 is a reply to message #684076] Thu, 01 April 2021 15:57 Go to previous messageGo to next message
JackBauer
Messages: 19
Registered: February 2021
Junior Member
I understand all that, but couldn't it be network latency?
Re: Query running slow [message #684078 is a reply to message #684077] Thu, 01 April 2021 16:02 Go to previous messageGo to next message
John Watson
Messages: 8547
Registered: January 2010
Location: Global Village
Senior Member
JackBauer wrote on Thu, 01 April 2021 21:57
I understand all that, but couldn't it be network latency?
It could be the phase of the moon. Better do as I suggested if you want to find out the real reason.
Re: Query running slow [message #684079 is a reply to message #684078] Thu, 01 April 2021 16:06 Go to previous messageGo to next message
JackBauer
Messages: 19
Registered: February 2021
Junior Member
Sure I will, but we are on holidays now.

But this does not give any details about the network...

set timing on
set autotrace traceonly
run the statement
Re: Query running slow [message #684080 is a reply to message #684077] Thu, 01 April 2021 20:06 Go to previous messageGo to next message
BlackSwan
Messages: 26763
Registered: January 2009
Location: SoCal
Senior Member
JackBauer wrote on Thu, 01 April 2021 13:57
I understand all that, but couldn't it be network latency?

If it is "network latency", then it should exist for any & EVERY network operation that occurs.
Using COPY & PASTE show us other network operation that are also slow from inherent network latency & not just database query.
Re: Query running slow [message #684081 is a reply to message #684079] Fri, 02 April 2021 01:16 Go to previous messageGo to next message
John Watson
Messages: 8547
Registered: January 2010
Location: Global Village
Senior Member
JackBauer wrote on Thu, 01 April 2021 22:06
Sure I will, but we are on holidays now.

But this does not give any details about the network...

set timing on
set autotrace traceonly
run the statement
Yes it does.
Re: Query running slow [message #684084 is a reply to message #684080] Fri, 02 April 2021 05:09 Go to previous messageGo to next message
JackBauer
Messages: 19
Registered: February 2021
Junior Member
BlackSwan wrote on Fri, 02 April 2021 03:06
JackBauer wrote on Thu, 01 April 2021 13:57
I understand all that, but couldn't it be network latency?

If it is "network latency", then it should exist for any & EVERY network operation that occurs.
Using COPY & PASTE show us other network operation that are also slow from inherent network latency & not just database query.
If I am in Spain and the DB is in the UK and the developer is in India, then we would have the same network latency?
Re: Query running slow [message #684085 is a reply to message #684084] Fri, 02 April 2021 05:30 Go to previous messageGo to next message
Michel Cadot
Messages: 67851
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It depends on the link so you have to ask the network admin.
And if you want to quickly know (and you already did know it instead of wasting everyone time) it you post what John asked you.

Re: Query running slow [message #684086 is a reply to message #684085] Fri, 02 April 2021 14:03 Go to previous messageGo to next message
JackBauer
Messages: 19
Registered: February 2021
Junior Member
As I said, I will, but we are on holidays.

But I get this question a lot, someone says the query runs slow and I try it, and it works very fast.
Re: Query running slow [message #684161 is a reply to message #684070] Tue, 13 April 2021 05:17 Go to previous message
JackBauer
Messages: 19
Registered: February 2021
Junior Member
So at the end it was a ports problem, the firewall was blocking some of them, then a PGA problem.

We had to increase the PGA because we saw PGA_AGGREGATE_LIMIT Throttling in the AWR reports.

Thanks
Previous Topic: Cannot index temp table after inserting rows
Next Topic: Performances and tuning
Goto Forum:
  


Current Time: Mon May 17 11:35:49 CDT 2021