RE: Two users, one query, two optimizer plans

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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_at_aya.yale.edu






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