Re: Two users, one query, two optimizer plans

  • From: Paul Baumgartel <paul.baumgartel@xxxxxxxxx>
  • To: Oracle-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 17 Jan 2006 16:51:45 -0500

When both sessions are running the query, the SQL IDs (from v$session)

SQL_ADDR SQL_HASH_VALUE SQL_ID
-------- -------------- -------------
B8E1D068     1036950108 95qchfhywx6kw

are identical.

On 1/17/06, Allen, Brandon <Brandon.Allen@xxxxxxxxxxx> wrote:
>
> Did you check v$sql_shared_cursor?  Maybe the users have different
> optimizer_mode, or other optimizer-affecting parameter settings?  Maybe the
> data is skewed so the plan is different depending on the input values
> for bind variables?  As you probably already know, the 10053 trace would
> probably give you the best info to answer this question.
>
> Regards,
> Brandon
>
> -----Original Message-----
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx]*On Behalf Of *Paul Baumgartel
> *Sent:* Tuesday, January 17, 2006 2: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
>
> Privileged/Confidential Information may be contained in this message or
> attachments hereto. Please advise immediately if you or your employer do not
> consent to Internet email for messages of this kind. Opinions, conclusions
> and other information in this message that do not relate to the official
> business of this company shall be understood as neither given nor endorsed
> by it.
>



--
Paul Baumgartel
paul.baumgartel@xxxxxxxxxxxx

Other related posts:

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