tipo - ¿Diferente comportamiento para REF CURSOR entre Oracle 10g y 11g cuando existe un índice único?

select cursor oracle

Question

Descripción

Tengo un procedimiento almacenado de Oracle que se ha ejecutado durante más o menos 7 años, tanto localmente en instancias de desarrollo como en varias instancias de producción y prueba de clientes que ejecutan Oracle 8, luego 9, luego 10 y recientemente 11. Ha funcionado de manera consistente hasta la actualización a Oracle 11g. Básicamente, el procedimiento abre un cursor de referencia, actualiza una tabla y luego se completa. En 10g, el cursor contendrá los resultados esperados, pero en 11g el cursor estará vacío. Ningún DML o DDL cambió después de la actualización a 11g. Este comportamiento es consistente en cada instancia de 10g o 11g que he intentado (10.2.0.3, 10.2.0.4, 11.1.0.7, 11.2.0.1 - todas ejecutadas en Windows).

El código específico es mucho más complicado, pero para explicar el problema en una descripción algo realista: tengo algunos datos en una tabla de encabezado y un grupo de tablas secundarias que se enviarán a PDF. La tabla de encabezado tiene una columna booleana (NÚMERO (1) donde 0 es falso y 1 es verdadero) que indica si los datos se han procesado todavía.

La vista se limita a mostrar solo las filas que no se han procesado (la vista también se une en otras tablas, realiza algunas consultas en línea y llamadas a funciones, etc.). Entonces, en el momento en que se abre el cursor, la vista muestra una o más filas, luego, una vez que se abre el cursor, se ejecuta una instrucción de actualización para voltear la bandera en la tabla de encabezado, se emite una confirmación y luego se completa el procedimiento.

En 10g, el cursor se abre, contiene la fila, luego la instrucción de actualización voltea la bandera y ejecutar el procedimiento una segunda vez no generaría datos.

En 11g, el cursor nunca contiene la fila, es como si el cursor no se abriera hasta que se ejecuta la instrucción de actualización.

Me preocupa que algo pueda haber cambiado en 11g (esperemos que una configuración que pueda configurarse) que pueda afectar otros procedimientos y otras aplicaciones. Lo que me gustaría saber es si alguien sabe por qué el comportamiento es diferente entre las dos versiones de la base de datos y si el problema se puede resolver sin cambios en el código.

Actualización 1: logré rastrear el problema hasta una restricción única. Parece que cuando la restricción única está presente en 11g, el problema es reproducible el 100% del tiempo, independientemente de si estoy ejecutando el código del mundo real contra los objetos reales o el siguiente ejemplo simple.

Actualización 2: Pude eliminar completamente la vista de la ecuación. He actualizado el ejemplo simple para mostrar que el problema existe incluso cuando se consulta directamente en la tabla.

Ejemplo simple

CREATE TABLE tbl1
(
  col1  VARCHAR2(10),
  col2  NUMBER(1)
);

INSERT INTO tbl1 (col1, col2) VALUES (''TEST1'', 0);

/* View is no longer required to demonstrate the problem
CREATE OR REPLACE VIEW vw1 (col1, col2) 
AS 
SELECT col1, col2 
  FROM tbl1 
 WHERE col2 = 0;
*/

CREATE OR REPLACE PACKAGE pkg1
AS
   TYPE refWEB_CURSOR IS REF CURSOR;

   PROCEDURE proc1 (crs  OUT  refWEB_CURSOR);

END pkg1;

CREATE OR REPLACE PACKAGE BODY pkg1 
IS
   PROCEDURE proc1 (crs  OUT  refWEB_CURSOR)
   IS
   BEGIN

      OPEN crs FOR
        SELECT col1
          FROM tbl1
         WHERE col1 = ''TEST1''
           AND col2 = 0;

      UPDATE tbl1
         SET col2 = 1
       WHERE col1 = ''TEST1'';

      COMMIT;

   END proc1;

END pkg1;

Demostración de bloque anónimo

DECLARE 
   crs1  pkg1.refWEB_CURSOR;

   TYPE rectype1 IS RECORD (
      col1  vw1.col1%TYPE
   );

   rec1  rectype1;
BEGIN 
   pkg1.proc1 ( crs1 );

   DBMS_OUTPUT.PUT_LINE(''begin first test'');

   LOOP
      FETCH crs1
       INTO rec1;

      EXIT WHEN crs1%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(rec1.col1);

   END LOOP;  

   DBMS_OUTPUT.PUT_LINE(''end first test'');

END; 

/* After creating this index, the problem is seen */
CREATE UNIQUE INDEX unique_col1 ON tbl1 (col1);

/* Reset data to initial values */
TRUNCATE TABLE tbl1;

INSERT INTO tbl1 (col1, col2) VALUES (''TEST1'', 0);

DECLARE 
   crs1  pkg1.refWEB_CURSOR;

   TYPE rectype1 IS RECORD (
      col1  vw1.col1%TYPE
   );

   rec1  rectype1;
