3 Replies Latest reply on Jul 26, 2019 10:17 AM by Jonathan Lewis

    strange things with Clustering Factor and Optimizer choosing to use Index

    Dimitri Avrutin

      Hello,

       

      I have noticed the following thing:

       

      Oracle 12.1.0.2:

       

      create table dudu.dada 
      (name varchar2(2000),age number)
      tablespace users ;
      
      --Insert 10 times 2000 rows 
      begin
          for j in 1 .. 10
          loop
              for i in 1 .. 2000
              loop
                  insert into  dudu.dada values ('X',i);
              end loop;
          end loop;
          commit;
      end;
      /
      
      
      create index dudu.dada_idx on dudu.dada(age);
      
      
      BEGIN
      DBMS_STATS.GATHER_TABLE_STATS (
      ownname => 'dudu',
      tabname => 'DADA');
      END;
      /  
      
      
      select clustering_factor from dba_indexes where INDEX_NAME='DADA_IDX';
      --result: 20000
      
      
      
      EXPLAIN PLAN FOR select * from dudu.dada where age=5;
       SELECT * FROM table(DBMS_XPLAN.DISPLAY);
      
      | Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
      ------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                    |          |    10 |    60 |    11   (0)| 00:00:01 |
      |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DADA     |    10 |    60 |    11   (0)| 00:00:01 |
      |*  2 |   INDEX RANGE SCAN                  | DADA_IDX |    10 |       |     1   (0)| 00:00:01 |
      ------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - access("AGE"=5)
      
      
      
      
      
      
      
      

       

      so... it used Index range scan even though it has the worst possible Clustering Factor?

      ok... lets do the next check (and here is the really weird part):

       

       

      drop table dudu.dada purge;
      
      --same table DDL
      create table dudu.dada 
      (name varchar2(2000),age number)
      tablespace users ;
      
      --Added another row each loop run, meaning I added exactly 10 rows:
      begin
          for j in 1 .. 10
          loop
              for i in 1 .. 2000
              loop
                  insert into  dudu.dada values ('X',i);
              end loop;
              insert into  dudu.dada values ('Z',j);    ---this is the new part that adds 10 times a new row 
          end loop;
          commit;
      end;
      /
      
      
      create index dudu.dada_idx on dudu.dada(age);
      
      
      BEGIN
      DBMS_STATS.GATHER_TABLE_STATS (
      ownname => 'dudu',
      tabname => 'DADA');
      END;
      /  
      
      
      select clustering_factor from dba_indexes where INDEX_NAME='DADA_IDX';
      --result 20001 - what...? it's starting to look strange from here
      
      EXPLAIN PLAN FOR select * from dudu.dada where age=5;
       SELECT * FROM table(DBMS_XPLAN.DISPLAY);
      
      
      | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
      --------------------------------------------------------------------------
      |   0 | SELECT STATEMENT  |      |    10 |    60 |    11   (0)| 00:00:01 |
      |*  1 |  TABLE ACCESS FULL| DADA |    10 |    60 |    11   (0)| 00:00:01 |
      --------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         1 - filter("AGE"=5)
      

       

       

       

      it uses full Table scan! the 10 rows made the difference.

       

       

      I have tried the same two checks on 11.2.0.3
      while the result was same Clustering factor but on both cases it did index range scan!

       

       

      so the questions are:

       

      1. why does it still use the index if the clustering factor is such horrible? does Optimizer knows to recognise based on the Histograms it would be still beneficial even though the horrible Clustering factor?

      2. how come the second clustering factor is 20,001 and not 20,010?

      3. why the behavior is different between 11.2 and 12.1..?

       

       

      thank you.

        • 1. Re: strange things with Clustering Factor and Optimizer choosing to use Index
          Mark D Powell

          Dimitri, I was able to duplicate your second script results on 12.1.0.2 but on 12.2 I got the range scan.

          - -

          MPOWEL01> @play5
          MPOWEL01> set echo on
          MPOWEL01> set feedback on
          MPOWEL01>
          MPOWEL01> drop table T1 purge;

          Table dropped.

          MPOWEL01>
          MPOWEL01> --same table DDL
          MPOWEL01> create table T1
            2  (name varchar2(2000),age number)
            3  tablespace usr ;

          Table created.

          MPOWEL01>
          MPOWEL01> --Added another row each loop run, meaning I added exactly 10 rows:
          MPOWEL01> begin
            2      for j in 1 .. 10
            3      loop
            4          for i in 1 .. 2000
            5          loop
            6              insert into  T1 values ('X',i);
            7          end loop;
            8          insert into  T1 values ('Z',j);    ---this is the new part that adds 10 times a new row
            9      end loop;
          10      commit;
          11  end;
          12  /

          PL/SQL procedure successfully completed.

          MPOWEL01>
          MPOWEL01>
          MPOWEL01> create index T1_idx on T1(age);

          Index created.

          MPOWEL01>
          MPOWEL01>
          MPOWEL01> BEGIN
            2  DBMS_STATS.GATHER_TABLE_STATS (
            3  ownname => 'mpowel01',
            4  tabname => 'T1',
            5  cascade => TRUE ) ;
            6  END;
            7  /

          PL/SQL procedure successfully completed.

          MPOWEL01>
          MPOWEL01>
          MPOWEL01> select clustering_factor from dba_indexes where INDEX_NAME='DADA_IDX';

          no rows selected

          MPOWEL01> --result 20001 - what...? it's starting to look strange from here
          MPOWEL01>
          MPOWEL01> EXPLAIN PLAN FOR select * from T1 where age=5;

          Explained.

          MPOWEL01>  SELECT * FROM table(DBMS_XPLAN.DISPLAY);

          PLAN_TABLE_OUTPUT
          --------------------------------------------------------------------------------
          Plan hash value: 1775246573

          --------------------------------------------------------------------------------
          --------------

          | Id  | Operation                          | Name  | Rows  | Bytes | Cost (%CP
          U)| Time    |

          --------------------------------------------------------------------------------
          --------------

          PLAN_TABLE_OUTPUT
          --------------------------------------------------------------------------------
          |  0 | SELECT STATEMENT                    |        |    10 |    60 |    12  (
          0)| 00:00:01 |

          |  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |    10 |    60 |    12  (
          0)| 00:00:01 |

          |*  2 |  INDEX RANGE SCAN                  | T1_IDX |    10 |      |    1  (
          0)| 00:00:01 |

          --------------------------------------------------------------------------------
          --------------

          PLAN_TABLE_OUTPUT
          --------------------------------------------------------------------------------

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

            2 - access("AGE"=5)

          14 rows selected.

          MPOWEL01>
          MPOWEL01> select * from V$version;

          BANNER
          --------------------------------------------------------------------------------
              CON_ID
          ----------
          Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
                  0

          PL/SQL Release 12.2.0.1.0 - Production
                  0

          CORE    12.2.0.1.0      Production
                  0

          BANNER
          --------------------------------------------------------------------------------
              CON_ID
          ----------
          TNS for Linux: Version 12.2.0.1.0 - Production
                  0

          NLSRTL Version 12.2.0.1.0 - Production
                  0

          5 rows selected.

          MPOWEL01> drop table t1;

          Table dropped.

          MPOWEL01>

          - -

          Difference in plan likely to minor modifications in CBO logic.

          - -

          HTH -- Mark D Powell --

          1 person found this helpful
          • 2. Re: strange things with Clustering Factor and Optimizer choosing to use Index
            AndrewSayer

            According to the execution plans, there’s not a whole lot of difference in cost, it’s likely that a few extra rows were enough to topple some comparison another way.

             

            Even if the clustering factor is the worst it can be, the limit is that you do 1 IO per row, if you have 10 rows to access then it will cost 10 (+ whatever the index range scan costs). Remember that internally with those extra rows in the table, Oracle is now expecting you to find 10.005 rows, so will be costing that as 10.005 internally but displaying the rounded figure. The extra 10 rows wasn’t enough to increase the size of your table - they still fit in the same blocks that have already been allocated, your full table scan cost is therefore not affected by the increase and is now suddenly cheaper but by a fraction.

             

            If you used wider rows then you’d probably see the index scan being chosen in both scenarios.

            1 person found this helpful
            • 3. Re: strange things with Clustering Factor and Optimizer choosing to use Index
              Jonathan Lewis

              1) As Andrew says, your data is an extreme example because the rows are so small. Running your code and gathering stats the table reported 35 blocks.  The (rounded) cost of your range scan was 11 (i.e. 11 single block reads), With default system stats the cost of the tablescan would have been very similar and would probably have reported as 11 as well.  Three simple changes could explain a switch between plans - (a) if the CPUSPEEDNW (no workload CPU speed) varied between machines then a small difference in the CPU cost could swing the plan from one option to the other  (b) using ASSM for the tablespaces the process_id of the inserting process would dictate which blocks in the table would be used to hold the rows so the number of blocks reported as used could differ between versions hence affecting the cost of the tablescan, (c) if you used the system generated extent sizing on one system and a uniform extent size on the other system then this could affect which blocks were populated, hence the cost of the tablescan.

               

              As a learning experience you could examine the 10053 trace for two hinted versions of the query to see the component costs that end up being reported as "11" in the execution plans.

               

              2)  Think about WHERE the data is going.

              The j=1 row is inserted after 2,000 i rows, and the second i=1 row is inserted right next to it.

              The third i=2 row is inserted 2 rows away from the j=2 row

              The fourth i=3 row is inserted 3 rows away from the j=3 row

              and so on.

               

              All the j rows are inserted very near a matching i row, and you probably have something like 600 rows per block, so in every case it's likely that the clustering factor walk picks up each j row with a matching i row in the same block - apart from the j=10 row which is the last row in the table, 1990 rows away from the previous i= 10 row.

               

               

              3) See my comments on (1) - possibly a side-effect of system stats, process id, tablespace definition.

               

              Regards

              Jonathan Lewis

              1 person found this helpful