Two users, one query, two optimizer plans

From: Paul Baumgartel <paul.baumgartel_at_gmail.com>
Date: Tue, 17 Jan 2006 16:25:48 -0500
Message-ID: <f8c47710601171325m75b7107md113990df67e97d3@mail.gmail.com>

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

| 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

| 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

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

Received on Tue Jan 17 2006 - 15:26:07 CST