Hmm...which may (or may not?) be related to RLS. Yeah, my guess is RLS. The question is, what to do about it....;-) -- 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: Paul Baumgartel [mailto:paul.baumgartel@xxxxxxxxx] Sent: Tuesday, January 17, 2006 5:08 PM To: Bobak, Mark Cc: Oracle-L Subject: Re: Two users, one query, two optimizer plans Well, interesting...that shows two child cursors, with AUTH_CHECK_MISMATCH set to Y for both; doc says that means Authorization/translation check failed for the existing child cursor. On 1/17/06, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxxxxx> wrote: Paul, What, if anything, does V$SQL_SHARED_CURSOR have to say on the matter? -- Mark J. Bobak Senior Oracle Architect P roQuest 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 -- 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