RE: Two users, one query, two optimizer plans

From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
To: <paul.baumgartel@xxxxxxxxxxxx>
Date: Tue, 17 Jan 2006 17:12:27 -0500

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@xxxxxxxxx] 
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@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxxxxx
        
        





-- 
Paul Baumgartel
paul.baumgartel@xxxxxxxxxxxx

BEGIN:VCARD
VERSION:2.1
N:Bobak;Mark
FN:Bobak, Mark
ORG:ProQuest Information and Learning;Database Group
TITLE:Software Architect, Sr-TPD
TEL;WORK;VOICE:734.975.6086
ADR;WORK:;ECC4-N01E04;1400 Eisenhower Parkway
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:ECC4-N01E04=0D=0A1400 Eisenhower Parkway
EMAIL;PREF;INTERNET:Mark.Bobak@xxxxxxxxxxxxxxx
REV:20051213T223654Z
END:VCARD

RE: Two users, one query, two optimizer plans

Other related posts: