Extendiendo la información estadistica en 11g (estadisticas multicolumna)

Una vez mas escribo una nota relacionada con el optimizador de Oracle y en especial sobre las estadisticas, que como ya se sabe son el pilar fundamental para garantizar un plan optimo. Asi como un matematico se basa en axiomas o teoremas para demostrar otro teorema en base a inferencias logicas, el optimizador utiliza la información estadistica que tiene a su disposición para inferir el plan de ejecución mas conveniente, el que menos recurso insume. En 11g se pueden suministrar extensiones a las estadisticas habituales para "ayudar" en ciertos casos particulares. Uno de los problemas que se daban esta relacionado con la correlación entre la información estadisticas de multiples columnas que se referencian en un predicado. Para entender mejor voy a mostrar un ejemplo completo (en una nota de diciembre habia escrito sobre estaditicas multicolumnas para mostrar SQL Profiles, pero esta nueva nota ahonda en mas detalle, ver nota: SQL Profiles. Una ayuda adicional...)

Primero, como es habitual, voy a crear una tabla T en base a los registros de la tabla DBA_OBJECTS y voy a crear un indice por dos columnas elegidas arbitrariamente:

rop@DESA11G> create table t as select * from dba_objects;

Tabla creada.

rop@DESA11G> create index t_idx on t(owner,object_type);

Índice creado.

rop@DESA11G>

Ahora vamos a ver la distribución de las columnas OWNER y OBJECT_TYPE:

rop@DESA11G> select owner,count(1)
2          from t
3          group by owner;

OWNER                            COUNT(1)
------------------------------ ----------
PUBLIC                              26723
SYSTEM                                518
XDB                                   811
OLAPSYS                               720
FLOWS_FILES                            12
SYS                                 30217
TSMSYS                                  3
MDSYS                                1303
SYSMAN                               3360
EXFSYS                                303
SI_INFORMTN_SCHEMA                      8
ORACLE_OCM                              8
WMSYS                                 315
ORDSYS                               2353
SCOTT                                   6
WK_TEST                                47
FLOWS_030000                         1526
CTXSYS                                372
ORDPLUGINS                             10
WKSYS                                 371
ROP                                   122
OUTLN                                   9
DBSNMP                                 55

23 filas seleccionadas.

rop@DESA11G> select object_type,count(1)
2          from t
3          group by object_type;

OBJECT_TYPE           COUNT(1)
------------------- ----------
INDEX                     3228
JOB CLASS                   13
CONTEXT                      7
TABLE SUBPARTITION          40
TYPE BODY                  238
INDEXTYPE                   11
PROCEDURE                  135
RESOURCE PLAN                7
RULE                         1
JAVA CLASS               22205
TABLE PARTITION            289
SCHEDULE                     2
WINDOW                       9
WINDOW GROUP                 4
JAVA RESOURCE              835
TABLE                     2576
TYPE                      2643
VIEW                      4788
LIBRARY                    181
FUNCTION                   296
TRIGGER                    484
PROGRAM                     18
MATERIALIZED VIEW            1
JAVA SOURCE                  1
CLUSTER                     10
SYNONYM                  26795
PACKAGE BODY              1213
CONSUMER GROUP              14
EVALUATION CONTEXT          11
QUEUE                       35
RULE SET                    17
DIRECTORY                    3
EDITION                      1
OPERATOR                    57
UNDEFINED                    6
JAVA DATA                  325
SEQUENCE                   230
LOB                        768
PACKAGE                   1274
INDEX PARTITION            289
LOB PARTITION                7
JOB                         11
XML SCHEMA                  94

43 filas seleccionadas.

De la distribución mostrada podemos ver que tenemos 30210 filas cuyo owner es SYS y 26795 cuyo object_type es SYNONYM. Analizando por separadas ambas distribuciones vemos que son un porcentaje alto del total de cada agrupación y evaluadas por separado suena coherente el acceso full scan cuando se filtra por dichas columnas para los valores analizados.
Ahora veamos que pasa si en un mismo predicado filtramos por owner y object_type:

rop@DESA11G> select count(1) from t where owner = 'SYS' and object_type = 'SYNONYM';

COUNT(1)
----------
      9

Observamos que combinando las dos columnas solo cumplen dicho filtro 9 filas.
Voy a recolectar estadisticas y analizar el plan:

rop@DESA11G> begin
2             dbms_stats.gather_table_Stats(user,
3                              'T',
4                              method_opt => 'for all columns size skewonly');
5* end;
rop@DESA11G> /

rop@DESA11G> explain plan for
2          select *
3          from t
4          where owner = 'SYS' and object_type = 'SYNONYM';

Explicado.

rop@DESA11G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 2153619298

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 11996 |  1183K|   288   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    | 11996 |  1183K|   288   (2)| 00:00:04 |
--------------------------------------------------------------------------

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

1 - filter("OBJECT_TYPE"='SYNONYM' AND "OWNER"='SYS')

13 filas seleccionadas.

Estimó 11996 lo cual es muy impreciso, no?, deberia ser 9 o cercano para se mas real. Por que se confundió tanto el optimizador y eligió ir por full scan?. Miremos la salida del trace con el evento 10053, que nos muestra en detalle los pasos que sigue el optimizador para decidir que hacer. Abajo copio solo la parte del trace que nos interesa (el trace completo es muy extenso y lista parametrizaciones, transformaciones,orden de evaluación de predicados, etc):

