RE: Two users, one query, two optimizer plans

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.

--
http://www.freelists.org/webpage/oracle-l