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 15:30:16 +0000
Hi Chris,
To be honest I am not that familiar with everything that VPD is doing. I know
in our system it has been developed so a package gets kicked off that does a
bunch of table lookups, to other instances as well, to generate a where clause
that will alter what you see. I do not believe it changes the SQL, it just
filters the data based on what you should be allowed to see. For example, a
user good select from a column and SQL will report 500 rows returned, but they
might only see 10. That is at least my understanding
Looking at the sqlarea are a bit more I have seen the following. Flushing the
shared pool seems to make it run fine everytime as far as I can tell. When the
shared pool is not flushed, it jumps to the bad hash value at some point
When it runs fine:
SQL_ID=6upz58z76q34w
PLAN_HASH_VALUE 70343966
OPTIMIZER_COST= 79643
When it goes bad
SQL_ID=6upz58z76q34w
PLAN_HASH_VALUEY0302661
OPTIMIZER_COST= 204507563
From: Christopher.Taylor2@xxxxxxxxxxxx [mailto:Christopher.Taylor2@xxxxxxxxxxxx]
Sent: Friday, September 14, 2012 8:25 AM
To: Michael Schmitt; oracle-l@xxxxxxxxxxxxx
Subject: RE: Execution plan changing
Does the VPD do anything special at the session level - does do any alter
sessions?
Very curious sequence of events based on what you've listed :)
You could check to see what's in V$SQLAREA for that specific SQL_TEXT
You can also flush (in 10.2.0.4 or higher) your specific SQL cursor and objects
using dbms_pool.purge
(You have to create dbms_pool if it doesn't exist - google instructions for
that)
Here's a pl/sql proc I use to do the flushing of the cursor and objects - make
absolutely sure you filter out any SQL_TEXT with V$SQLAREA in it otherwise you
lock your own session LOL (don't ask how I figured that one out).
You can run this bit to find the sql addresses and hash values matching your
sql text
select /*+ ALL_ROWS */ address||','||hash_value as name
from v$sqlarea
where upper(sql_text) like 'SELECT%QB_NAME%OUTER%' -- Put your SQL_TEXT here
that you want to flush, use % for spaces to get better matches
and upper(sql_text) not like '%V$SQLAREA%'
and parsing_schema_name = '&username'; -- Put your username here
Run this to just flush your sql and objects from the pool (generates a new plan
everytime)
DECLARE
name varchar2(50);
version varchar2(3);
cursor c1 is
select /*+ ALL_ROWS */ address||','||hash_value as name
from v$sqlarea
where upper(sql_text) like 'SELECT%QB_NAME%OUTER%' -- Put your SQL_TEXT here
that you want to flush, use % for spaces to get better matches
and upper(sql_text) not like '%V$SQLAREA%'
and parsing_schema_name = '&username'; -- Put your username here
rec_c1 c1%rowtype;
BEGIN
select regexp_replace(version,'\..*') into version from v$instance;
if version = '10' then
execute immediate
q'[alter session set events '5614566 trace name context forever']'; -- bug fix
for 10.2.0.4 backport
end if;
for rec_C1 in c1 loop
begin
dbms_output.put_line('Name = '||rec_c1.name);
sys.dbms_shared_pool.purge(rec_c1.name,'C',1);
end;
end loop;
END;
/
Chris
-----Original Message-----
From: Michael Schmitt [mailto:mschmitt@xxxxxxxxxxxx]
Sent: Friday, September 14, 2012 8:17 AM
To: Taylor Christopher - Nashville;
oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: RE: Execution plan changing
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>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
Christopher.Taylor2@xxxxxxxxxxxx<mailto:Christopher.Taylor2@xxxxxxxxxxxx>
Sent: Friday, September 14, 2012 6:59 AM
To: Michael Schmitt; oracle-l@xxxxxxxxxxxxx<mailto: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>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Michael Schmitt
Sent: Thursday, September 13, 2012 10:49 PM
To: oracle-l@xxxxxxxxxxxxx<mailto: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úLSE 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:
