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*
> *ProQuest 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