RE: Execution plan changing
- From: Michael Schmitt <mschmitt@xxxxxxxxxxxx>
- To: "Christopher.Taylor2@xxxxxxxxxxxx" <Christopher.Taylor2@xxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 14 Sep 2012 13:17:09 +0000
Thanks Chris,
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Christopher.Taylor2@xxxxxxxxxxxx
Sent: Friday, September 14, 2012 6:59 AM
To: Michael Schmitt; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Michael Schmitt
Sent: Thursday, September 13, 2012 10:49 PM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
