martes, 2 de febrero de 2010

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.

No hay comentarios:

Publicar un comentario