sql - leer - long oracle

Rendimiento de SUBSTR en CLOB

Question

Tengo un procedimiento PL / SQL que hace muchos SUBSTR en un parámetro VARCHAR2 . Me gustaría eliminar el límite de longitud, así que intenté cambiarlo a CLOB .

Funciona bien, pero el rendimiento se resiente, así que hice algunas pruebas (basadas en estas pruebas de 2005).

ACTUALIZACIÓN : Puedo reproducir esto en varias instancias diferentes con diferentes versiones de Oracle y hardware diferente, dbms_lob.substr siempre se nota más lento que substr(CLOB) y mucho más lento que SUBSTR(VARCHAR2) .

Los resultados de Bob y las pruebas en el enlace anterior cuentan una historia diferente.

¿Alguien puede explicar esto, o al menos reproducir los resultados de Bob o mis resultados? ¡Gracias!

Resultados de la prueba:

+000000000 00:00:00. 004000000 (VARCHAR2)
+000000000 00:00:00. 298000000 (CLOB SUBSTR)
+000000000 00:00:00. 356000000 (DBMS_LOB.SUBSTR)

Código de prueba:

DECLARE
  l_text   VARCHAR2(30) := ''This is a test record'';
  l_clob   CLOB := l_text;
  l_substr VARCHAR2(30);
  t TIMESTAMP;
BEGIN
  t := SYSTIMESTAMP;
  FOR i IN 1..100000 LOOP
    l_substr := SUBSTR(l_text,1,14);
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || '' (VARCHAR2)'');

  t := SYSTIMESTAMP;
  FOR i IN 1..100000 LOOP
    l_substr := SUBSTR(l_clob,1,14);
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || '' (CLOB SUBSTR)'');

  t := SYSTIMESTAMP;
  FOR i IN 1..100000 LOOP
    l_substr := DBMS_LOB.SUBSTR(l_clob,14,1);
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || '' (DBMS_LOB.SUBSTR)'');
END;

Answer 1

(Mentiras, malditas mentiras, y puntos de referencia ...)

Repetí la prueba 10 veces, ampliando la cadena para que tuviera una longitud total de 30 caracteres y obtuve los siguientes resultados promediados:

+000000000 00:00:00.011694200 (VARCHAR2)
+000000000 00:00:00.901000600 (CLOB SUBSTR)
+000000000 00:00:00.013169200 (DBMS_LOB.SUBSTR)

Luego cambié el rango de subcadenas a 5,14 (14,5 para DBMS_LOB.SUBSTR) y obtuve:

+000000000 00:00:00.011731000 (VARCHAR2)
+000000000 00:00:01.010840000 (CLOB SUBSTR)
+000000000 00:00:00.011427000 (DBMS_LOB.SUBSTR)

Luego cambié el rango a 17,14 (14,17 para DBMS_LOB.SUBSTR) y obtuve

+000000000 00:00:00.013578900 (VARCHAR2)
+000000000 00:00:00.964527400 (CLOB SUBSTR)
+000000000 00:00:00.011416800 (DBMS_LOB.SUBSTR)

Finalmente, cambié el rango a 25,14 (14,25 para DBMS_LOB.SUBSTR) y obtuve

+000000000 00:00:00.011210200 (VARCHAR2)
+000000000 00:00:00.916439800 (CLOB SUBSTR)
+000000000 00:00:00.013781300 (DBMS_LOB.SUBSTR)

Mi conclusión es que cuando se trabaja contra CLOB es mejor usar DBMS_LOB.SUBSTR ya que parece no tener una penalización de rendimiento en comparación con el uso de SUBSTR contra un VARCHAR2 "normal". SUBSTR contra un CLOB parece sufrir una penalización de rendimiento significativa. Para el registro - OS = HP / UX (variante de Unix), Oracle version = 11.1, processor = HP Itanium 2-plex. YMMV.

Comparte y Disfruta.

Y porque si vale la pena hacerlo, vale la pena exagerar, aquí hay algunos resultados más con las cadenas expandidas a 32767 caracteres. Rangos de subcadena dados con cada conjunto de resultados:

1, 25000
+000000000 00:00:00.198466400 (VARCHAR2)
+000000000 00:00:02.870958700 (CLOB SUBSTR)
+000000000 00:00:00.174490100 (DBMS_LOB.SUBSTR)

1000, 25000
+000000000 00:00:00.253447900 (VARCHAR2)
+000000000 00:00:02.491790500 (CLOB SUBSTR)
+000000000 00:00:00.193560100 (DBMS_LOB.SUBSTR)

10000, 25000
+000000000 00:00:00.217812000 (VARCHAR2)
+000000000 00:00:02.268794800 (CLOB SUBSTR)
+000000000 00:00:00.222200200 (DBMS_LOB.SUBSTR)

