Wednesday, January 28, 2009

Common tuning issues - avoiding the "fetch first rows quickly" trap

It seems to be a commonly encountered issue that as part of the analysis of a potentially poor performing SQL query statement the investigation is mislead by the fact that running the query in a GUI database front-end tool like TOAD, SQLDeveloper or similar shows an instant reply.

Most of these tools by default fetch only the first n rows of the result set conveniently into a grid-like GUI component.

This might lead to the conclusion that the statement seems to perform well and therefore doesn't require further investigations respectively tuning efforts.

The potential problem with this approach is that due to several possible causes a statement might return the first rows very quickly, but depending on how the statement is actually used this doesn't necessarily mean that all rows will be processed in a efficient manner.

Usually the following circumstances can lead to such side-effects:

- Grossly underestimated cardinalities by the optimizer
- An efficient access path available to the optimizer based on that wrong cardinality estimate, usually an index

There are several scenarios that can be the root cause of the underestimated cardinalities, among them are:

- Wrong statistics of involved tables or indexes
- Sharing of child cursors (execution plans) when using bind variables that cause different cardinalities, can also be forced indirectly by CURSOR_SHARING=FORCE
- Incorrect estimation of join cardinalities: There are simply scenarios where the optimizer is not able to come up with a good estimate of a join, even (or possibly because of) with the help of histograms

Although starting from 10g on there is a default statistics collection job which should help to prevent the first point on the list from happening, it's actually this default job in conjunction with a particular pattern of database usage that can lead exactly to this result.

The following test case run on Oracle 10g XE should simulate this pattern to demonstrate one of the possible scenarios.

It creates table which represents a "staging" table of a bulk load process. This "staging" table is accidentally empty during the night when the default statistics collection job is executed. Afterwards the table gets loaded with 1,500,000 rows and a query is executed without refreshing the statistics after the load is complete.

You'll see that based on the wrong statistics the optimizer comes to a wrong conclusion.

The interesting side effect of this is that the query performs very well in a front-end tool that fetches only the first n rows, since the plan accidentally corresponds to a very efficient "first_rows(n)" plan, although the optimizer_mode is ALL_ROWS by default in 10g.

I've carefully crafted this example to show that even the deliberate usage of an "ORDER BY" in an attempt to force the database to process the whole result set for sorting before returning the first rows can be misleading, since it's possible to process such a query without a sort operation in case a suitable index exists.

Only by definitely forcing the database to process all rows you get the real picture if you know that all rows from your statement actually need to be processed. But it's not that easy to force this; the cost based optimizer is smart and most of the time simply wrapping the statement in a "COUNT(*) from (...)" is again misleading, because the optimizer often is able to avoid the random row access to the table, which is where most of the time is usually spent under such circumstances.

Let's start with setting up the environment:


SQL>
SQL> show parameter optimizer_mode

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
SQL>
SQL> drop table t_fetch_first_rows purge;

Table dropped.

Elapsed: 00:00:00.84
SQL>
SQL> create table t_fetch_first_rows (
2 id number not null,
3 name varchar2(30) not null,
4 type varchar2(30) not null,
5 measure number
6 );

Table created.

Elapsed: 00:00:00.09
SQL>
SQL> create index idx_fetch_first_rows on t_fetch_first_rows (type, id);

Index created.

Elapsed: 00:00:00.03
SQL>
SQL> -- Simulate an "empty" staging table analyzed by the nightly default
SQL> -- statistics collection job
SQL> exec dbms_stats.gather_table_stats(null, 't_fetch_first_rows')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.29
SQL>
SQL> select num_rows, blocks
2 from user_tab_statistics
3 where table_name = 'T_FETCH_FIRST_ROWS';

NUM_ROWS BLOCKS
---------- ----------
0 0

Elapsed: 00:00:00.01
SQL>
SQL> select blevel, leaf_blocks, distinct_keys, num_rows, clustering_factor
2 from user_ind_statistics
3 where index_name = 'IDX_FETCH_FIRST_ROWS';

BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS CLUSTERING_FACTOR
---------- ----------- ------------- ---------- -----------------
0 0 0 0 0

Elapsed: 00:00:00.03
SQL>
SQL> -- Load the data, the object type is deliberately skewed
SQL> -- Most of the values are "VIEW"
SQL> insert /*+ append */ into t_fetch_first_rows (
2 id,
3 name,
4 type,
5 measure)
6 select object_id, object_name, object_type, object_id as measure
7 from all_objects, (select level as id from dual connect by level <= 1000) dup
8 where object_type in ('VIEW', 'SCHEDULE')
9 and rownum <= 1500000;

1500000 rows created.

Elapsed: 00:00:39.36
SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL>
SQL> column segment_name format a20
SQL>
SQL> select segment_name, bytes, blocks, extents
2 from user_segments
3 where segment_name = 'T_FETCH_FIRST_ROWS'
4 or segment_name = 'IDX_FETCH_FIRST_ROWS';

SEGMENT_NAME BYTES BLOCKS EXTENTS
-------------------- ---------- ---------- ----------
T_FETCH_FIRST_ROWS 62914560 7680 75
IDX_FETCH_FIRST_ROWS 31457280 3840 45

Elapsed: 00:00:00.31
SQL>
SQL> select type, count(*) from t_fetch_first_rows
2 group by type
3 order by 2 desc;

TYPE COUNT(*)
------------------------------ ----------
VIEW 1499497
SCHEDULE 503

Elapsed: 00:00:01.37
SQL>


The "empty" staging table has been analyzed and afterwards 1,500,000 rows have been loaded which represent approx. 60MB of data, supported by a 30MB index. The table consists of 7,680 blocks (8KB default block size), the index has 3,840 blocks.

Note that the data is skewed, most of entries have a object type of "VIEW", so filtering for this value will return almost all of the rows from the table.


SQL> explain plan for
2 select * from t_fetch_first_rows
3 where type = 'VIEW'
4 order by id;

Explained.

Elapsed: 00:00:00.01
SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3754482025

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 60 | 0 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_FETCH_FIRST_ROWS | 1 | 60 | 0 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_FETCH_FIRST_ROWS | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

2 - access("TYPE"='VIEW')
filter("TYPE"='VIEW')

15 rows selected.

Elapsed: 00:00:00.03
SQL>
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:18.95
SQL>
SQL> set autotrace traceonly
SQL>
SQL> -- Force to fetch all rows
SQL> -- Note that COUNT(*) itself is not sufficient
SQL> -- because the optimizer is smart and doesn't visit the table
SQL> -- in this case which means that the test case would be no longer
SQL> -- representative
SQL> select count(*) from (
2 select * from t_fetch_first_rows
3 where type = 'VIEW'
4 order by id
5 );

Elapsed: 00:00:05.92

Execution Plan
----------------------------------------------------------
Plan hash value: 3912594726

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | INDEX RANGE SCAN| IDX_FETCH_FIRST_ROWS | 1 | 17 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

2 - access("TYPE"='VIEW')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3758 consistent gets
3758 physical reads
0 redo size
414 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> set autotrace off
SQL>


Running above query including a filter predicate and an ORDER BY clause will return almost immediately, suggesting good response time.

