Mi primer encuentro con un problema serio de tuning sucedió en el año 2000.
Me encontraba trabajando para una empresa que tenía instalado un ERP (si es quue así se le podía llamar). Este "ERP" constaba de 4 aplicaciones individuales con muchas interfaces para compartir información.
El sistema que presentaba problemas de desempeño era System ESS de Industri-Matematik Inc. Todo el problema estaba relacionado con el proceso batch nocturno, en la parte de reservas de órdenes. El proceso que debía de durar aproximadamente 2 horas, tomaba más de 6 horas, y estos tiempos impactaban a las interfaces para integrar la información con el sistema financiero (Oracle Applications).
Las bases de datos de los sistemas eran Oracle, versiones 8.1.6.3 y se tenía en modo reglas el optimizador (RBO).
Recuerdo que habíamos más de 10 DBA's intentando ver el problema de desempeño. Recuerdo haber tenido mi primer acercamiento con el trace de Oracle y el comando oradebug para activarlo y ver los eventos de espera.
SQL> oradebug setospid
SQL> oradebug event 10046 trace name context forever, level 8;
Este comando lo que me generó fue un archivo de 10 mb (tamaño bobernado por el parámetro max_dumpfile_size). El contenido del archivo lo único que incluía eran líneas muy similares a
WAIT #1: nam=’db file sequential read’ ela= 1 p1=4 p2=1352 p3=1Lo primero que hice fue investigar lo que "db file sequential read" significaba...
Todo lo que encontraba referente a esta espera, iba relacionado a la lectura de un sólo bloque de datos, seguramente un acceso a través de un índice. Logré ver la sentencia SQL que me generó los 10m de trace y el plan de ejecución mostraba el acceso a la tabla de órdenes a través de un índice que quizá no era el mejor índice para acceder a la tabla.
Como yo venía de tomar el curso de DBA's de 8i, y había aprendido sobre la existencia de índices de Bitmap, y al ver que la sentencia SQL accedía a la tabla de órdenes a través del campo de status, y que status tenía una muy baja Cardinalidad... a esto sumada mi poca experiencia... pues decidí crear un índice de bitmap....
Al revisar el plande ejecución nuevamente, observé que mi índice no era tomado en cuenta!!!
Acto seguido, intenté poner lo que yo creía era un hint para obligar al optimizador de costos a tomar mi súper-índice... Pero oh sorpresa, mi índice nuevamente fue rechazado, no fue tomado en cuenta, sin embargo el plan de ejecución se veía diferente y al ejecutar el Query, el tiempo para un caso en particular bajó de 5 minutos a 1 segundo!!!! Pero qué fue lo que realmente sucedió?
Mi hint era incorrecto, porque no sabía usar hints y lo único que hice fue:
select /*+INDEX(nombre_indice) */ ...
Digamos que mi error fue no haber usado el alias de la tabla (o el nombre según sea el caso) para indicar que sobre esa tabla se debe de usar el índice correcto. Al tener la solución rápida (con el índice que sin querer funcionó) me dediqué a hacer el hint de forma correcta con el índice correcto y borré mi índice de bitmap (por fortuna).
Pero hasta muchos años después realmente me di cuenta de lo que realmente sucedió.
tengamos como ejemplo lo siguiente:
CREATE TABLE "USER"."A"
("CAMPO1" VARCHAR2(1),
"CAMPO2" VARCHAR2(1),
"CAMPO3" VARCHAR2(1),
"CAMPO4" VARCHAR2(1),
PRIMARY KEY ("CAMPO1")
USING INDEX);
CREATE INDEX "USER"."IDXA"
ON "USER"."A"("CAMPO2",
"CAMPO3", "CAMPO4");
Una vez teniendo la tabla y la estructura creada, alteramos el modo del optimizador para simular el modo reglas de mi 8.1.6.3 (estas pruebas ya son sobre 10.2.0.3, pero el funcionamiento del punto a explicar es el mismo).
SQL> ALTER SESSION SET optimizer_mode = rule;
Sesión modificada.
SQL> SET autotrace traceonly EXPLAIN;
SQL> select * from a a1, a a2
2 where a1.campo1= a2.campo1
3 and a2.campo2 =1;
Plan de Ejecución
----------------------------------------------------------
Plan hash value: 3288122215
----------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
|* 2 | TABLE ACCESS FULL | A |
| 3 | TABLE ACCESS BY INDEX ROWID| A |
|* 4 | INDEX UNIQUE SCAN | SYS_C005988 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER("A2"."CAMPO2")=1)
4 - access("A1"."CAMPO1"="A2"."CAMPO1")
Note
-----
- rule based optimizer used (consider using cbo)
Recuerden que el optimizador de la base de datos está en modo Reglas. Pero, ¿qué sucede al poner un hint erróneo (o correcto)?.
SQL> select /*+INDEX (lo_que_sea)*/ *
2 from a a1, a a2
3 where a1.campo1= a2.campo1
4* and a2.campo2 =1
Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1585513397
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 64 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 4 | 64 | 5 (20)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| A | 4 | 32 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| A | 5 | 40 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A1"."CAMPO1"="A2"."CAMPO1")
2 - filter(TO_NUMBER("A2"."CAMPO2")=1)
Automáticamente el optimizador del RDBMS cambia a modo Costos, alterando de forma dramática nuestro plan de ejecución!!!
Por eso siempre he dicho que lo único que se necesita para mejorar el desempeño en una aplicación, son ganas. Acepto que mi primer tuning fue como el burro que tocó la flauta. Pero fue una solución que le permitió al negocio funcionar adecuadamente y a mi me permitió interesarme mucho sobre el tema de mejora en el desempeño con oracle en general.
