This discussion is archived
9 Replies Latest reply: Nov 23, 2011 8:06 AM by user8175606 RSS

query not using index for certain user

user8175606 Newbie
Currently Being Moderated
Hello,

I have a query which execute in less than a seconds for sys, system or schema owner. However, another user (test_user) will take 30 seconds to execute the same query.

I granted dba and the same identical privileges to test_user as schmea_user, but the result are the same.

I checked

Select * from V$SYS_OPTIMIZER_ENV;

Both are the same for both users.

I check the explain plan for both users. I noticed that for sys/system/schema_owner, the query is using an index, but not the test_user.

Have any one experience the issue where one user is using an index, but not the other?

Thank you for any assistance.
  • 1. Re: query not using index for certain user
    sb92075 Guru
    Currently Being Moderated
    user8175606 wrote:
    Hello,

    I have a query which execute in less than a seconds for sys, system or schema owner. However, another user (test_user) will take 30 seconds to execute the same query.

    I granted dba and the same identical privileges to test_user as schmea_user, but the result are the same.

    I checked

    Select * from V$SYS_OPTIMIZER_ENV;

    Both are the same for both users.

    I check the explain plan for both users. I noticed that for sys/system/schema_owner, the query is using an index, but not the test_user.

    Have any one experience the issue where one user is using an index, but not the other?

    Thank you for any assistance.
    WHY MY INDEX IS NOT BEING USED
    http://communities.bmc.com/communities/docs/DOC-10031

    http://searchoracle.techtarget.com/tip/Why-isn-t-my-index-getting-used

    http://www.orafaq.com/tuningguide/not%20using%20index.html
  • 2. Re: query not using index for certain user
    CharlesHooper Expert
    Currently Being Moderated
    In addition to what sb92075 mentioned, you should check the V$SES_OPTIMIZER_ENV view to compare the session level parameters for the two sessions, and not V$SYS_OPTIMIZER_ENV. You might also compare V$SQL_OPTIMIZER_ENV for the various child cursors. Are bind variables used by the SQL statement? Are views used in the SQL statement (could be a side-effect of secure view merging).

    The SYS user is special in a couple of cases - executing SQL statements with that user could very well result in different performance than executing the same SQL statement with a different user account. Just as one for-instance, VPD (virtual private database) restrictions do not apply to the SYS user (using DBMS_XPLAN.DISPLAY_CURSOR to show the execution plans in memory would tell you if VPD is the cause). The exp utility also typically cannot create a consistent export when connected as the SYS user due to another restriction that is imposed on the SYS user.

    If you have a copy of Tom Kyte's latest version of the "Expert Oracle Database Architecture" book, there is a detailed section starting on page 479 that describes why an index might not be used:
    http://books.google.com/books?id=mV6b43HuEAkC&pg=PA479

    Charles Hooper
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 3. Re: query not using index for certain user
    user8175606 Newbie
    Currently Being Moderated
    Thank you Charles.

    I willl check V$SQL_OPTIMIZER_ENV and V$SES_OPTIMIZER_ENV. It returned thousand of row. I will take some time to review.

    <b>I don't know why the optimizer favors one user over the others (schema vs non-schema user).</b>

    There is no view use in this query. However, in the explain plan, it seems the view is use. I think this is partition table view, not the real view.

    I am doing a test, so no bind variable were use.

    Here is the execution plan for test_user (the bad).
    ---------------------------------------------------------------------------------
    | Id  | Operation                         | Name                        | Rows  |
    ---------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                  |                             |     4 |
    |*  1 |  FILTER                           |                             |       |
    |   2 |   NESTED LOOPS OUTER              |                             |     4 |
    |   3 |    NESTED LOOPS OUTER             |                             |     1 |
    |   4 |     NESTED LOOPS                  |                             |     1 |
    |   5 |      NESTED LOOPS                 |                             |     1 |
    |   6 |       NESTED LOOPS                |                             |     1 |
    |*  7 |        TABLE ACCESS BY INDEX ROWID| PEOPLE_PPNY_CONTAINERS    |     1 |
    |*  8 |         INDEX UNIQUE SCAN         | PEOPLE_PPNY_CONTAINERS_PK |     1 |
    |*  9 |        TABLE ACCESS BY INDEX ROWID| LOCATIONS           |  3935 |
    |* 10 |         INDEX UNIQUE SCAN         | LOCATIONS           |     1 |
    |* 11 |       TABLE ACCESS BY INDEX ROWID | PEOPLE_TRACKINGS           |  3152K|
    |* 12 |        INDEX UNIQUE SCAN          | PEOPLE_TRACKINGS_PK        |     1 |
    |* 13 |      TABLE ACCESS BY INDEX ROWID  | PEOPLES                   |  3286K|
    |* 14 |       INDEX UNIQUE SCAN           | PEOPLES_PK                |     1 |
    |  15 |     REMOTE                        | IPEO                        |  4515K|
    |  16 |    PARTITION RANGE ALL            |                             |    16 |
    |  17 |     PARTITION HASH ALL            |                             |    16 |
    |* 18 |      VIEW                         | PEOPLE_TRANSACTIONS       |    16 |
    |* 19 |       TABLE ACCESS FULL           | PEOPLE_TRANSACTIONS       |    12M|
    ---------------------------------------------------------------------------------
    As you can see line 18 is using view, but the view doesn't existed. Line 18 is using a full table scan on PEOPLE_TRANSACTIONS table, but it suppose to use an index.

    Here is the original query:
    SELECT   ob.TRACKING_no
    FROM   IPEO@DB_LINK r,
               PEOPLES O,
               PEOPLE_PPNY_CONTAINERS opc,
               PEOPLE_TRACKINGs ob,
               PEOPLE_transactions ot,
               LOCATIONS ps
    WHERE       opc.PEOPLE_bk_id = ob.PEOPLE_bk_id
               AND ot.PEOPLE_id(+) = ob.root_PEOPLE_id
               AND BKG_TRACKING_NO(+) = ob.TRACKING_no
               AND O.PEOPLE_ID = OB.PEOPLE_ID
               AND ps.location_id = opc.location_id
               AND (ot.ACCOUNT_TYPE = 'REG' OR ot.ACCOUNT_TYPE IS NULL)
               AND opc.PROPERTY_CONTAINER_ID = 123
    For schema owner, the execution PLAN is (the good):
    ------------------------------------------------------------------------------------
    | Id  | Operation                            | Name                        | Rows  |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                     |                             |     4 |
    |*  1 |  FILTER                              |                             |       |
    |   2 |   NESTED LOOPS OUTER                 |                             |     4 |
    |   3 |    NESTED LOOPS OUTER                |                             |     1 |
    |   4 |     NESTED LOOPS                     |                             |     1 |
    |   5 |      NESTED LOOPS                    |                             |     1 |
    |   6 |       NESTED LOOPS                   |                             |     1 |
    |   7 |        TABLE ACCESS BY INDEX ROWID   | PEOPLE_PPNY_CONTAINERS    |     1 |
    |*  8 |         INDEX UNIQUE SCAN            | PEOPLE_PPNY_CONTAINERS_PK |     1 |
    |*  9 |        INDEX UNIQUE SCAN             | LOCATIONS           |  3935 |
    |  10 |       TABLE ACCESS BY INDEX ROWID    | PEOPLE_BOOKINGS           |  3152K|
    |* 11 |        INDEX UNIQUE SCAN             | PEOPLE_BOOKINGS_PK        |     1 |
    |* 12 |      INDEX UNIQUE SCAN               | PEOPLES_PK                |  3286K|
    |  13 |     REMOTE                           | IPEO                        |  4515K|
    |  14 |    TABLE ACCESS BY GLOBAL INDEX ROWID| PEOPLE_TRANSACTIONS       |    16 |
    |* 15 |     INDEX RANGE SCAN                 | PEOPLE_TRANSACTIONS_NI7   |    16 |
    Thanks.

    I hope someone can help me.

    Edited by: user8175606 on Nov 23, 2011 8:07 AM
  • 4. Re: query not using index for certain user
    CharlesHooper Expert
    Currently Being Moderated
    user8175606 wrote:
    Thank you Charles.

    I willl check V$SQL_OPTIMIZER_ENV and V$SES_OPTIMIZER_ENV. It returned thousand of row. I will take some time to review.
    You need to query those views using a specifiic SQL_ID or SID, respectively.
    <b>I don't know why the optimizer favors one user over the others (schema vs non-schema user).</b>

    There is no view use in this query. However, in the explain plan, it seems the view is use. I think this is partition table view, not the real view.

    I am doing a test, so no bind variable were use.

    Here is the execution plan for test_user (the bad).
    ---------------------------------------------------------------------------------
    | Id  | Operation                         | Name                        | Rows  |
    ---------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                  |                             |     4 |
    |*  1 |  FILTER                           |                             |       |
    |   2 |   NESTED LOOPS OUTER              |                             |     4 |
    |   3 |    NESTED LOOPS OUTER             |                             |     1 |
    |   4 |     NESTED LOOPS                  |                             |     1 |
    |   5 |      NESTED LOOPS                 |                             |     1 |
    |   6 |       NESTED LOOPS                |                             |     1 |
    |*  7 |        TABLE ACCESS BY INDEX ROWID| PEOPLE_PPNY_CONTAINERS      |     1 |
    |*  8 |         INDEX UNIQUE SCAN         | PEOPLE_PPNY_CONTAINERS_PK   |     1 |
    |*  9 |        TABLE ACCESS BY INDEX ROWID| LOCATIONS                   |  3935 |
    |* 10 |         INDEX UNIQUE SCAN         | LOCATIONS                   |     1 |
    |* 11 |       TABLE ACCESS BY INDEX ROWID | PEOPLE_TRACKINGS            |  3152K|
    |* 12 |        INDEX UNIQUE SCAN          | PEOPLE_TRACKINGS_PK         |     1 |
    |* 13 |      TABLE ACCESS BY INDEX ROWID  | PEOPLES                     |  3286K|
    |* 14 |       INDEX UNIQUE SCAN           | PEOPLES_PK                  |     1 |
    |  15 |     REMOTE                        | IPEO                        |  4515K|
    |  16 |    PARTITION RANGE ALL            |                             |    16 |
    |  17 |     PARTITION HASH ALL            |                             |    16 |
    |* 18 |      VIEW                         | PEOPLE_TRANSACTIONS         |    16 |
    |* 19 |       TABLE ACCESS FULL           | PEOPLE_TRANSACTIONS         |    12M|
    ---------------------------------------------------------------------------------
    >
    As you can see line 18 is using view, but the view doesn't existed. Line 18 is using a full table scan on PEOPLE_TRANSACTIONS table, but it suppose to use an index.

    Here is the original query:
    +(slightly reformatted)+
    SELECT
      ob.TRACKING_no
    FROM
      IPEO@DB_LINK r,
      PEOPLES O,
      PEOPLE_PPNY_CONTAINERS opc,
      PEOPLE_TRACKINGs ob,
      PEOPLE_transactions ot,
      LOCATIONS ps
    WHERE
      opc.PEOPLE_bk_id = ob.PEOPLE_bk_id
      AND ot.PEOPLE_id(+) = ob.root_PEOPLE_id
      AND BKG_TRACKING_NO(+) = ob.TRACKING_no
      AND O.PEOPLE_ID = OB.PEOPLE_ID
      AND ps.location_id = opc.location_id
      AND (ot.ACCOUNT_TYPE = 'REG' OR ot.ACCOUNT_TYPE IS NULL)
      AND opc.PROPERTY_CONTAINER_ID = 123
    >
    For schema owner, the execution PLAN is (the good):
    ------------------------------------------------------------------------------------
    | Id  | Operation                            | Name                        | Rows  |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                     |                             |     4 |
    |*  1 |  FILTER                              |                             |       |
    |   2 |   NESTED LOOPS OUTER                 |                             |     4 |
    |   3 |    NESTED LOOPS OUTER                |                             |     1 |
    |   4 |     NESTED LOOPS                     |                             |     1 |
    |   5 |      NESTED LOOPS                    |                             |     1 |
    |   6 |       NESTED LOOPS                   |                             |     1 |
    |   7 |        TABLE ACCESS BY INDEX ROWID   | PEOPLE_PPNY_CONTAINERS      |     1 |
    |*  8 |         INDEX UNIQUE SCAN            | PEOPLE_PPNY_CONTAINERS_PK   |     1 |
    |*  9 |        INDEX UNIQUE SCAN             | LOCATIONS                   |  3935 |
    |  10 |       TABLE ACCESS BY INDEX ROWID    | PEOPLE_BOOKINGS             |  3152K|
    |* 11 |        INDEX UNIQUE SCAN             | PEOPLE_BOOKINGS_PK          |     1 |
    |* 12 |      INDEX UNIQUE SCAN               | PEOPLES_PK                  |  3286K|
    |  13 |     REMOTE                           | IPEO                        |  4515K|
    |  14 |    TABLE ACCESS BY GLOBAL INDEX ROWID| PEOPLE_TRANSACTIONS         |    16 |
    |* 15 |     INDEX RANGE SCAN                 | PEOPLE_TRANSACTIONS_NI7     |    16 |
    >
    >
    Thanks.

    I hope someone can help me.
    You did not include the Predicate Information section of the execution plans, which might provide additional details. One thing to note is that you did not include the schema name in front of each table name - that suggests that public synonyms exist that the users who are not the schema owners utilize to locate the table (unless an ALTER SESSION SET CURRENT_SCHEMA command is used to specify the table owner's schema). There is a chance that the public synonym for PEOPLE_TRANSACTIONS actually points to a view - that is one explanation why the execution plan for the schema owner does not include the view.

    Another explanation is that the query was able to be further transformed by the optimizer for the schema owner to completely eliminate the view. Secure view merging, enabled by default starting in Oracle Database 10.1 (or maybe it was 10.2) could prevent that same transformation from taking place for the non-schema owner. A 10053 trace during a hard parse of the query for both users should show if an additional transformation took place to eliminate the view.

    The Oracle Database documentation for the OPTIMIZER_SECURE_VIEW_MERGING parameter is poor, and a bit confusing:
    http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams169.htm

    A much better explanation is found in the book "Troubleshooting Oracle Performance". The author of that book recently wrote an article that further explained what is affected when the OPTIMIZER_SECURE_VIEW_MERGING parameter os adjusted:
    http://antognini.ch/2011/09/optimizer_secure_view_merging-and-vpd/

    As a test you could try to set the OPTIMIZER_SECURE_VIEW_MERGING parameter to FALSE at the system level to see if the execution plan for the SQL statement changes for the non-schema owner. Be aware that (as explained in the above article) there are potential security risks related to changing the default value of that parameter.

    Charles Hooper
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 5. Re: query not using index for certain user
    user8175606 Newbie
    Currently Being Moderated
    Charles, Thanks again.

    Also, thank you for reformat my writing. It definitely look more readable. I got to learn how to do that.

    database version: 10.2.0.4

    <b>Predicate Information (identified by operation id): (execute as schema owner)</b>
    ---------------------------------------------------

    7 - access("OPC"."PROPERTY_CONTAINER_ID"=123)--ok
    8 - access("PS"."LOCATION_ID"="OPC"."LOCATION_ID")
    10 - access("OPC"."PEOPLE_BK_ID"="OB"."PEOPLE_BK_ID")
    11 - access("O"."PEOPLE_ID"="OB"."PEOPLE_ID")
    13 - access("OT"."PEOPLE_ID"(+)="OB"."ROOT_PEOPLE_ID")

    Remote SQL Information (identified by operation id):
    ----------------------------------------------------

    12 - SELECT "TRACKING_NO" FROM "IPEO" "R" WHERE "TRACKING_NO"=:1 (accessing
    'DB_LINK' )


    <b>Predicate Information (identified by operation id): (execute as non-schema owner)</b>
    ---------------------------------------------------

    6 - filter("SEAL_FLAG" IS NULL OR "SEAL_FLAG"<>'Y')
    7 - access("PROPERTY_CONTAINER_ID"=123)
    8 - filter("SEAL_FLAG" IS NULL OR "SEAL_FLAG"<>'Y')
    9 - access("LOCATION_ID"="LOCATION_ID")
    10 - filter("SEAL_FLAG"<>'Y' OR "SEAL_FLAG" IS NULL)
    11 - access("PEOPLE_BOOK_ID"="PEOPLE_BOOK_ID")
    12 - filter("SEAL_FLAG"<>'Y' OR "SEAL_FLAG" IS NULL)
    13 - access("PEOPLE_ID"="PEOPLE_ID")
    17 - filter("OT"."PEOPLE_ID"(+)="ROOT_PEOPLE_ID")
    18 - filter("SEAL_FLAG"<>'Y' OR "SEAL_FLAG" IS NULL)

    Remote SQL Information (identified by operation id):
    ----------------------------------------------------

    14 - SELECT "TRACKING_NO" FROM "IPEO" "R" WHERE "TRACKING_NO"=:1 (accessing 'DB_LINK' )


    I don't know how SEAL_FLAG coming to play with this query.

    Again, there is no view use in this query:

    select distinct owner, object_name, object_type
    from dba_objects
    where object_name ='PEOPLE_TRANSACTIONS'

    OWNER | OBJECT_NAME | OBJECT_TYPE
    -------------------------------------------------------------------------------------------------------
    PUBLIC | PEOPLE_TRANSACTIONS | SYNONYM
    SCHEMA_OWNER | PEOPLE_TRANSACTIONS | TABLE PARTITION
    SCHEMA_OWNER | PEOPLE_TRANSACTIONS | TABLE
    SCHEMA_OWNER | PEOPLE_TRANSACTIONS | TABLE SUBPARTITION
    ------------------------------------------------------------------------------------------------------------

    I re-execute this script with schema name in front of all the tables, but did not make any difference. There is one database link table. There is a trigger on PEOPLE_TRANSACTIONS table (for update and insert), and I wonder this could affect the query.



    Can you give more info for 10053 trace?

    ALTER SYSTEM SET OPTIMIZER_SECURE_VIEW_MERGING=FALSE SCOPE=MEMORY;

    I set the OPTIMIZER_SECURE_VIEW_MERGING=FALSE and exit the session and re-executed, but it did not change the execution plan.

    Thanks again.

    Edited by: user8175606 on Nov 22, 2011 8:45 AM
  • 6. Re: query not using index for certain user
    CharlesHooper Expert
    Currently Being Moderated
    user8175606 wrote:
    Charles, Thanks again.

    Also, thank you for reformat my writing. It definitely look more readable. I got to learn how to do that.

    database version: 10.2.0.4

    (snip)

    I don't know how SEAL_FLAG coming to play with this query.

    Again, there is no view use in this query:
    select distinct owner, object_name, object_type
    from    dba_objects
    where object_name ='PEOPLE_TRANSACTIONS'
    
    OWNER      |     OBJECT_NAME               |            OBJECT_TYPE
    -------------------------------------------------------------------------------------------------------
    PUBLIC           |       PEOPLE_TRANSACTIONS    |    SYNONYM
    SCHEMA_OWNER   | PEOPLE_TRANSACTIONS     |   TABLE PARTITION
    SCHEMA_OWNER  |  PEOPLE_TRANSACTIONS      |  TABLE
    SCHEMA_OWNER  |  PEOPLE_TRANSACTIONS       | TABLE SUBPARTITION
    ------------------------------------------------------------------------------------------------------------
    I re-execute this script with schema name in front of all the tables, but did not make any difference. There is one database link table. There is a trigger on PEOPLE_TRANSACTIONS table (for update and insert), and I wonder this could affect the query.



    Can you give more info for 10053 trace?

    ALTER SYSTEM SET OPTIMIZER_SECURE_VIEW_MERGING=FALSE SCOPE=MEMORY;

    I set the OPTIMIZER_SECURE_VIEW_MERGING=FALSE and exit the session and re-executed, but it did not change the execution plan.

    Thanks again.
    First, to use monospaced fonts to retain spaces in SQL, code, or execution plans, use the following set of characters { code } (without spaces before and after the the word code):
    { code }
    |   7 |        TABLE ACCESS BY INDEX ROWID   | PEOPLE_PPNY_CONTAINERS      |     1 |
    { code }

    Second, the Predicate Information sections that you posted do not match the execution plans that you previously posted (a work-around is found below):
    ------------------------------------------------------------------------------------
    | Id  | Operation                            | Name                        | Rows  |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                     |                             |     4 |
    |*  1 |  FILTER                              |                             |       |
    |   2 |   NESTED LOOPS OUTER                 |                             |     4 |
    |   3 |    NESTED LOOPS OUTER                |                             |     1 |
    |   4 |     NESTED LOOPS                     |                             |     1 |
    |   5 |      NESTED LOOPS                    |                             |     1 |
    |   6 |       NESTED LOOPS                   |                             |     1 |
    |   7 |        TABLE ACCESS BY INDEX ROWID   | PEOPLE_PPNY_CONTAINERS      |     1 |
    |*  8 |         INDEX UNIQUE SCAN            | PEOPLE_PPNY_CONTAINERS_PK   |     1 |
    |*  9 |        INDEX UNIQUE SCAN             | LOCATIONS                   |  3935 |
    |  10 |       TABLE ACCESS BY INDEX ROWID    | PEOPLE_BOOKINGS             |  3152K|
    |* 11 |        INDEX UNIQUE SCAN             | PEOPLE_BOOKINGS_PK          |     1 |
    |* 12 |      INDEX UNIQUE SCAN               | PEOPLES_PK                  |  3286K|
    |  13 |     REMOTE                           | IPEO                        |  4515K|
    |  14 |    TABLE ACCESS BY GLOBAL INDEX ROWID| PEOPLE_TRANSACTIONS         |    16 |
    |* 15 |     INDEX RANGE SCAN                 | PEOPLE_TRANSACTIONS_NI7     |    16 |
    ------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):   (execute as schema owner)</b>
    ---------------------------------------------------
       7 - access("OPC"."PROPERTY_CONTAINER_ID"=123)--ok
       8 - access("PS"."LOCATION_ID"="OPC"."LOCATION_ID")
      10 - access("OPC"."PEOPLE_BK_ID"="OB"."PEOPLE_BK_ID")
      11 - access("O"."PEOPLE_ID"="OB"."PEOPLE_ID")
      13 - access("OT"."PEOPLE_ID"(+)="OB"."ROOT_PEOPLE_ID")
     
    Remote SQL Information (identified by operation id):
    ----------------------------------------------------
      12 - SELECT "TRACKING_NO" FROM "IPEO" "R" WHERE "TRACKING_NO"=:1 (accessing
            'DB_LINK' )
    In the above, notice that there is a * next to operation 15, but there is no matching information in the predicate information section. Additionally, the execution plan shows that the remote operation is operation 13, but the Remote section indicates that should be operation 12.
    ---------------------------------------------------------------------------------
    | Id  | Operation                         | Name                        | Rows  |
    ---------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                  |                             |     4 |
    |*  1 |  FILTER                           |                             |       |
    |   2 |   NESTED LOOPS OUTER              |                             |     4 |
    |   3 |    NESTED LOOPS OUTER             |                             |     1 |
    |   4 |     NESTED LOOPS                  |                             |     1 |
    |   5 |      NESTED LOOPS                 |                             |     1 |
    |   6 |       NESTED LOOPS                |                             |     1 |
    |*  7 |        TABLE ACCESS BY INDEX ROWID| PEOPLE_PPNY_CONTAINERS      |     1 |
    |*  8 |         INDEX UNIQUE SCAN         | PEOPLE_PPNY_CONTAINERS_PK   |     1 |
    |*  9 |        TABLE ACCESS BY INDEX ROWID| LOCATIONS                   |  3935 |
    |* 10 |         INDEX UNIQUE SCAN         | LOCATIONS                   |     1 |
    |* 11 |       TABLE ACCESS BY INDEX ROWID | PEOPLE_TRACKINGS            |  3152K|
    |* 12 |        INDEX UNIQUE SCAN          | PEOPLE_TRACKINGS_PK         |     1 |
    |* 13 |      TABLE ACCESS BY INDEX ROWID  | PEOPLES                     |  3286K|
    |* 14 |       INDEX UNIQUE SCAN           | PEOPLES_PK                  |     1 |
    |  15 |     REMOTE                        | IPEO                        |  4515K|
    |  16 |    PARTITION RANGE ALL            |                             |    16 |
    |  17 |     PARTITION HASH ALL            |                             |    16 |
    |* 18 |      VIEW                         | PEOPLE_TRANSACTIONS         |    16 |
    |* 19 |       TABLE ACCESS FULL           | PEOPLE_TRANSACTIONS         |    12M|
    ---------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
       6 - filter("SEAL_FLAG" IS NULL OR "SEAL_FLAG"!='Y')
       7 - access("PROPERTY_CONTAINER_ID"=123)
       8 - filter("SEAL_FLAG" IS NULL OR "SEAL_FLAG"!='Y')
       9 - access("LOCATION_ID"="LOCATION_ID")
      10 - filter("SEAL_FLAG"!='Y' OR "SEAL_FLAG" IS NULL)
      11 - access("PEOPLE_BOOK_ID"="PEOPLE_BOOK_ID")
      12 - filter("SEAL_FLAG"!='Y' OR "SEAL_FLAG" IS NULL)
      13 - access("PEOPLE_ID"="PEOPLE_ID")
      17 - filter("OT"."PEOPLE_ID"(+)="ROOT_PEOPLE_ID")
      18 - filter("SEAL_FLAG"!='Y' OR "SEAL_FLAG" IS NULL)
     
    Remote SQL Information (identified by operation id):
    ----------------------------------------------------
      14 - SELECT "TRACKING_NO" FROM "IPEO" "R" WHERE "TRACKING_NO"=:1 (accessing 'DB_LINK' )
    Same problem in the above. As you have noticed, 5 operations in the predicate information section include the following restriction that is not in the SQL statement:
    SEAL_FLAG"!='Y' OR "SEAL_FLAG" IS NULL
    {code}
    
    If the above appeared in the predicate information section of both execution plans, I would assume that the above is a generated predicate based on column level constraints.  However, because that predicate only appears in the slow execution plan that is executed by a user other than the schema owner, my first assumption is that Virtual Private Database (VPD) or Label Security is generating the additional predicates.  
    
    There are certainly more knowledgeable people on this forum regarding the use of VPD and/or Label Security, so maybe someone else will offer some advice.  From the documentation:
    Virtual Private Database
    http://docs.oracle.com/cd/B19306_01/network.102/b14266/apdvpoli.htm#CHDFGBEB
    
    Label Security
    http://docs.oracle.com/cd/B19306_01/network.102/b14267/intro.htm
    
    Label Security related data dictionary views:
    http://docs.oracle.com/cd/B19306_01/network.102/b14267/appxc.htm
    
    I suggeset generating a DBMS_XPLAN execute plan and 10053 trace using the following approach:
    For the Schema Owner (note that the tracefile identifier is being set so that you are able to find the trace file easily):
    {code}
    SET LINESIZE 120
    SET PAGESIZE 1000
    SET TRIMSPOOL ON
     
    ALTER SESSION SET TRACEFILE_IDENTIFIER = 'Schema_Owner_10053';
    ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
     
    SELECT /*+ GATHER_PLAN_STATISTICS */
      ob.TRACKING_no
    FROM
      IPEO@DB_LINK r,
      PEOPLES O,
      PEOPLE_PPNY_CONTAINERS opc,
      PEOPLE_TRACKINGs ob,
      PEOPLE_transactions ot,
      LOCATIONS ps
    WHERE
      opc.PEOPLE_bk_id = ob.PEOPLE_bk_id
      AND ot.PEOPLE_id(+) = ob.root_PEOPLE_id
      AND BKG_TRACKING_NO(+) = ob.TRACKING_no
      AND O.PEOPLE_ID = OB.PEOPLE_ID
      AND ps.location_id = opc.location_id
      AND (ot.ACCOUNT_TYPE = 'REG' OR ot.ACCOUNT_TYPE IS NULL)
      AND opc.PROPERTY_CONTAINER_ID = 123;
     
    SELECT
      *
    FROM
      TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
     
    ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';
    {code}
    
    For the other user (note that the tracefile identifier is being set so that you are able to find the trace file easily):
    {code}
    SET LINESIZE 120
    SET PAGESIZE 1000
    SET TRIMSPOOL ON
     
    ALTER SESSION SET TRACEFILE_IDENTIFIER = 'Other_User_10053';
    ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
     
    SELECT /*+ GATHER_PLAN_STATISTICS */
      ob.TRACKING_no
    FROM
      IPEO@DB_LINK r,
      PEOPLES O,
      PEOPLE_PPNY_CONTAINERS opc,
      PEOPLE_TRACKINGs ob,
      PEOPLE_transactions ot,
      LOCATIONS ps
    WHERE
      opc.PEOPLE_bk_id = ob.PEOPLE_bk_id
      AND ot.PEOPLE_id(+) = ob.root_PEOPLE_id
      AND BKG_TRACKING_NO(+) = ob.TRACKING_no
      AND O.PEOPLE_ID = OB.PEOPLE_ID
      AND ps.location_id = opc.location_id
      AND (ot.ACCOUNT_TYPE = 'REG' OR ot.ACCOUNT_TYPE IS NULL)
      AND opc.PROPERTY_CONTAINER_ID = 123;
     
    SELECT
      *
    FROM
      TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
     
    ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';
    {code}
    
    You will find the generated 10053 trace files in the server's udump directory.
    
    Charles Hooper
    http://hoopercharles.wordpress.com/ 
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
    
    Edited by: Charles Hooper on Nov 22, 2011 2:14 PM
    Normal not-equal characters disappeared in the code sections, replaced with !=                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 7. Re: query not using index for certain user
    user8175606 Newbie
    Currently Being Moderated
    Sorry Charles. As I troubleshoot, I removed some rows and forgot about it. That's why the result look confusion. I took a lot of time to modify the code to the way it was before I posted.

    Here is the result of explain plan.

    Non_schema:
    17:02:30 SQL> SELECT
    17:02:38   2    *
    17:02:38   3  FROM
    17:02:38   4    TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------------------------
    
    SQL_ID  0wcs85uywn72m, child number 1
    -------------------------------------
    (remove statement)
    
    Plan hash value: 3809927635
    
    ---------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                         | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ---------------------------------------------------------------------------------------------------------------------------
    |*  1 |  FILTER                           |                             |      1 |        |      0 |00:00:00.01 |       3 |
    |   2 |   NESTED LOOPS OUTER              |                             |      1 |      4 |      0 |00:00:00.01 |       3 |
    |   3 |    NESTED LOOPS OUTER             |                             |      1 |      1 |      0 |00:00:00.01 |       3 |
    |   4 |     NESTED LOOPS                  |                             |      1 |      1 |      0 |00:00:00.01 |       3 |
    |   5 |      NESTED LOOPS                 |                             |      1 |      1 |      0 |00:00:00.01 |       3 |
    |   6 |       NESTED LOOPS                |                             |      1 |      1 |      0 |00:00:00.01 |       3 |
    |*  7 |        TABLE ACCESS BY INDEX ROWID| PEOPLE_PPNY_CONTAINERS      |      1 |      1 |      0 |00:00:00.01 |       3 |
    |*  8 |         INDEX UNIQUE SCAN         | PEOPLE_PPNY_CONTAINERS_PK   |      1 |      1 |      0 |00:00:00.01 |       3 |
    |*  9 |        TABLE ACCESS BY INDEX ROWID| LOCATIONS                   |      0 |   3935 |      0 |00:00:00.01 |       0 |
    |* 10 |         INDEX UNIQUE SCAN         | LOCATIONS                   |      0 |      1 |      0 |00:00:00.01 |       0 |
    |* 11 |       TABLE ACCESS BY INDEX ROWID | PEOPLE_TRACKINGS            |      0 |   3152K|      0 |00:00:00.01 |       0 |
    |* 12 |        INDEX UNIQUE SCAN          | PEOPLE_TRACKINGS_PK         |      0 |      1 |      0 |00:00:00.01 |       0 |
    |* 13 |      TABLE ACCESS BY INDEX ROWID  | PEOPLES                     |      0 |   3286K|      0 |00:00:00.01 |       0 |
    |* 14 |       INDEX UNIQUE SCAN           | PEOPLES_PK                  |      0 |      1 |      0 |00:00:00.01 |       0 |
    |  15 |     REMOTE                        | IPEO                        |      0 |   4515K|      0 |00:00:00.01 |       0 |
    |  16 |    PARTITION RANGE ALL            |                             |      0 |     16 |      0 |00:00:00.01 |       0 |
    |  17 |     PARTITION HASH ALL            |                             |      0 |     16 |      0 |00:00:00.01 |       0 |
    |* 18 |      VIEW                         | PEOPLE_TRANSACTIONS         |      0 |     16 |      0 |00:00:00.01 |       0 |
    |* 19 |       TABLE ACCESS FULL           | PEOPLE_TRANSACTIONS         |      0 |     12M|      0 |00:00:00.01 |       0 |
    ---------------------------------------------------------------------------------------------------------------------------
    
    
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(("OT"."ACCOUNT_TYPE"='REG' OR "OT"."ACCOUNT_TYPE" IS NULL))
       7 - filter(("SEAL_FLAG" IS NULL OR "SEAL_FLAG"<>'Y'))
       8 - access("PROPERTY_CONTAINER_ID"=123)
       9 - filter(("SEAL_FLAG" IS NULL OR "SEAL_FLAG"<>'Y'))
      10 - access("location_id"="location_id")
      11 - filter(("SEAL_FLAG"<>'Y' OR "SEAL_FLAG" IS NULL))
      12 - access("PEOPLE_BK_ID"="PEOPLE_BK_ID")
      13 - filter(("SEAL_FLAG"<>'Y' OR "SEAL_FLAG" IS NULL))
      14 - access("PEOPLE_ID"="PEOPLE_ID")
      18 - filter("OT"."PEOPLE_ID"="ROOT_PEOPLE_ID")
      19 - filter(("SEAL_FLAG"<>'Y' OR "SEAL_FLAG" IS NULL))
    
    
    50 rows selected.
    Schema_user:
    16:59:42 SQL> SELECT
    16:59:49   2    *
    16:59:49   3  FROM
    16:59:49   4    TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
    
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------------------------------------------
    SQL_ID  0wcs85uywn72m, child number 0
    -------------------------------------
    (remove statement)
    
    Plan hash value: 1012224898
    
    ------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                            | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ------------------------------------------------------------------------------------------------------------------------------
    |*  1 |  FILTER                              |                             |      1 |        |      0 |00:00:00.01 |       3 |
    |   2 |   NESTED LOOPS OUTER                 |                             |      1 |      4 |      0 |00:00:00.01 |       3 |
    |   3 |    NESTED LOOPS OUTER                |                             |      1 |      1 |      0 |00:00:00.01 |       3 |
    |   4 |     NESTED LOOPS                     |                             |      1 |      1 |      0 |00:00:00.01 |       3 |
    |   5 |      NESTED LOOPS                    |                             |      1 |      1 |      0 |00:00:00.01 |       3 |
    |   6 |       NESTED LOOPS                   |                             |      1 |      1 |      0 |00:00:00.01 |       3 |
    |   7 |        TABLE ACCESS BY INDEX ROWID   | PEOPLE_PPNY_CONTAINERS      |      1 |      1 |      0 |00:00:00.01 |       3 |
    |*  8 |         INDEX UNIQUE SCAN            | PEOPLE_PPNY_CONTAINERS_PK   |      1 |      1 |      0 |00:00:00.01 |       3 |
    |*  9 |        INDEX UNIQUE SCAN             | LOCATIONS                   |      0 |   3935 |      0 |00:00:00.01 |       0 |
    |  10 |       TABLE ACCESS BY INDEX ROWID    | PEOPLE_TRACKINGS            |      0 |   3152K|      0 |00:00:00.01 |       0 |
    |* 11 |        INDEX UNIQUE SCAN             | PEOPLE_TRACKINGS_PK         |      0 |      1 |      0 |00:00:00.01 |       0 |
    |* 12 |      INDEX UNIQUE SCAN               | PEOPLES_PK                  |      0 |   3286K|      0 |00:00:00.01 |       0 |
    |  13 |     REMOTE                           | IPEO                        |      0 |   4515K|      0 |00:00:00.01 |       0 |
    |  14 |    TABLE ACCESS BY GLOBAL INDEX ROWID| PEOPLE_TRANSACTIONS         |      0 |     16 |      0 |00:00:00.01 |       0 |
    |* 15 |     INDEX RANGE SCAN                 | PEOPLE_TRANSACTIONS_NI7     |      0 |     16 |      0 |00:00:00.01 |       0 |
    ------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(("OT"."ACCOUNT_TYPE"='REG' OR "OT"."ACCOUNT_TYPE" IS NULL))
       8 - access("OPC"."PROPERTY_CONTAINER_ID"=123)
       9 - access("PS"."location_id"="OPC"."location_id")
      11 - access("OPC"."PEOPLE_BK_ID"="OB"."PEOPLE_BK_ID")
      12 - access("O"."OFFENDER_ID"="OB"."OFFENDER_ID")
      15 - access("OT"."PEOPLE_ID"="OB"."ROOT_PEOPLE_ID")
    
    
    41 rows selected.
    I can't post the result of 10053 trace, it would be too long to post it here.

    Do you have a guideline on how to interpret the result?

    Thanks again.
  • 8. Re: query not using index for certain user
    CharlesHooper Expert
    Currently Being Moderated
    Thank you for posting the formatted output - that output is much easier to read.

    One of the first things that you might notice about the execution plans is that for the non-schema owner "SQL_ID 0wcs85uywn72m, child number 1" appears in the DBMS_XPLAN output, while "SQL_ID 0wcs85uywn72m, child number 0" (the same SQL_ID but a different child number) appears for the schema owner. Considering that the SQL_ID is the same, that indicates that the client application submitted exactly the same SQL statement, so that is a good start.

    Next, notice that in the Predicate Information section for the non-schema owner the following appears (sometimes with the order of the two conditions switched in position) as a condition placed on every table that is accessed in the schema:
    filter(("SEAL_FLAG" IS NULL OR "SEAL_FLAG"'Y'))
    The above suggests the presence of virtual private database (or a superset of virtual private database) generated predicates. You should be able to confirm that is the case by querying V$VPD_POLICY using the SQL_ID that was displayed in the DBMS_XPLAN output:
    SELECT
      *
    FROM
      V$VPD_POLICY
    WHERE
      SQL_ID='0wcs85uywn72m';
    As a test I made a couple of minor adjustments to the example found on this page:
    http://antognini.ch/2011/09/optimizer_secure_view_merging-and-vpd/
    I changed the table name from T to T12 and specified TESTUSER for the schema names. I then created the S function from that page as follows:
    CREATE OR REPLACE FUNCTION s (schema IN VARCHAR2, tab IN VARCHAR2) RETURN VARCHAR2 AS
    BEGIN
      RETURN 'ID < 10';
    END;
    /
    I then added a couple of rows into the T12 test table:
    INSERT INTO T12 VALUES (1,1,NULL);
    INSERT INTO T12 VALUES (4,1,NULL);
    INSERT INTO T12 VALUES (10,1,NULL);
    INSERT INTO T12 VALUES (12,1,NULL);
     
    COMMIT;
    With a 10053 trace enabled, I executed the following SQL statement:
    SELECT id, pad
      FROM t12
      WHERE
      spy(id, pad) = 1
    The SQL_ID (in my case found in the 10053 trace file) was 6hqw5p9d8g8wf, so I checked V$VPD_POLICY for that SQL_ID:
    SELECT
      *
    FROM
      V$VPD_POLICY
    WHERE
      SQL_ID='6hqw5p9d8g8wf';
     
    ADDRESS          PARADDR            SQL_HASH SQL_ID        CHILD_NUMBER OBJECT_OWNER OBJECT_NAME                    POLICY_GROUP                   POLICY                 POLICY_FUNCTION_OWNER          PREDICATE
    ---------------- ---------------- ---------- ------------- ------------ ------------ ------------------------------ ------------------------------ ---------------------- ------------------------------ ------------------------------------------------------------------------------------
    000007FFB7701608 000007FFB7743350 1518838670 6hqw5p9d8g8wf            0 TESTUSER     T12                            SYS_DEFAULT                    T_SEC                  TESTUSER                       ID < 10
    As shown above, the VPD test function named S added the predicate "ID < 10" to the SQL statement.

    There were not a lot of clues in the 10053 trace file in my test that VPD generated additional predicates. Near the start of the trace file the following was found (this is the originally submitted SQL statement):
    ----- Current SQL Statement for this session (sql_id=6hqw5p9d8g8wf) -----
    SELECT id, pad
      FROM t12
      WHERE
      spy(id, pad) = 1
    I then searched down in the trace file for Final query after transformations (note that this phrase could be slightly different in the various Oracle Database versions). This is what I found:
    Final query after transformations: ******* UNPARSED QUERY IS *******
    SELECT "T12"."ID" "ID","T12"."PAD" "PAD" FROM "TESTUSER"."T12" "T12" WHERE "TESTUSER"."SPY"("T12"."ID","T12"."PAD")=1 AND "T12"."ID"<10
    kkoqbc: optimizing query block SEL$F5BB74E1 (#0)
    Notice that the final query after transformation shows how the final version of the query that was rewritten by the query optimizer before the SQL statement was executed, and this version of the query includes AND "T12"."ID"<10. If I was attempting to determine how that T12.ID<10 predicate was added to the SQL statement, I would start at the "Current SQL Statement for" line in the trace file and search down the trace file for *<10* - in this case, the following is what I found as the first search result, very close to the "Current SQL Statement for" line in the trace file:
    **************************
    Predicate Move-Around (PM)
    **************************
    PM:     PM bypassed: Outer query contains no views.
    PM:     PM bypassed: Outer query contains no views.
    query block SEL$F5BB74E1 (#0) unchanged
    FPD: Considering simple filter push in query block SEL$F5BB74E1 (#0)
    "TESTUSER"."SPY"("T12"."ID","T12"."PAD")=1 AND "T12"."ID"<10
    try to generate transitive predicate from check constraints for query block SEL$F5BB74E1 (#0)
    finally: "TESTUSER"."SPY"("T12"."ID","T12"."PAD")=1 AND "T12"."ID"<10
    As can be seen by the above (since the new predicate appeared before and after the line containing the word "finally:"), the AND "T12"."ID"<10 predicate was already added to the original SQL statement by the time the Predicate Move-Around section of the trace file was written, and that is the first mention of <10 in the trace file. In your case, you would search the 10053 trace file for:
    "SEAL_FLAG" IS NULL
    If V$VPD_POLICY revealed that there are virtual private database (VPD) generated predicates applied to the SQL statement, take a look at the following article from the Oracle documentation library:
    http://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm

    The above article lists various views that may be queried to learn more about the VPD rules that are in effect in the schema. For example, with my VPD test:
    SELECT
      *
    FROM
      ALL_POLICIES;
     
    OBJECT_OWNER                   OBJECT_NAME                    POLICY_GROUP                  POLICY_NAME                    PF_OWNER                       PACKAGE                       FUNCTION                       SEL INS UPD DEL IDX CHK ENA STA POLICY_TYPE              LON
    ------------------------------ ------------------------------ ----------------------------- ------------------------------ ------------------------------ ----------------------------- ------------------------------ --- --- --- --- --- --- --- --- ------------------------ ---
    TESTUSER                       T12                            SYS_DEFAULT                   T_SEC                          TESTUSER                       S                                                            YES YES YES YES NO  NO  YES NO  DYNAMIC                  NO
    There are known performance problems related to the use of VPD, some of which are Oracle Database version dependent, and some of which have been corrected in recent versions. Take a look at the following articles if you have access to My Oracle Support:
    Metalink (MOS) Doc ID 728292.1 "Known Performance Issues When Using TDE and Indexes on the Encrypted Columns"
    Metalink (MOS) Doc ID 967042.1 "How to Investigate Query Performance Regressions Caused by VPD (FGAC) Predicates?"

    You might find by working through the second of the above articles that the performance problem is caused by an Oracle Database bug.

    One side note. The execution plans that you posted include a value of 0 in the Starts column of several of the operations in the execution plan. That 0 indicates that the operation never actually executed. A 0 is included in the Starts column of the line that includes the TABLE ACCESS FULL of PEOPLE_TRANSACTIONS, so at least with the OPC.PROPERTY_CONTAINER_ID value of 123, a full table scan of the PEOPLE_TRANSACTIONS table was not actually performed.

    Charles Hooper
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 9. Re: query not using index for certain user
    user8175606 Newbie
    Currently Being Moderated
    Charles,

    Awesome!!!! You have solved my problem. Thank you very much.
    BEGIN
      SYS.DBMS_RLS.ADD_POLICY     (
        object_schema          => 'SCHEMA_OWNER'
        ,object_name           => 'PEOPLE_TRANSACTIONS'
        ,policy_name           => 'TAG_SEAL'
        ,function_schema       => 'SYS'
        ,policy_function       => 'TAG_POLICY'
        ,statement_types       => 'SELECT,UPDATE,DELETE'
        ,policy_type           => dbms_rls.shared_context_sensitive
        ,long_predicate        => FALSE
        ,update_check          => FALSE
        ,static_policy         => FALSE
        ,enable                => TRUE );
    END;
    SELECT
      policy, policy_function_owner, predicate
    FROM
      V$VPD_POLICY
    WHERE
      SQL_ID='0wcs85uywn72m';
    
    POLICY      POLICY_FUNCTION PREDICATE
    ----------- --------------- ----------------------------------------
    TAG_SEAL    SYS
    TAG_SEAL    SYS
    TAG_SEAL    SYS
    TAG_SEAL    SYS
    TAG_SEAL    SYS
    TAG_SEAL    SYS             SEAL_FLAG IS NULL OR SEAL_FLAG != 'Y'
    TAG_SEAL    SYS             SEAL_FLAG IS NULL OR SEAL_FLAG != 'Y'
    TAG_SEAL    SYS             SEAL_FLAG IS NULL OR SEAL_FLAG != 'Y'
    TAG_SEAL    SYS             SEAL_FLAG IS NULL OR SEAL_FLAG != 'Y'
    TAG_SEAL    SYS             SEAL_FLAG IS NULL OR SEAL_FLAG != 'Y'
    I can't view TAG_SEAL/TAG_POLICY function thus. I used Toad to view it, but it look garbage.

    Again thank you.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points