Also look on scratchpad. JL hosted a community augmented laundry list of
things to look for when "nothing changed." If you get through that list and
do what the others have suggested I will be surprised if you don't find your
root cause.
Side-by-side Wolfgang traces are of course what you need to actually see
where Oracle "changes its mind" (loses its mind?) one from when it is a good
plan and one from when it is a bad plan. (And of course you'll get 42 good
parses of the query in a row once you turn on the trace, because Murphy is
alive and walking around on the planet. But eventually you'll get a trace
for each plan. That is the no guessing at all, answer in the back of the
book process, but it IS tedious. Bonus: It does not necessarily tell you how
to avoid getting the "bad" plan, it just very definitively tells you what
the CBO considered as it proceeded to screw up.)
Of course with adaptive feedback, Oracle can now screw up a good plan in
flight, but on 11.2 I don't think that can happen yet for you.
Good luck.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Stauffer, Bob
Sent: Wednesday, January 18, 2017 2:49 PM
To: 'Ken Naim'; oracle-l@xxxxxxxxxxxxx
Cc: 'JBECKSTROM@xxxxxxxxx'; 'tkiernan@xxxxxxxxxxx';
'carlos.sierra.usa@xxxxxxxxx'
Subject: RE: inconsistent query performance
Hi Ken,
It's the exact same query and literals each time. And it's based on views -
which are based on other views. ;-) So that may be an issue, too.
Anyway, I didn't want to burden everyone with the details of the query. I
was just hoping to get some avenues to pursue. Like I said in the original
post, I'm rusty with Oracle and needed some starting points. Thanks to you,
Carlos, TJ, and Jeffrey. Your suggestions will get me started.
Y'all are a great resource.
Bob Stauffer
DBA/Database Developer
From: Ken Naim [mailto:kennethnaim@xxxxxxxxx] ;
Sent: Wednesday, January 18, 2017 2:31 PM
To: Stauffer, Bob <RStauffer@xxxxxxxx>; oracle-l@xxxxxxxxxxxxx
Subject: RE: inconsistent query performance
How complex is the query? Can you send out the sql and/or the explain plan?
Are there just tables in the from(s) or are there also views. Also are there
any function calls? Are you seeing different performance on the same exact
query or are you changing any of the literals?
Ken
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Stauffer, Bob
Sent: Wednesday, January 18, 2017 1:28 PM
To: 'oracle-l@xxxxxxxxxxxxx' <oracle-l@xxxxxxxxxxxxx>
Subject: inconsistent query performance
Hi all,
Oracle RDBMS EE 11.2.0.4.0 - 64bit
Red Hat Enterprise Linux 5.11
Any suggestions on how I can troubleshoot a query that performs
inconsistently? Sometimes it takes a minute to run and sometimes it takes
over an hour. It generally uses one of two explain plans - one fast and one
slow. Although it sometimes uses a different plan. I've checked the stats
and they are up to date. I generally run it in TOAD, but the same problem
occurs in SQLPlus, SQL Developer, and other interfaces. It doesn't matter
whether the system is busy or quiet. And it happens within the same session
- I can run it one right after another and it will change the plan for each
execution.
Any help would be appreciated. I'm a little rusty with Oracle.
Thanks much.
Bob Stauffer
DBA/Database Developer
***CONFIDENTIALITY NOTICE***This email contains confidential information
which may also be legally privileged and which is intended only for the use
of the recipient(s) named above. If you are not the intended recipient, you
are hereby notified that forwarding or copying of this email, or the taking
of any action in reliance on its contents, may be strictly prohibited. If
you have received this email in error, please notify us immediately by reply
email and delete this message from your inbox. Thank you. E-Mail Opt Out
Notice: This e-mail message may include an advertisement. You have the right
to request that we not send future advertisements to you at this e-mail
address. If you prefer not to receive future advertisements from us at this
e-mail address, please access the following url http://www.optoutffc.com/
Fulton Financial Corporation, P.O. Box 4887, Lancaster, Pa. 17604
***CONFIDENTIALITY NOTICE***This email contains confidential information
which may also be legally privileged and which is intended only for the use
of the recipient(s) named above. If you are not the intended recipient, you
are hereby notified that forwarding or copying of this email, or the taking
of any action in reliance on its contents, may be strictly prohibited. If
you have received this email in error, please notify us immediately by reply
email and delete this message from your inbox. Thank you. E-Mail Opt Out
Notice: This e-mail message may include an advertisement. You have the right
to request that we not send future advertisements to you at this e-mail
address. If you prefer not to receive future advertisements from us at this
e-mail address, please access the following url http://www.optoutffc.com/
Fulton Financial Corporation, P.O. Box 4887, Lancaster, Pa. 17604
