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