Pregunta sobre plsql, oracle11g, clob, sql, oracle – Rendimiento de SUBSTR en CLOB

25

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

Funciona bien, pero el rendimiento se resiente, así que hice algunas pruebas (basadas enestas pruebas desde 2005).

ACTUALIZAR: Puedo reproducir esto en varias instancias diferentes con diferentes versiones de Oracle y hardware diferente,dbms_lob.substr Siempre se nota más lento quesubstr(CLOB), y mucho mas lento queSUBSTR(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:

<code>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;
</code>
En mis máquinas, DBMS_LOB.SUBSTR es un poco más lento que CLOB_SUBSTR (20%). Y ambos más despacio que varchar2 (70 veces más lento). Corro en 11gR2. Florin Ghita
AIX 6 (no sé el menor ... 2 o 3). Es una máquina IBM de 128 cores. ¿Qué sistema operativo y máquina tienes? Florin Ghita
@FlorinGhita: ¡Gracias! ¿Qué sistema operativo está utilizando, en caso de que esto haga una diferencia? Peter Lang
@Dems: Gracias por tu aporte! los14,1 y1,14 es correcto (gracias a Oracle por las APIs consistentes). Estoy tratando de romper el32767 Límite de bytes (PL / SQL, no SQL), y los resultados son más o menos los mismos cuando se usa texto con esa longitud(LPAD('X', 32767, 'X')). He pensado en esa solución de tabla múltiple-varchar, pero me gustaría evitarla :) Y sí importa, ya que el procedimiento se llama muy a menudo, pero sobre todo tengo curiosidad si hay alternativas ... Peter Lang
Nota: la prueba tres es14,1 donde los otros estan1,14. También probaría algo como10000, 5000 como el punto es que estás buscando romper el límite de 4k de VARCHAR. Además, como los resultados son aproximadamente 75x más lentos para los que no son VARCHAR, ¿puedes ver un algoritmo que se ocupa de varios VARCHAR?[Por ejemplo, una tabla normalizada en la que un campo es el 'id de secuencia' que muestra la posición relativa de esta cadena y el otro es el VARCHAR]. Finalmente, aunque hay grandesrelativo diferencia, laabsoluto la diferencia es baja Entonces, ¿importa?[Pre-optimización] MatBailie

Tu respuesta

4   la respuesta
2

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

Aquí están los resultados:

<code>+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)
</code>
¡Gracias! ¿Te importaría compartir qué sistema operativo usas, en caso de que esto haga una diferencia? Peter Lang
@PeterLang Claro. Redhat Enterprise Linux: 2.6.9-67.ELsmp tp9
17

Maldita sea 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:

<code>+000000000 00:00:00.011694200 (VARCHAR2)
+000000000 00:00:00.901000600 (CLOB SUBSTR)
+000000000 00:00:00.013169200 (DBMS_LOB.SUBSTR)
</code>

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

<code>+000000000 00:00:00.011731000 (VARCHAR2)
+000000000 00:00:01.010840000 (CLOB SUBSTR)
+000000000 00:00:00.011427000 (DBMS_LOB.SUBSTR)
</code>

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

<code>+000000000 00:00:00.013578900 (VARCHAR2)
+000000000 00:00:00.964527400 (CLOB SUBSTR)
+000000000 00:00:00.011416800 (DBMS_LOB.SUBSTR)
</code>

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

<code>+000000000 00:00:00.011210200 (VARCHAR2)
+000000000 00:00:00.916439800 (CLOB SUBSTR)
+000000000 00:00:00.013781300 (DBMS_LOB.SUBSTR)
</code>

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), versión de Oracle = 11.1, procesador = 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:

<code>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)
</code>

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:

<code>+000000000 00:00:00.176883200 (VARCHAR2)
+000000000 00:00:02.069482600 (CLOB SUBSTR)
+000000000 00:00:00.175341500 (DBMS_LOB.SUBSTR)
</code>

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

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

YMMV.

@BobJarvis: Me sorprendería si esto hiciera una gran diferencia, pero quién sabe ... ¿Tiene alguna referencia para estos hallazgos? Solo veo esoOCILobCopy está en desuso, y esoOCILobCopy2 debe utilizarse para LOB de tamaño superior a 4 GB. Peter Lang
Eso es raro. Probé mis pruebas en tres instancias diferentes (11.2 en Windows, 11.2 en Linux y 10.2 en Linux), y los resultados son más o menos los mismos que en mi publicación. Sería genial tener más resultados de otras personas ... Peter Lang
@PeterLang - Noté que en los documentos de OCI hay dos versiones de OCILobCopy, que son OCILobCopy y OCILobCopy2, con la regla de usar la versión "2" de cualquier función OCI si tal función existe porque las versiones "2" Son mejores / más rápidos / mejorados / más capaces.Quizás "sistema operativo básico" (p. ej., Windows, Linux, etc.) Oracle usa una versión mientras que las versiones "sistema operativo comercial" (p. ej., para z / OS, HP-UX, etc.) usa una versión diferente, o una usa código C mientras que la otra utiliza ensamblador optimizado a mano. ??? YMMV Bob Jarvis
Mis comentarios sobre posibles diferencias entre OCILobCopy y OCILobCopy2 fueron solo suposiciones de mi parte, no hechos. Veo que @ tp9 publicó algunos resultados que son aproximadamente los mismos que he encontrado. También utilizó 11.1. Me gustaría tener un DB de 11.2 para probar. Bob Jarvis
2

.substr, pero para 11gR2 la función es lenta.

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

<code>+000000000 00:00:00.009440000 (VARCHAR2)
+000000000 00:00:00.749113000 (CLOB SUBSTR)
+000000000 00:00:01.177685000 (DBMS_LOB.SUBSTR)
</code>
2

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:

<code>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;
/
</code>
<code>+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)
</code>

los11gR2 documentos muestre 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. Ya sea debido a un cambio endbms_lob o un cambio más amplio en la forma en que PL / SQL maneja los valores numéricos por defecto no está claro. No he encontrado nada en MOS que parezca relevante.

Preguntas relacionadas