RE: Execution plan changing
Date: Fri, 14 Sep 2012 06:58:39 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E885158F412E_at_NADCWPMSGCMS10.hca.corpad.net>
How is this SQL executed - in an application or ad-hoc or what? Is this query executed by more than one application (for example in a daily windows application and in batch report run)?
When do statistics run?
What I'm getting at is this - it is possible something is stomping on your plan and that plan stays in the pool at certain points of the day so the next execution gets the "bad" plan.
And if it is an ad-hoc query, then is it the same users complaining? Perhaps a tool is setting a session variable somewhere for those specific users causing an initial poor plan.
Also check the OS at the time of the query and database usage at the long run times using Grid Control or some other monitoring tools - perhaps the query is running into a WAIT condition at certain points.
FINALLY - are you *positive* the shared pool flush is completed before kicking off the query? If the cursor is in use when the shared pool flush happens, I think you may get some contention like cursor pin wait on x but I'm not positive on that one.
Chris
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Michael Schmitt
Sent: Thursday, September 13, 2012 10:49 PM
To: oracle-l_at_freelists.org
Subject: Execution plan changing
Hi All,
I know this topic comes up a lot so sorry if it is repetitive, but I am seeing an execution plan change on me in 11201 that I am having some difficulty explaining
We are running VPD and we are only seeing this for accounts that have the policy in place. Running the query time after time will return in about 1 second using a plan that it determines will cost about 74k. Every once in awhile in the same session with nothing else happening in the database (except share pool flushes), the query will run for 15 minutes and chooses a plan with a cost of 204million.
I was originally thinking we might be seeing bind peeking within VPD which is resulting in the different plans. One of the developers who brought this to me said he was setting optim_peek_user_binds=FALSE and still seeing it though
Any guidance would be appreciated.
Thanks in advance
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Sep 14 2012 - 06:58:39 CDT