OraFAQ Forum: Performance Tuning » Slower DB response [Oracle 10g on Solaris 5.10]

Home » RDBMS Server » Performance Tuning » Slower DB response [Oracle 10g on Solaris 5.10] (Oracle 10g on Solaris 5.10) (

) 1 Vote

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

Slower DB response [Oracle 10g on Solaris 5.10] [message #526547]

Tue, 11 October 2011 09:28

suddhasatwa_bhaumik
Messages: 18
Registered: October 2011
Location: India

Junior Member

Hello
We are having a database running in Oracle 10.2.0.4 on Solaris 5.10 V240 server. From some time now, there is an application batch job which is taking double the time than it used to take usually.
One of the many findings that I have is, there was a minor code change in the batch job wherein one equijoin on a heavily used table was added, and also on the same table an 'IN' clause was added to support a recent functionality.
My query is:
Can this change have (anyhow) impacted the execution plans of those SQL queries which are changed?

Also, we are seeing very high DB sequential file read from STATSPACK/AWR reports when the job runs. At the same time when the job runs, I can see that RMAN backup is scheduled to take backups on disk. Can this be one more reason of this performance issue?

Any help on this matter would really be helpful.
In case of further inputs, please let me know.

SB.

Report message to a moderator

Re: Slower DB response [Oracle 10g on Solaris 5.10] [message #526549 is a reply to message #526547]

Tue, 11 October 2011 09:33

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Can this change have (anyhow) impacted the execution plans of those SQL queries which are changed?
yes

>Can this be one more reason of this performance issue?
yes

Report message to a moderator

Re: Slower DB response [Oracle 10g on Solaris 5.10] [message #526552 is a reply to message #526549]

Tue, 11 October 2011 09:37

suddhasatwa_bhaumik
Messages: 18
Registered: October 2011
Location: India

Junior Member

Thanks for such a prompt reply.

A brief explanation on how/why addition of equijoins and 'IN' clauses in WHERE clause may/can cause performance bottlenecks would really be helpful. Please note that there are no other changes done on the database, neither with any of the DB parameters nor with any of the other codes/scripts.

Regarding RMAN Query-
If (suppose) the business requirement restricts us from changing the schedule for these RMAN backups on disk, what are the possible options to 'rune' the existing RMAN backups so as to minimize the high DB sequential file reads?

Cheers!
SB.

Report message to a moderator

Re: Slower DB response [Oracle 10g on Solaris 5.10] [message #526556 is a reply to message #526552]

Tue, 11 October 2011 09:47

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>A brief explanation on how/why addition of equijoins and 'IN' clauses in WHERE clause may/can cause performance bottlenecks would really be helpful.
>Please note that there are no other changes done on the database, neither with any of the DB parameters nor with any of the other codes/scripts.

EXPLAIN PLAN changes

>If (suppose) the business requirement restricts us from changing the schedule for these RMAN backups on disk, what are the possible options to 'rune' the existing RMAN backups so as to minimize the high DB sequential file reads?
There are none.
Accept reality

Report message to a moderator

Re: Slower DB response [Oracle 10g on Solaris 5.10] [message #526557 is a reply to message #526556]

Tue, 11 October 2011 09:49

suddhasatwa_bhaumik
Messages: 18
Registered: October 2011
Location: India

Junior Member

many thanks for the information.

Report message to a moderator

Previous Topic:	Performance Issue (merged 2)
Next Topic:	sga,pga parameter resizing

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 05:56:32 CDT 2024