From oracle-l-bounce@freelists.org Tue Jan 17 15:26:07 2006 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id k0HLQ7uS024405 for ; Tue, 17 Jan 2006 15:26:07 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id k0HLQ2g7024392 for ; Tue, 17 Jan 2006 15:26:02 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8E4EF27C07D; Tue, 17 Jan 2006 16:25:59 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 27618-01; Tue, 17 Jan 2006 16:25:59 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id EEA7D27B93A; Tue, 17 Jan 2006 16:25:58 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 17 Jan 2006 16:25:50 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1710527B05E for ; Tue, 17 Jan 2006 16:25:50 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 27593-02 for ; Tue, 17 Jan 2006 16:25:49 -0500 (EST) Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.195]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BA4C927BD47 for ; Tue, 17 Jan 2006 16:25:49 -0500 (EST) Received: by zproxy.gmail.com with SMTP id z3so1360853nzf for ; Tue, 17 Jan 2006 13:25:49 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type; b=qe6B/fCy8E1WYn24XTZgHH0kr2p8/iZWPpj40/qb2nsLB6xmc4X9lleDl+eqaZDXGRSATnrssAvVTYw9m0tu40ocV3bon9CtTG4NLoXKaLgB7wdmTNV4NXUlMniOjDgXdkWYCVurgo17t+H5sVxNWuOpsW1QSf+Jssm/P00zLfg= Received: by 10.64.204.6 with SMTP id b6mr1049781qbg; Tue, 17 Jan 2006 13:25:48 -0800 (PST) Received: by 10.65.61.15 with HTTP; Tue, 17 Jan 2006 13:25:48 -0800 (PST) Message-ID: Date: Tue, 17 Jan 2006 16:25:48 -0500 From: Paul Baumgartel To: Oracle-L Subject: Two users, one query, two optimizer plans MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_113_10446061.1137533148758" X-archive-position: 30114 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: paul.baumgartel@gmail.com Precedence: normal Reply-To: paul.baumgartel@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Not scanned: please contact your Internet E-Mail Service Provider for details X-MailScanner-From: oracle-l-bounce@freelists.org ------=_Part_113_10446061.1137533148758 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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 FROM WHERE company_fk in (hextoraw('') 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@aya.yale.edu ------=_Part_113_10446061.1137533148758 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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 ow= ner of object).

I have one query in particular that produces different optimizer pl= ans depending on whether it's run by user A or user B. 

The pl= an produced by user A (schema owner) is

---------------------------------------------------------------------------= ----------
| Id  | Operation &nb= sp;            =        | Name     &n= bsp;          | Rows  | B= ytes | Cost  |
-----------------------------------------= --------------------------------------------
|   0 | SELEC= T STATEMENT          &nbs= p;   |          = ;           |  =    1 |    98 |   689 |
|   1 |  SORT GROUP BY      &n= bsp;         |   &nb= sp;            =      |     1 |    98= |   689 |
|   2 |&nbs= p;  MERGE JOIN CARTESIAN        |&n= bsp;            = ;        |   203 | 19894 |&nbs= p;  688 |
|   3 |    TABLE= ACCESS BY INDEX ROWID| DM_ISSUEGRANT       |=      1 |    88 |    = 1 |
|   4 | =     INDEX RANGE SCAN      &nbs= p;   | DM_ISSUEGRANT_IX01  |     1 |&nbs= p;      |     1 |
|   5 |    BUFFER SORT    &nbs= p;           |  = ;            &n= bsp;      |   162K|  1588K| &n= bsp; 687 |
|   6 |  &n= bsp;  TABLE ACCESS FULL        = ; | DM_GRANTPARTICIPANT |   162K|  1588K|   686 |
-----------------------------------------= --------------------------------------------

This plan is ine= fficient (see full table scan at ID 6) and query takes approximately 35 min= utes to run.

The plan produced by user B is

----------------------------------------------------= ---------------------------------------
| Id  | Operation&= nbsp;           &nbs= p;         | Name   =             &nb= sp;     | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------= ----------------
|   0 | SELECT = STATEMENT           =     |         &= nbsp;           &nbs= p;    |     1 |   115 | &= nbsp;   3 |
|   1 |  SORT GROUP BY&nbs= p;            &= nbsp;   |         &n= bsp;            = ;    |     1 |   115 | &n= bsp;   3 |
|   2 | =   MERGE JOIN CARTESIAN        = |            &= nbsp;           &nbs= p; |     1 |   115 |     = 2 |
|   3 |    TABLE ACCESS BY INDEX ROWID | DM_GRANTP= ARTICIPANT      |     1 | = ;   27 |     1 |
|   4 |     INDEX RANGE SCAN  =          | DM_GRANTPARTICIPANT_IX03= |     1 |       | &= nbsp;   1 |
|   5 |    BUFFE= R SORT           &nb= sp;     |       &nbs= p;            &= nbsp;     |     1 |  &nbs= p; 88 |     2 |
|   6 | =     TABLE ACCESS BY INDEX ROWID| DM_ISSUEGRANT  &n= bsp;         |   &nb= sp; 1 |    88 |     1 |
|   7 |      INDEX RANGE SCAN  = ;        | DM_ISSUEGRANT_IX01  = ;     |     1 |   &n= bsp;   |     1 |
----------------------------------------------------------------------= ---------------------

and takes a couple of minutes.

For each table subject= to row-level security policy, the RLS view is of the form

SELECT &= lt;columns> FROM <table> WHERE company_fk in (hextoraw('<value&= gt;')

My theory at this point is that RLS is causing the discrepancy in o= ptimizer plans.  Has anyone seen this?  Is there another reason w= hy the plans would differ?

Thanks,


--
P= aul Baumgartel
paul.baumgartel@aya.yal= e.edu

------=_Part_113_10446061.1137533148758-- -- http://www.freelists.org/webpage/oracle-l