RE: Two users, one query, two optimizer plans

Hmm...which may (or may not?) be related to RLS. Yeah, my guess is RLS. The question is, what to do about it....;-)  

-- 
Mark J. Bobak 
Senior Oracle Architect 
ProQuest Information & Learning 

"There are 10 types of people in the world:  Those who understand
binary, and those who don't." 

 

________________________________

From: Paul Baumgartel [mailto:paul.baumgartel_at_gmail.com] 
Sent: Tuesday, January 17, 2006 5:08 PM
To: Bobak, Mark
Cc: Oracle-L
Subject: Re: Two users, one query, two optimizer plans


Well, interesting...that shows two child cursors, with
AUTH_CHECK_MISMATCH set to Y for both; doc says that means

Authorization/translation check failed for the existing child cursor. 




On 1/17/06, Bobak, Mark <Mark.Bobak_at_il.proquest.com> wrote: 

	Paul,
	 
	What, if anything,  does V$SQL_SHARED_CURSOR have to say on the
matter?
	 

	-- 
	Mark J. Bobak 
	Senior Oracle Architect 
	P roQuest Information & Learning 

	"There are 10 types of people in the world:  Those who
understand binary, and those who don't." 

	 

________________________________

	From: oracle-l-bounce_at_freelists.org [mailto:
oracle-l-bounce_at_freelists.org] On Behalf Of Paul Baumgartel 
	
	Sent: Tuesday, January 17, 2006 4:26 PM
	To: Oracle-L
	Subject: Two users, one query, two optimizer plans
	

	
	User A is schema owner.  User B has select on user A's objects,
and is subject to row-level security policy on user A's objects.
(Row-level security predicate function returns empty string if user
issuing SQL is owner of object). 
	
	I have one query in particular that produces different optimizer
plans depending on whether it's run by user A or user B.  
	
	The plan produced by user A (schema owner) is 
	
	


------------------------------------------------------------------------

-------------

	| Id  | Operation                     | Name                |
Rows  | Bytes | Cost  | 
	


------------------------------------------------------------------------

------------- 

	|   0 | SELECT STATEMENT              |                     |
1 |    98 |   689 |
	|   1 |  SORT GROUP BY                |                     |
1 |    98 |   689 |
	|   2 |   MERGE JOIN CARTESIAN        |                     |
203 | 19894 |   688 | 
	|   3 |    TABLE ACCESS BY INDEX ROWID| DM_ISSUEGRANT       |
1 |    88 |     1 |
	|   4 |     INDEX RANGE SCAN          | DM_ISSUEGRANT_IX01  |
1 |       |     1 |
	|   5 |    BUFFER SORT                |                     |
162K|  1588K|   687 |
	|   6 |     TABLE ACCESS FULL         | DM_GRANTPARTICIPANT |
162K|  1588K|   686 | 
	


------------------------------------------------------------------------

------------- 

	
	This plan is inefficient (see full table scan at ID 6) and query
takes approximately 35 minutes to run. 
	
	The plan produced by user B is
	
	


------------------------------------------------------------------------

-------------------

	| Id  | Operation                      | Name
| Rows  | Bytes | Cost  |
	


------------------------------------------------------------------------

------------------- 

	|   0 | SELECT STATEMENT               |
|     1 |   115 |     3 | 
	|   1 |  SORT GROUP BY                 |
|     1 |   115 |     3 |
	|   2 |   MERGE JOIN CARTESIAN         |
|     1 |   115 |     2 |
	|   3 |    TABLE ACCESS BY INDEX ROWID | DM_GRANTPARTICIPANT
|     1 |    27 |     1 |
	|   4 |     INDEX RANGE SCAN           |
DM_GRANTPARTICIPANT_IX03 |     1 |       |     1 | 
	|   5 |    BUFFER SORT                 |
|     1 |    88 |     2 |
	|   6 |     TABLE ACCESS BY INDEX ROWID| DM_ISSUEGRANT
|     1 |    88 |     1 |
	|   7 |      INDEX RANGE SCAN          | DM_ISSUEGRANT_IX01
|     1 |       |     1 |
	


------------------------------------------------------------------------

------------------- 

	
	and takes a couple of minutes.
	
	For each table subject to row-level security policy, the RLS
view is of the form 
	
	SELECT <columns> FROM <table> WHERE company_fk in
(hextoraw('<value>') 
	
	My theory at this point is that RLS is causing the discrepancy
in optimizer plans.  Has anyone seen this?  Is there another reason why
the plans would differ?
	
	Thanks,
	
	
	-- 
	Paul Baumgartel 
	paul.baumgartel_at_aya.yale.edu
	
	





-- 
Paul Baumgartel
paul.baumgartel_at_aya.yale.edu






--
http://www.freelists.org/webpage/oracle-l