Alexander Anokhin

It may seem strange, but in some cases estimation of cardinality can depends on size (in blocks) of a segment.

1. Estimation of cardinality can depends on size (in blocks) of a segment.
First, I will create table with two partitions with different size.

-- Creation of the table
SQL>
  2  create table test
  3  ( state    varchar2(10)  not null,
  4    val      number(4)     not null
  5  )
  6  partition by list (state)
  7  (
  8    partition p1 values ('Open'),
  9    partition p2 values ('Closed')
 10  );

Table created

-- Populating of the table
SQL>
  2
  3  insert into test
  4      select 'Open',
  5             decode(mod(rownum, 10), 0, 0, 1)
  6        from dual
  7     connect by level <= 10000;   10000 rows inserted SQL>
  2  insert into test
  3      select 'Closed',
  4             100 + mod(rownum, 10)
  5        from dual
  6     connect by level <= 90000;   

90000 rows inserted 

SQL> commit;

Commit complete

SQL>
  2  begin
  3      dbms_stats.gather_table_stats(user,
  4                                    'test',
  5                                    cascade          => true,
  6                                    granularity      => 'ALL',
  7                                    estimate_percent => 100,
  8                                    method_opt       => 'for all columns size 10'
  9                                    );
 10  end;
 11  /

PL/SQL procedure successfully completed

At this point we have a table with two partitions – P1 and P2 with gathered histograms on all coumns. The partition P1 consists of 1000 rows with column VAL = 0 and 9000 rows with VAL = 1.
Let’s try to see estimation of cardinality

SQL>
  2  explain plan for
  3  SELECT t.*
  4    FROM test t
  5   WHERE t.state = 'Open'
  6     AND t.val = 1;

Explained

SQL> select * from table(dbms_xplan.display);

----------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |  8999 | 62993 |    23  (48)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE|      |  8999 | 62993 |    23  (48)| 00:00:01 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL   | TEST |  8999 | 62993 |    23  (48)| 00:00:01 |     1 |     1 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T"."VAL"=1)

Oracle correctly estimates cardinality = 8999 (only 1 was lost during rounding).
In this case cardinality of FTS calculated as
selectivity(state = ‘Open’) * selectivity(val = 1) * num_rows (all from P1)
Selectivities are calculated on the basis of histograms.

Let’s create a local index and look at the plan of the same query.

SQL> create index testidx on test(val) local;

Index created

SQL>
  2  begin
  3      dbms_stats.gather_index_stats(user,
  4                                    'testidx',
  5                                    granularity      => 'ALL',
  6                                    estimate_percent => 100
  7                                    );
  8  end;
  9  /

PL/SQL procedure successfully completed

SQL>
  2  explain plan for
  3  SELECT --+ index(t)
  4         t.*
  5    FROM test t
  6   WHERE t.state = 'Open'
  7     AND t.val = 1;

Explained

SQL> select * from table(dbms_xplan.display);

---------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name    | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |         |  8999 | 62993 |   119  (14)|       |       |
|   1 |  PARTITION LIST SINGLE             |         |  8999 | 62993 |   119  (14)|   KEY |   KEY |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TEST    |  8999 | 62993 |   119  (14)|     1 |     1 |
|*  3 |    INDEX RANGE SCAN                | TESTIDX |   818 |       |    43  (17)|     1 |     1 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T"."VAL"=1)

Cardinality of INDEX RANGE SCAN is 818. Trace 10053 does not contain any info about how this value is calculated.

BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: TEST  Alias:  T  Partition [0]
    #Rows: 10000  #Blks:  40  AvgRowLen:  7.00
    #Rows: 10000  #Blks:  40  AvgRowLen:  7.00
Index Stats::
  Index: TESTIDX  Col#: 2  PARTITION [0]
    LVLS: 1  #LB: 40  #DK: 2  LB/K: 20.00  DB/K: 37.00  CLUF: 74.00
    LVLS: 1  #LB: 40  #DK: 2  LB/K: 20.00  DB/K: 37.00  CLUF: 74.00
    User hint to use this index
***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#1): STATE(VARCHAR2)  Part#: 0
    AvgLen: 5.00 NDV: 1 Nulls: 0 Density: 5.0000e-05
    Histogram: Freq  #Bkts: 1  UncompBkts: 10000  EndPtVals: 1
  Column (#1): STATE(VARCHAR2)
    AvgLen: 5.00 NDV: 1 Nulls: 0 Density: 5.0000e-05
    Histogram: Freq  #Bkts: 1  UncompBkts: 10000  EndPtVals: 1
  Column (#2): VAL(NUMBER)  Part#: 0
    AvgLen: 3.00 NDV: 2 Nulls: 0 Density: 0.05 Min: 0 Max: 1
    Histogram: Freq  #Bkts: 2  UncompBkts: 10000  EndPtVals: 2
  Column (#2): VAL(NUMBER)
    AvgLen: 3.00 NDV: 2 Nulls: 0 Density: 0.05 Min: 0 Max: 1
    Histogram: Freq  #Bkts: 2  UncompBkts: 10000  EndPtVals: 2
  Table: TEST  Alias: T
    Card: Original: 10000  Rounded: 8999  Computed: 8999.05  Non Adjusted: 8999.05
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: index (AllEqRange)
    Index: TESTIDX
    resc_io: 103.00  resc_cpu: 4291511
    ix_sel: 0.89995  ix_sel_with_filters: 0.89995
    Cost: 118.86  Resp: 118.86  Degree: 1
  Best:: AccessPath: IndexRange  Index: TESTIDX
         Cost: 118.86  Degree: 1  Resp: 118.86  Card: 8999.05  Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:  TEST[T]#0
***********************
Best so far: Table#: 0  cost: 118.8565  card: 8999.0500  bytes: 62993
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations tried: 1
*********************************
Final - All Rows Plan:  Best join order: 1
  Cost: 118.8565  Degree: 1  Card: 8999.0000  Bytes: 62993
  Resc: 118.8565  Resc_io: 103.0000  Resc_cpu: 4291511
  Resp: 118.8565  Resp_io: 103.0000  Resc_cpu: 4291511
kkoipt: Query block SEL$1 (#0)
******* UNPARSED QUERY IS *******
SELECT /*+ INDEX ("T") */ "T"."STATE" "STATE","T"."VAL" "VAL" FROM "SA"."TEST" "T" WHERE "T"."VAL"=1
kkoqbc-subheap (delete addr=ffffffff7cf189b8, in-use=12592, alloc=16040)
kkoqbc-end
          : call(in-use=20992, alloc=49080), compile(in-use=41208, alloc=44568)
apadrv-end: call(in-use=20992, alloc=49080), compile(in-use=42008, alloc=44568)

sql_id=7vvtk89y1v5zb.
Current SQL statement for this session:
explain plan for
SELECT --+ index(t)
       t.*
  FROM test t
 WHERE t.state = 'Open'
   AND t.val = 1

============
Plan Table
============
------------------------------------------------------+-----------------------+---------------+
| Id  | Operation                           | Name    | Rows  | Bytes | Cost  | Pstart| Pstop |
------------------------------------------------------+-----------------------+---------------+
| 0   | SELECT STATEMENT                    |         |       |       |   119 |       |       |
| 1   |  PARTITION LIST SINGLE              |         |  8999 |   62K |   119 | KEY   | KEY   |
| 2   |   TABLE ACCESS BY LOCAL INDEX ROWID | TEST    |  8999 |   62K |   119 | 1     | 1     |
| 3   |    INDEX RANGE SCAN                 | TESTIDX |   818 |       |    43 | 1     | 1     |
------------------------------------------------------+-----------------------+---------------+
Predicate Information:
----------------------
3 - access("T"."VAL"=1)

In fact Oracle corrects cardinality of INDEX RANGE SCAN by proportion of numblocks(P1) in numblocks(table).

Cardinality in this case calculated as:
Cardinality = estimated cardinality multiplied by numblocks(P1)/numblocks(table)

SQL> select partition_name,
  2         blocks
  3    from dba_tab_statistics s
  4   where table_name = 'TEST'
  5     and (partition_name is null or partition_name = 'P1')
  6  ;

PARTITION_NAME                     BLOCKS
------------------------------ ----------
P1                                     40
                                      440

Thus, cardinality of IRS = 8999 * 40/440 = 818

It is strange, especially with gathered histograms. In fact figures from histograms gathered inside partitions is enough in this case.
(It is important to note, if column val would be “nullable” this effect disappears).
It is the first.

And the second, notice, that step 2 “TABLE ACCESS BY LOCAL INDEX ROWID” has right cardinality and cost.
It, as expected, calculated as
blevel + (leaf_blocks + cluf) * selectivity + cpu_cost/(cpuspeed*sreadtim*1000) = 1 + (40 + 74)*0.89995 + cpu_cost/(cpuspeed*sreadtim*1000) = 119
(I’ve ommitted some minor details)
Looks like that wrong cardinality of IRS is not a big problem, because parent step has right figures.

The script is used in this part:

drop table test;

-- Creation of the table
create table test
( state    varchar2(10)  not null,
  val      number(4)     not null
)
partition by list (state)
(
  partition p1 values ('Open'),
  partition p2 values ('Closed')
);


insert into test
    select 'Open',
           decode(mod(rownum, 10), 0, 0, 1)
      from dual 
   connect by level <= 10000;

insert into test
    select 'Closed',
           100 + mod(rownum, 10)
      from dual 
   connect by level <= 90000;

commit;

begin
    dbms_stats.gather_table_stats(user, 
                                  'test', 
                                  cascade          => true, 
                                  granularity      => 'ALL', 
                                  estimate_percent => 100,
                                  method_opt       => 'for all columns size 10'
                                  ); 
end;
/

explain plan for
SELECT t.*
  FROM test t
 WHERE t.state = 'Open'
   AND t.val = 1;

select * from table(dbms_xplan.display);

-- Let’s create a local index and look at the plan of the same query.
create index testidx on test(val) local;

begin 
    dbms_stats.gather_index_stats(user, 
                                  'testidx', 
                                  granularity      => 'ALL', 
                                  estimate_percent => 100
                                  ); 
end;
/

explain plan for
SELECT --+ index(t)
       t.*
  FROM test t
 WHERE t.state = 'Open'
   AND t.val = 1;

select * from table(dbms_xplan.display);

Two little additional points:
2. An oddity with Index Fast Full Scan when table access is not required
What if we change the query to avoid the table lookup and to scan only the index?
as this

    SELECT count(*)
      FROM test t
     WHERE t.state = 'Open'
       AND t.val = 1;

Let’s remind that our table is partitioned by column STATE and the index is LOCAL. Thus we can expect that Index Fast Full Scan of the one partition is enough in this case.

Let’s try to force Oracle to use Index Fast Full Scan:

SQL> explain plan for
  2      SELECT --+ index_ffs(t testidx)
  3             count(*)
  4        FROM test t
  5       WHERE t.state = 'Open'
  6         AND t.val = 1;
 
Explained

SQL> select * from table(dbms_xplan.display);

----------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |  8999 | 62993 |    23  (48)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE|      |  8999 | 62993 |    23  (48)| 00:00:01 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL   | TEST |  8999 | 62993 |    23  (48)| 00:00:01 |     1 |     1 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T"."VAL"=1)

Oracle cannot use Index Fast Full Scan here and will be able to do it only if the index will contain column STATE. I suggest that it is because Oracle believes that it needs to visit the table for column STATE.
Looks like the bug 10037603.

3. An oddity with the cost of the statement
Fortunately it is enough smart to use Index Range Scan here, for the same query as above, without table access.
From examples above we can expect cost of TABLE ACCESS FULL is 23 and the cost of INDEX RANGE SCAN is 43 for the following query:

    SELECT count(*)
      FROM test t
     WHERE t.state = 'Open'
       AND t.val = 1;

TABLE ACCESS FULL is cheaper and it is expected that Oracle will chose full scan.
So, I am going to increase size of the table and do cost of TABLE ACCESS FULL much than INDEX RANGE SCAN.

SQL> declare
  2    i integer;
  3  begin
  4
  5    dbms_stats.set_table_stats(user, 'TEST', 'P1', numblks => 200);
  6
  7  end;
  8  /

PL/SQL procedure successfully completed

SQL> explain plan for
  2  SELECT count(*)
  3    FROM test t
  4   WHERE t.state = 'Open'
  5     AND t.val = 1;

Explained

SQL> select * from table(dbms_xplan.display);

------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |     7 |     65  (0)|       |       |
|   1 |  SORT AGGREGATE        |      |     1 |     7 |            |       |       |
|   2 |   PARTITION LIST SINGLE|      |  8999 | 62993 |     65  (0)|   KEY |   KEY |
|*  3 |    TABLE ACCESS FULL   | TEST |  8999 | 62993 |     65  (0)|     1 |     1 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T"."VAL"=1)

Full scan again. Notice, cost is 65 now. But I expected to see INDEX RANGE SCAN here because it is cheaper, cost of IRS should be = 43.

Let’s try to force Oracle use index:

SQL> explain plan for
  2  SELECT --+ index(t)
  3         count(*)
  4    FROM test t
  5   WHERE t.state = 'Open'
  6     AND t.val = 1;

Explained

SQL> select * from table(dbms_xplan.display);

---------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |     1 |     7 |   119   (1)|       |       |
|   1 |  SORT AGGREGATE        |         |     1 |     7 |            |       |       |
|   2 |   PARTITION LIST SINGLE|         |  8999 | 62993 |    43   (0)|   KEY |   KEY |
|*  3 |    INDEX RANGE SCAN    | TESTIDX |  8999 | 62993 |    43   (0)|     1 |     1 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T"."VAL"=1)

Pay attention to the costs of the step 3 (=43) and especially step 1.
Cost of the step 1 = 119 it is the cost of TABLE ACCESS BY INDEX ROWID in the plan above. Oracle continues to take into account cost of table lookup (clustering factor) when such step does not exist in the plan!

Personally I consider this as a bug. Oracle uses CLUF in the estimations for a plan without TABLE ACCESS BY INDEX ROWID, it is absolutely wrong.