Re: Two users, one query, two optimizer plans

Anyone know what causes this auth_check_mismatch to occur?

Thanks to all for the help. I had completely forgotten about v$sql_shared_cursor.

On 1/17/06, Paul Baumgartel <paul.baumgartel_at_gmail.com> wrote:
>
> 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
>
>

--
Paul Baumgartel
paul.baumgartel_at_aya.yale.edu

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