RE: Execution plan changing

Some additional information from your questions.

This is just in a test system right now. I am the only person in the system and I am running this through sqlplus on the unix box (the behavior has been repeated through toad and BO by others though). I have not manually updated stats since the problem was discovered since I am trying to determine why this might be happening. The box is pretty much idle except for me.

What I have done right now to replicate this is setup a script that does the following

1) flushes the shared pool
2) executes a procedure that setups my access via VPD (you pass a username which then selects from some tables to determine what rows you should be able to access)
3) Executes the query

Right now I have determined if I repeat the above process time after time, it works fine. However, if I do not flush the shared pool (comment out that step), then I run into the problem where the query uses the plan that runs for 15minutes. It seems like it starts on the second execution of the sql, after not flushing the shared pool, but I have only been able to repeat that test 3 times so far.

Thanks  

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Christopher.Taylor2_at_parallon.net Sent: Friday, September 14, 2012 6:59 AM To: Michael Schmitt; oracle-l_at_freelists.org Subject: RE: Execution plan changing

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

--

http://www.freelists.org/webpage/oracle-l Received on Fri Sep 14 2012 - 08:17:09 CDT