Re: Two users, one query, two optimizer plans

  • From: Paul Baumgartel <paul.baumgartel@xxxxxxxxx>
  • To: "JayMiller@xxxxxxxxxxxxxxxx" <JayMiller@xxxxxxxxxxxxxxxx>
  • Date: Tue, 17 Jan 2006 16:52:37 -0500

User B does "alter session set current_schema = A" on login.

On 1/17/06, JayMiller@xxxxxxxxxxxxxxxx <JayMiller@xxxxxxxxxxxxxxxx> wrote:
>
>  Is user B accessing through a synonym or with a schema prefix? I think
> synonym user can (rarely) cause a different plan.   In any event it's
> something that's easy to check.
>
>
>
>
>
> -----Original Message-----
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx]
> *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
>
>  ------------------------------
>
> * This message is confidential and sent by TD Waterhouse solely for
> use by the intended recipient. If you are not the intended
> recipient, you are hereby notified that any use, distribution or
> copying of this communication is strictly prohibited. This should
> not be deemed as an offer or solicitation, to buy or sell any
> product. Any 3rd party information contained herein was prepared by
> sources deemed reliable, but is not guaranteed. TD Waterhouse does
> not accept electronic instructions that would require an original
> signature. Information received by or sent from TD Waterhouse is
> stored, subject to review, and may be produced to regulatory
> authorities or others with a legal right to such. *
>



--
Paul Baumgartel
paul.baumgartel@xxxxxxxxxxxx

Other related posts: