5 Replies Latest reply: Dec 30, 2011 5:20 AM by Jonathan Lewis RSS

    9.2.0.8 - CBO's strange attitude while choosing index

    778703
      Hello,

      9.2.0.8

      Have the following output of explain plan -
      SQL> explain plan for SELECT *
        FROM SUPERMAG.SMDOCUMENTS D,
             SUPERMAG.SMSPEC      S
       WHERE D.DOCTYPE IN ('CS', 'CR')
         AND D.DOCTYPE = S.DOCTYPE
         AND D.ID = S.DOCID
         AND D.CREATEDAT BETWEEN :B2 AND :B1;  2    3    4    5    6    7
      
      Explained.
      
      SQL> @?/rdbms/admin/utlxpls
      
      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      
      --------------------------------------------------------------------------------------------------------
      | Id  | Operation                         |  Name              | Rows  | Bytes | Cost  | Pstart| Pstop |
      --------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                  |                    |    19 |  3097 |  6906 |       |       |
      |   1 |  TABLE ACCESS BY LOCAL INDEX ROWID| SMSPEC             |     3 |   225 |     1 |       |       |
      |   2 |   NESTED LOOPS                    |                    |    19 |  3097 |  6906 |       |       |
      |   3 |    INLIST ITERATOR                |                    |       |       |       |       |       |
      |*  4 |     TABLE ACCESS BY INDEX ROWID   | SMDOCUMENTS        |     6 |   528 |  6905 |       |       |
      |*  5 |      INDEX RANGE SCAN             | SMDOCUMENTS_STATE  |   993K|       |   368 |       |       |
      |   6 |    INLIST ITERATOR                |                    |       |       |       |       |       |
      |   7 |     PARTITION LIST ITERATOR       |                    |       |       |       |KEY(I) |KEY(I) |
      |*  8 |      INDEX RANGE SCAN             | SMCSPEC_PK         |     1 |       |     1 |KEY(I) |KEY(I) |
      --------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         4 - filter("D"."CREATEDAT">=:Z AND SYS_OP_DESCEND("D"."CREATEDAT")<=SYS_OP_DESCEND(:Z) AND
                    "D"."CREATEDAT"<=:Z AND SYS_OP_DESCEND("D"."CREATEDAT")>=SYS_OP_DESCEND(:Z))
         5 - access("D"."DOCTYPE"='CR' OR "D"."DOCTYPE"='CS')
         8 - access(("S"."DOCTYPE"='CR' OR "S"."DOCTYPE"='CS') AND "D"."ID"="S"."DOCID")
             filter("D"."DOCTYPE"="S"."DOCTYPE")
      
      Note: cpu costing is off
      
      25 rows selected.
      
      SQL> explain plan for SELECT /*+ INDEX(D SMDOCUMENTS_CREATEDAT) */ *
        FROM SUPERMAG.SMDOCUMENTS D,
             SUPERMAG.SMSPEC      S
       WHERE D.DOCTYPE IN ('CS', 'CR')
         AND D.DOCTYPE = S.DOCTYPE
         AND D.ID = S.DOCID
         AND D.CREATEDAT BETWEEN :B2 AND :B1;  2    3    4    5    6    7
      
      Explained.
      
      SQL> @?/rdbms/admin/utlxpls
      
      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      
      ------------------------------------------------------------------------------------------------------------
      | Id  | Operation                         |  Name                  | Rows  | Bytes | Cost  | Pstart| Pstop |
      ------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                  |                        |    19 |  3097 |    15 |       |       |
      |   1 |  TABLE ACCESS BY LOCAL INDEX ROWID| SMSPEC                 |     3 |   225 |     1 |       |       |
      |   2 |   NESTED LOOPS                    |                        |    19 |  3097 |    15 |       |       |
      |*  3 |    TABLE ACCESS BY INDEX ROWID    | SMDOCUMENTS            |     6 |   528 |    14 |       |       |
      |*  4 |     INDEX RANGE SCAN              | SMDOCUMENTS_CREATEDAT  |   103 |       |   115 |       |       |
      |   5 |    INLIST ITERATOR                |                        |       |       |       |       |       |
      |   6 |     PARTITION LIST ITERATOR       |                        |       |       |       |KEY(I) |KEY(I) |
      |*  7 |      INDEX RANGE SCAN             | SMCSPEC_PK             |     1 |       |     1 |KEY(I) |KEY(I) |
      ------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         3 - filter("D"."DOCTYPE"='CR' OR "D"."DOCTYPE"='CS')
         4 - access("D"."CREATEDAT">=:Z AND "D"."CREATEDAT"<=:Z)
             filter(SYS_OP_DESCEND("D"."CREATEDAT")<=SYS_OP_DESCEND(:Z) AND
                    SYS_OP_DESCEND("D"."CREATEDAT")>=SYS_OP_DESCEND(:Z))
         7 - access(("S"."DOCTYPE"='CR' OR "S"."DOCTYPE"='CS') AND "D"."ID"="S"."DOCID")
             filter("D"."DOCTYPE"="S"."DOCTYPE")
      
      Note: cpu costing is off
      
      25 rows selected.
      
      SQL>
      Briefly, when run explain plan for the plain query, CBO chooses index SMDOCUMENTS_STATE (cost = 368). But when I've hinted another index (SMDOCUMENTS_CREATEDAT), CBO takes the index and shows that its cost is much lower (cost = 115).

      Event 10046 realy shows that at runtime high cost index (SMDOCUMENTS_STATE) is chosen.

      Outline can't be cause of the issue.

      What is the matter? Any Ideas?
        • 1. Re: 9.2.0.8 - CBO's strange attitude while choosing index
          sybrand_b
          I seem to recall in 9i CBO has no accurate histograms for date columns, and/or always assumes the cardinality of a date column to be 1/NDV (Number of Distinct values). Which would explain why the selectivity of the index is considered to be insufficient.

          ---------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: 9.2.0.8 - CBO's strange attitude while choosing index
            Jonathan Lewis
            user13141097 wrote:
            Predicate Information (identified by operation id):
            ---------------------------------------------------
            3 - filter("D"."DOCTYPE"='CR' OR "D"."DOCTYPE"='CS')
            4 - access("D"."CREATEDAT">=:Z AND "D"."CREATEDAT"<=:Z)
            filter(SYS_OP_DESCEND("D"."CREATEDAT")<=SYS_OP_DESCEND(:Z) AND
            SYS_OP_DESCEND("D"."CREATEDAT")>=SYS_OP_DESCEND(:Z))
            7 - access(("S"."DOCTYPE"='CR' OR "S"."DOCTYPE"='CS') AND "D"."ID"="S"."DOCID")
            filter("D"."DOCTYPE"="S"."DOCTYPE")
            It would be helpful to see the definition of the index sm_documents_createdat - it looks as if it has been defined with the first column declared as descending.
            This costing anomaly is to be expected ( http://jonathanlewis.wordpress.com/2010/09/07/cbo-surprise-3/ ) if you've declared the index the way I've guessed. There is a special case of costs involving indexes accessed through unpeekable bind variables (or their equivalent) that allows Oracle to ignore the index unless hinted.


            Regards
            Jonathan Lewis
            http://jonathanlewis.wordpress.com
            Author: <b><em>Oracle Core</em></b>
            • 3. Re: 9.2.0.8 - CBO's strange attitude while choosing index
              778703
              The index is quite ordinary -
              create index SUPERMAG.SMDOCUMENTS_CREATEDAT on SUPERMAG.SMDOCUMENTS (CREATEDAT)
                tablespace SMINDX_BIG
                pctfree 10
                initrans 2
                maxtrans 255
                storage
                (
                  initial 64K
                  minextents 1
                  maxextents unlimited
                );
              its clustering factor is lower (SMDOCUMENTS_CREATEDAT=414840 against SMDOCUMENTS_STATE=577650).
              by the way, if I remove binding variables by literals, then explan plan shows that CBO choose SMDOCUMENTS_CREATEDAT -
              SQL> explain plan for SELECT *
                FROM SUPERMAG.SMDOCUMENTS D,
                     SUPERMAG.SMSPEC      S
               WHERE D.DOCTYPE IN ('CS', 'CR')
                 AND D.DOCTYPE = S.DOCTYPE
                 AND D.ID = S.DOCID
                 AND D.CREATEDAT BETWEEN '15-DEC-11' AND '28-DEC-11';  2    3    4    5    6    7
              
              Explained.
              
              SQL> @?/rdbms/admin/utlxpls
              
              PLAN_TABLE_OUTPUT
              --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
              
              ------------------------------------------------------------------------------------------------------------
              | Id  | Operation                         |  Name                  | Rows  | Bytes | Cost  | Pstart| Pstop |
              ------------------------------------------------------------------------------------------------------------
              |   0 | SELECT STATEMENT                  |                        |    13 |  2119 |     6 |       |       |
              |   1 |  TABLE ACCESS BY LOCAL INDEX ROWID| SMSPEC                 |     3 |   225 |     1 |       |       |
              |   2 |   NESTED LOOPS                    |                        |    13 |  2119 |     6 |       |       |
              |*  3 |    TABLE ACCESS BY INDEX ROWID    | SMDOCUMENTS            |     4 |   352 |     5 |       |       |
              |*  4 |     INDEX RANGE SCAN              | SMDOCUMENTS_CREATEDAT  |    33 |       |    39 |       |       |
              |   5 |    INLIST ITERATOR                |                        |       |       |       |       |       |
              |   6 |     PARTITION LIST ITERATOR       |                        |       |       |       |KEY(I) |KEY(I) |
              |*  7 |      INDEX RANGE SCAN             | SMCSPEC_PK             |     1 |       |     1 |KEY(I) |KEY(I) |
              ------------------------------------------------------------------------------------------------------------
              
              Predicate Information (identified by operation id):
              ---------------------------------------------------
              
                 3 - filter("D"."DOCTYPE"='CR' OR "D"."DOCTYPE"='CS')
                 4 - access("D"."CREATEDAT">='15-DEC-11' AND "D"."CREATEDAT"<='28-DEC-11')
                     filter(SYS_OP_DESCEND("D"."CREATEDAT")<=SYS_OP_DESCEND('15-DEC-11') AND
                            SYS_OP_DESCEND("D"."CREATEDAT")>=SYS_OP_DESCEND('28-DEC-11'))
                 7 - access(("S"."DOCTYPE"='CR' OR "S"."DOCTYPE"='CS') AND "D"."ID"="S"."DOCID")
                     filter("D"."DOCTYPE"="S"."DOCTYPE")
              
              Note: cpu costing is off
              
              25 rows selected.
              
              SQL>
              There is a special case of costs involving indexes accessed through unpeekable bind variables (or their equivalent) that allows Oracle to ignore the index unless hinted.
              Very interesting what is the case?
              • 4. Re: 9.2.0.8 - CBO's strange attitude while choosing index
                sybrand_b
                already answered. Re-read Jonathan's last sentence.

                -------
                Sybrand Bakker
                Senior Oracle DBA
                • 5. Re: 9.2.0.8 - CBO's strange attitude while choosing index
                  Jonathan Lewis
                  user13141097 wrote:
                  The index is quite ordinary -
                  create index SUPERMAG.SMDOCUMENTS_CREATEDAT on SUPERMAG.SMDOCUMENTS (CREATEDAT)
                  tablespace SMINDX_BIG
                  pctfree 10
                  initrans 2
                  maxtrans 255
                  storage
                  (
                  initial 64K
                  minextents 1
                  maxextents unlimited
                  );
                  Note the appearance of the sys_op_descend() function in the filter predcates, that means you must have an index that includes "createdat desc". Since it's not in the index in question I ran up a quick test that shows you've found a bug. Oracle has found the hidden column associated with the other index and used it (when it should not have done so) to generate some extra predicates for your query - with the side-effect of introducing "unknowable input values" to the optimizer code, leading to the special case.

                  My testcase worked correctly in 10.2.0.3. I'll post it on my blog later on today.

                  Regards
                  Jonathan Lewis
                  http://jonathanlewis.wordpress.com
                  Author: <b><em>Oracle Core</em></b>