Había concluido el upgrade de una base de datos desde Oracle 11.2 hacia 19.24, y se reinician las aplicaciones. Minutos después se reporta el primer incidente: un proceso batch aborta luego de unos instantes de iniciado.
java.sql.SQLException: ORA-01502: index 'SCOTT.EMP_IX' or partition of such index is in unusable state ORA-06512: at "SCOTT.EMP_LOAD", line 1714 ORA-06512: at line 1 Ya que esta rutina se ejecuta diariamente, esta falla es automáticamente atribuida a algún problema con la nueva versión de Oracle, así que hay que poner manos a la obra y ver de qué se trata.
¡Acompáñenme en esta interesante aventura!
El problema
Revisando el código del procedimiento almacenado EMP_LOAD, la línea 1714 que dispara el error es una en la que se ejecuta el comando ANALYZE:
INSERT INTO SCOTT.EMP SELECT . . .
EXECUTE IMMEDIATE 'ANALYZE TABLE SCOTT.EMP ESTIMATE STATISTICS';Trato de ejecutarlo directamente y logro reproducir el error:
SQL> ANALYZE TABLE SCOTT.EMP ESTIMATE STATISTICS;
ANALYZE TABLE SCOTT.EMP ESTIMATE STATISTICS
*
ERROR at line 1:
ORA-01502: index 'SCOTT.EMP_IX' or partition of such
index is in unusable state
La pregunta es: ¿por qué el índice EMP_IX está en estado UNUSABLE?
Pues resulta que esta rutina es invocada por otra (EMP_BATCH), que contiene el siguiente código:
EXECUTE IMMEDIATE 'TRUNCATE TABLE SCOTT.EMP REUSE STORAGE';
EXECUTE IMMEDIATE 'ALTER TABLE SCOTT.EMP DEALLOCATE UNUSED';
EXECUTE IMMEDIATE 'ALTER INDEX SCOTT.EMP_IX UNUSABLE';
INSERT /*+APPEND*/ INTO SCOTT.EMP SELECT . . .
EMP_LOAD;
EXECUTE IMMEDIATE 'ALTER INDEX SCOTT.EMP_IX REBUILD';
EXECUTE IMMEDIATE 'ANALYZE TABLE SCOTT.EMP ESTIMATE STATISTICS';
Se observa que en la línea 7 se invoca a la rutina que ejecuta el ANALYZE que aborta, y en la línea 10 se invoca nuevamente a ANALYZE.
Resulta que como desean hacer una carga masiva de datos, lo primero que hacen es truncar la tabla y para reducir el trabajo ponen los índices en estado UNUSABLE.
Luego de eso llenan la tabla tanto desde EMP_BATCH como desde EMP_LOAD, para finalmente reconstruir los índices y actualizar las estadísticas.
Todo estaría bien de no ser porque en EMP_LOAD también se actualizan las estadísticas y—a diferencia de versiones previas—es evidente que en Oracle 19c no está permitiendo calcularlas si existe algún índice en estado UNUSABLE.
La solución
Para empezar, el uso de ANALYZE debe ser evitado. Ya desde Oracle 8i apareció en su reemplazo el paquete DBMS_STATS y la documentación nos viene advirtiendo sobre ello.
El uso de ANALYZE para la recopilación de estadísticas del optimizador es obsoleto.
Si desea recopilar estadísticas del optimizador, utilice el paquete DBMS_STATS.
Luego de comprobar que la ejecución de DBMS_STATS no tiene problemas si existe algún índice en estado UNUSABLE, la medida inmediata es la de reemplazar toda ocurrencia de ANALYZE por DBMS_STATS.
El problema ahora es que existen 67 paquetes que contienen 1,215 llamadas a ANALYZE, por lo que un cambio manual será una labor titánica. No se trata de simplemente abrir SQL Developer, cargar el código del paquete y ejecutar un Search & Replace.
Expresiones regulares al rescate
Una inspección visual del código nos permite identificar que las llamadas a ANALYZE tienen el siguiente patrón:
EXECUTE IMMEDIATE 'ANALYZE TABLE OWNER.TABLE_NAME ESTIMATE STATISTICS';
EXECUTE IMMEDIATE 'ANALYZE TABLE OWNER.TABLE_NAME ESTIMATE STATISTICS SAMPLE 40 PERCENT';
EXECUTE IMMEDIATE 'ANALYZE TABLE OWNER.TABLE_NAME COMPUTE STATISTICS';
EXECUTE IMMEDIATE 'ANALYZE INDEX OWNER.INDEX_NAME ESTIMATE STATISTICS';
EXECUTE IMMEDIATE 'ANALYZE INDEX OWNER.INDEX_NAME COMPUTE STATISTICS';Es decir: puede invocarse para una tabla o para un índice, y en algunos casos se añade la cláusula SAMPLE.
Todo apunta a que es un escenario en el que podemos hacer uso de expresiones regulares para implementar el cambio masivo de ANALYZE por DBMS_STATS.
Si bien nunca antes había usado expresiones regulares, la premura por resolver el incidente y evitar otros que se pudieran presentar más adelante, hace inevitable investigar sobre el tema, y afortunadamente resulta que no es tan complicado.
Empezamos por definir que el patrón a identificar y transformar es el siguiente:
EXECUTE IMMEDIATE 'ANALYZE TABLE|INDEX [[:alnum:]_\$#]+\.[[:alnum:]_\$#]+ ESTIMATE|COMPUTE .+';Sí, es un jeroglífico, pero si prestan atención unos instantes trataré de ayudarles a descifrarlo:
- El texto a buscar inicia con EXECUTE IMMEDIATE ‘ANALYZE, eso es invariable.
- Luego de eso puede que se quiera procesar una tabla o un índice, ese «o» se traduce en ‘|’, de allí que se use ‘TABLE|INDEX’.
- Luego viene el nombre del esquema y el nombre de la tabla o indice, separados por un punto. Como estos nombres están limitado al uso de letras, números, ‘_’ y ‘#’, usamos ‘[[:alnum:]_\$#]+’ como el patrón de cada nombre.
- Como los caracteres ‘$’ y ‘.’ tienen un significado especial en expresiones regulares, debemos precederlas de ‘\’ para que sean interpretados literalmente.
- Como no sabemos cuántos caracteres tienen los nombes de esquema y de tabla o índice, usamos ‘+’, que quiere decir «una o más ocurrencias».
- Viene ahora ESTIMATE o COMPUTE, que traducimos por ‘ESTIMATE|COMPUTE’.
- Lo que viene después ya no nos interesa, pues con lo que hemos identificado ya estamos seguros de que se trata de un cálculo de estadísticas con ANALYZE y tenemos todos los datos para reescribirlo. Esto lo expresamos con ‘.+’ que quiere decir ‘uno o mas caracteres’.
- Finalmente tenemos ‘;’ para cerrar el texto que nos interesa procesar.
Con esto ya estamos listos para transforma el código usando un programa PL/SQL:
- Empezaremos por crear una tabla en la que obtendremos un respaldo del código que vamos a procesar.
CREATE TABLE system.ddl_analyze_objects (
object_id NUMBER,
data_object_id NUMBER,
owner VARCHAR2(128),
object_name VARCHAR2(128),
object_type VARCHAR2(23),
ddl_object CLOB,
CONSTRAINT ddl_analyze_objects_pk PRIMARY KEY ( object_id ) );- Ahora identificaremos el código almacenado (paquetes, procedimientos y funciones) que contiene líneas que siguen el patrón que hemos establecido, y usando DBMS_METADATA volcaremos el código en la tabla previamente creada. Ojo: debemos ignorar los objetos que son gestionados por Oracle.
DECLARE
ddl_tmp CLOB;
h NUMBER;
th NUMBER;
BEGIN
FOR i IN ( SELECT DISTINCT obj.object_id, obj.data_object_id,
obj.owner, obj.object_name,
DECODE( obj.object_type, 'PACKAGE BODY', 'PACKAGE_BODY', object_type) AS object_type
FROM dba_objects obj,
dba_source src
WHERE obj.owner = src.owner
AND obj.object_name = src.name
AND obj.object_type = src.type
AND obj.oracle_maintained = 'N'
AND REGEXP_LIKE( src.text, 'EXECUTE IMMEDIATE ''ANALYZE TABLE|INDEX [[:alnum:]_\$#]+\.[[:alnum:]_\$#]+ ESTIMATE|COMPUTE .+'';', 'i' )
ORDER BY obj.object_id, obj.data_object_id, obj.owner, obj.object_name, object_type
)
LOOP
h := DBMS_METADATA.open( object_type => i.object_type );
DBMS_METADATA.set_filter( handle => h, name => 'SCHEMA', value => i.owner) ;
DBMS_METADATA.set_filter( handle => h, name => 'NAME', value => i.object_name );
th := DBMS_METADATA.add_transform ( handle => h, name => 'DDL' );
ddl_tmp := DBMS_METADATA.fetch_clob( handle => h );
INSERT INTO system.ddl_analyze_objects (
object_id, data_object_id, owner, object_name, object_type, ddl_object )
VALUES (
i.object_id, i.data_object_id, i.owner, i.object_name, i.object_type, ddl_tmp
);
DBMS_METADATA.close( handle => h );
END LOOP;
COMMIT;
END;
/
- Explicar todo el código escapa al alcance de este Post, por lo que nos centraremos en el uso de REGEXP_LIKE en el predicado del query principal. El primer parámetro que requiere es la columna a evaluar, en este caso se trata de DBA_SOURCE.TEXT. El segundo parámetro es el patrón a buscar, que ya explicamos a detalle líneas arriba. El parámetro final es ‘i’, que quiere decir «ignorar si se trata de mayúsculas/minúsculas».
- Ahora procesaremos las filas de nuestra tabla DDL_ANALYZE_OBJECTS, usando REGEXP_REPLACE para reemplazar el código original que usa ANALYZE por su equivalente con DBMS_STATS.
BEGIN
FOR i IN ( SELECT owner, object_name, object_type,
REGEXP_REPLACE(
ddl_object,
'EXECUTE IMMEDIATE ''ANALYZE (TABLE|INDEX) ([[:alnum:]_\$#]+)\.([[:alnum:]_\$#]+) (ESTIMATE|COMPUTE) .+'';',
UPPER('DBMS_STATS.GATHER_\1_STATS(''\2'',''\3'');'),
1, 0, 'i'
) as ddl_object
FROM system.ddl_analyze_objects
)
LOOP
EXECUTE IMMEDIATE i.ddl_object;
END LOOP;
END;
/
Nuevamente es hora de concentrarnos para comprender qué está pasando:
- El primer parámetro que requiere REGEXP_REPLACE es la columna a transformar, en este caso se trata de DDL_OBJECT.
- El segundo parámetro es el patrón a buscar, pero notarán que hay una pequeña diferencia: los datos que nos interesan están ahora rodeados de ‘(‘ y ‘)’, esto los transforma en variables. En otras palabras ‘(TABLE|INDEX)’ se transforma en la variable ‘1’, el primer ‘([[:alnum:]_\$#]+)’ se transforma en la variable ‘2’ (o sea el nombre del esquema), y el segundo ‘([[:alnum:]_\$#]+)’ se transforma en la variable ‘3’ (o sea el nombre de la tabla o índice).
- El tercer parámetro es el patrón de transformación. Para poder referenciar a las variables mencionadas en el paso previo se deben prefijar con ‘\’.
- El cuarto parámetro indica que la evaluación debe hacerse desde el primer carácter de la columna que estamos procesando.
- El quinto parámetro indica que deseamos procesar todas las ocurrencias que hayan dentro de la columna que estamos procesando.
- El parámetro final es ‘i’, que quiere decir «ignorar si se trata de mayúsculas/minúsculas».
Resultado final
INSERT INTO SCOTT.EMP SELECT . . .
DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP');Algo similar ocurre con EMP_BATCH, que ahora contiene el siguiente código (ver línea 10):
EXECUTE IMMEDIATE 'TRUNCATE TABLE SCOTT.EMP REUSE STORAGE';
EXECUTE IMMEDIATE 'ALTER TABLE SCOTT.EMP DEALLOCATE UNUSED';
EXECUTE IMMEDIATE 'ALTER INDEX SCOTT.EMP_IX UNUSABLE';
INSERT /*+APPEND*/ INTO SCOTT.EMP SELECT . . .
EMP_LOAD;
EXECUTE IMMEDIATE 'ALTER INDEX SCOTT.EMP_IX REBUILD';
DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP');Con el cambio ya implementado, probamos a ejecutar la rutina que dio vida a este incidente y ahora culmina sin errores.
Con esto ya podemos dar por concluido el trabajo y por resuelto el incidente. Lo que hubiera representado una tarea manual—tediosa y sujeta a errores—de varios días toma ahora unos pocos segundos!
Para complementar lo aquí expuesto, especialmente sobre el uso de DBMS_METADATA, les recomiendo la lectura de las notas:
| 237293.1 | How to Move from ANALYZE to DBMS_STATS - Introduction |
| 263140.1 | How to Use Regular Expressions from Oracle 10G Onwards |
| 556823.1 | How To Use The Package DBMS_METADATA With SCHEMA_EXPORT To Extract DDLs Owned By Another User |
Una respuesta
Buen trabajo!. Un punto más a tener en cuenta en las previas al upgrade a 19c. Gracias por compartir la información.