BEGIN 
   pkg1.proc1 ( crs1 );

   DBMS_OUTPUT.PUT_LINE(''begin second test'');

   LOOP
      FETCH crs1
       INTO rec1;

      EXIT WHEN crs1%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(rec1.col1);

   END LOOP;  

   DBMS_OUTPUT.PUT_LINE(''end second test'');

END; 

Ejemplo de lo que sería la salida en 10g:
comenzar la primera prueba
Prueba1
terminar la primera prueba
comenzar la segunda prueba
Prueba1
terminar la segunda prueba

Ejemplo de lo que sería la salida en 11g:
comenzar la primera prueba
Prueba1
terminar la primera prueba
comenzar la segunda prueba
terminar la segunda prueba

Aclaración

No puedo eliminar el COMMIT porque en el escenario del mundo real el procedimiento se llama desde una aplicación web. Cuando el proveedor de datos en el extremo delantero llama al procedimiento, emitirá un COMPROMISO implícito cuando se desconecte de la base de datos de todos modos. Entonces, si elimino el COMMIT en el procedimiento, entonces sí, la demostración anónima de bloques funcionaría, pero el escenario del mundo real no lo haría porque el COMMIT aún sucedería.

Pregunta

¿Por qué 11g se está comportando de manera diferente? ¿Hay algo que pueda hacer que no sea volver a escribir el código?

Answer 1

De Metalink (también conocido como soporte de Oracle)

Error de estado 10425196: 92 - Cerrado, no es un error

PROBLEMA:

Cuando se llama a un procedimiento almacenado que devuelve un CURSOR DE REF, se observa un comportamiento diferente en 10.2.0.5 y versiones anteriores frente a 11.1.0.6 y posteriores.

Secuencia de eventos

1. Procedimiento almacenado de llamada pasando en un cursor de referencia.
2. Cursor de referencia abierto contra la tabla A
3. Actualizar algunos datos dentro de TableA desde dentro del procedimiento almacenado
4. COMPROMETE la actualización
5. La ejecución del procedimiento termina devolviendo el cursor de referencia al llamante

10.2.0.5 y anteriores

El cursor devuelto no ve los datos actualizados tal como se abrió antes de que se actualicen los datos. Este es el comportamiento esperado.

11.1.0.6 y posteriores

El cursor devuelto ve los datos actualizados y devuelve los datos actualizados que son diferentes de 10.2.0.5 y el comportamiento anterior.

ANÁLISIS DE DIAGNÓSTICO:

10.2.0.4 Comportamiento esperado de Windows 10.2.0.5 Comportamiento esperado de Solaris 11.1.0.6 Comportamiento inesperado de Solaris 11.1.0.7 Comportamiento inesperado de Windows 11.2.0.1 Comportamiento no esperado de Solaris 11.2.0.2 Comportamiento inesperado de Solaris

ERRORES RELACIONADOS:

Nada encontrado.

Si es necesario, puede volver al comportamiento anterior a 10.2.0.5 configurando el siguiente parámetro de inicio y reiniciar la base de datos.

_row_cr = falso

Answer 2

Este es un tema extraño, gracias por compartir!

Realmente parece un cambio de comportamiento en Oracle a partir de Oracle 11.1 e incluso hay un error confirmado con un problema similar en metalink (error # 10425196). Lamentablemente, en este momento no hay mucha información disponible en Metalink sobre el tema, pero también abrí SR con Oracle pidiendo más información.

Si bien en este momento no puedo proporcionarle una explicación de por qué sucede y si hay un parámetro (oculto) que puede revertir este comportamiento al estilo de 10 g, creo que puedo proporcionarle una solución alternativa. Puede usar la funcionalidad de consulta de flashback de Oracle para obligar a Oracle a recuperar datos en el momento esperado.

Si cambias tu código de la siguiente manera:

OPEN crs FOR 
  SELECT col1
>>> FROM vw1 as of scn dbms_flashback.get_system_change_number
   WHERE col1 = ''TEST1'';

entonces el resultado debe ser el mismo que en 10g.

Y esta es la versión simplificada del caso de prueba original:

SQL> drop table tbl1;
Table dropped
SQL> create table tbl1(col1 varchar2(10), col2 number);
Table created
SQL> create unique index tbl1_idx on tbl1(col1);
Index created
SQL> insert into tbl1(col1,col2) values(''TEST1'',0);
1 row inserted
SQL> DECLARE
  2    cursor web_cursor is
  3          SELECT col1
  4            FROM tbl1
  5           WHERE col2 = 0 and col1 = ''TEST1'';
  6  
  7    rec1  web_cursor%rowtype;
  8  BEGIN
  9    OPEN web_cursor;
 10  
 11    UPDATE tbl1
 12       SET col2 = 1
 13     WHERE col1 = ''TEST1'';
 14  
 15    -- different result depending on commit!
 16    commit;
 17  
 18     DBMS_OUTPUT.PUT_LINE(''Start'');
 19     LOOP
 20        FETCH web_cursor
 21         INTO rec1;
 22  
 23        EXIT WHEN web_cursor%NOTFOUND;
 24  
 25        DBMS_OUTPUT.PUT_LINE(rec1.col1);
 26     END LOOP;
 27     DBMS_OUTPUT.PUT_LINE(''Finish'');
 28  END;
 29  /

Start
Finish

PL/SQL procedure successfully completed

Si realiza un comentario sobre la línea 16, la salida será:

Start
TEST1
Finish

PL/SQL procedure successfully completed

Answer 3

Esto parece ser un error descubierto recientemente. Metalink Bug 1045196 describe el problema exacto. Esperemos que un parche sea lanzado pronto. Para aquellos de ustedes que no pueden pasar el muro de Metalink aquí hay algunos detalles:

Metalink

Error 10425196: EL CURSOR DE REFERENCIA DE PL / SQL ACTÚA DIFERENTEMENTE EN 11.1.0.6 VS 10.2.0.5

Tipo: Defecto
Gravedad: 2 - Pérdida severa de servicio
Estado: Code Bug Creado: 22-Dic-2010

ANÁLISIS DE DIAGNÓSTICO de la presentación del caso original :
- 10.2.0.4 Comportamiento esperado de Windows
- 10.2.0.5 Comportamiento esperado de Solaris
- 11.1.0.6 Comportamiento inesperado de Solaris
- 11.1.0.7 Comportamiento inesperado de Windows
- 11.2.0.1 Comportamiento inesperado de Solaris
- 11.2.0.2 Comportamiento inesperado de Solaris

MÁS DETALLES puedo confirmar :
- 10.2.0.3 Comportamiento esperado de Windows
- 11.2.0.1 Comportamiento inesperado de Windows

Detalles adicionales

Cambiar el parámetro OPTIMIZER_FEATURES_ENABLE = ''10 .2.0.4 '' no resuelve el problema. Por lo tanto, parece estar relacionado más con un cambio de diseño en el motor de base de datos de 11g que con un pellizco optimizador.

Código de solución

Esto parece ser un resultado del uso del índice al consultar la tabla y no el acto de actualizar la tabla y / o confirmar. Usando mi ejemplo anterior, aquí hay dos formas de asegurar que la consulta no use el índice. Ambos pueden afectar el rendimiento de la consulta.

Afectar el rendimiento de la consulta puede ser temporalmente aceptable hasta que se lance un parche, pero creo que el uso de FLASHBACK como sugirió @Edgar Chupit podría afectar el rendimiento de toda la instancia (o puede que no esté disponible en algunos casos), por lo que la opción podría no estar disponible. aceptable para algunos. De cualquier manera, en este punto en el tiempo, los cambios en el código parecen ser la única solución conocida.

Método 1 : cambie su código para envolver la columna en una función para evitar que se use el índice único en esta columna. En mi caso, esto es aceptable porque, aunque la columna es única, nunca contendrá caracteres en minúscula.

    SELECT col1
      FROM tbl1
     WHERE UPPER(col1) = ''TEST1''
       AND col2 = 0;

Método 2 : cambie su consulta para usar una sugerencia que impida que se use el índice. Puede esperar que la sugerencia NO_INDEX (unique_col1) funcione, pero no lo hace. La sugerencia de REGLA no funciona. Puede usar la sugerencia FULL (tbl1) pero es probable que esto ralentice su consulta más que usando el método 1 .

    SELECT /*+ FULL(tbl1) */ col1
      FROM tbl1
     WHERE col1 = ''TEST1''
       AND col2 = 0;

Respuesta de Oracle y solución propuesta

El soporte de Oracle finalmente ha respondido con la siguiente actualización de Metalink:

Oracle Support - July 20, 2011 5:51:19 AM GMT-07:00 [ODM Proposed Solution(s)] Development has reported this will be a significant issue to fix and has suggested that the following workaround be applied: edit init.ora/spfile with the following undocumented parameter: "_row_cr" = false Oracle Support - July 20, 2011 5:49:20 AM GMT-07:00 [ODM Cause Justification] Development has determined this to be a defect Oracle Support - July 20, 2011 5:48:27 AM GMT-07:00 [ODM Cause Determination] Cause has been traced to a row source cursor optimization Oracle Support - July 20, 2011 5:47:27 AM GMT-07:00 [ODM Issue Verification] Development has confirmed this to be an issue in 11.2.0.1

Después de más correspondencia, parece que esto no se trata como un error sino como una decisión de diseño que avanza:

Oracle Support - July 21, 2011 5:58:07 AM GMT-07:00 [ODM Proposed Solution Justif] From 10.2.0.5 onward (which includes 11.2.0.2) we have an optimization called ROW CR it is only applicable to queries which use an unique index to determine the row in the table. A brief overview of this optimization is that we try to avoid rollbacks while constructing a CR block if the present block has no uncommitted changes. So the difference seen in 11.2.0.2 is because of this optimization. The suggested workaround is to turn off of this optimization so that things will work exactly as they used to work in 10.2.0.4

En nuestro caso, dados los entornos de nuestros clientes y dado que está aislado en un solo procedimiento almacenado, continuaremos usando nuestra solución de código para evitar que efectos secundarios desconocidos en toda la instancia afecten a otras aplicaciones y usuarios.