Usando OUTLINES en 10g

Hace unas semanas estuve trabajando en una migración de 8i a 10g para un banco. Si bien se había realizado un análisis intensivo de impacto en las sentencias Top hubo algunas que no se consideraron y luego de la migración con la carga online se comenzaron a degradar. Dado que la reescritura de las sentencias implicaba un cambio de código mayor se optó por usar outlines para fijar el plan a dos de las sentencias con problemas.
Antes de mostrarles un ejemplo de uso me gustaría aclarar que desde 10g se pueden usar advisors, en particular el dbms_sqltune que generan propuestas de mejoras de sentencias. Una de las propuestas podria ser agregar un indice, una vista materializada o un profile. Los profiles son muy interesantes ya que pueden verse como una evolución de los outlines o los hints. Con los profiles lo que se hace es darle mayor información al optimizador para que conozca mas sobre al correlación de los datos y proponga un mejor plan de ejecución. Ya en otro nota explicaré mas detalle de los profiles.
Ahora les voy a mostrar un ejemplo de uso de outlines.

Primero, cree una tabla T copia de la dba_objects, luego hice un update a la T del campo object_id y cambie el 90% de las filas poniendo como object_id = 99. Con esa distribucion, el optimizador por costos en all_rows (default en 10g) va por un full scan y por regla va por el indice. Es claro que dado que tiene que procesar el 90% de las filas el path mas adecuado deberia ser por FullScan. Lo que hice es forzar al optimizador CBO de 10g para que vaya por indice igual. Abajo les pegue el detalle:



SQL*Plus: Release 10.2.0.4.0 - Production on Mar Ene 20 10:38:35 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

-- Primero muestro cual es el plan elegido por el cbo de 10g

rop@DESA10G> set autotr trace exp
rop@DESA10G> select count(distinct object_name) from t where object_id = 99;

Plan de Ejecución
----------------------------------------------------------

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    79 |   777 |
|   1 |  SORT GROUP BY     |      |     1 |    79 |       |
|   2 |   TABLE ACCESS FULL| T    | 16985 |  1310K|   777 |
-----------------------------------------------------------

-- Preparo la sesion para que use RBO y por lo tanto use otro plan

rop@DESA10G> alter session set optimizer_mode = rule;

-- Ahora activo para grabar el outline el plan generado con RBO

rop@DESA10G> alter session set USE_STORED_OUTLINES = true;

Sesión modificada.

rop@DESA10G> select count(distinct object_name) from t where object_id = 99;

Plan de Ejecución
----------------------------------------------------------

----------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost  |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |    79 |    53 |
|   1 |  SORT GROUP BY               |       |     1 |    79 |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| T     |  1649 |   127K|    53 |
|   3 |    INDEX RANGE SCAN          | T_IDX |   660 |       |     1 |
----------------------------------------------------------------------

-- Como vemos se eligio ir por el indice

rop@DESA10G> alter session set USE_STORED_OUTLINES = false;

Sesión modificada.

-- Vuelvo a poner el modo del optimizador en su valor default

rop@DESA10G> alter session set optimizer_mode = all_rows;

Sesión modificada.

rop@DESA10G> select count(distinct object_name) from t where object_id = 99;

Plan de Ejecución
----------------------------------------------------------

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    79 |   777 |
|   1 |  SORT GROUP BY     |      |     1 |    79 |       |
|   2 |   TABLE ACCESS FULL| T    | 16985 |  1310K|   777 |
-----------------------------------------------------------

-- Vemos que usa el full scan

- Ahora activamos el outline

rop@DESA10G> alter session set USE_STORED_OUTLINES = true;

Sesión modificada.

rop@DESA10G> select count(distinct object_name) from t where object_id = 99;

Plan de Ejecución
----------------------------------------------------------

----------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost  |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |    79 |    53 |
|   1 |  SORT GROUP BY               |       |     1 |    79 |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| T     |  1649 |   127K|    53 |
|   3 |    INDEX RANGE SCAN          | T_IDX |   660 |       |     1 |
----------------------------------------------------------------------

Podemos observar que se uso el plan guardado en los outlines

rop@DESA10G> set autotr off
rop@DESA10G> select * from dba_outlines;

NAME                           OWNER                          CATEGORY                       USED
------------------------------ ------------------------------ ------------------------------ ------
TIMESTAMP VERSION
--------- ----------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
SIGNATURE                        COMPATIBLE   ENABLED  FORMAT
-------------------------------- ------------ -------- ------
SYS_OUTLINE_09012010432243201  ROP                            DEFAULT                        USED
20-ENE-09 10.2.0.1.0
select count(distinct object_name) from t where object_id = 99
C7ABDCAEF65EDD55840EBEC9D9AC70C3 COMPATIBLE   ENABLED  NORMAL

-- En la consulta anterior vemos que se uso el outline almacenado con el plan fijado.