RE: Two users, one query, two optimizer plans

  • From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
  • To: <paul.baumgartel@xxxxxxxxx>, "Oracle-L" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 17 Jan 2006 16:48:57 -0500

Paul,
 
What, if anything,  does V$SQL_SHARED_CURSOR have to say on the matter?
 

-- 
Mark J. Bobak 
Senior Oracle Architect 
ProQuest Information & Learning 

"There are 10 types of people in the world:  Those who understand
binary, and those who don't." 

 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Paul Baumgartel
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



BEGIN:VCARD
VERSION:2.1
N:Bobak;Mark
FN:Bobak, Mark
ORG:ProQuest Information and Learning;Database Group
TITLE:Software Architect, Sr-TPD
TEL;WORK;VOICE:734.975.6086
ADR;WORK:;ECC4-N01E04;1400 Eisenhower Parkway
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:ECC4-N01E04=0D=0A1400 Eisenhower Parkway
EMAIL;PREF;INTERNET:Mark.Bobak@xxxxxxxxxxxxxxx
REV:20051213T223654Z
END:VCARD

Other related posts:

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