Home » RDBMS Server » Performance Tuning » Performance difference of same query in 2 database (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi, Solaris)
Performance difference of same query in 2 database [message #561713] Tue, 24 July 2012 23:50 Go to next message
sasnrock
Messages: 19
Registered: August 2007
Junior Member
Hi,

We have 2 database. One is the main DB and other is its replica used for reporting purpose.
We are running a query in the main database, its returning the output in few millisec (~150-200ms). However, when I executed the same query in the reporting DB, its taking more than 2 secs.
I compared the execution plan in both the database and the plan is exactly the same (including cost, bytes).
I checked this even during the off peak time considering might be problem due to huge load on reporting DB.

Could you please let me know what else can be checked as we have a requirement that the query output should be returned in less than 1sec.

I'm not sure if the above information is enough. But appreciate any inputs so that it will be helpful in analysing further.

Thanks
Re: Performance difference of same query in 2 database [message #561714 is a reply to message #561713] Tue, 24 July 2012 23:53 Go to previous messageGo to next message
BlackSwan
Messages: 26720
Registered: January 2009
Location: SoCal
Senior Member
> However, when I executed the same query in the reporting DB, its taking more than 2 secs.
Why are you obsessing over 2 seconds?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Performance difference of same query in 2 database [message #561717 is a reply to message #561714] Wed, 25 July 2012 00:05 Go to previous messageGo to next message
sasnrock
Messages: 19
Registered: August 2007
Junior Member
Hi,

The reason is, we have a external system that uses the query to produce some output to the customer. Their session will be timed out if they dont get the response within 1 second. Actually this is a SLA where the response time should be less than 1 sec. External systems point to reporting DB and hence we are trying to resolve the issue.

Temporarily, we have created a DB link to main DB to avoid the issue. However, we want to avoid the dblink and run the query directly in the reporting DB.
Re: Performance difference of same query in 2 database [message #561733 is a reply to message #561717] Wed, 25 July 2012 01:28 Go to previous messageGo to next message
John Watson
Messages: 8293
Registered: January 2010
Location: Global Village
Senior Member
Hello - are you licensed to use AWR? If so, can you post the AWR SQL report for the query from both databases?
Re: Performance difference of same query in 2 database [message #562769 is a reply to message #561733] Sat, 04 August 2012 13:44 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Run the query from SQLPLUS using SET AUTOTRACE TRACEONLY so that we can see what it does in both systems.

Also, would be nice to have seen the query? I am amazed at how many posts say "my query is slow" but then no query is given.

Kevin
Previous Topic: Not exists inside exists is having very low performence
Next Topic: Cannot find TraceFile for SESSION
Goto Forum:
  


Current Time: Sat Jul 04 11:22:09 CDT 2020