***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T[T]
Column (#1):
 NewDensity:0.000091, OldDensity:0.000007 BktCnt:5480, PopBktCnt:5478, PopValCnt:16, NDV:23
Column (#6):
 NewDensity:0.000091, OldDensity:0.000007 BktCnt:5480, PopBktCnt:5475, PopValCnt:26, NDV:43
ColGroup (#1, Index) T_IDX
 Col#: 1 6    CorStregth: 3.96
ColGroup Usage:: PredCnt: 2  Matches Full:  Partial:
Table: T  Alias: T
 Card: Original: 69172.000000  Rounded: 11646  Computed: 11645.63  Non Adjusted: 11645.63
 Access Path: TableScan
 Cost:  288.43  Resp: 288.43  Degree: 0
   Cost_io: 285.00  Cost_cpu: 31622103
   Resp_io: 285.00  Resp_cpu: 31622103
ColGroup Usage:: PredCnt: 2  Matches Full:  Partial:
ColGroup Usage:: PredCnt: 2  Matches Full:  Partial:
Access Path: index (AllEqRange)
 Index: T_IDX
 resc_io: 559.00  resc_cpu: 11318715
 ix_sel: 0.168358  ix_sel_with_filters: 0.168358
 Cost: 560.23  Resp: 560.23  Degree: 1
Best:: AccessPath: TableScan
      Cost: 288.43  Degree: 1  Resp: 288.43  Card: 11645.63  Bytes: 0

***************************************

En el análisis el optimizador asigna un costo de 560 al plan que utiliza el indice y 288 al plan que utiliza el full scan, y como ya sabemos se queda con el que menor costo arroja, por lo tanto estima que es el mejor path.

Afortunadamente en 11g se pueden recolectar estadisticas a nivel multicoluma lo cual aporta información de correlación muy util, veamos como hacerlo:

rop@DESA11G> declare
2      out varchar2(30);
3  begin
4      out := dbms_stats.create_extended_stats(user,'T', '(owner,object_type)');
5  end;
6  /

rop@DESA11G> begin
2      dbms_stats.gather_table_stats(null,'T',
3      method_opt =>'for all columns size auto for columns (owner,object_type)');
4  end;
5  /

rop@DESA11G> explain plan for
2  select *
3  from t
4  where owner = 'SYS' and object_type = 'SYNONYM';

Explicado.

rop@DESA11G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1020776977

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

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

2 - access("OWNER"='SYS' AND "OBJECT_TYPE"='SYNONYM')

14 filas seleccionadas.

El plan ahora uso el indice y ademas observar que la estimación de filas a retornar es de 6, lo cual es bastante cercano a la realidad. Veamos el trace del evento 10053 para analizar que hizo ahora el optimizador:

***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T[T]
Column (#1):
 NewDensity:0.000091, OldDensity:0.000007 BktCnt:5480, PopBktCnt:5478, PopValCnt:16, NDV:23
Column (#6):
 NewDensity:0.000091, OldDensity:0.000007 BktCnt:5480, PopBktCnt:5475, PopValCnt:26, NDV:43
Column (#16):
 NewDensity:0.000091, OldDensity:0.000007 BktCnt:5480, PopBktCnt:5441, PopValCnt:104, NDV:250
ColGroup (#1, VC) SYS_STUXJ8K0YTS_5QD1O0PEA514IY
 Col#: 1 6    CorStregth: 3.96
ColGroup Usage:: PredCnt: 2  Matches Full:   Using density: 0.000091 of col #16 as selectivity of unpopular value pred
#1  Partial:  Sel: 0.0001
Table: T  Alias: T
 Card: Original: 69172.000000  Rounded: 6  Computed: 6.31  Non Adjusted: 6.31
Access Path: TableScan
 Cost:  288.20  Resp: 288.20  Degree: 0
   Cost_io: 285.00  Cost_cpu: 29526903
   Resp_io: 285.00  Resp_cpu: 29526903
ColGroup Usage:: PredCnt: 2  Matches Full:   Using density: 0.000091 of col #16 as selectivity of unpopular value pred
#1  Partial:  Sel: 0.0001
ColGroup Usage:: PredCnt: 2  Matches Full:   Using density: 0.000091 of col #16 as selectivity of unpopular value pred
#1  Partial:  Sel: 0.0001
Access Path: index (AllEqRange)
 Index: T_IDX
 resc_io: 2.00  resc_cpu: 19503
 ix_sel: 0.000091  ix_sel_with_filters: 0.000091
 Cost: 2.00  Resp: 2.00  Degree: 1
Best:: AccessPath: IndexRange
Index: T_IDX
      Cost: 2.00  Degree: 1  Resp: 2.00  Card: 6.31  Bytes: 0

***************************************

El costo del full scan sigue siendo, obviamente, el mismo (288) pero ahora el acceso por indice es de tan solo 2 y por lo tanto es el path elegido.
Como les mostré en esta nota podemos garantizar un correcto funcionamiento para predicados con mas de un filtro recolectando las estadisticas extendidas, nuevas en 11g.