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:25:48 -0500

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

Other related posts:

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