And by simply wrapping the query in a "SELECT COUNT(*) FROM (...)" block it seems the result confirms that the query performs good: Only 3,758 blocks read (by the way, if you check the execution plan, it's an index only execution and not visiting the table at all - the blocks read basically represent all blocks from the index) in five seconds. So obviously there is nothing wrong...


SQL>
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.01
SQL> -- alter session set tracefile_identifier = 'fetch_first_rows1';
SQL>
SQL> -- exec dbms_monitor.session_trace_enable
SQL>
SQL> set autotrace traceonly
SQL>
SQL> -- Force to fetch all rows
SQL> -- Note that COUNT(*) itself is not sufficient
SQL> -- because the optimizer is smart and doesn't visit the table
SQL> -- in this case which means that the test case would be no longer
SQL> -- representative
SQL> select sum(measure), count(*) from (
2 select * from t_fetch_first_rows
3 where type = 'VIEW'
4 order by id
5 );

Elapsed: 00:01:04.31

Execution Plan
----------------------------------------------------------
Plan hash value: 1903859112

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T_FETCH_FIRST_ROWS | 1 | 30 | 0 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_FETCH_FIRST_ROWS | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

3 - access("TYPE"='VIEW')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1503256 consistent gets
11236 physical reads
72 redo size
483 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> set autotrace off
SQL>
SQL> -- exec dbms_monitor.session_trace_disable
SQL>


But look what happens if I force to process all rows in a realistic manner: It takes roughly a minute and more significantly approx. 1,500,000 consistent block gets to process a table that consists of 7,680 blocks ! That's a logical I/O amount representing roughly 12GB.

And if you check the AUTOTRACE statistics you'll notice that I was actually lucky: I only read 11,236 blocks physically, the vast majority of the blocks came from the buffer cache after all blocks of the table have been read into it. In a real production system where the segments might be much larger and potentially multiple of such statements might compete for the buffer cache simultaneously a lot more buffer gets might lead to real physical I/O making things even worse.

The impressive amount of logical I/O is caused by the fact that using this execution plan the database scanned the index leaf blocks for the rows according to the filter criteria and accessed the corresponding table in a random fashion.

But the smallest entity that the database knows in terms of I/O is a block, so in order to read a single random row the whole block where the row resides in needs to be read. Due to the bad clustering factor of the index, which means that the table rows are differently ordered than the index, for each row randomly accessed a different block of the table had to be read. So on average each block of the table was accessed almost 200 times during the process!

If the table and the index were ordered in the same way (represented by a low clustering factor of the index close to number of blocks of the table, whereas a bad clustering factor is close to the number of rows of the table) then the amount of logical I/O required could be significantly less, since the rows accessed randomly reside in the same block and therefore the same table block can be re-used to process the row.

Now I'm going to show that having representative index statistics is as important as having the tables properly analyzed:


SQL> -- Relevance of index stats: Gather proper table statistics but without indexes
SQL> exec dbms_stats.gather_table_stats(null, 't_fetch_first_rows', cascade => false, no_invalidate=>false)

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.40
SQL>
SQL> select num_rows, blocks
2 from user_tab_statistics
3 where table_name = 'T_FETCH_FIRST_ROWS';

NUM_ROWS BLOCKS
---------- ----------
1479394 7606

Elapsed: 00:00:00.03
SQL>
SQL> select blevel, leaf_blocks, distinct_keys, num_rows, clustering_factor
2 from user_ind_statistics
3 where index_name = 'IDX_FETCH_FIRST_ROWS';

BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS CLUSTERING_FACTOR
---------- ----------- ------------- ---------- -----------------
0 0 0 0 0

Elapsed: 00:00:00.04
SQL>
SQL> explain plan for
2 select * from t_fetch_first_rows
3 where type = 'VIEW'
4 order by id;

Explained.

Elapsed: 00:00:00.00
SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3754482025

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1478K| 42M| 0 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_FETCH_FIRST_ROWS | 1478K| 42M| 0 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_FETCH_FIRST_ROWS | 1478K| | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

2 - access("TYPE"='VIEW')
filter("TYPE"='VIEW')

15 rows selected.

Elapsed: 00:00:00.03
SQL>
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.18
SQL>
SQL> -- alter session set tracefile_identifier = 'fetch_first_rows2';
SQL>
SQL> -- exec dbms_monitor.session_trace_enable
SQL>
SQL> set autotrace traceonly
SQL>
SQL> -- Force to fetch all rows
SQL> -- Note that COUNT(*) itself is not sufficient
SQL> -- because the optimizer is smart and doesn't visit the table
SQL> -- in this case which means that the test case would be no longer
SQL> -- representative
SQL> select sum(measure), count(*) from (
2 select * from t_fetch_first_rows
3 where type = 'VIEW'
4 order by id
5 );

Elapsed: 00:01:03.90

Execution Plan
----------------------------------------------------------
Plan hash value: 1903859112

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T_FETCH_FIRST_ROWS | 1478K| 15M| 0 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_FETCH_FIRST_ROWS | 1478K| | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

3 - access("TYPE"='VIEW')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1503255 consistent gets
11236 physical reads
0 redo size
483 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> set autotrace off
SQL>
SQL> -- exec dbms_monitor.session_trace_disable
SQL>


Although I got now proper table statistics, the optimizer is still fooled by the incorrect index statistics and again chooses the inefficient index access.

Gathering proper statistics on both table and index leads to a better decision of the optimizer:


SQL> -- Now gather proper statistics
SQL> exec dbms_stats.gather_table_stats(null, 't_fetch_first_rows', cascade => true, no_invalidate=>false)

PL/SQL procedure successfully completed.

Elapsed: 00:00:13.40
SQL>
SQL> select num_rows, blocks
2 from user_tab_statistics
3 where table_name = 'T_FETCH_FIRST_ROWS';

NUM_ROWS BLOCKS
---------- ----------
1504130 7606

Elapsed: 00:00:00.01
SQL>
SQL> select blevel, leaf_blocks, distinct_keys, num_rows, clustering_factor
2 from user_ind_statistics
3 where index_name = 'IDX_FETCH_FIRST_ROWS';

BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS CLUSTERING_FACTOR
---------- ----------- ------------- ---------- -----------------
2 3757 2984 1500000 1500000

Elapsed: 00:00:00.03
SQL>
SQL> explain plan for
2 select * from t_fetch_first_rows
3 where type = 'VIEW'
4 order by id;

Explained.

Elapsed: 00:00:00.01
SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2749563054

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1503K| 43M| | 14713 (4)| 00:02:57 |
| 1 | SORT ORDER BY | | 1503K| 43M| 127M| 14713 (4)| 00:02:57 |
|* 2 | TABLE ACCESS FULL| T_FETCH_FIRST_ROWS | 1503K| 43M| | 2159 (5)| 00:00:26 |
-------------------------------------------------------------------------------------------------

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

2 - filter("TYPE"='VIEW')

14 rows selected.

Elapsed: 00:00:00.01
SQL>
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.26
SQL>
SQL> -- alter session set tracefile_identifier = 'fetch_first_rows3';
SQL>
SQL> -- exec dbms_monitor.session_trace_enable
SQL>
SQL> set autotrace traceonly
SQL>
SQL> -- Force to fetch all rows
SQL> -- Note that COUNT(*) itself is not sufficient
SQL> -- because the optimizer is smart and doesn't visit the table
SQL> -- in this case which means that the test case would be no longer
SQL> -- representative
SQL> select sum(measure), count(*) from (
2 select * from t_fetch_first_rows
3 where type = 'VIEW'
4 order by id
5 );

Elapsed: 00:00:02.98

Execution Plan
----------------------------------------------------------
Plan hash value: 2125410158

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 2159 (5)| 00:00:26 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| T_FETCH_FIRST_ROWS | 1503K| 15M| 2159 (5)| 00:00:26 |
-----------------------------------------------------------------------------------------

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

2 - filter("TYPE"='VIEW')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7488 consistent gets
7479 physical reads
0 redo size
483 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> set autotrace off
SQL>
SQL> -- exec dbms_monitor.session_trace_disable
SQL>


A simple full table scan took only three seconds, and processed 7,488 blocks, representing simply the net size of the table. What a vast difference compared to the 12GB processed via the index.

Note that when I repeat the statement using the index access without clearing the buffer cache its actual run time will drop in my test environment from one minute to less than 10 seconds, but that's not really good news, since it's usually quite unrealistic to have all blocks in the buffer cache.

To underline that this inefficient statement represents a real scaling inhibitor, I'll use Tom Kyte's RUNSTATS package to demonstrate the different resource consumption of the two statements:


SQL> exec runstats_pkg.rs_start

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL>
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.03
SQL>
SQL> -- Show the different resource consumption
SQL> select sum(measure), count(*) from (
2 select /*+ index(t_fetch_first_rows) */ * from t_fetch_first_rows
3 where type = 'VIEW'
4 order by id
5 );

SUM(MEASURE) COUNT(*)
------------ ----------
6876814594 1499497

Elapsed: 00:01:04.14
SQL>
SQL> exec runstats_pkg.rs_middle

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
SQL>
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.04
SQL>
SQL> select sum(measure), count(*) from (
2 select * from t_fetch_first_rows
3 where type = 'VIEW'
4 order by id
5 );

SUM(MEASURE) COUNT(*)
------------ ----------
6876814594 1499497

Elapsed: 00:00:02.43
SQL>
SQL> -- Show only values different by 150 least
SQL> exec runstats_pkg.rs_stop(150)
Run1 ran in 6417 hsecs
Run2 ran in 250 hsecs
run 1 ran in 2566,8% of the time

Name Run1 Run2 Diff
STAT...redo size 2,580 2,856 276
LATCH.channel operations paren 310 22 -288
LATCH.checkpoint queue latch 414 27 -387
LATCH.messages 442 41 -401
STAT...bytes sent via SQL*Net 1,960 1,501 -459
LATCH.JS queue state obj latch 468 0 -468
STAT...CPU used by this sessio 836 61 -775
STAT...CPU used when call star 836 61 -775
LATCH.enqueues 1,047 8 -1,039
LATCH.enqueue hash chains 1,059 8 -1,051
LATCH.SQL memory manager worka 1,480 73 -1,407
STAT...physical reads 11,240 7,486 -3,754
STAT...physical reads cache 11,240 7,486 -3,754
STAT...free buffer requested 11,245 7,491 -3,754
LATCH.cache buffers lru chain 11,329 7,540 -3,789
STAT...user I/O wait time 5,612 196 -5,416
STAT...DB time 6,418 254 -6,164
STAT...Elapsed Time 6,422 255 -6,167
STAT...physical reads cache pr 0 7,403 7,403
STAT...table scan blocks gotte 0 7,478 7,478
STAT...physical read IO reques 11,240 83 -11,157
STAT...physical read total IO 11,240 83 -11,157
LATCH.simulator lru latch 93,302 932 -92,370
LATCH.simulator hash latch 94,001 1,398 -92,603
STAT...session uga memory max 130,928 0 -130,928
STAT...session pga memory -213,636 0 213,636
STAT...Cached Commit SCN refer 1,499,291 7,477 -1,491,814
STAT...consistent gets from ca 1,503,273 7,504 -1,495,769
STAT...consistent gets 1,503,273 7,504 -1,495,769
STAT...no work - consistent re 1,503,252 7,478 -1,495,774
STAT...session logical reads 1,503,310 7,533 -1,495,777
STAT...buffer is pinned count 1,499,496 0 -1,499,496
STAT...table fetch by rowid 1,499,501 3 -1,499,498
STAT...buffer is not pinned co 1,499,506 6 -1,499,500
STAT...table scan rows gotten 0 1,500,000 1,500,000
LATCH.cache buffers chains 3,019,149 23,679 -2,995,470
STAT...physical read bytes 92,078,080 61,325,312 -30,752,768
STAT...physical read total byt 92,078,080 61,325,312 -30,752,768

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
3,243,130 53,136 -3,189,994 6,103.45%

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL>


The most obvious threat can be seen from the LATCH statistics: Latches are Oracle internal serialization devices that are used to protect resources from being modified by multiple processes at the same time. Latches therefore serialize access to certain memory structures that e.g. are used to maintain the buffers in the buffer cache. The more latch activity, the more CPU is used and the less scalable is a system, since the concurrent processes will have to wait for each other at the serialization points.

So the inefficient statement reported more than 3,000,000 latches, whereas the simple table scan required only approx. 50,000. So even if you not too concerned with the different run times in this single user test case, running such statements simultaneously in a real multi-user system will significantly influence the performance of the whole system.

Saturday, January 24, 2009

Correlation, nocorrelation and extended stats - amendments

Riyaj Shamsudeen recently published a very interesting blog post about the 11g extended statistics feature and correlated column values.

Because I think that some of his findings were significantly influenced by the fact that he generated frequency histograms on all columns, I've repeated his test case with some more variations, in particular with and without histograms and in case of histograms, with and without the new feature "Bug 5483301: Cardinality of 1 when predicate value non-existent in frequency histogram" which was introduced in 10.2.0.4 and 11.1.0.6. More details about this change can be found in Metalink Doc ID 5483301.8.

The following results were obtained using 11.1.0.7 Enterprise Edition on Windows 32bit.

I'll start with his case 1:


create table y1 (a number, b number, c number);

begin
for i in 1..1000 loop
for j in 1..10 loop
insert into y1 values (j,mod(j,5), mod(j,2) );
end loop;
end loop;
end;
/

commit;

REM Distribution of these column values given below.
select a, b, count(*) from y1 group by a,b order by a,b
/
A B COUNT(*)
---------- ---------- ----------
1 1 1000
2 2 1000
3 3 1000
4 4 1000
5 0 1000
6 1 1000
7 2 1000
8 3 1000
9 4 1000
10 0 1000

10 rows selected.

REM Let's also add an index to this table
create index y1_i1 on y1 (a, b);

REM The number of distinct keys is used
REM to determine selectivity if
REM a) an all-equal operation on the entire index is used
REM and obviously
REM b) NO histogram is present
select distinct_keys from user_indexes where index_name = 'Y1_I1';

DISTINCT_KEYS
-------------
10

begin
dbms_stats.gather_table_stats (
ownname =>null,
tabname=>'y1',
estimate_percent=>null,
cascade=>true,
method_opt =>'for all columns size 254');
end;
/

REM this shows that although the index is in place
REM the DISTINCT_KEYS information is ignored
REM but the histogram is used instead
REM and we fall back to the default selectivity formula
REM see at the end of the script for a demonstration
REM without index which comes to the same result
alter session set tracefile_identifier = 'correlated1';

alter session set events '10053 trace name context forever, level 1';

explain plan for select c from y1 where a=1 and b=1;

alter session set events '10053 trace name context off';


The corresponding 10053 trace file excerpt shows:


SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for Y1[Y1]
Column (#1):
NewDensity:0.050000, OldDensity:0.000050 BktCnt:10000, PopBktCnt:10000, PopValCnt:10, NDV:10
Column (#2):
NewDensity:0.100000, OldDensity:0.000050 BktCnt:10000, PopBktCnt:10000, PopValCnt:5, NDV:5
ColGroup (#1, Index) Y1_I1
Col#: 1 2 CorStregth: 5.00
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
Table: Y1 Alias: Y1
Card: Original: 10000.000000 Rounded: 200 Computed: 200.00 Non Adjusted: 200.00
Access Path: TableScan
Cost: 12.96 Resp: 12.96 Degree: 0
Cost_io: 12.00 Cost_cpu: 2396429
Resp_io: 12.00 Resp_cpu: 2396429
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
Access Path: index (AllEqRange)
Index: Y1_I1
resc_io: 5.00 resc_cpu: 114847
ix_sel: 0.020000 ix_sel_with_filters: 0.020000
Cost: 5.05 Resp: 5.05 Degree: 1
Best:: AccessPath: IndexRange
Index: Y1_I1
Cost: 5.05 Degree: 1 Resp: 5.05 Card: 200.00 Bytes: 0


In contrast to Riyah's interpretation I would say that it shows that the index information is not used to derive the selectivity ("Matches Full:"). Rather it looks like the presence of the frequency histogram causes a fall back to the default formula used for non-correlated columns: selectivity(A) * selectivity(B) which leads to the selectivity of 0.02 and hence cardinality of 200.

Now repeating the same without any histograms:


begin
dbms_stats.delete_table_stats (
ownname =>null,
tabname=>'y1',
cascade_indexes=>true);
end;
/

begin
dbms_stats.gather_table_stats (
ownname =>null,
tabname=>'y1',
estimate_percent=>null,
cascade=>true,
method_opt =>'for all columns size 1');
end;
/

REM without histograms
REM the all-equal on entire index rule is used
REM hence the selectivity is 0.1 => 1/DISTINCT_KEYS of index
alter session set tracefile_identifier = 'correlated2';

alter session set events '10053 trace name context forever, level 1';

explain plan for select c from y1 where a=1 and b=1;

alter session set events '10053 trace name context off';


The corresponding 10053 trace file excerpt:


SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for Y1[Y1]
ColGroup (#1, Index) Y1_I1
Col#: 1 2 CorStregth: 5.00
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.1000
Table: Y1 Alias: Y1
Card: Original: 10000.000000 Rounded: 1000 Computed: 1000.00 Non Adjusted: 1000.00
Access Path: TableScan
Cost: 12.96 Resp: 12.96 Degree: 0
Cost_io: 12.00 Cost_cpu: 2412429
Resp_io: 12.00 Resp_cpu: 2412429
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.1000
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.1000
Access Path: index (AllEqRange)
Index: Y1_I1
resc_io: 22.00 resc_cpu: 547522
ix_sel: 0.100000 ix_sel_with_filters: 0.100000
Cost: 22.22 Resp: 22.22 Degree: 1
Best:: AccessPath: TableScan
Cost: 12.96 Degree: 1 Resp: 12.96 Card: 1000.00 Bytes: 0


Now it can be seen that the index information actually gets used ("Matches Full: #1 Partial: Sel: 0.1000") and we come to the correct cardinality estimate.

Unfortunately this approach has some shortcomings, as can be seen when using out-of-range attributes:


REM unfortunately this approach
REM doesn't cope very well with "out-of-range" predicates
REM although the 10053 trace shows a detection of the "out-of-range"
REM attribute, the final index selectivity is left unchanged
REM => Room for improvement?
alter session set tracefile_identifier = 'correlated3';

alter session set events '10053 trace name context forever, level 1';

explain plan for select c from y1 where a=6 and b=6;

alter session set events '10053 trace name context off';

REM switching off the "new frequency histogram unpopular value"
REM bug fix doesn't change anything here obviously
REM as we don't have a histogram in place
alter session set "_fix_control"='5483301:off';

alter session set tracefile_identifier = 'correlated4';

alter session set events '10053 trace name context forever, level 1';

explain plan for select c from y1 where a=6 and b=6;

alter session set events '10053 trace name context off';


The corresponding 10053 trace file excerpt:


SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for Y1[Y1]
ColGroup (#1, Index) Y1_I1
Col#: 1 2 CorStregth: 5.00
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.1000
Using prorated density: 0.100000 of col #2 as selectvity of out-of-range/non-existent value pred
Table: Y1 Alias: Y1
Card: Original: 10000.000000 Rounded: 1000 Computed: 1000.00 Non Adjusted: 1000.00
Access Path: TableScan
Cost: 12.96 Resp: 12.96 Degree: 0
Cost_io: 12.00 Cost_cpu: 2412429
Resp_io: 12.00 Resp_cpu: 2412429
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.1000
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.1000
Using prorated density: 0.100000 of col #2 as selectvity of out-of-range/non-existent value pred
Access Path: index (AllEqRange)
Index: Y1_I1
resc_io: 22.00 resc_cpu: 547522
ix_sel: 0.100000 ix_sel_with_filters: 0.100000
Cost: 22.22 Resp: 22.22 Degree: 1
Best:: AccessPath: TableScan
Cost: 12.96 Degree: 1 Resp: 12.96 Card: 1000.00 Bytes: 0


Although there is a "out-of-range" detection, the final selectivity is left unchanged, which looks a bit odd, and the default formula that adjusts the selectivity based on the distance from the known low/high values doesn't apply either. Switching off above mentioned "bug fix" doesn't change this behaviour which seems to be consistent since we don't have a histogram in place.

Now again adding histograms and checking "out-of-range" predicates:


begin
dbms_stats.delete_table_stats (
ownname =>null,
tabname=>'y1',
cascade_indexes=>true);
end;
/

begin
dbms_stats.gather_table_stats (
ownname =>null,
tabname=>'y1',
estimate_percent=>null,
cascade=>true,
method_opt =>'for all columns size 254');
end;
/

REM this corresponds to having histogram
REM but no index in place, since the histograms
REM take obviously precedence
REM not sure how we arrive at the cardinality of 50
REM it looks like the NewDensity is 0.5 / NUM_DISTINCT
REM and gets divided by 2 according to the formula
REM for out-of-range predicates (see case 14 below)
alter session set "_fix_control"='5483301:on';

alter session set tracefile_identifier = 'correlated5';

alter session set events '10053 trace name context forever, level 1';

explain plan for select c from y1 where a=6 and b=6;

alter session set events '10053 trace name context off';


The corresponding 10053 trace file excerpt:


SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for Y1[Y1]
Column (#1):
NewDensity:0.050000, OldDensity:0.000050 BktCnt:10000, PopBktCnt:10000, PopValCnt:10, NDV:10
Column (#2):
NewDensity:0.100000, OldDensity:0.000050 BktCnt:10000, PopBktCnt:10000, PopValCnt:5, NDV:5
ColGroup (#1, Index) Y1_I1
Col#: 1 2 CorStregth: 5.00
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
Using prorated density: 0.050000 of col #2 as selectvity of out-of-range/non-existent value pred
Table: Y1 Alias: Y1
Card: Original: 10000.000000 Rounded: 50 Computed: 50.00 Non Adjusted: 50.00
Access Path: TableScan
Cost: 12.95 Resp: 12.95 Degree: 0
Cost_io: 12.00 Cost_cpu: 2368429
Resp_io: 12.00 Resp_cpu: 2368429
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
Using prorated density: 0.050000 of col #2 as selectvity of out-of-range/non-existent value pred
Access Path: index (AllEqRange)
Index: Y1_I1
resc_io: 2.00 resc_cpu: 34983
ix_sel: 0.005000 ix_sel_with_filters: 0.005000
Cost: 2.01 Resp: 2.01 Degree: 1
Best:: AccessPath: IndexRange
Index: Y1_I1
Cost: 2.01 Degree: 1 Resp: 2.01 Card: 50.00 Bytes: 0


I'm not sure about how the optimizer arrives at the selectivity of 0.005 resp. cardinality of 50 in this particular case.

Switching off the new behaviour regarding non-existing values in frequency histograms outlined above shows that we get a cardinality of 1 in this case:


REM switch to the old behaviour
REM interestingly this influences the "NewDensity"
REM calculation and sets it effectively to the "OldDensity"
REM Now we get a cardinality of 1
REM when querying for a non-existent value
alter session set "_fix_control"='5483301:off';

alter session set tracefile_identifier = 'correlated6';

alter session set events '10053 trace name context forever, level 1';

explain plan for select c from y1 where a=6 and b=6;

alter session set events '10053 trace name context off';


The corresponding 10053 trace file excerpt:


SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for Y1[Y1]
Column (#1):
NewDensity:0.000050, OldDensity:0.000050 BktCnt:10000, PopBktCnt:10000, PopValCnt:10, NDV:10
Column (#2):
NewDensity:0.000050, OldDensity:0.000050 BktCnt:10000, PopBktCnt:10000, PopValCnt:5, NDV:5
ColGroup (#1, Index) Y1_I1
Col#: 1 2 CorStregth: 5.00
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
Table: Y1 Alias: Y1
Card: Original: 10000.000000 Rounded: 1 Computed: 0.05 Non Adjusted: 0.05
Access Path: TableScan
Cost: 12.94 Resp: 12.94 Degree: 0
Cost_io: 12.00 Cost_cpu: 2342474
Resp_io: 12.00 Resp_cpu: 2342474
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
Access Path: index (AllEqRange)
Index: Y1_I1
resc_io: 2.00 resc_cpu: 15483
ix_sel: 0.000005 ix_sel_with_filters: 0.000005
Cost: 2.01 Resp: 2.01 Degree: 1
Best:: AccessPath: IndexRange
Index: Y1_I1
Cost: 2.01 Degree: 1 Resp: 2.01 Card: 0.05 Bytes: 0


Now we are going to introduce the extended statistics available in 11g, and generate a frequency histogram on the new virtual column (which is unnecessary since we have uniform distribution).


REM Adding extended statistics to educate CBO about correlation between columns a and b.

SELECT
dbms_stats.create_extended_stats(
ownname=>user,
tabname => 'Y1',
extension => '(a, b)' ) AS a_b_correlation
FROM dual;

REM Extended stats depicted as above, adds a virtual column to the table with an internal function call.
REM Collecting stats on the table with histograms.

begin
dbms_stats.delete_table_stats (
ownname =>null,
tabname=>'y1',
cascade_indexes=>true);
end;
/

begin
dbms_stats.gather_table_stats (
ownname =>null,
tabname=>'y1',
estimate_percent=>null,
cascade=>true,
method_opt =>'for all columns size 254');
end;
/

REM using the histogram on the newly
REM generated virtual column
REM gets us the correct cardinality
alter session set "_fix_control"='5483301:on';

alter session set tracefile_identifier = 'correlated7';

alter session set events '10053 trace name context forever, level 1';

explain plan for select c from y1 where a=1 and b=1;

alter session set events '10053 trace name context off';

REM no change with old "out-of-range" behaviour
alter session set "_fix_control"='5483301:off';

alter session set tracefile_identifier = 'correlated8';

alter session set events '10053 trace name context forever, level 1';

explain plan for select c from y1 where a=1 and b=1;

alter session set events '10053 trace name context off';


The corresponding 10053 trace file excerpt:


SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for Y1[Y1]
Column (#1):
NewDensity:0.050000, OldDensity:0.000050 BktCnt:10000, PopBktCnt:10000, PopValCnt:10, NDV:10
Column (#2):
NewDensity:0.100000, OldDensity:0.000050 BktCnt:10000, PopBktCnt:10000, PopValCnt:5, NDV:5
Column (#4):
NewDensity:0.050000, OldDensity:0.000050 BktCnt:10000, PopBktCnt:10000, PopValCnt:10, NDV:10
ColGroup (#1, VC) SYS_STUNA$6DVXJXTP05EH56DTIR0X
Col#: 1 2 CorStregth: 5.00
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.1000
Table: Y1 Alias: Y1
Card: Original: 10000.000000 Rounded: 1000 Computed: 1000.00 Non Adjusted: 1000.00
Access Path: TableScan
Cost: 12.96 Resp: 12.96 Degree: 0
Cost_io: 12.00 Cost_cpu: 2412429
Resp_io: 12.00 Resp_cpu: 2412429
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.1000
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.1000
Access Path: index (AllEqRange)
Index: Y1_I1
resc_io: 22.00 resc_cpu: 547522
ix_sel: 0.100000 ix_sel_with_filters: 0.100000
Cost: 22.22 Resp: 22.22 Degree: 1
Best:: AccessPath: TableScan
Cost: 12.96 Degree: 1 Resp: 12.96 Card: 1000.00 Bytes: 0


The column group gets used and the correct cardinality is estimated.

The "bug fix" switch for the non-existent values in frequency histograms doesn't change the result - which is consistent since we're looking for a existent value.

But since we have a frequency histogram in place, once we start to query for non-existent values, the bug fix becomes relevant:


REM new rule introduced with 10.2.0.4
REM and 11.1.0.6, see bug 5483301 resp.
REM Metalink Note 5483301.8:
REM Instead of reporting a cardinality of 1
REM for a non-existent value in a frequency
REM histogram, the cardinality will be
REM 0.5 times the cardinality of the least
REM popular value in the histogram
REM => hence 1,000 * 0.5 in our case => 500

alter session set "_fix_control"='5483301:on';

alter session set tracefile_identifier = 'correlated9';

alter session set events '10053 trace name context forever, level 1';

explain plan for select c from y1 where a=6 and b=6;

alter session set events '10053 trace name context off';


The corresponding 10053 trace file excerpt:


SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for Y1[Y1]
Column (#1):
NewDensity:0.050000, OldDensity:0.000050 BktCnt:10000, PopBktCnt:10000, PopValCnt:10, NDV:10
Column (#2):
NewDensity:0.100000, OldDensity:0.000050 BktCnt:10000, PopBktCnt:10000, PopValCnt:5, NDV:5
Column (#4):
NewDensity:0.050000, OldDensity:0.000050 BktCnt:10000, PopBktCnt:10000, PopValCnt:10, NDV:10
ColGroup (#1, VC) SYS_STUNA$6DVXJXTP05EH56DTIR0X
Col#: 1 2 CorStregth: 5.00
ColGroup Usage:: PredCnt: 2 Matches Full: Using prorated density: 0.049909 of col #4 as selectvity of out-of-range/non-existent value pred
#1 Partial: Sel: 0.0499
Using prorated density: 0.050000 of col #2 as selectvity of out-of-range/non-existent value pred
Table: Y1 Alias: Y1
Card: Original: 10000.000000 Rounded: 499 Computed: 499.09 Non Adjusted: 499.09
Access Path: TableScan
Cost: 12.95 Resp: 12.95 Degree: 0
Cost_io: 12.00 Cost_cpu: 2377409
Resp_io: 12.00 Resp_cpu: 2377409
ColGroup Usage:: PredCnt: 2 Matches Full: Using prorated density: 0.049909 of col #4 as selectvity of out-of-range/non-existent value pred
#1 Partial: Sel: 0.0499
ColGroup Usage:: PredCnt: 2 Matches Full: Using prorated density: 0.049909 of col #4 as selectvity of out-of-range/non-existent value pred
#1 Partial: Sel: 0.0499
Using prorated density: 0.050000 of col #2 as selectvity of out-of-range/non-existent value pred
Access Path: index (AllEqRange)
Index: Y1_I1
resc_io: 12.00 resc_cpu: 281307
ix_sel: 0.049909 ix_sel_with_filters: 0.049909
Cost: 12.11 Resp: 12.11 Degree: 1
Best:: AccessPath: IndexRange
Index: Y1_I1
Cost: 12.11 Degree: 1 Resp: 12.11 Card: 499.09 Bytes: 0


The new rule applies that returns us 0.5 times the cardinality of the least popular value in the histogram. Since we have uniform distribution, every popular value has a cardinality of 1,000, hence the adjusted cardinality according to the new formula is 500 (or lets say, close to 500).

Switching off the bug fix returns us to the old behaviour, which seems to be reasonable: If the value can not be found in the frequency histogram, a cardinality of 1 should be estimated:


REM switch to the old behaviour
REM interestingly this influences the "NewDensity"
REM calculation and sets it effectively to the "OldDensity"
REM Now we get a cardinality of 1
REM when querying for a non-existent value
alter session set "_fix_control"='5483301:off';

alter session set tracefile_identifier = 'correlated10';

alter session set events '10053 trace name context forever, level 1';

explain plan for select c from y1 where a=6 and b=6;

alter session set events '10053 trace name context off';


The corresponding 10053 trace file excerpt:


SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for Y1[Y1]
Column (#1):
NewDensity:0.000050, OldDensity:0.000050 BktCnt:10000, PopBktCnt:10000, PopValCnt:10, NDV:10
Column (#2):
NewDensity:0.000050, OldDensity:0.000050 BktCnt:10000, PopBktCnt:10000, PopValCnt:5, NDV:5
Column (#4):
NewDensity:0.000050, OldDensity:0.000050 BktCnt:10000, PopBktCnt:10000, PopValCnt:10, NDV:10
ColGroup (#1, VC) SYS_STUNA$6DVXJXTP05EH56DTIR0X
Col#: 1 2 CorStregth: 5.00
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.0001
Table: Y1 Alias: Y1
Card: Original: 10000.000000 Rounded: 1 Computed: 0.50 Non Adjusted: 0.50
Access Path: TableScan
Cost: 12.94 Resp: 12.94 Degree: 0
Cost_io: 12.00 Cost_cpu: 2342474
Resp_io: 12.00 Resp_cpu: 2342474
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.0001
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.0001
Access Path: index (AllEqRange)
Index: Y1_I1
resc_io: 2.00 resc_cpu: 15483
ix_sel: 0.000050 ix_sel_with_filters: 0.000050
Cost: 2.01 Resp: 2.01 Degree: 1
Best:: AccessPath: IndexRange
Index: Y1_I1
Cost: 2.01 Degree: 1 Resp: 2.01 Card: 0.50 Bytes: 0


As we can see in both cases the column group is used but they get different selectivities applied, with the bug fix switched off we come to the cardinality of 1.

If we use the extended statistic without a histogram, we get the same behaviour as we got when we were using the composite index without a histogram:


begin
dbms_stats.delete_table_stats (
ownname =>null,
tabname=>'y1',
cascade_indexes=>true);
end;
/

begin
dbms_stats.gather_table_stats (
ownname =>null,
tabname=>'y1',
estimate_percent=>null,
cascade=>true,
method_opt =>'for all columns size 1');
end;
/

REM extended statistics
REM without histogram
REM shows correct cardinality
REM since this is a uniform distribution
REM no histogram required
alter session set "_fix_control"='5483301:on';

alter session set tracefile_identifier = 'correlated11';

alter session set events '10053 trace name context forever, level 1';

explain plan for select c from y1 where a=1 and b=1;

alter session set events '10053 trace name context off';


The corresponding 10053 trace file excerpt:


SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for Y1[Y1]
ColGroup (#1, VC) SYS_STUNA$6DVXJXTP05EH56DTIR0X
Col#: 1 2 CorStregth: 5.00
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.1000
Table: Y1 Alias: Y1
Card: Original: 10000.000000 Rounded: 1000 Computed: 1000.00 Non Adjusted: 1000.00
Access Path: TableScan
Cost: 12.96 Resp: 12.96 Degree: 0
Cost_io: 12.00 Cost_cpu: 2412429
Resp_io: 12.00 Resp_cpu: 2412429
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.1000
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.1000
Access Path: index (AllEqRange)
Index: Y1_I1
resc_io: 22.00 resc_cpu: 547522
ix_sel: 0.100000 ix_sel_with_filters: 0.100000
Cost: 22.22 Resp: 22.22 Degree: 1
Best:: AccessPath: TableScan
Cost: 12.96 Degree: 1 Resp: 12.96 Card: 1000.00 Bytes: 0


So the cardinality estimate is correct, but see what happens again when using "out-of-range" predicates:


REM But same behaviour as case 3 (index without histogram):
REM Unfortunately this approach
REM doesn't cope very well with "out-of-range" predicates
REM although the 10053 trace shows a detection of the "out-of-range"
REM attribute, the final index selectivity is left unchanged
REM => Room for improvement?
alter session set tracefile_identifier = 'correlated12';

alter session set events '10053 trace name context forever, level 1';

explain plan for select c from y1 where a=6 and b=6;

alter session set events '10053 trace name context off';


The corresponding 10053 trace file excerpt:


SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for Y1[Y1]
ColGroup (#1, VC) SYS_STUNA$6DVXJXTP05EH56DTIR0X
Col#: 1 2 CorStregth: 5.00
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.1000
Using prorated density: 0.100000 of col #2 as selectvity of out-of-range/non-existent value pred
Table: Y1 Alias: Y1
Card: Original: 10000.000000 Rounded: 1000 Computed: 1000.00 Non Adjusted: 1000.00
Access Path: TableScan
Cost: 12.96 Resp: 12.96 Degree: 0
Cost_io: 12.00 Cost_cpu: 2412429
Resp_io: 12.00 Resp_cpu: 2412429
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.1000
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.1000
Using prorated density: 0.100000 of col #2 as selectvity of out-of-range/non-existent value pred
Access Path: index (AllEqRange)
Index: Y1_I1
resc_io: 22.00 resc_cpu: 547522
ix_sel: 0.100000 ix_sel_with_filters: 0.100000
Cost: 22.22 Resp: 22.22 Degree: 1
Best:: AccessPath: TableScan
Cost: 12.96 Degree: 1 Resp: 12.96 Card: 1000.00 Bytes: 0


Again the adjustment is without any consequence, similar to and consistent with the "index without histogram" case above.

Finally let's see what happens without index and without extended statistics:


begin dbms_stats.drop_extended_stats(
ownname=>user,
tabname => 'Y1',
extension => '(a, b)' );
end;
/

drop index y1_i1;

begin
dbms_stats.delete_table_stats (
ownname =>null,
tabname=>'y1',
cascade_indexes=>true);
end;
/

begin
dbms_stats.gather_table_stats (
ownname =>null,
tabname=>'y1',
estimate_percent=>null,
cascade=>true,
method_opt =>'for all columns size 1');
end;
/

REM without extended stats
REM and without index
REM and without histogram
REM the standard formula for non-correlated data
REM applies, which gives us a cardinality of 200
alter session set tracefile_identifier = 'correlated13';

alter session set events '10053 trace name context forever, level 1';

explain plan for select c from y1 where a=1 and b=1;

alter session set events '10053 trace name context off';


The corresponding 10053 trace file excerpt:


SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for Y1[Y1]

Table: Y1 Alias: Y1
Card: Original: 10000.000000 Rounded: 200 Computed: 200.00 Non Adjusted: 200.00
Access Path: TableScan
Cost: 12.96 Resp: 12.96 Degree: 0
Cost_io: 12.00 Cost_cpu: 2396429
Resp_io: 12.00 Resp_cpu: 2396429
Best:: AccessPath: TableScan
Cost: 12.96 Degree: 1 Resp: 12.96 Card: 200.00 Bytes: 0


The standard formula for uncorrelated column values applies which gives us a (wrong) cardinality of 200.

If we use "out-of-range" predicates, the default formula applies:


REM out-of-range predicates correspond to the standard formula
REM outside range / high-low value, for column b this means:
REM (6 - 4) / (4 - 0) => 0.5,
REM adjust the cardinality by 50% => 200 - (200 * 0.5) => 100
alter session set tracefile_identifier = 'correlated14';

alter session set events '10053 trace name context forever, level 1';

explain plan for select c from y1 where a=6 and b=6;

alter session set events '10053 trace name context off';


The corresponding 10053 trace file excerpt:


SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for Y1[Y1]

Using prorated density: 0.100000 of col #2 as selectvity of out-of-range/non-existent value pred
Table: Y1 Alias: Y1
Card: Original: 10000.000000 Rounded: 100 Computed: 100.00 Non Adjusted: 100.00
Access Path: TableScan
Cost: 12.96 Resp: 12.96 Degree: 0
Cost_io: 12.00 Cost_cpu: 2394429
Resp_io: 12.00 Resp_cpu: 2394429
Best:: AccessPath: TableScan
Cost: 12.96 Degree: 1 Resp: 12.96 Card: 100.00 Bytes: 0


If we are outside the range of high value - low value, then we get a cardinality of 0 resp. 1:


REM We'll get zero if are at least outside the range by range
alter session set tracefile_identifier = 'correlated15';

alter session set events '10053 trace name context forever, level 1';

explain plan for select c from y1 where a=100 and b=100;

alter session set events '10053 trace name context off';


The corresponding 10053 trace file excerpt:


SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for Y1[Y1]

Using prorated density: 0.000050 of col #1 as selectvity of out-of-range/non-existent value pred
Using prorated density: 0.000050 of col #2 as selectvity of out-of-range/non-existent value pred
Table: Y1 Alias: Y1
Card: Original: 10000.000000 Rounded: 1 Computed: 0.00 Non Adjusted: 0.00
Access Path: TableScan
Cost: 12.94 Resp: 12.94 Degree: 0
Cost_io: 12.00 Cost_cpu: 2342474
Resp_io: 12.00 Resp_cpu: 2342474
Best:: AccessPath: TableScan
Cost: 12.94 Degree: 1 Resp: 12.94 Card: 0.00 Bytes: 0


Finally we'll explore what happens if we have a histogram in place:


begin
dbms_stats.delete_table_stats (
ownname =>null,
tabname=>'y1',
cascade_indexes=>true);
end;
/

begin
dbms_stats.gather_table_stats (
ownname =>null,
tabname=>'y1',
estimate_percent=>null,
cascade=>true,
method_opt =>'for all columns size 254');
end;
/

REM finally generating histograms
REM but still without extended statistics
REM and without index
REM compare this to the case "index" + "histogram"
REM which was case 1
alter session set tracefile_identifier = 'correlated16';

alter session set events '10053 trace name context forever, level 1';

explain plan for select c from y1 where a=1 and b=1;

alter session set events '10053 trace name context off';


The corresponding 10053 trace file excerpt:


SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for Y1[Y1]
Column (#1):
NewDensity:0.050000, OldDensity:0.000050 BktCnt:10000, PopBktCnt:10000, PopValCnt:10, NDV:10
Column (#2):
NewDensity:0.100000, OldDensity:0.000050 BktCnt:10000, PopBktCnt:10000, PopValCnt:5, NDV:5

Table: Y1 Alias: Y1
Card: Original: 10000.000000 Rounded: 200 Computed: 200.00 Non Adjusted: 200.00
Access Path: TableScan
Cost: 12.96 Resp: 12.96 Degree: 0
Cost_io: 12.00 Cost_cpu: 2396429
Resp_io: 12.00 Resp_cpu: 2396429
Best:: AccessPath: TableScan
Cost: 12.96 Degree: 1 Resp: 12.96 Card: 200.00 Bytes: 0


Note that this case corresponds to the case "index with histogram" and shows that having the histogram in place effectively makes the index useless for selectivity/cardinality estimates. Same applies when querying for non-existent values:


REM corresponds to case 2
alter session set tracefile_identifier = 'correlated17';

alter session set events '10053 trace name context forever, level 1';

explain plan for select c from y1 where a=6 and b=6;

alter session set events '10053 trace name context off';

REM corresponds to case 15
alter session set tracefile_identifier = 'correlated18';

alter session set events '10053 trace name context forever, level 1';

explain plan for select c from y1 where a=100 and b=100;

alter session set events '10053 trace name context off';


The corresponding 10053 trace file excerpt:


SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for Y1[Y1]
Column (#1):
NewDensity:0.050000, OldDensity:0.000050 BktCnt:10000, PopBktCnt:10000, PopValCnt:10, NDV:10
Column (#2):
NewDensity:0.100000, OldDensity:0.000050 BktCnt:10000, PopBktCnt:10000, PopValCnt:5, NDV:5

Using prorated density: 0.050000 of col #2 as selectvity of out-of-range/non-existent value pred
Table: Y1 Alias: Y1
Card: Original: 10000.000000 Rounded: 50 Computed: 50.00 Non Adjusted: 50.00
Access Path: TableScan
Cost: 12.95 Resp: 12.95 Degree: 0
Cost_io: 12.00 Cost_cpu: 2368429
Resp_io: 12.00 Resp_cpu: 2368429
Best:: AccessPath: TableScan
Cost: 12.95 Degree: 1 Resp: 12.95 Card: 50.00 Bytes: 0


Finally we can see that the switching off the "bug fix" for non-existent values in frequency histograms shows us a cardinality of 1 if we query for a non-existent value:


REM with old behaviour for non-existent values
REM in frequency histograms we get a cardinality of 1
REM instead of 50
alter session set "_fix_control"='5483301:off';

alter session set tracefile_identifier = 'correlated19';

alter session set events '10053 trace name context forever, level 1';

explain plan for select c from y1 where a=6 and b=6;

alter session set events '10053 trace name context off';

alter session set "_fix_control"='5483301:on';


The corresponding 10053 trace file excerpt:


SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for Y1[Y1]
Column (#1):
NewDensity:0.000050, OldDensity:0.000050 BktCnt:10000, PopBktCnt:10000, PopValCnt:10, NDV:10
Column (#2):
NewDensity:0.000050, OldDensity:0.000050 BktCnt:10000, PopBktCnt:10000, PopValCnt:5, NDV:5

Table: Y1 Alias: Y1
Card: Original: 10000.000000 Rounded: 1 Computed: 0.05 Non Adjusted: 0.05
Access Path: TableScan
Cost: 12.94 Resp: 12.94 Degree: 0
Cost_io: 12.00 Cost_cpu: 2342474
Resp_io: 12.00 Resp_cpu: 2342474
Best:: AccessPath: TableScan
Cost: 12.94 Degree: 1 Resp: 12.94 Card: 0.05 Bytes: 0


I'm now going to simulate what is going to happen if one decides to merge indexes, e.g. two indexes on a,b and a,b,c will render the one on a,b redundant, or if it is decided to add column c to the existing index since some queries might benefit from this additional column:


create index y1_i2 on y1 (a, b, c);

REM Distribution of these column values given below.
select a, b, c, count(*) from y1 group by a,b,c order by a,b,c
/
A B C COUNT(*)
---------- ---------- ---------- ----------
1 1 1 1000
2 2 0 1000
3 3 1 1000
4 4 0 1000
5 0 1 1000
6 1 0 1000
7 2 1 1000
8 3 0 1000
9 4 1 1000
10 0 0 1000

10 rows selected.

begin
dbms_stats.delete_table_stats (
ownname =>null,
tabname=>'y1',
cascade_indexes=>true);
end;
/

begin
dbms_stats.gather_table_stats (
ownname =>null,
tabname=>'y1',
estimate_percent=>null,
cascade=>true,
method_opt =>'for all columns size 1');
end;
/

alter session set tracefile_identifier = 'correlated21';

alter session set events '10053 trace name context forever, level 1';

explain plan for select c from y1 where a=1 and b=1;

alter session set events '10053 trace name context off';


The corresponding 10053 trace file excerpt:


SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for Y1[Y1]
ColGroup (#1, Index) Y1_I2
Col#: 1 2 3 CorStregth: 10.00
ColGroup Usage:: PredCnt: 2 Matches Full: Partial: #1 (1 2 ) Sel: 0.1000
Table: Y1 Alias: Y1
Card: Original: 10000.000000 Rounded: 1000 Computed: 1000.00 Non Adjusted: 1000.00
Access Path: TableScan
Cost: 12.96 Resp: 12.96 Degree: 0
Cost_io: 12.00 Cost_cpu: 2412429
Resp_io: 12.00 Resp_cpu: 2412429
Access Path: index (index (FFS))
Index: Y1_I2
resc_io: 23.00 resc_cpu: 2056222
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 23.82 Resp: 23.82 Degree: 1
Cost_io: 23.00 Cost_cpu: 2056222
Resp_io: 23.00 Resp_cpu: 2056222
ColGroup Usage:: PredCnt: 2 Matches Full: Partial: #1 (1 2 ) Sel: 0.1000
ColGroup Usage:: PredCnt: 2 Matches Full: Partial: #1 (1 2 ) Sel: 0.1000
Access Path: index (IndexOnly)
Index: Y1_I2
resc_io: 6.00 resc_cpu: 242729
ix_sel: 0.100000 ix_sel_with_filters: 0.100000
Cost: 6.10 Resp: 6.10 Degree: 1
Best:: AccessPath: IndexRange
Index: Y1_I2
Cost: 6.10 Degree: 1 Resp: 6.10 Card: 1000.00 Bytes: 0


It's interesting to note that the optimizer using a higher "CorStregth" value of 10 and decides on the partial match ("Partial: #1 (1 2 )") to use the information from the index, although we don't use the entire index.

For the remaining test cases with out-of-range predicates we get consistent behaviour:


alter session set tracefile_identifier = 'correlated22';

alter session set events '10053 trace name context forever, level 1';

explain plan for select c from y1 where a=6 and b=6;

alter session set events '10053 trace name context off';


The corresponding 10053 trace file excerpt:


SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for Y1[Y1]
ColGroup (#1, Index) Y1_I2
Col#: 1 2 3 CorStregth: 10.00
ColGroup Usage:: PredCnt: 2 Matches Full: Partial: #1 (1 2 ) Sel: 0.1000
Using prorated density: 0.100000 of col #2 as selectvity of out-of-range/non-existent value pred
Table: Y1 Alias: Y1
Card: Original: 10000.000000 Rounded: 1000 Computed: 1000.00 Non Adjusted: 1000.00
Access Path: TableScan
Cost: 12.96 Resp: 12.96 Degree: 0
Cost_io: 12.00 Cost_cpu: 2412429
Resp_io: 12.00 Resp_cpu: 2412429
Access Path: index (index (FFS))
Index: Y1_I2
resc_io: 23.00 resc_cpu: 2056222
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 23.82 Resp: 23.82 Degree: 1
Cost_io: 23.00 Cost_cpu: 2056222
Resp_io: 23.00 Resp_cpu: 2056222
ColGroup Usage:: PredCnt: 2 Matches Full: Partial: #1 (1 2 ) Sel: 0.1000
ColGroup Usage:: PredCnt: 2 Matches Full: Partial: #1 (1 2 ) Sel: 0.1000
Using prorated density: 0.100000 of col #2 as selectvity of out-of-range/non-existent value pred
Access Path: index (IndexOnly)
Index: Y1_I2
resc_io: 6.00 resc_cpu: 242729
ix_sel: 0.100000 ix_sel_with_filters: 0.100000
Cost: 6.10 Resp: 6.10 Degree: 1
Best:: AccessPath: IndexRange
Index: Y1_I2
Cost: 6.10 Degree: 1 Resp: 6.10 Card: 1000.00 Bytes: 0


And completely "out-of-range":


alter session set tracefile_identifier = 'correlated23';

alter session set events '10053 trace name context forever, level 1';

explain plan for select c from y1 where a=100 and b=100;

alter session set events '10053 trace name context off';


The corresponding 10053 trace file excerpt:


SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for Y1[Y1]
ColGroup (#1, Index) Y1_I2
Col#: 1 2 3 CorStregth: 10.00
ColGroup Usage:: PredCnt: 2 Matches Full: Partial: #1 (1 2 ) Sel: 0.1000
Using prorated density: 0.100000 of col #2 as selectvity of out-of-range/non-existent value pred
Table: Y1 Alias: Y1
Card: Original: 10000.000000 Rounded: 1000 Computed: 1000.00 Non Adjusted: 1000.00
Access Path: TableScan
Cost: 12.96 Resp: 12.96 Degree: 0
Cost_io: 12.00 Cost_cpu: 2412429
Resp_io: 12.00 Resp_cpu: 2412429
Access Path: index (index (FFS))
Index: Y1_I2
resc_io: 23.00 resc_cpu: 2056222
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 23.82 Resp: 23.82 Degree: 1
Cost_io: 23.00 Cost_cpu: 2056222
Resp_io: 23.00 Resp_cpu: 2056222
ColGroup Usage:: PredCnt: 2 Matches Full: Partial: #1 (1 2 ) Sel: 0.1000
ColGroup Usage:: PredCnt: 2 Matches Full: Partial: #1 (1 2 ) Sel: 0.1000
Using prorated density: 0.100000 of col #2 as selectvity of out-of-range/non-existent value pred
Access Path: index (IndexOnly)
Index: Y1_I2
resc_io: 6.00 resc_cpu: 242729
ix_sel: 0.100000 ix_sel_with_filters: 0.100000
Cost: 6.10 Resp: 6.10 Degree: 1
Best:: AccessPath: IndexRange
Index: Y1_I2
Cost: 6.10 Degree: 1 Resp: 6.10 Card: 1000.00 Bytes: 0


Again the prorated density is left unchanged, and therefore the final selectivity/cardinality is still the same.

Now let's see what happens if the index on all three columns shows a different pattern:


truncate table y1;

begin
for i in 1..1000 loop
for j in 1..10 loop
insert into y1 values (j,mod(j,5), i);
end loop;
end loop;
end;
/

commit;

select count(*) from (
select distinct a, b, c from y1
);

COUNT(*)
----------
10000

begin
dbms_stats.delete_table_stats (
ownname =>null,
tabname=>'y1',
cascade_indexes=>true);
end;
/

begin
dbms_stats.gather_table_stats (
ownname =>null,
tabname=>'y1',
estimate_percent=>null,
cascade=>true,
method_opt =>'for all columns size 1');
end;
/

alter session set tracefile_identifier = 'correlated24';

alter session set events '10053 trace name context forever, level 1';

explain plan for select c from y1 where a=1 and b=1;

alter session set events '10053 trace name context off';


The corresponding 10053 trace file excerpt:


SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for Y1[Y1]
ColGroup (#1, Index) Y1_I2
Col#: 1 2 3 CorStregth: 5.00
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
Table: Y1 Alias: Y1
Card: Original: 10000.000000 Rounded: 200 Computed: 200.00 Non Adjusted: 200.00
Access Path: TableScan
Cost: 14.97 Resp: 14.97 Degree: 0
Cost_io: 14.00 Cost_cpu: 2432036
Resp_io: 14.00 Resp_cpu: 2432036
Access Path: index (index (FFS))
Index: Y1_I2
resc_io: 25.00 resc_cpu: 2077586
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 25.83 Resp: 25.83 Degree: 1
Cost_io: 25.00 Cost_cpu: 2077586
Resp_io: 25.00 Resp_cpu: 2077586
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
Access Path: index (IndexOnly)
Index: Y1_I2
resc_io: 2.00 resc_cpu: 54443
ix_sel: 0.020000 ix_sel_with_filters: 0.020000
Cost: 2.02 Resp: 2.02 Degree: 1
Best:: AccessPath: IndexRange
Index: Y1_I2
Cost: 2.02 Degree: 1 Resp: 2.02 Card: 200.00 Bytes: 0


The "CorStregth" value reverts to 5 and the index information is ignored. The selectivity/cardinality corresponds to the default formula used for uncorrelated column values, so in this case we lose the additional information about the correlation and fall back to default statistics.

Finally the "ZODIAC" example which actually represents correlated and skewed data is influenced by the bug fix for the non-existent values in frequency histograms as well.

Repeating the test case with the fix enabled:


select min(cnt)
from (
select a.*,
endpoint_number - lag(endpoint_number, 1, 0) over (order by endpoint_number) as cnt
from user_tab_histograms a
where table_name = 'CALENDAR' and column_name like 'SYS%'
);

MIN(CNT)
----------
7

alter session set "_fix_control"='5483301:on';

alter session set tracefile_identifier = 'zodiac1';

alter session set events '10053 trace name context forever, level 1';

explain plan for select count(*) from calendar c where zodiac = 'TAURUS' and month = 'JAN';

alter session set events '10053 trace name context off';


The corresponding 10053 trace file excerpt:


SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for CALENDAR[C]
Column (#3):
NewDensity:0.039726, OldDensity:0.001370 BktCnt:365, PopBktCnt:365, PopValCnt:12, NDV:12
Column (#2):
NewDensity:0.038356, OldDensity:0.001370 BktCnt:365, PopBktCnt:365, PopValCnt:12, NDV:12
Column (#4):
NewDensity:0.009589, OldDensity:0.001370 BktCnt:365, PopBktCnt:365, PopValCnt:24, NDV:24
ColGroup (#1, VC) SYS_STUWHPY_ZSVI_W3#C$I3EUUYB4
Col#: 2 3 CorStregth: 6.00
ColGroup Usage:: PredCnt: 2 Matches Full: Using density: 0.009589 of col #4 as selectivity of unpopular value pred
#1 Partial: Sel: 0.0096
Using density: 0.038356 of col #2 as selectivity of unpopular value pred
Table: CALENDAR Alias: C
Card: Original: 365.000000 Rounded: 4 Computed: 3.50 Non Adjusted: 3.50
Access Path: TableScan
Cost: 3.04 Resp: 3.04 Degree: 0
Cost_io: 3.00 Cost_cpu: 102543
Resp_io: 3.00 Resp_cpu: 102543
Best:: AccessPath: TableScan
Cost: 3.04 Degree: 1 Resp: 3.04 Card: 3.50 Bytes: 0


We can see that we get an estimated cardinality of 3.5 (which is again consistent with the rule introduced by the bug fix: 0.5 times the cardinality of the least popular value in the frequency histogram, the query returns 7 => 7 * 0.5 => 3.5) due to a "unpopular" value, but if you think about it, there no such thing like a "unpopular" value in a frequency histogram, basically the value should be treated as "non-existent", which is prevented by the bug fix.

Enabling previous behaviour shows the following:


alter session set "_fix_control"='5483301:off';

alter session set tracefile_identifier = 'zodiac2';

alter session set events '10053 trace name context forever, level 1';

explain plan for select count(*) from calendar c where zodiac = 'TAURUS' and month = 'JAN';

alter session set events '10053 trace name context off';


The corresponding 10053 trace file excerpt:


SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for CALENDAR[C]
Column (#3):
NewDensity:0.001370, OldDensity:0.001370 BktCnt:365, PopBktCnt:365, PopValCnt:12, NDV:12
Column (#2):
NewDensity:0.001370, OldDensity:0.001370 BktCnt:365, PopBktCnt:365, PopValCnt:12, NDV:12
Column (#4):
NewDensity:0.001370, OldDensity:0.001370 BktCnt:365, PopBktCnt:365, PopValCnt:24, NDV:24
ColGroup (#1, VC) SYS_STUWHPY_ZSVI_W3#C$I3EUUYB4
Col#: 2 3 CorStregth: 6.00
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.0014
Table: CALENDAR Alias: C
Card: Original: 365.000000 Rounded: 1 Computed: 0.50 Non Adjusted: 0.50
Access Path: TableScan
Cost: 3.04 Resp: 3.04 Degree: 0
Cost_io: 3.00 Cost_cpu: 101868
Resp_io: 3.00 Resp_cpu: 101868
Best:: AccessPath: TableScan
Cost: 3.04 Degree: 1 Resp: 3.04 Card: 0.50 Bytes: 0


In this case we get the expected output of 0 resp. 1 cardinality.

So in summary this is again a very interesting test case that shows that generating histograms can have a significant impact on the estimates of the optimizer - even in the particular case where the column or expression was distributed uniformly.

Friday, January 9, 2009

Limitations of histograms - rounding issues

As outlined in e.g. Jonathan Lewis' "Cost-Based Oracle Fundamentals" on page 117 et seqq., histograms are based only on a subset of column data, e.g. for character columns only the first 32 bytes (not characters in case of multi-byte character sets) are examined and while converting the resulting substring to a number, further truncation takes place: Only the first 15 bytes are used, the remaining ones are padded with zeros, the resulting hex number is converted to decimal and again rounded to 15 significant figures.

Since this means that for single-byte character strings only the first six to seven characters are relevant for the finally resulting number representation (in case of multi-byte character sets it's potentially even less relevant characters) Oracle populates the ENDPOINT_ACTUAL_VALUE using the first 32 bytes to be able to distinguish strings that are the same for the first six to seven bytes.

Recently there was a interesting thread on the OTN forum where it was pointed out that similar issues can arise if you attempt to generate a histogram on numeric data with a very high precision. It turned out that Oracle actually performs a similar rounding with number data although from a technical point of view it's not obvious why a rounding would be required since no conversion is necessary for a column of NUMBER data type; the ENDPOINT_VALUE seems to be a NUMBER type, too, in the data dictionary.

The behaviour although is a bit different between the handling of strings and numbers: Whereas strings that do not differ for the first 32 bytes simply generate a one bucket histogram (so they group to a single value), a number that is the same for the first 15 digits still generates an appropriate number of histogram buckets, but the ENDPOINT_VALUE is the same for all entries. Note that it doesn't matter if the digits are left or right to the decimal point, any number having a precision larger than 15 will get rounded.

The following test case (run on Windows (32bit) 10.2.0.4, but the results on 9.2.0.8 and 11.1.0.7 were the same) shall demonstrate the issues described. It populates a table with suitable data to demonstrate the rounding issues.


SQL>
SQL> alter session set nls_numeric_characters = ',.';

Session altered.

SQL>
SQL> drop table test_histogram_rounding purge;

Table dropped.

SQL>
SQL> create table test_histogram_rounding as
2 select rownum as id,
3 1.00000000000001 + (trunc(sqrt(rownum)) / 100000000000000) as the_number1,
4 1.000000000000001 + (trunc(sqrt(rownum)) / 1000000000000000) as the_number2,
5 10.00000000000001 + (trunc(sqrt(rownum)) / 100000000000000) as the_number3,
6 100.00000000000001 + (trunc(sqrt(rownum)) / 100000000000000) as the_number4,
7 to_char(100.00000000000001 + (trunc(sqrt(rownum)) / 100000000000000), 'TM') as the_number4_as_string,
8 10000000000000001 + trunc(sqrt(rownum)) as the_number5,
9 to_char(1000000000000000000000000000000001 + trunc(sqrt(rownum)), 'TM') as the_number6_as_string,
10 'AAAAA' || trunc(sqrt(rownum)) as the_string1,
11 'AAAAAAAAAA' || trunc(sqrt(rownum)) as the_string2,
12 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' || trunc(sqrt(rownum)) as the_string3
13 -- Use this in AL32UTF8 character set to see the effects of multi-byte character sets
14 --'ÄÄÄÄÄ' || trunc(sqrt(rownum)) as the_string1,
15 --'ÄÄÄÄÄÄÄÄÄÄ' || trunc(sqrt(rownum)) as the_string2,
16 --'ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ' || trunc(sqrt(rownum)) as the_string3
17 from dual
18 connect by level <= 1000;

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats(null, 'test_histogram_rounding', method_opt=>'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 THE_NUMBER1,THE_NUMBER2,THE_NUMBER3,THE_NUMBER4,THE_NUMBER4_AS_STRING,THE_NUMBER5,THE_NUMBER6_AS_STRING,THE_STRING1,THE_STRING2,THE_STRING3')

PL/SQL procedure successfully completed.

SQL>
SQL> column column_name format a22
SQL> column endpoint_number format 9999
SQL> column endpoint_actual_value format a32
SQL>
SQL> select column_name, endpoint_number, endpoint_value, endpoint_actual_value
2 from user_tab_histograms where table_name = 'TEST_HISTOGRAM_ROUNDING';

COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
---------------------- --------------- -------------------------------------- --------------------------------
THE_NUMBER1 3 1,00000000000002
THE_NUMBER1 8 1,00000000000003
THE_NUMBER1 15 1,00000000000004
THE_NUMBER1 24 1,00000000000005
THE_NUMBER1 35 1,00000000000006
THE_NUMBER1 48 1,00000000000007
THE_NUMBER1 63 1,00000000000008
THE_NUMBER1 80 1,00000000000009
THE_NUMBER1 99 1,0000000000001
THE_NUMBER1 120 1,00000000000011
THE_NUMBER1 143 1,00000000000012
THE_NUMBER1 168 1,00000000000013
THE_NUMBER1 195 1,00000000000014
THE_NUMBER1 224 1,00000000000015
THE_NUMBER1 255 1,00000000000016
THE_NUMBER1 288 1,00000000000017
THE_NUMBER1 323 1,00000000000018
THE_NUMBER1 360 1,00000000000019
THE_NUMBER1 399 1,0000000000002
THE_NUMBER1 440 1,00000000000021
THE_NUMBER1 483 1,00000000000022
THE_NUMBER1 528 1,00000000000023
THE_NUMBER1 575 1,00000000000024
THE_NUMBER1 624 1,00000000000025
THE_NUMBER1 675 1,00000000000026
THE_NUMBER1 728 1,00000000000027
THE_NUMBER1 783 1,00000000000028
THE_NUMBER1 840 1,00000000000029
THE_NUMBER1 899 1,0000000000003
THE_NUMBER1 960 1,00000000000031
THE_NUMBER1 1000 1,00000000000032
THE_NUMBER2 3 1
THE_NUMBER2 8 1
THE_NUMBER2 15 1
THE_NUMBER2 24 1,00000000000001
THE_NUMBER2 35 1,00000000000001
THE_NUMBER2 48 1,00000000000001
THE_NUMBER2 63 1,00000000000001
THE_NUMBER2 80 1,00000000000001
THE_NUMBER2 99 1,00000000000001
THE_NUMBER2 120 1,00000000000001
THE_NUMBER2 143 1,00000000000001
THE_NUMBER2 168 1,00000000000001
THE_NUMBER2 195 1,00000000000001
THE_NUMBER2 224 1,00000000000002
THE_NUMBER2 255 1,00000000000002
THE_NUMBER2 288 1,00000000000002
THE_NUMBER2 323 1,00000000000002
THE_NUMBER2 360 1,00000000000002
THE_NUMBER2 399 1,00000000000002
THE_NUMBER2 440 1,00000000000002
THE_NUMBER2 483 1,00000000000002
THE_NUMBER2 528 1,00000000000002
THE_NUMBER2 575 1,00000000000002
THE_NUMBER2 624 1,00000000000003
THE_NUMBER2 675 1,00000000000003
THE_NUMBER2 728 1,00000000000003
THE_NUMBER2 783 1,00000000000003
THE_NUMBER2 840 1,00000000000003
THE_NUMBER2 899 1,00000000000003
THE_NUMBER2 960 1,00000000000003
THE_NUMBER2 1000 1,00000000000003
THE_NUMBER3 3 10
THE_NUMBER3 8 10
THE_NUMBER3 15 10
THE_NUMBER3 24 10,0000000000001
THE_NUMBER3 35 10,0000000000001
THE_NUMBER3 48 10,0000000000001
THE_NUMBER3 63 10,0000000000001
THE_NUMBER3 80 10,0000000000001
THE_NUMBER3 99 10,0000000000001
THE_NUMBER3 120 10,0000000000001
THE_NUMBER3 143 10,0000000000001
THE_NUMBER3 168 10,0000000000001
THE_NUMBER3 195 10,0000000000001
THE_NUMBER3 224 10,0000000000002
THE_NUMBER3 255 10,0000000000002
THE_NUMBER3 288 10,0000000000002
THE_NUMBER3 323 10,0000000000002
THE_NUMBER3 360 10,0000000000002
THE_NUMBER3 399 10,0000000000002
THE_NUMBER3 440 10,0000000000002
THE_NUMBER3 483 10,0000000000002
THE_NUMBER3 528 10,0000000000002
THE_NUMBER3 575 10,0000000000002
THE_NUMBER3 624 10,0000000000003
THE_NUMBER3 675 10,0000000000003
THE_NUMBER3 728 10,0000000000003
THE_NUMBER3 783 10,0000000000003
THE_NUMBER3 840 10,0000000000003
THE_NUMBER3 899 10,0000000000003
THE_NUMBER3 960 10,0000000000003
THE_NUMBER3 1000 10,0000000000003
THE_NUMBER4 3 100
THE_NUMBER4 8 100
THE_NUMBER4 15 100
THE_NUMBER4 24 100
THE_NUMBER4 35 100
THE_NUMBER4 48 100
THE_NUMBER4 63 100
THE_NUMBER4 80 100
THE_NUMBER4 99 100
THE_NUMBER4 120 100
THE_NUMBER4 143 100
THE_NUMBER4 168 100
THE_NUMBER4 195 100
THE_NUMBER4 224 100
THE_NUMBER4 255 100
THE_NUMBER4 288 100
THE_NUMBER4 323 100
THE_NUMBER4 360 100
THE_NUMBER4 399 100
THE_NUMBER4 440 100
THE_NUMBER4 483 100
THE_NUMBER4 528 100
THE_NUMBER4 575 100
THE_NUMBER4 624 100
THE_NUMBER4 675 100
THE_NUMBER4 728 100
THE_NUMBER4 783 100
THE_NUMBER4 840 100
THE_NUMBER4 899 100
THE_NUMBER4 960 100
THE_NUMBER4 1000 100
THE_NUMBER4_AS_STRING 3 255399918356579000000000000000000000 100,00000000000002
THE_NUMBER4_AS_STRING 8 255399918356579000000000000000000000 100,00000000000003
THE_NUMBER4_AS_STRING 15 255399918356579000000000000000000000 100,00000000000004
THE_NUMBER4_AS_STRING 24 255399918356579000000000000000000000 100,00000000000005
THE_NUMBER4_AS_STRING 35 255399918356579000000000000000000000 100,00000000000006
THE_NUMBER4_AS_STRING 48 255399918356579000000000000000000000 100,00000000000007
THE_NUMBER4_AS_STRING 63 255399918356579000000000000000000000 100,00000000000008
THE_NUMBER4_AS_STRING 80 255399918356579000000000000000000000 100,00000000000009
THE_NUMBER4_AS_STRING 99 255399918356579000000000000000000000 100,0000000000001
THE_NUMBER4_AS_STRING 120 255399918356579000000000000000000000 100,00000000000011
THE_NUMBER4_AS_STRING 143 255399918356579000000000000000000000 100,00000000000012
THE_NUMBER4_AS_STRING 168 255399918356579000000000000000000000 100,00000000000013
THE_NUMBER4_AS_STRING 195 255399918356579000000000000000000000 100,00000000000014
THE_NUMBER4_AS_STRING 224 255399918356579000000000000000000000 100,00000000000015
THE_NUMBER4_AS_STRING 255 255399918356579000000000000000000000 100,00000000000016
THE_NUMBER4_AS_STRING 288 255399918356579000000000000000000000 100,00000000000017
THE_NUMBER4_AS_STRING 323 255399918356579000000000000000000000 100,00000000000018
THE_NUMBER4_AS_STRING 360 255399918356579000000000000000000000 100,00000000000019
THE_NUMBER4_AS_STRING 399 255399918356579000000000000000000000 100,0000000000002
THE_NUMBER4_AS_STRING 440 255399918356579000000000000000000000 100,00000000000021
THE_NUMBER4_AS_STRING 483 255399918356579000000000000000000000 100,00000000000022
THE_NUMBER4_AS_STRING 528 255399918356579000000000000000000000 100,00000000000023
THE_NUMBER4_AS_STRING 575 255399918356579000000000000000000000 100,00000000000024
THE_NUMBER4_AS_STRING 624 255399918356579000000000000000000000 100,00000000000025
THE_NUMBER4_AS_STRING 675 255399918356579000000000000000000000 100,00000000000026
THE_NUMBER4_AS_STRING 728 255399918356579000000000000000000000 100,00000000000027
THE_NUMBER4_AS_STRING 783 255399918356579000000000000000000000 100,00000000000028
THE_NUMBER4_AS_STRING 840 255399918356579000000000000000000000 100,00000000000029
THE_NUMBER4_AS_STRING 899 255399918356579000000000000000000000 100,0000000000003
THE_NUMBER4_AS_STRING 960 255399918356579000000000000000000000 100,00000000000031
THE_NUMBER4_AS_STRING 1000 255399918356579000000000000000000000 100,00000000000032
THE_NUMBER5 3 10000000000000000
THE_NUMBER5 8 10000000000000000
THE_NUMBER5 15 10000000000000000
THE_NUMBER5 24 10000000000000000
THE_NUMBER5 35 10000000000000000
THE_NUMBER5 48 10000000000000000
THE_NUMBER5 63 10000000000000000
THE_NUMBER5 80 10000000000000000
THE_NUMBER5 99 10000000000000000
THE_NUMBER5 120 10000000000000000
THE_NUMBER5 143 10000000000000000
THE_NUMBER5 168 10000000000000000
THE_NUMBER5 195 10000000000000000
THE_NUMBER5 224 10000000000000000
THE_NUMBER5 255 10000000000000000
THE_NUMBER5 288 10000000000000000
THE_NUMBER5 323 10000000000000000
THE_NUMBER5 360 10000000000000000
THE_NUMBER5 399 10000000000000000
THE_NUMBER5 440 10000000000000000
THE_NUMBER5 483 10000000000000000
THE_NUMBER5 528 10000000000000000
THE_NUMBER5 575 10000000000000000
THE_NUMBER5 624 10000000000000000
THE_NUMBER5 675 10000000000000000
THE_NUMBER5 728 10000000000000000
THE_NUMBER5 783 10000000000000000
THE_NUMBER5 840 10000000000000000
THE_NUMBER5 899 10000000000000000
THE_NUMBER5 960 10000000000000000
THE_NUMBER5 1000 10000000000000000
THE_NUMBER6_AS_STRING 1000 255399919594519000000000000000000000 10000000000000000000000000000000
THE_STRING1 3 338822822454902000000000000000000000 AAAAA1
THE_STRING1 24 338822822454903000000000000000000000 AAAAA10
THE_STRING1 47 338822822454903000000000000000000000 AAAAA11
THE_STRING1 72 338822822454903000000000000000000000 AAAAA12
THE_STRING1 99 338822822454903000000000000000000000 AAAAA13
THE_STRING1 128 338822822454903000000000000000000000 AAAAA14
THE_STRING1 159 338822822454903000000000000000000000 AAAAA15
THE_STRING1 192 338822822454903000000000000000000000 AAAAA16
THE_STRING1 227 338822822454903000000000000000000000 AAAAA17
THE_STRING1 264 338822822454903000000000000000000000 AAAAA18
THE_STRING1 303 338822822454903000000000000000000000 AAAAA19
THE_STRING1 308 338822822454907000000000000000000000 AAAAA2
THE_STRING1 349 338822822454907000000000000000000000 AAAAA20
THE_STRING1 392 338822822454907000000000000000000000 AAAAA21
THE_STRING1 437 338822822454907000000000000000000000 AAAAA22
THE_STRING1 484 338822822454907000000000000000000000 AAAAA23
THE_STRING1 533 338822822454907000000000000000000000 AAAAA24
THE_STRING1 584 338822822454907000000000000000000000 AAAAA25
THE_STRING1 637 338822822454908000000000000000000000 AAAAA26
THE_STRING1 692 338822822454908000000000000000000000 AAAAA27
THE_STRING1 749 338822822454908000000000000000000000 AAAAA28
THE_STRING1 808 338822822454908000000000000000000000 AAAAA29
THE_STRING1 815 338822822454911000000000000000000000 AAAAA3
THE_STRING1 876 338822822454912000000000000000000000 AAAAA30
THE_STRING1 916 338822822454912000000000000000000000 AAAAA31
THE_STRING1 925 338822822454916000000000000000000000 AAAAA4
THE_STRING1 936 338822822454921000000000000000000000 AAAAA5
THE_STRING1 949 338822822454925000000000000000000000 AAAAA6
THE_STRING1 964 338822822454930000000000000000000000 AAAAA7
THE_STRING1 981 338822822454935000000000000000000000 AAAAA8
THE_STRING1 1000 338822822454940000000000000000000000 AAAAA9
THE_STRING2 3 338822822454979000000000000000000000 AAAAAAAAAA1
THE_STRING2 24 338822822454979000000000000000000000 AAAAAAAAAA10
THE_STRING2 47 338822822454979000000000000000000000 AAAAAAAAAA11
THE_STRING2 72 338822822454979000000000000000000000 AAAAAAAAAA12
THE_STRING2 99 338822822454979000000000000000000000 AAAAAAAAAA13
THE_STRING2 128 338822822454979000000000000000000000 AAAAAAAAAA14
THE_STRING2 159 338822822454979000000000000000000000 AAAAAAAAAA15
THE_STRING2 192 338822822454979000000000000000000000 AAAAAAAAAA16
THE_STRING2 227 338822822454979000000000000000000000 AAAAAAAAAA17
THE_STRING2 264 338822822454979000000000000000000000 AAAAAAAAAA18
THE_STRING2 303 338822822454979000000000000000000000 AAAAAAAAAA19
THE_STRING2 308 338822822454979000000000000000000000 AAAAAAAAAA2
THE_STRING2 349 338822822454979000000000000000000000 AAAAAAAAAA20
THE_STRING2 392 338822822454979000000000000000000000 AAAAAAAAAA21
THE_STRING2 437 338822822454979000000000000000000000 AAAAAAAAAA22
THE_STRING2 484 338822822454979000000000000000000000 AAAAAAAAAA23
THE_STRING2 533 338822822454979000000000000000000000 AAAAAAAAAA24
THE_STRING2 584 338822822454979000000000000000000000 AAAAAAAAAA25
THE_STRING2 637 338822822454979000000000000000000000 AAAAAAAAAA26
THE_STRING2 692 338822822454979000000000000000000000 AAAAAAAAAA27
THE_STRING2 749 338822822454979000000000000000000000 AAAAAAAAAA28
THE_STRING2 808 338822822454979000000000000000000000 AAAAAAAAAA29
THE_STRING2 815 338822822454979000000000000000000000 AAAAAAAAAA3
THE_STRING2 876 338822822454979000000000000000000000 AAAAAAAAAA30
THE_STRING2 916 338822822454979000000000000000000000 AAAAAAAAAA31
THE_STRING2 925 338822822454979000000000000000000000 AAAAAAAAAA4
THE_STRING2 936 338822822454979000000000000000000000 AAAAAAAAAA5
THE_STRING2 949 338822822454979000000000000000000000 AAAAAAAAAA6
THE_STRING2 964 338822822454979000000000000000000000 AAAAAAAAAA7
THE_STRING2 981 338822822454979000000000000000000000 AAAAAAAAAA8
THE_STRING2 1000 338822822454979000000000000000000000 AAAAAAAAAA9
THE_STRING3 1000 338822822454979000000000000000000000
ID 0 1
ID 1 1000

252 rows selected.

SQL>
SQL> set autotrace on explain
SQL>
SQL> select count(*)
2 from test_histogram_rounding
3 where the_number1 = 1.00000000000002;

COUNT(*)
--------------------------------------
3


Execution Plan
----------------------------------------------------------
Plan hash value: 3690517312

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 13 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | TABLE ACCESS FULL| TEST_HISTOGRAM_ROUNDING | 3 | 30 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - filter("THE_NUMBER1"=1.00000000000002)

SQL>
SQL> select count(*)
2 from test_histogram_rounding
3 where the_number1 = 1.00000000000031;

COUNT(*)
--------------------------------------
61


Execution Plan
----------------------------------------------------------
Plan hash value: 3690517312

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 13 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | TABLE ACCESS FULL| TEST_HISTOGRAM_ROUNDING | 61 | 610 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - filter("THE_NUMBER1"=1.00000000000031)

SQL>
SQL> select count(*)
2 from test_histogram_rounding
3 where the_number2 = 1.000000000000002;

COUNT(*)
--------------------------------------
3


Execution Plan
----------------------------------------------------------
Plan hash value: 3690517312

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 13 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| TEST_HISTOGRAM_ROUNDING | 5 | 55 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - filter("THE_NUMBER2"=1.000000000000002)

SQL>
SQL> select count(*)
2 from test_histogram_rounding
3 where the_number2 = 1.000000000000031;

COUNT(*)
--------------------------------------
61


Execution Plan
----------------------------------------------------------
Plan hash value: 3690517312

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 13 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| TEST_HISTOGRAM_ROUNDING | 55 | 605 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - filter("THE_NUMBER2"=1.000000000000031)

SQL>
SQL> select count(*)
2 from test_histogram_rounding
3 where the_number3 = 10.00000000000002;

COUNT(*)
--------------------------------------
3


Execution Plan
----------------------------------------------------------
Plan hash value: 3690517312

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 13 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | TABLE ACCESS FULL| TEST_HISTOGRAM_ROUNDING | 5 | 50 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - filter("THE_NUMBER3"=10.00000000000002)

SQL>
SQL> select count(*)
2 from test_histogram_rounding
3 where the_number3 = 10.00000000000031;

COUNT(*)
--------------------------------------
61


Execution Plan
----------------------------------------------------------
Plan hash value: 3690517312

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 13 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | TABLE ACCESS FULL| TEST_HISTOGRAM_ROUNDING | 55 | 550 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - filter("THE_NUMBER3"=10.00000000000031)

SQL>
SQL> select count(*)
2 from test_histogram_rounding
3 where the_number4 = 100.00000000000002;

COUNT(*)
--------------------------------------
3


Execution Plan
----------------------------------------------------------
Plan hash value: 3690517312

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 13 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| TEST_HISTOGRAM_ROUNDING | 33 | 363 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - filter("THE_NUMBER4"=100.00000000000002)

SQL>
SQL> select count(*)
2 from test_histogram_rounding
3 where the_number4 = 100.00000000000031;

COUNT(*)
--------------------------------------
61


Execution Plan
----------------------------------------------------------
Plan hash value: 3690517312

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 13 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| TEST_HISTOGRAM_ROUNDING | 33 | 363 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - filter("THE_NUMBER4"=100.00000000000031)

SQL>
SQL> select count(*)
2 from test_histogram_rounding
3 where the_number4_as_string = 100.00000000000002;

COUNT(*)
--------------------------------------
3


Execution Plan
----------------------------------------------------------
Plan hash value: 3690517312

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 13 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 19 | | |
|* 2 | TABLE ACCESS FULL| TEST_HISTOGRAM_ROUNDING | 3 | 57 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - filter(TO_NUMBER("THE_NUMBER4_AS_STRING")=100.00000000000002)

SQL>
SQL> select count(*)
2 from test_histogram_rounding
3 where the_number4_as_string = 100.00000000000031;

COUNT(*)
--------------------------------------
61


Execution Plan
----------------------------------------------------------
Plan hash value: 3690517312

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 13 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 19 | | |
|* 2 | TABLE ACCESS FULL| TEST_HISTOGRAM_ROUNDING | 61 | 1159 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - filter(TO_NUMBER("THE_NUMBER4_AS_STRING")=100.00000000000031)

SQL>
SQL> alter session set nls_numeric_characters = '.,';

Session altered.

SQL>
SQL> select count(*)
2 from test_histogram_rounding
3 where the_number4_as_string = 100.00000000000002;
where the_number4_as_string = 100.00000000000002
*
ERROR at line 3:
ORA-01722: invalid number


SQL>
SQL> alter session set nls_numeric_characters = ',.';

Session altered.

SQL>
SQL> select count(*)
2 from test_histogram_rounding
3 where the_number5 = 10000000000000002;

COUNT(*)
--------------------------------------
3


Execution Plan
----------------------------------------------------------
Plan hash value: 3690517312

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 13 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| TEST_HISTOGRAM_ROUNDING | 33 | 363 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - filter("THE_NUMBER5"=10000000000000002)

SQL>
SQL> select count(*)
2 from test_histogram_rounding
3 where the_number5 = 10000000000000031;

COUNT(*)
--------------------------------------
61


Execution Plan
----------------------------------------------------------
Plan hash value: 3690517312

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 13 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| TEST_HISTOGRAM_ROUNDING | 33 | 363 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - filter("THE_NUMBER5"=10000000000000031)

SQL>
SQL> select count(*)
2 from test_histogram_rounding
3 where the_number6_as_string = 1000000000000000000000000000000002;

COUNT(*)
--------------------------------------
3


Execution Plan
----------------------------------------------------------
Plan hash value: 3690517312

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 13 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 35 | | |
|* 2 | TABLE ACCESS FULL| TEST_HISTOGRAM_ROUNDING | 1000 | 35000 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - filter(TO_NUMBER("THE_NUMBER6_AS_STRING")=1000000000000000000000000000000002)

SQL>
SQL> select count(*)
2 from test_histogram_rounding
3 where the_number6_as_string = 1000000000000000000000000000000031;

COUNT(*)
--------------------------------------
61


Execution Plan
----------------------------------------------------------
Plan hash value: 3690517312

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 13 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 35 | | |
|* 2 | TABLE ACCESS FULL| TEST_HISTOGRAM_ROUNDING | 1000 | 35000 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - filter(TO_NUMBER("THE_NUMBER6_AS_STRING")=1000000000000000000000000000000031)

SQL>
SQL> select count(*)
2 from test_histogram_rounding
3 where the_string1 = 'AAAAA1';

COUNT(*)
--------------------------------------
3


Execution Plan
----------------------------------------------------------
Plan hash value: 3690517312

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 13 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL| TEST_HISTOGRAM_ROUNDING | 3 | 24 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - filter("THE_STRING1"='AAAAA1')

SQL>
SQL> select count(*)
2 from test_histogram_rounding
3 where the_string1 = 'AAAAA30';

COUNT(*)
--------------------------------------
61


Execution Plan
----------------------------------------------------------
Plan hash value: 3690517312

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 13 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL| TEST_HISTOGRAM_ROUNDING | 61 | 488 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - filter("THE_STRING1"='AAAAA30')

SQL>
SQL> select count(*)
2 from test_histogram_rounding
3 where the_string2 = 'AAAAAAAAAA1';

COUNT(*)
--------------------------------------
3


Execution Plan
----------------------------------------------------------
Plan hash value: 3690517312

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 13 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| TEST_HISTOGRAM_ROUNDING | 3 | 39 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - filter("THE_STRING2"='AAAAAAAAAA1')

SQL>
SQL> select count(*)
2 from test_histogram_rounding
3 where the_string2 = 'AAAAAAAAAA30';

COUNT(*)
--------------------------------------
61


Execution Plan
----------------------------------------------------------
Plan hash value: 3690517312

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 13 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| TEST_HISTOGRAM_ROUNDING | 61 | 793 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - filter("THE_STRING2"='AAAAAAAAAA30')

SQL>
SQL> select count(*)
2 from test_histogram_rounding
3 where the_string3 = 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA1';

COUNT(*)
--------------------------------------
3


Execution Plan
----------------------------------------------------------
Plan hash value: 3690517312

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 13 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 35 | | |
|* 2 | TABLE ACCESS FULL| TEST_HISTOGRAM_ROUNDING | 1000 | 35000 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - filter("THE_STRING3"='AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA1')

SQL>
SQL> select count(*)
2 from test_histogram_rounding
3 where the_string3 = 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA30';

COUNT(*)
--------------------------------------
61


Execution Plan
----------------------------------------------------------
Plan hash value: 3690517312

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 13 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 35 | | |
|* 2 | TABLE ACCESS FULL| TEST_HISTOGRAM_ROUNDING | 1000 | 35000 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - filter("THE_STRING3"='AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA30')

SQL>
SQL> spool off


As you can see from the histogram dictionary information, the numeric data gets rounded but the correct number of histogram buckets get generated. The string behaves differently, and you can see the different handling of the ENDPOINT_VALUE and the ENDPOINT_ACTUAL_VALUE.

When the rounding issues kick in for the numbers you start to get (slightly) incorrect estimates based on the histogram information, and once the precision gets greater than 15, the estimates will be significantly incorrect.

As a side note, as you can see from the test case, it's a bad idea to generate a histogram on a string that is the same for at least the 32 first bytes, since you'll get a single bucket and the estimates will be way off.

A potential workaround of the numeric issue is to store the number as string (as long as the precision is less than 32 due to the limitation of the string handling). According to "Cost-Based Oracle Fundamentals" by Jonathan Lewis from 9.2.0.6 and 10.1.0.4 on Oracle will use the histogram information properly, even with the implicit number conversion in place, but I don't have the older versions currently available for testing, so I can't tell the exact outcome when using the older releases.

Of course this workaround introduces various issues, the most obvious threat being the implicit conversion relying on client NLS settings, as can be seen in the script.