Cursor sharing, histograms y bind peeking, lo que deberías saber para evitar sorpresas.

"Si fuese a escribir un libro sobre cómo construir aplicaciones Oracle no escalables, entonces "No Use Bind Variables" sería el título del primer y último capítulo." (Thomas Kyte)

No es infrecuente toparnos con aplicaciones que hacen caso omiso del principio de usar bind variables y vemos como el desempeño se va al suelo por la elevada tasa de hard parsing que se introduce. Casi sin pensarlo nos refugiamos en el uso del parámetro cursor_sharing como nuestra única alternativa para capear el temporal, pero ¿sabías que bajo determinadas circunstancias lo único que se logra es exacerbar el problema?

Para poder comprenderlo mejor, empecemos por recordar algo de teoría sobre el parámetro cursor_sharing. De acuerdo a la documentación podemos asignarle uno de tres valores:

• Force. Fuerza a las sentencias que pueden diferir en algunos literales, pero que de otra forma son idénticas, a compartir un mismo cursor.
• Similar. Causa que las sentencias que difieren en algunos literales, pero que de otra forma son idénticas, compartan un mismo cursor, a menos que los literales afecten ya sea el significado de la sentencia o el grado al cual el plan de ejecución es optimizado.
• Exact (default). Sólo permite que sentencias con un texto idéntico compartan un mismo cursor.

Ahora, para ir viendo el efecto de ir modificando este parámetro, consideraremos una aplicación hipotética que genera sentencias de este tipo:

select avg(sal) from emp where deptno = 10
select avg(sal) from emp where deptno = 20
select avg(sal) from emp where deptno = 30
select avg(sal) from emp where deptno = 40
select avg(sal) from emp where deptno = 50

Asumiremos también que la tabla emp cuenta con un índice en la columna deptno y que hay una distribución no uniforme de sus contenidos:

SCOTT@orcl > select deptno, count(*)
2  from emp group by deptno;

DEPTNO   COUNT(*)
---------- ----------
10     200000
20          1
30          1
40          1
50          1

Finalmente nos aseguramos de contar con estadísticas actualizadas:

SCOTT@orcl > exec dbms_stats.gather_table_stats( ownname=>'SCOTT', tabname=>'EMP', cascade=>TRUE, method_opt => 'for all indexed columns size auto' )

Teniendo en cuenta que por defecto cursor_sharing=exact, al inspeccionar el Shared Pool no es sorpresa encontrar que cada sentencia de nuestra aplicación hipotética genere su propio cursor:

SYS@orcl > select sql_text, executions, child_number, plan_hash_value
2  from v$sql where sql_text like 'select max(sal)%';

SQL_TEXT                  CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE
------------------------- ------------ ---------- ---------------
select max(sal) from emp             0          1      2233664114
where deptno = 40

select max(sal) from emp             0          1      2083865914
where deptno = 10

select max(sal) from emp             0          1      2233664114
where deptno = 20

select max(sal) from emp             0          1      2233664114
where deptno = 50

select max(sal) from emp             0          1      2233664114
where deptno = 30

Si observamos con cuidado, notaremos que al consultar por deptno=10, el plan de ejecución es distinto que para los demás casos. Esto se explica por el hecho de que el histograma existente permite al optimizador deducir que un full table scan es lo óptimo, para los demás casos escoge más bien el uso del índice existente por la columna dept. Esta hipótesis es corroborada inspeccionando los planes de ejecución:

Plan hash value: 2083865914

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   283 (100)|
|   1 |  SORT AGGREGATE    |      |     1 |    16 |            |
|*  2 |   TABLE ACCESS FULL| EMP  |   202K|  3162K|   283   (4)|
----------------------------------------------------------------

Plan hash value: 2233664114

-----------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |     4 (100)|
|   1 |  SORT AGGREGATE              |         |     1 |    16 |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    16 |     4   (0)|
|*  3 |    INDEX RANGE SCAN          | EMP_DEP |     1 |       |     3   (0)|
-----------------------------------------------------------------------------

Ha llegado el momento de hacer modificaciones, el primer cambio a experimentar será cursor_sharing=force, ejecutamos los queries y volvemos a inspeccionar el Shared Pool:

SQL_TEXT                  CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE
------------------------- ------------ ---------- ---------------
select max(sal) from emp             0          5      2083865914
where deptno = :"SYS_B_0"

Lo primero que notamos es que ahora solamente se emplea un cursor, lo cual nos alienta a creer que hemos mejorado la situación, pero luego nos percatamos que el plan de ejecución corresponde al del Full Table Scan, es decir hemos trasladado el problema desde el Shared Pool hacia el Buffer Cache, aumentando la cantidad de lecturas físicas y lógicas. ¿A qué se debe esto? Ha llegado la hora de hablar de Bind Peeking.

Bind Peeking fue introducido en Oracle 9i y consiste en que el optimizador le da un vistazo a los valores de las bind variables en la primera invocación del cursor, esto le permite determinar la selectividad y escoger un plan adecuado, a partir de allí toda invocación de este cursor ya no pasa por esta inspección y el plan de ejecución es automáticamente aplicado.

Alteremos ahora el orden de los queries para corroborar la teoría, dejaremos la consulta por deptno=10 para el final y miren lo que encontramos en el Shared Pool:

SQL_TEXT                  CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE
------------------------- ------------ ---------- ---------------
select max(sal) from emp             0          5      2233664114
where deptno = :"SYS_B_0"