Mismo día, misma conclusión.

Cthulhu fhtagn.

(Una vez más a la brecha, queridos amigos, una vez más ...)

Vuelva a ejecutar los puntos de referencia, cambiando el tamaño del CLOB a 3276700, y tomando la subcadena desde el medio a partir de 2475000 para una longitud de 25000, obtengo:

+000000000 00:00:00.176883200 (VARCHAR2)
+000000000 00:00:02.069482600 (CLOB SUBSTR)
+000000000 00:00:00.175341500 (DBMS_LOB.SUBSTR)

(Tenga en cuenta que los cambios solo afectan a las dos últimas pruebas).

Y ... mismos resultados, día diferente.

YMMV.

Answer 2

Ejecute su script tres veces en el siguiente sistema:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Producción de 64 bits

Aquí están los resultados:

+000000000 00:00:00.007787000 (VARCHAR2)
+000000000 00:00:03.093258000 (CLOB SUBSTR)
+000000000 00:00:00.340017000 (DBMS_LOB.SUBSTR)

+000000000 00:00:00.019460000 (VARCHAR2)
+000000000 00:00:03.302425000 (CLOB SUBSTR)
+000000000 00:00:00.336915000 (DBMS_LOB.SUBSTR)

+000000000 00:00:00.007773000 (VARCHAR2)
+000000000 00:00:03.210619000 (CLOB SUBSTR)
+000000000 00:00:00.336689000 (DBMS_LOB.SUBSTR)

Answer 3

Sé que esto es muy antiguo, pero aún puede ser relevante para las personas en sistemas más antiguos. Esto parece un problema de conversión de tipo de datos. Basado en algo que noté al observar el efecto que vio @ bernhard.weingartner, el tipo de datos de los argumentos de compensación y cantidad parece hacer una gran diferencia.

Esto se ejecuta en 11.2.0.3 en Linux (OEL 5.6) y se incrementa a un millón de iteraciones solo para hacer las diferencias aún más obvias:

DECLARE
  l_text   VARCHAR2(30) := ''This is a test record'';
  l_clob   CLOB := l_text;
  l_substr VARCHAR2(30);
  t TIMESTAMP;
BEGIN
  t := SYSTIMESTAMP;
  FOR i IN 1..1000000 LOOP
    l_substr := SUBSTR(l_text,1,14);
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || '' (VARCHAR2)'');

  t := SYSTIMESTAMP;
  FOR i IN 1..1000000 LOOP
    l_substr := SUBSTR(l_clob,1,14);
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || '' (CLOB SUBSTR)'');

  t := SYSTIMESTAMP;
  FOR i IN 1..1000000 LOOP
    l_substr := DBMS_LOB.SUBSTR(l_clob,14,1);
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || '' (DBMS_LOB.SUBSTR with 14,1)'');

  t := SYSTIMESTAMP;
  FOR i IN 1..1000000 LOOP
    l_substr := DBMS_LOB.SUBSTR(l_clob,14.0,1.0);
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || '' (DBMS_LOB.SUBSTR with 14.0,1.0)'');

  t := SYSTIMESTAMP;
  FOR i IN 1..1000000 LOOP
    l_substr := DBMS_LOB.SUBSTR(l_clob,cast(14 as number), cast(1 as number));
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || '' (DBMS_LOB.SUBSTR with casts)'');
END;
/

+000000000 00:00:00.043019000 (VARCHAR2)
+000000000 00:00:03.671621000 (CLOB SUBSTR)
+000000000 00:00:05.704337000 (DBMS_LOB.SUBSTR with 14,1)
+000000000 00:00:00.040097000 (DBMS_LOB.SUBSTR with 14.0,1.0)
+000000000 00:00:00.040907000 (DBMS_LOB.SUBSTR with casts)

Los documentos 11gR2 muestran los parámetros formales como tipo INTEGER, pero en realidad pasar un entero (o pls_integer o binary_double) es lento, mientras que pasar un número explícitamente es rápido.

De su pregunta original y los resultados de Bob, esto parece algo que cambió entre 11.1 y 11.2. No tengo una instancia 12c para probar, así que no sé si ha cambiado de nuevo. No está claro si se debe a un cambio en dbms_lob o un cambio más amplio en la forma en que PL / SQL maneja los valores numéricos por defecto. No he encontrado nada en MOS que parezca relevante.

Answer 4

Veo que en 11gR1 las pruebas se ejecutaron sin problemas para DBMS_LOB.substr, pero para 11gR2 la función es lenta.

Debajo de mi prueba en Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production en AIX6.

+000000000 00:00:00.009440000 (VARCHAR2)
+000000000 00:00:00.749113000 (CLOB SUBSTR)
+000000000 00:00:01.177685000 (DBMS_LOB.SUBSTR)