RE: Execution plan changing
Date: Fri, 14 Sep 2012 15:30:16 +0000
Message-ID: <1184E7EFAB1D1C47A5038D06F64BE92602023254_at_XM-MBX-02-PROD.ad.uchicago.edu>
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_at_parallon.net [mailto:Christopher.Taylor2_at_parallon.net]
Sent: Friday, September 14, 2012 8:25 AM
To: Michael Schmitt; oracle-l_at_freelists.org
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_at_uchicago.edu]
Sent: Friday, September 14, 2012 8:17 AM
To: Taylor Christopher - Nashville; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>
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_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Christopher.Taylor2_at_parallon.net<mailto:Christopher.Taylor2_at_parallon.net>
Sent: Friday, September 14, 2012 6:59 AM
To: Michael Schmitt; oracle-l_at_freelists.org<mailto: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> [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<mailto: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úLSE 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 - 10:30:16 CDT