User B does "alter session set current_schema = A" on login.
On 1/17/06, JayMiller_at_tdwaterhouse.com <JayMiller_at_tdwaterhouse.com> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org]
> *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
>
> ------------------------------
>
> * 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_at_aya.yale.edu
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jan 17 2006 - 15:53:01 CST
