Pergunta sobre clob, sql, oracle, oracle11g, plsql – Desempenho do SUBSTR no CLOB

25

Eu tenho um procedimento PL / SQL que faz um monte deSUBSTRs em umVARCHAR2 parâmetro. Eu gostaria de remover o limite de comprimento, então eu tentei mudá-lo paraCLOB.

Funciona bem, mas o desempenho sofre, então eu fiz alguns testes (com base emesses testes desde 2005).

ATUALIZAR: Eu posso reproduzir isso em várias instâncias diferentes com diferentes versões do Oracle e hardware diferente,dbms_lob.substr é sempre perceptível mais devagar do quesubstr(CLOB)e muito mais lento queSUBSTR(VARCHAR2).

Os resultados de Bob e os testes no link acima contam uma história diferente.

Alguém pode explicar isso, ou pelo menos reproduzir os resultados de Bob ou meus? Obrigado!

Resultado dos testes:

+000000000 00:00:00.004000000 (VARCHAR2)
+000000000 00:00:00.298000000 (CLOB SUBSTR)
+000000000 00:00:00.356000000 (DBMS_LOB.SUBSTR)

Código de teste:

<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>
Nota: O teste três é14,1 onde os outros estão1,14. Eu também testaria algo como10000, 5000 como o ponto é que você está olhando para quebrar o limite de 4K de VARCHAR. Além disso, como os resultados são cerca de 75x mais lentos para não VARCHAR, você consegue ver um algoritmo que lida com vários VARCHARs?[Tal como uma tabela normalizada onde um campo é o 'sequence_id' mostrando a posição relativa desta string, e o outro é o VARCHAR]. Finalmente, embora haja grandesrelativo diferença, oabsoluto a diferença é baixa. Então, isso importa?[Pré-otimização] MatBailie
AIX 6 (não sei o menor ... 2 ou 3). É uma máquina IBM de 128 núcleos. que sistema operacional e máquina você tem? Florin Ghita
Em minhas máquinas DBMS_LOB.SUBSTR é um pouco mais lenta que CLOB_SUBSTR (20%). E ambos os mooore são mais lentos que o varchar2 (70 vezes mais lento). Eu corro em 11gR2. Florin Ghita
@FlorinGhita: Obrigado! Qual SO você está usando, caso isso faça alguma diferença? Peter Lang
@Dems: Obrigado pela sua contribuição! o14,1 e1,14 está correto (obrigado Oracle pelas APIs consistentes). Estou tentando quebrar o32767 Byte limit (PL / SQL, não SQL) e os resultados são mais ou menos os mesmos quando se usa texto com esse comprimento(LPAD('X', 32767, 'X')). Pensei nessa solução de várias tabelas varchar, mas gostaria de evitá-la :) E isso importa, já que o procedimento é chamado com muita frequência, mas acima de tudo estou curioso para saber se existem alternativas ... Peter Lang

Sua resposta

4   a resposta
2

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Produção de 64 bits

Aqui estão os 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>
Obrigado! Você se importaria de compartilhar qual sistema operacional você usa, caso isso faça alguma diferença? Peter Lang
@PeterLang Claro que sim. Redhat Enterprise Linux: 2.6.9-67.ELsmp tp9
2

mas para 11gR2 a função é lenta.

Abaixo meu teste emOracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production no AIX6.

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

Droga mentiras e benchmarks ...)

Voltei a executar o teste 10 vezes, expandindo a cadeia por um total de 30 caracteres e obtendo os seguintes resultados médios:

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

Em seguida, alterei o intervalo de substring para 5,14 (14,5 para DBMS_LOB.SUBSTR) e obtive:

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

Em seguida, mudei o intervalo para 17,14 (14,17 para DBMS_LOB.SUBSTR) e obtive

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

Finalmente, mudei o intervalo para 25,14 (14,25 para DBMS_LOB.SUBSTR) e obtive

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

Minha conclusão é que, quando se trabalha contra o CLOB, é melhor usar o DBMS_LOB.SUBSTR, já que parece não ter efetivamente nenhuma penalidade de desempenho comparado ao uso do SUBSTR contra um VARCHAR2 "normal". SUBSTR contra um CLOB parece sofrer uma penalidade significativa no desempenho. Para o registro - OS = HP / UX (variante Unix), versão Oracle = 11.1, processador = HP Itanium 2-plex. YMMV.

Compartilhe e curta.

E se vale a pena fazer algo que vale a pena fazer, aqui estão mais alguns resultados com as strings expandidas para 32767 caracteres. Intervalos de subcadeia fornecidos com 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>

Mesmo dia, mesma conclusão.

Cthulhu fhtagn.

(Mais uma vez até a brecha, queridos amigos, mais uma vez ...)

Corrija os benchmarks, alterando o tamanho do CLOB para 3276700, e pegando a substring a partir do meio a partir de 2475000 para o comprimento 25000.

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

(Observe que as alterações afetam apenas os dois últimos testes).

E ... os mesmos resultados, dia diferente.

YMMV.

@PeterLang - testes adicionados para grandes (3 meg) CLOB. Mesmos resultados Bob Jarvis
Meus comentários sobre possíveis diferenças entre OCILobCopy e OCILobCopy2 foram apenas suposições de minha parte, não de fatos. Eu vejo que @ tp9 postou alguns resultados que são aproximadamente os mesmos que eu encontrei. Ele também usou 11.1. Eu gostaria de ter um 11.2 DB para testar. Bob Jarvis
Eu duvido que isso seja apenas relacionado ao hardware. Como eu disse no meu primeiro comentário, meus dois primeiros testes são mais rápidos que os seus, mas o terceiro é significativamente mais lento ... Peter Lang
Oracle 11.1. E sim, os resultados dos testes são repetíveis. Eu mudei os loops para iterar 1 milhão de vezes cada, depois dividi o intervalo de tempo resultante em 10 para torná-los mais ou menos comparáveis ​​aos testes originais. Eu corri os testes várias vezes e cada resultado foi comparável. Bob Jarvis
2

mas ainda pode ser relevante para pessoas em sistemas mais antigos. Isso parece um problema de conversão de tipo de dados. Baseado em algo que notei olhando para o efeito @ bernhard.weingartner, o tipo de dados dos argumentos offset e amount parece fazer uma enorme diferença.

Isso é executado no 11.2.0.3 no Linux (OEL 5.6) e aumentou para um milhão de iterações apenas para tornar as diferenças ainda mais óbvias:

<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>

o11gR2 docs mostre os parâmetros formais como type INTEGER, mas passar um inteiro (ou pls_integer ou binary_double) é lento, enquanto explicitamente passar um número é rápido.

De sua pergunta original e dos resultados de Bob, isso parece algo que mudou entre 11.1 e 11.2. Eu não tenho uma instância 12c para testar, então não sei se foi alterado novamente. Seja devido a uma mudança nadbms_lob ou uma mudança mais ampla na forma como o PL / SQL manipula valores numéricos por padrão não está claro. Eu não encontrei nada no MOS que pareça relevante.

Perguntas relacionadas