A partir de 10g existen los SQL Profiles que corrigen estos problemas aportandole al optimizador mayor información, hay que pensarlo como estadisticas particulares sobre las sentencias. En notas anteriores mostré las caracteristicas de STORED_OUTLINES(8i) y de las SQL BASELINES (11g). En esta nota con SQL PROFILES(10g) , en orden no cronólogico, se completa la trilogia de mecanismos de corrección de planes que se han ido agregando versión tras versión. Este conjunto de herramientas se podrían pensar como mecanismos de hinteo encubierto que han ido evolucionando y sofisticandose con cada nuevo realease de base.
A diferencia de los STORED_OUTLINES que fijan un determinado plan a una sentencia, los SQL PROFILES son un set de hints que corrigen ciertos calculos internos del optimizador para que no se desvien los planes.
Para obtener los profiles se puede usar el paquete DBMS_SQLTUNE que crea un tarea y realiza una pseudo ejecución tomando métricas que le permiten conocer mayor detalle y analizar las correlaciones entre las columnas referenciadas en los predicados de la sentencia.
En el ejemplo que armé, cree una tabla TBL_AUTOS que podría representar datos de un concesionario o fabrica automotriz, con 3 campos: un identificación unica del auto (ID), una categoria o segmento: (A) Segmento Bajo, (B) Segmento Medio, (C)) Segmento Alto y (D) Gama Premium y como tercer campo el precio o valor del vehiculo en dolares, es una aproximación de los valores aqui en Argentina. Ahora voy a crear la tabla tratando de generar en forma ficticia una distribución cercana a una distribución real.
rop@ROP102> create table tbl_autos (id int,categoria char(1),valor int);
Tabla creada.
rop@ROP102> create sequence seq_autos;
Secuencia creada.
rop@ROP102> insert into tbl_autos
2 select seq_autos.nextval,
3 'A',
4 trunc(dbms_random.value(8000,12000))
5 from dual
6 connect by rownum <= 60000;
60000 filas creadas.
rop@ROP102> insert into tbl_autos
2 select seq_autos.nextval,
3 'B',
4 trunc(dbms_random.value(12000,15000))
5 from dual
6 connect by rownum <= 25000;
25000 filas creadas.
rop@ROP102> insert into tbl_autos
2 select seq_autos.nextval,
3 'C',
4 trunc(dbms_random.value(15000,30000))
5 from dual
6 connect by rownum <= 14500;
14500 filas creadas.
rop@ROP102> insert into tbl_autos
2 select seq_autos.nextval,
3 'D',
4 trunc(dbms_random.value(40000,80000))
5 from dual
6 connect by rownum <= 500 ;
500 filas creadas.
rop@ROP102> create index autos_idx on tbl_autos (categoria,valor);
Índice creado.
rop@ROP102> begin
2 dbms_stats.gather_table_stats(ownname => user,
tabname => 'TBL_AUTOS',
cascade =>true);
3 end;
4 /
Procedimiento PL/SQL terminado correctamente.
Con los pasos listado arriba tengo una tabla TBL_AUTOS, indexada por (categoria,valor) y con las estadísticas recolectadas.
Los vehiculos de categoria 'A' nunca superan los 12000 dólares entonces es de esperar que si realizo una consulta filtrando por categoria='A' y valor > 20000, el resultado debería ser 0, verdad?. Veamos que pasa.
rop@ROP102> explain plan for select min(id)
2 from tbl_autos
3 where categoria = 'A'
4 and valor > 20000;
Explicado.
rop@ROP102> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 649483413
--------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------
0 SELECT STATEMENT 1 11 73 (7) 00:00:01
1 SORT AGGREGATE 1 11
* 2 TABLE ACCESS FULL TBL_AUTOS 20832 223K 73 (7) 00:00:01
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("VALOR">20000 AND "CATEGORIA"='A')
14 filas seleccionadas.
Fijense que se estimaron 20832 filas, lo cual dista muchisimo de la realidad. Al calcular tan mal la cardinalidad el optimizador optó por un plan inadecuado, dictando recorrer toda la tabla para obtener el resultado (FULL SCAN), suena ilógico, no?. Esto se da por lo que comenté antes respecto a que el optimizador no cuenta con información de distribución correlacionada o multi-columna.
Voy a ejecutar el optimizador automático o SQL TUNING ADVISOR para ver como puedo evitar que se infiera un plan sub-optimo.
rop@ROP102> declare
2 l_task_id varchar2(20);
3 begin
4 l_task_id := dbms_sqltune.create_tuning_task (
5 sql_text => 'select min(id)
6 from tbl_autos
7 where categoria = ''A''
8 and valor > 20000',
9 user_name => 'ROP',
10 scope => 'COMPREHENSIVE',
11 time_limit => 120,
12 task_name => 'test'
13 );
14 dbms_sqltune.execute_tuning_task ('test');
15 end;
16 /
Procedimiento PL/SQL terminado correctamente.
rop@ROP102> set serveroutput on size 999999
rop@ROP102> set long 999999
rop@ROP102> select dbms_sqltune.report_tuning_task ('test') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : test
Tuning Task Owner : ROP
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 120
Completion Status : COMPLETED
Started at : 12/07/2009 14:41:01
Completed at : 12/07/2009 14:41:01
-------------------------------------------------------------------------------
Schema Name: ROP
SQL ID : 7b5twtc2yrgsy
SQL Text : select min(id)
from tbl_autos
where categoria = 'A'
and valor > 20000
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
Se ha encontrado un plan de ejecución potencialmente mejor para esta
sentencia.
Recommendation (estimated benefit: 99.19%)
------------------------------------------
- Puede aceptar el perfil SQL recomendado.
execute dbms_sqltune.accept_sql_profile(task_name => 'test', task_owner
=> 'ROP', replace => TRUE);
Validation results
------------------
Se ha probado SQL profile ejecutando su plan y el plan original y midiendo
sus respectivas estadísticas de ejecución. Puede que uno de los planes se
haya ejecutado sólo parcialmente si el otro se ha ejecutado por completo en
menos tiempo.r
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time(ms): 19 0 100%
CPU Time(ms): 10 0 100%
User I/O Time(ms): 0 0
Buffer Gets: 248 2 99.19%
Disk Reads: 0 0
Direct Writes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 649483413
--------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------
0 SELECT STATEMENT 1 11 73 (7) 00:00:01
1 SORT AGGREGATE 1 11
* 2 TABLE ACCESS FULL TBL_AUTOS 1 11 73 (7) 00:00:01
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("VALOR">20000 AND "CATEGORIA"='A')
2- Original With Adjusted Cost
------------------------------
Plan hash value: 649483413
--------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------
0 SELECT STATEMENT 1 11 73 (7) 00:00:01
1 SORT AGGREGATE 1 11
* 2 TABLE ACCESS FULL TBL_AUTOS 1 11 73 (7) 00:00:01
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("VALOR">20000 AND "CATEGORIA"='A')
3- Using SQL Profile
--------------------
Plan hash value: 552074332
--------------------------------------------------------------------------------
----------
Id Operation Name Rows Bytes Cost (%CPU)
Time
--------------------------------------------------------------------------------
----------
0 SELECT STATEMENT 1 11 3 (0)
00:00:01
1 SORT AGGREGATE 1 11
2 TABLE ACCESS BY INDEX ROWID TBL_AUTOS 1 11 3 (0)
00:00:01
* 3 INDEX RANGE SCAN AUTOS_IDX 1 2 (0)
00:00:01
--------------------------------------------------------------------------------
----------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CATEGORIA"='A' AND "VALOR">20000 AND "VALOR" IS NOT NULL)
-------------------------------------------------------------------------------
El analizador automático sugiere aplicar un sql profile y estima una mejora de casi el 100%. Apliquemos el profile y veamos como resuelve la sentencia con esa información adicional.
1 begin
2 dbms_sqltune.accept_sql_profile(task_name => 'test',
3 task_owner => 'ROP',
4 replace => TRUE);
5* end;
rop@ROP102> /
Procedimiento PL/SQL terminado correctamente.
rop@ROP102> explain plan for select min(id)
2 from tbl_autos
3 where categoria = 'A'
4 and valor > 20000;
Explicado.
rop@ROP102> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 552074332
------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
------------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 11 3 (0) 00:00:01
1 SORT AGGREGATE 1 11
2 TABLE ACCESS BY INDEX ROWID TBL_AUTOS 1 11 3 (0) 00:00:01
* 3 INDEX RANGE SCAN AUTOS_IDX 1 2 (0) 00:00:01
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CATEGORIA"='A' AND "VALOR">20000 AND "VALOR" IS NOT NULL)
Note
-----
- SQL profile "SYS_SQLPROF_01256a3d43600000" used for this statement
Se puede observar el el optimizador ahora utilizó el profile "SYS_SQLPROF_01256a3d43600000" estimando ahora una cardinalidad de 1 (debería ser 0 (cero) pero nunca se muestra 0 a menos que se trate de un predicado con una contradicción, por ejemplo: 1=2).
A partir de 11g existen las denominadas "extended statistics" que nos permiten recolectar estadísticas del tipo function based y multi-column y minimizar los problemas de estimación de cardinalidad con filtros de columna correlacionadas.
.JPG)
FELICITACIONES!!! muye bueno su articulo y en general el blog!!!
ResponderEliminarMuchas Gracias!, los comentarios, sean buenos o malos, me incentivan a seguir escribiendo ya que me dan la pauta que las notas se leen.
ResponderEliminarPablo excelente articulo, en realidad me ha resultado de mucha ayuda!
ResponderEliminarUna pregunta ¿Puedo utilzar los SQL profiles para consultas que cuentan con variables bind?
Esto es porque tengo una consulta con este tipo de variables que cambia su plan de ejecución de manera esporadica dependiendo si el valor de la variable bind es muy alto o muy bajo.
Saludos y muchas felicidades por este blog!
Si claro, los podes usar perfectamente. De todas formas es esperable que en ciertos casos cambie el plan ya que de acuerdo con que valor "bindea" puede cambiar la selectividad de las columnas y cardinalidad resultante, por lo tanto el plan se deberá adecuar a ese cambio. Por ejemplo, si para un cierto valor de bind Oracle estima que solo debe retornar unas pocas filas, seguramente se arme un plan que use indices, por otro lado, si la cardinalidad se estima como alta (muchas filas de retorno o agregacion/agrupacion sobre todas las filas) seria mas conveniente usar full scan.
ResponderEliminar