3 Replies Latest reply on Feb 22, 2018 1:59 PM by Ric Van Dyke

    Autotrace in Exadata not showing predicate information correctly

    Ric Van Dyke

      I've noticed that the autotrace display of predicate information is not quite right in exadata plans.   Predicates being applied to the storage are showing as "access predicates" not "storage predicates" as they should and do when using DBMS_XPLAN.  Example with DBMS_XPLAN.DISPLAY_CURSOR:

      PLAN_TABLE_OUTPUT
      ----------------------------------------------------------------------------------------------------------------------------------------------------------
      ----------------------------------------------------------------------------------------------------
      SQL_ID  0bxjs903a3n34, child number 0
      -------------------------------------
      select /*+ qb_name(opt) */b.object_name, b.object_type, a.username
      from allusers_tab a, bigger_tab b  where a.username = b.owner    and
      b.object_type = 'PROCEDURE'    and a.username not in ('SYS','SYSTEM')

      Plan hash value: 2223160658

      ----------------------------------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                   | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1
      ----------------------------------------------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT            |              |      1 |        |       | 81140 (100)|          |  12288 |00:00:00.22 |     297K|    297K|       |
      |*  1 |  HASH JOIN                  |              |      1 |  11317 |   453K| 81140   (1)| 00:00:04 |  12288 |00:00:00.22 |     297K|    297K|  4038K|  4
      |   2 |   JOIN FILTER CREATE        | :BF0000      |      1 |     17 |   136 |     3   (0)| 00:00:01 |     17 |00:00:00.01 |       9 |      0 |       |
      |*  3 |    TABLE ACCESS STORAGE FULL| ALLUSERS_TAB |      1 |     17 |   136 |     3   (0)| 00:00:01 |     17 |00:00:00.01 |       9 |      0 |  1025K|  1
      |   4 |   JOIN FILTER USE           | :BF0000      |      1 |  11946 |   384K| 81137   (1)| 00:00:04 |  12288 |00:00:00.21 |     297K|    297K|       |
      |*  5 |    TABLE ACCESS STORAGE FULL| BIGGER_TAB   |      1 |  11946 |   384K| 81137   (1)| 00:00:04 |  12288 |00:00:00.21 |     297K|    297K|  1025K|  1
      ----------------------------------------------------------------------------------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

         1 - access("A"."USERNAME"="B"."OWNER")
         3 - storage(("A"."USERNAME"<>'SYS' AND "A"."USERNAME"<>'SYSTEM'))
             filter(("A"."USERNAME"<>'SYS' AND "A"."USERNAME"<>'SYSTEM'))
         5 - storage(("B"."OBJECT_TYPE"='PROCEDURE' AND "B"."OWNER"<>'SYS' AND "B"."OWNER"<>'SYSTEM' AND SYS_OP_BLOOM_FILTER(:BF0000,"B"."OWNER")))
             filter(("B"."OBJECT_TYPE"='PROCEDURE' AND "B"."OWNER"<>'SYS' AND "B"."OWNER"<>'SYSTEM' AND SYS_OP_BLOOM_FILTER(:BF0000,"B"."OWNER")))

      Note
      -----
         - dynamic statistics used: dynamic sampling (level=2)
         - 1 Sql Plan Directive used for this statement

       

      **************************

      Same plan in SQL Developer with autotrace:

       

      Autotrace_SQLDEV_exadata.jpg

        • 1. Re: Autotrace in Exadata not showing predicate information correctly
          Ric Van Dyke

          Sorry I meant to put in the version information.  My bad.  Database 12.1.0.2 Enterprise Exadata, SQL Developer drop 17.4.0.355  

          • 2. Re: Autotrace in Exadata not showing predicate information correctly
            Vadim Tropashko-Oracle

            Both plan_table (AKA plantable$) and v$sql_plan feature ACCESS_PREDICATES and FILTER_PREDICATE columns, and I failed to find any sign of STORAGE_PREDICATES. Should "access predicates" be renamed to "storage predicates" whenever OPERATION column contains STORAGE? I don't see the point, but appreciate any  argument in favor of thereof or a pointer to a literature/documentation.

            • 3. Re: Autotrace in Exadata not showing predicate information correctly
              Ric Van Dyke

              In answer to your question, Yes I think that would be correct.  Clearly that is what display cursor  is doing. If nothing else it would be great to have consistency between the tools used to show the plan. It's sort of important since in Exadata these are the predicates that could be applied as a smart scan and maybe use a storage index if available. The documentation is rather silent on this, about the only thing is for the explain plan the parameter CELL_OFFLOAD_PLAN_DISPLAY can be used to control the display of storage predicates.  There doesn't appear to be a similar parameter for display cursor, and this parameter seems to have not effect on display cursor based on my quick tests. 

              This was logged as a bug with display cursor (Bug 8307061) when it didn't show this and they fixed it so it does now.   That seems to indicate that this is a good idea to show.

              There are folks that have blogged about how showing this in display cursor is a good idea (Tanel Poder and Kerry Osborne for example).  But there isn't much in the doc set, OTN or even My Oracle Support about this.