Re: Two users, one query, two optimizer plans

  • From: Paul Baumgartel <paul.baumgartel@xxxxxxxxx>
  • To: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
  • Date: Tue, 17 Jan 2006 17:07:30 -0500

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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 01-2006