OraFAQ Forum: Performance Tuning » Query running slow

Home » RDBMS Server » Performance Tuning » Query running slow (19c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query running slow [message #684070]

Wed, 31 March 2021 18:02

JackBauer
Messages: 24
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

Report message to a moderator

Re: Query running slow [message #684072 is a reply to message #684070]

Thu, 01 April 2021 07:00

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Query running slow [message #684073 is a reply to message #684072]

Thu, 01 April 2021 08:19

JackBauer
Messages: 24
Registered: February 2021

Junior Member

It is a select * from table;

Report message to a moderator

Re: Query running slow [message #684074 is a reply to message #684073]

Thu, 01 April 2021 09:14

John Watson
Messages: 8964
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.

Report message to a moderator

Re: Query running slow [message #684076 is a reply to message #684073]

Thu, 01 April 2021 14:53

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Query running slow [message #684077 is a reply to message #684076]

Thu, 01 April 2021 15:57

JackBauer
Messages: 24
Registered: February 2021

Junior Member

I understand all that, but couldn't it be network latency?

Report message to a moderator

Re: Query running slow [message #684078 is a reply to message #684077]

Thu, 01 April 2021 16:02

John Watson
Messages: 8964
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.

Report message to a moderator

Re: Query running slow [message #684079 is a reply to message #684078]

Thu, 01 April 2021 16:06

JackBauer
Messages: 24
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

Report message to a moderator

Re: Query running slow [message #684080 is a reply to message #684077]

Thu, 01 April 2021 20:06

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Query running slow [message #684081 is a reply to message #684079]

Fri, 02 April 2021 01:16

John Watson
Messages: 8964
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.

Report message to a moderator

Re: Query running slow [message #684084 is a reply to message #684080]

Fri, 02 April 2021 05:09

JackBauer
Messages: 24
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?

Report message to a moderator

Re: Query running slow [message #684085 is a reply to message #684084]

Fri, 02 April 2021 05:30

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Query running slow [message #684086 is a reply to message #684085]

Fri, 02 April 2021 14:03

JackBauer
Messages: 24
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.

Report message to a moderator

Re: Query running slow [message #684161 is a reply to message #684070]

Tue, 13 April 2021 05:17

JackBauer
Messages: 24
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

Report message to a moderator

Previous Topic:	Cannot index temp table after inserting rows
Next Topic:	Function blocking itself

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 02 11:54:51 CST 2025