Why Isn’t My Index Used… When USER2 Executes this Query?

November 23, 2011

I previously wrote a couple of articles that mention reasons why an index might not be use for a particular query, including an article that was formatted as a True or False quiz with several reference articles.  A few days ago I saw an OTN thread that caught my curiosity, where the original poster (OP) claimed that the optimizer simply will not use an index to access a table when any user other than the schema owner or the SYS user executes a particular query.

Why is the OP attempting to execute the SQL statement as the SYS user?  The SYS user is special.  As mentioned in my review of the book “Practical Oracle 8i“, as I read the book I wrote the following paraphrase into my notes, the SYS user is special:

Oracle 8i introduces row level security, which uses a PL/SQL function to apply an additional WHERE clause predicate to a table – row level security does not apply to the SYS user. It is important to use CONSISTENT=Y when exporting partitioned tables. When CONSISTENT=N is specified, the export of each partition in a table is treated as a separate transaction, and may be exported at a different SCN number (incremented when any session commits). When tables are exported which contain nested tables, the two physical segments are exported in separate transactions, potentially resulting in inconsistent data during the import if the export was performed with the default CONSISTENT=N.

Is the above paraphrase from this 10 year old book a clue?  Maybe it is a problem related to secure view merging because the SQL statement uses the index when the schema owner executes the SQL statement (there is a very good example of this type of problem found in the book “Troubleshooting Oracle Performance“).  Maybe it is a problem where the public synonym for the table actually points to a view or an entirely different table – the execution plan for the non-schema owner did show a VIEW operation, while the execution plan for the schema owner did not show the VIEW operation.  Maybe it is a problem where the optimizer parameters are adjusted differently for different users – in such a case we might need to dig into the V$SYS_OPTIMIZER_ENV, V$SES_OPTIMIZER_ENV, and V$SQL_OPTIMIZER_ENV views.

Maybe taking a look at the DBMS_XPLAN output would help.  Why does the Predicate Information section of the execution plan show the following only for the non-schema owner?

7 - filter(("SEAL_FLAG" IS NULL OR "SEAL_FLAG"<>'Y'))
9 - filter(("SEAL_FLAG" IS NULL OR "SEAL_FLAG"<>'Y'))
11 - filter(("SEAL_FLAG"<>'Y' OR "SEAL_FLAG" IS NULL))
13 - filter(("SEAL_FLAG"<>'Y' OR "SEAL_FLAG" IS NULL))
19 - filter(("SEAL_FLAG"<>'Y' OR "SEAL_FLAG" IS NULL)) 

A significant clue?  If those predicates were also found in the DBMS_XPLAN generated output for the schema owner (and the SYS user), I would probably conclude that the optimizer generated those additional predicates from defined column constraints, and that a review of a 10053 trace file might help determine what caused those predicates to be automatically created.  However, those predicates did not appear in the execution plan that was generated for the schema owner.  It might be time to start checking the V$VPD_POLICY view for this particular SQL_ID, for example (a completely unrelated test case output):

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 

Maybe we should also check some of the other virtual private database (VPD) related views including ALL_POLICIES (once again from a completely unrelated test case):

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.  Maybe a quick check of one of the following articles would help, if the OP finds that VPD is in fact in use (the second article provides step by step directions for investigation):

• 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?”

—

Take a look at the OTN thread.  Any other suggestions for the OP?