Todas los queries comparten el mismo cursor, pero ahora con el plan de ejecución que usa el índice por deptno.

Llegamos a la conclusión que usar cursor_sharing=force es como jugar a la ruleta rusa, dependiendo qué sentencia llegue primero al Shared Pool su plan de ejecución será el que prevalezca con resultados nada convenientes. Pero ¿y si usamos cursor_sharing=similar? ¿Será la solución mágica que estamos esperando?, pues ejecutemos nuevamente los queries e inspeccionemos el Shared Pool:

SQL_TEXT                  CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE
------------------------- ------------ ---------- ---------------
select max(sal) from emp             0          1      2083865914
where deptno = :"SYS_B_0"

select max(sal) from emp             1          1      2233664114
where deptno = :"SYS_B_0"

select max(sal) from emp             2          1      2233664114
where deptno = :"SYS_B_0"

select max(sal) from emp             3          1      2233664114
where deptno = :"SYS_B_0"

select max(sal) from emp             4          1      2233664114
where deptno = :"SYS_B_0"

Se ha creado un cursor por cada sentencia, igual que cuando cursor_sharing=exact, pero los textos son iguales y se ve que tienen bind variables, ¿qué pasó entonces? Pues con cursor_sharing=similar los cursores son compartidos siempre y cuando el optimizador considere seguro hacerlo así. Para nuestro escenario, el optimizador considera inseguro compartir los cursores debido a la existencia de un histograma para la columna deptno, para probarlo eliminamos el histograma, ejecutamos las sentencias e inspeccionamos el Shared Pool:

SCOTT@orcl > exec dbms_stats.gather_table_stats( ownname=>'SCOTT', tabname=>'EMP', cascade=>FALSE, method_opt => 'for columns deptno size 1' )

SQL_TEXT                  CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE
------------------------- ------------ ---------- ---------------
select max(sal) from emp             0          5      2083865914
where deptno = :"SYS_B_0"

Todas las sentencias comparten el mismo cursor, pero al carecer de histogramas el Optimizador no es capaz de identificar que algunas sentencias se benefician del uso del índice por deptno, y termina escogiendo Full Table Scan (FTS) al creer que hay un único valor:

SCOTT@orcl > select num_distinct, histogram from user_tab_cols
2  where table_name = 'EMP' and column_name = 'DEPTNO';

NUM_DISTINCT HISTOGRAM
------------ ---------------------------------------------
1 NONE

Si ahora borramos totalmente las estadísticas de la columna deptno, obtenemos otro resultado:

SCOTT@orcl > exec dbms_stats.delete_column_stats( ownname => 'SCOTT', tabname => 'EMP', colname=>'DEPTNO')

SQL_TEXT                  CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE
------------------------- ------------ ---------- ---------------
select max(sal) from emp             0          5      2233664114
where deptno = :"SYS_B_0"

Ahora el Optimizador opta por usar el índice por deptno, explicado por el hecho de que ahora no cuenta con información y asume que la selectividad es alta:

SCOTT@orcl > select num_distinct, histogram from user_tab_cols
2  where table_name = 'EMP' and column_name = 'DEPTNO';

NUM_DISTINCT HISTOGRAM
------------ ---------------------------------------------
NONE

Resumiendo, con cursor_sharing=similar en caso de existir histogramas los cursores no serán compartidos y al no poder recurrir a información sobre la distribución de los datos, el Optimizador puede escoger un plan poco eficiente.

Existe un detalle final que no podemos pasar por alto y es que por defecto Oracle genera las estadísticas con histogramas, lo cual ciertamente afecta el posible efecto benéfico de emplear cursor_sharing=similar.

SYS@orcl > select dbms_stats.get_param('method_opt') from dual;
DBMS_STATS.GET_PARAM('METHOD_OPT')
----------------------------------
FOR ALL COLUMNS SIZE AUTO

En conclusión, nada es mejor que desarrollar las aplicaciones haciendo uso debido de bind variables, tratar de corregir el error inicial de no usarlos resulta en una tarea larga y costosa, si creemos que es facilmente superable con el parámetro cursor_sharing, pues nos equivocamos, en el mejor de los casos es únicamente un paliativo, una aspirina con la que pretendemos dar tratamiento a un cáncer, así que a hacer un mejor seguimiento a los equipos de desarrollo de aplicaciones, y si estamos por adquirir una aplicacion empaquetada, a revisar el código fuente, no vaya a ser que terminemos adquiriendo un problema en lugar de una solución.

Si deseas profundizar en el tema, revisa los Notes: 377847.1 Unsafe Peeked Bind Variables and Histograms, 261020.1 High Version Count with CURSOR_SHARING = SIMILAR or FORCE, 296377.1 Troubleshooting Guide to high version_counts, 430208.1 Bind Peeking By Example y 369427.1 Case Study: The Mysterious Performance Drop.

Posts Relacionados:

• ¿Cómo deshacernos de un cursor en el Shared Pool?
• ¿Query dinámico?, pero sin perder de vista el contexto
• El query dinámico de los pobres

¿Te pareció interesante este artículo?, ¿te quedaron algunas dudas?, ¿quieres sugerirme un tema a tratar?, pues déjame tus comentarios o envíame un email y para que NO te pierdas ningún Post, suscríbete por email ahora mismo!