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.
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 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. |
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 = 50SCOTT@orcl> select deptno, count(*)
from emp group by deptno;
DEPTNO COUNT(*)
---------- ----------
10 200000
20 1
30 1
40 1
50 1SCOTT@orcl> exec dbms_stats.gather_table_stats( ownname=>'SCOTT', tabname=>'EMP' )SYS@orcl> select sql_text, executions,
child_number, plan_hash_value
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 = 30Si 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 DEPTNO.
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)|
-----------------------------------------------------------------------------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"SCOTT@orcl> select num_distinct, histogram
from user_tab_cols
where table_name = 'EMP'
and column_name = 'DEPTNO';
NUM_DISTINCT HISTOGRAM
------------ ------------
1 NONESCOTT@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"SCOTT@orcl> select num_distinct, histogram
from user_tab_cols
where table_name = 'EMP'
and column_name = 'DEPTNO';
NUM_DISTINCT HISTOGRAM
------------ -----------------
NONEResumiendo, 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 AUTOEn 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 este problema es fácilmente 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.
Si estamos por adquirir una aplicación empaquetada, mejor empezar 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 las notas:
| 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 |
| 369427.1 | Case Study: The Mysterious Performance Drop |