Extended Stats

I’m very keen on the 11g extended stats feature, but I’ve just discovered the first of a list of critical weaknesses in the implementation details that could lead to some surprising instability in execution plans [See end of article for links to further problems]. It’s a combination of “column group” statistics and “out of range” predicates. Let’s start with  some sample data. (Note: Initially tested on 11.2.0.4 and 12.1.0.1 – still accurate on 12.2.0.1 and 19.3.0.0):

rem
rem     Script:         extended_stats_04.sql
rem     Author:         Jonathan Lewis
rem     Dated:          April 2012
rem
rem     Last tested 
rem             12.1.0.1
rem             11.2.0.4
rem

create table t1
as
with generator as (
        select  --+ materialize
                rownum id
        from dual
        connect by
                level <= 10000 -- > comment to avoid wordpress format issue
)
select
        mod(rownum,100)         col1,
        mod(rownum,10)          col2
from
        generator       v1,
        generator       v2
where
        rownum <= 50000 -- > comment to avoid wordpress format issue
;

begin 
        dbms_stats.gather_table_stats(
                ownname         => user,
                tabname         =>'T1',
                method_opt      => 'for all columns size 1'
        );
end;
/

I’ve got 100 distinct values for col1 and 10 distinct values for col2 – so the optimizer will assume that I’ve got 1,000 distinct values for the combination. Since there are 50,000 rows this means 50 rows per combination. So let’s see this demonstrated through a simple query and plan:

set autotrace traceonly explain

select  *
from    t1
where
        col1 = 50
and     col2 = 5
;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    50 |   300 |    13 |
|*  1 |  TABLE ACCESS FULL| T1   |    50 |   300 |    13 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("COL1"=50 AND "COL2"=5)

The optimizer has, as predicted, estimated the cardinality (Rows) for this query as 50. If you examine my data definition, though, you will see that the col1 and col2 values align in a way that means there are really only 100 distinct combination, with 500 rows per combination. This looks like one of those cases where extended statistics might be useful. So let’s create a suitable set:

begin
        dbms_output.put_line(
                dbms_stats.create_extended_stats(
                        ownname         => user,
                        tabname         => 'T1',
                        extension       => '(col1, col2)'
                )
        );

        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'T1',
                block_sample     => true,
                method_opt       => 'for all hidden columns size 1'
        );
end;
/

With these stats in place, I repeat my query with the following effect:

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   500 |  3000 |    13 |
|*  1 |  TABLE ACCESS FULL| T1   |   500 |  3000 |    13 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("COL1"=50 AND "COL2"=5)

Oracle has used the “column group” statistics to derive a good estimate of the combined selectivity and produced an estimate of 500 rows. But what happens as new data (following the same pattern) is entered, and the queries change to keep up with the data; consider, for example, the slightly artificial query:

select  *
from    t1
where
        col1 = 50
and     col2 = 9.001
;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    50 |   300 |    13 |
|*  1 |  TABLE ACCESS FULL| T1   |    50 |   300 |    13 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("COL1"=50 AND "COL2"=9.001)

I’ve used the value 9.001 because it is only just outside the known value range for col2 (which ranges from 0 to 9) – and the optimizer has immediately switched from the extended stats back to the single column stats and the “product of independent predicates” model to estimate the cardinality, leading to a dramatic change in the estimate.

This strategy may be a deliberate choice on the part of the developers, but personally I think that it’s not appropriate. If you’ve created column group stats like this it seems reasonably likely that you’re trying to show Oracle a consistent pattern in the data; however it’s quite possible that the data you’re using shows some sort of time-dependency such that the pattern doesn’t change, but the actual values in (at least one) of the columns keeps increasing.

In this case, applying the normal “linear decay” strategy to the extended stats seems much more sensible – if one (or both) of the predicates is slightly out of range then use the extended stats to derive an estimate then apply the decay factor(s) to that value, rather than falling back on multiplying the individual selectivities.

Side note: The case where I saw this problem was one where rows were constantly being inserted and deleted. One column was a time-based audit id, the other was an action id, and every audit id produced a few dozen action ids, with some overlap of actions across audit ids. New, higher valued, audit rows were constantly being added to this table, while old, lower ones, were being deleted. The effect was that the extended stats because useless within seconds of being generated.

Footnote:

Coincidentally, Randolf Geist highlighted another edge case a couple of days ago – but it hasn’t stopped me thinking that extended stats, and “column group” stats especially are a terrific feature.

Update (Feb 2014)

Christian Antognini has come up with another example of how extended stats might  not behave as you expect; in this case relating to side effects of histograms. (With a reference to  Bug 6972291 and the associated fix_control to change the behaviour to something that may be more desirable: set “_fix_control”=”6972291:ON”

Update (Nov 2015)

An item on OTN has just highlighted another anomaly – if your column group includes a nullable column and your chosen multi-column where clause includes an “is null” predicate then the column group stats aren’t applied. For more details here’s a blog note I wrote as a follow-up.

Update (Jan 2016)

Another anomaly that can appear with extended stats got a mention on Twitter yesterday, with a link to an article by Nenad Noveljic that demonistrates a parsing problem and offers a workaround to the problem.

Update (Dec 2019)

Bobby Durrett reports a problem when upgrading from 11.2 to 19c using expdp/impdp.  The import failed on a table that had extended stats defined because of a problem handling the virtual column.