RE: Query processing takes very variable time


Sorry,  I am not a DBA and was trying to get help for in-house DBAs
while they find the reason.



It is Oracle 10.2.0.4 on Solaris platform with a 2-node RAC (though
shutting down and making it single-node also gives the same problem). We
don't see problem with first query only. It is intermittent. The shared
storage is SUN-StorEdge3320.



However, one thing they are looking at is related to Oracle HWM.



I will update with more details once we resolve this.



Thanks for all the help.



-- Pradeep



________________________________

From: David Barbour [mailto:david.barbour1@xxxxxxxxx]
Sent: Tuesday, December 30, 2008 12:56 PM
To: oracledba.williams@xxxxxxxxx
Cc: Pradeep Chetal; Jared Still; oracle-l@xxxxxxxxxxxxx
Subject: Re: Query processing takes very variable time



It might help if we knew the version of Oracle and the Operating System
at a minimum.  Since we're tossing about WAGS, why not check to make
sure the optimizer settings on both instances are identical?

On Tue, Dec 30, 2008 at 11:06 AM, Dennis Williams
<oracledba.williams@xxxxxxxxx> wrote:

Pradeep,



As others have pointed out, there can be many reasons for erratic
behavior.



Here is my personal favorite. If you are using bind variables, the first
time the statement is parsed, an execution plan is created, and all
subsequent executions use that plan. So, suppose after the database is
bounced, the first execution has bind variables that causes an unusual
execution plan. All future executions will get that plan. Nice.



Dennis Williams

On Mon, Dec 29, 2008 at 6:53 PM, Pradeep Chetal
<Pradeep.Chetal@xxxxxxxxxxxxxx> wrote:



Sorry, Jared for the cryptic request. It is a complex process to set up
the tables, etc, but once they are set, we run a stored proc that works
on these tables - and the SQL code seems very simple - selects, inserts,
and updates [even those are simple, nothing fancy ]. However, the
processing factor takes long sometimes (not all the times).



I was hoping to look for *extraneous* factors (outside the code) that
can affect the processing and if anyone on this list has any experience
like that.



Thanks,



-- Pradeep



________________________________

From: Jared Still [mailto:jkstill@xxxxxxxxx]
Sent: Monday, December 29, 2008 3:17 PM
To: Pradeep Chetal
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Query processing takes very variable time





On Mon, Dec 29, 2008 at 5:46 AM, Pradeep Chetal
<Pradeep.Chetal@xxxxxxxxxxxxxx> wrote:

Without going into complete details, has anyone on the list seen
something like that? Any ideas.

Without going into complete explanations, yes.


Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist







Pradeep Chetal
Sr. Director - Infrastructure Architecture

________________________________

Mformation Technologies Inc.
Switchboard: +1 732 692 6200



Visit

WWW.MFORMATION.COM and see how the MFORMATION SERVICE MANAGER can help
you to transform your business

Please consider the environment before printing









Visit WWW.MFORMATION.COM and see how the MFORMATION SERVICE MANAGER can help 
you to transform your business


Please consider the environment before printing

image/com

Other related posts: