Criando Aplicativos Escaláveis

Projete seus aplicativos para usar os mesmos recursos, independentemente de populações de usuários e volumes de dados, e não para sobrecarregar recursos do sistema.

Sobre Aplicativos Escaláveis

Um aplicativo escalável pode processar uma carga de trabalho maior com um aumento proporcional no uso de recursos do sistema.

Um aplicativo escalável pode processar uma carga de trabalho maior com um aumento proporcional no uso de recursos do sistema. Por exemplo, se você duplicar sua carga de trabalho, um aplicativo escalável usará o dobro de recursos do sistema.

Um aplicativo não escalável esgota um recurso do sistema; portanto, se você aumentar a carga de trabalho do aplicativo, não será possível mais throughput. Aplicativos não escaláveis resultam em throughputs fixos e tempos de resposta ruins.

Exemplos de esgotamento de recursos são:

Projete seus aplicativos para usar os mesmos recursos, independentemente de populações de usuários e volumes de dados, e não para sobrecarregar recursos do sistema.

Usando Variáveis de Bind para Melhorar a Escalabilidade

Vincular variáveis, usadas corretamente, permite que você desenvolva aplicativos eficientes e escaláveis.

Uma variável de bind é um espaço reservado em uma instrução SQL que deve ser substituída por um valor ou endereço de valor válido para que a instrução seja executada com sucesso. Usando variáveis de bind, você pode gravar uma instrução SQL que aceite entradas ou parâmetros no run-time.

Assim como um subprograma pode ter parâmetros, cujos valores são fornecidos pelo chamador, uma instrução SQL pode ter placeholders de variáveis de bind, cujos valores (chamados variáveis de bind) são fornecidos no runtime. Assim como um subprograma é compilado uma vez e executado muitas vezes com parâmetros diferentes, uma instrução SQL com placeholders de variáveis de ligação é analisada uma vez e, em seguida, analisada com variáveis de ligação diferentes.

Um hard parse, que inclui otimização e geração de origem de linha, é uma operação com uso intenso de CPU. Uma parsing suave, que ignora a otimização e a geração de origem de linha e prossegue diretamente para a execução, geralmente é muito mais rápida do que uma análise difícil da mesma instrução. (Para obter uma visão geral do processamento de SQL, que inclui a diferença entre um hard e soft parse, consulte Conceitos do Oracle Database.)

Não é apenas uma operação com uso intenso de CPU, é uma operação não escalável, porque não pode ser feita simultaneamente com muitas outras operações. Para obter mais informações sobre simultaneidade e escalabilidade, consulte "Sobre simultaneidade e escalabilidade".

Exemplo 8-1 mostra a diferença de desempenho entre uma consulta sem uma variável de bind e uma consulta semanticamente equivalente com uma variável de bind. O primeiro é mais lento e usa muito mais travas (para obter informações sobre como as travas afetam a escalabilidade, consulte). Para coletar e exibir estatísticas de desempenho, o exemplo usa a ferramenta Runstats, descrita em "Comparing Programming Techniques with Runstats".

Observação:

Observação:

Embora o soft parsing seja mais eficiente do que o hard parsing, o custo do soft parsing de uma instrução muitas vezes ainda é muito alto. Para maximizar a eficiência e a escalabilidade do seu aplicativo, minimize a análise. A maneira mais fácil de minimizar a análise é usar PL/SQL.

Exemplo 8-1 Variável de Bind Melhora o Desempenho

CREATE TABLE t ( x VARCHAR2(5) );

DECLARE
  TYPE rc IS REF CURSOR;
  l_cursor rc;
BEGIN
  runstats_pkg.rs_start;  -- Collect statistics for query without bind variable

  FOR i IN 1 .. 5000 LOOP
    OPEN l_cursor FOR 'SELECT x FROM t WHERE x = ' || TO_CHAR(i);
    CLOSE l_cursor;
  END LOOP;

  runstats_pkg.rs_middle;  -- Collect statistics for query with bind variable

  FOR i IN 1 .. 5000 LOOP
    OPEN l_cursor FOR 'SELECT x FROM t WHERE x = :x' USING i;
    CLOSE l_cursor;
  END LOOP;

  runstats_pkg.rs_stop(500);  -- Stop collecting statistics
end;
/

O resultado é semelhante ao seguinte texto:

Run 1 ran in 740 hsec
Run 2 ran in 30 hsec
Run 1 ran in 
2466.67% of the time of run 2

Name                                   Run 1         Run 2    Difference
STAT...recursive cpu usage               729            19          -710
STAT...CPU used by this sessio           742            30          -712
STAT...parse time elapsed              1,051             4        -1,047
STAT...parse time cpu                  1,066             2        -1,064
STAT...session cursor cache hi             1         4,998         4,997
STAT...table scans (short tabl         5,000             1        -4,999
STAT...parse count (total)            10,003         5,004        -4,999
LATCH.session idle bit                 5,003             3        -5,000
LATCH.session allocation               5,003             3        -5,000
STAT...execute count                  10,003         5,003        -5,000
STAT...opened cursors cumulati        10,003         5,003        -5,000
STAT...parse count (hard)             10,001             5        -9,996
STAT...CCursor + sql area evic        10,000             1        -9,999
STAT...enqueue releases               10,008             7       -10,001
STAT...enqueue requests               10,009             7       -10,002
STAT...calls to get snapshot s        20,005         5,006       -14,999
STAT...calls to kcmgcs                20,028            35       -19,993
STAT...consistent gets pin (fa        20,013            17       -19,996
LATCH.call allocation                 20,002             6       -19,996
STAT...consistent gets from ca        20,014            18       -19,996
STAT...consistent gets                20,014            18       -19,996
STAT...consistent gets pin            20,013            17       -19,996
LATCH.simulator hash latch            20,014            11       -20,003
STAT...session logical reads          20,080            75       -20,005
LATCH.shared pool simulator           20,046             5       -20,041
LATCH.enqueue hash chains             20,343            15       -20,328
STAT...recursive calls                40,015        15,018       -24,997
LATCH.cache buffers chains            40,480           294       -40,186
STAT...session pga memory max        131,072        65,536       -65,536
STAT...session pga memory            131,072        65,536       -65,536
LATCH.row cache objects              165,209           139      -165,070
STAT...session uga memory max        219,000             0      -219,000
LATCH.shared pool                    265,108           152      -264,956
STAT...logical read bytes from   164,495,360       614,400  -163,880,960

Run 1 latches total compared to run 2 -- difference and percentage
         Run 1         Run 2          Diff       Pct
       562,092           864      -561,228  2,466.67%

PL/SQL procedure successfully completed.

Uso de PL/SQL para Melhorar a Escalabilidade

Certos recursos de PL/SQL podem ajudar a melhorar a escalabilidade do aplicativo.

Como o PL/SQL Minimiza a Análise

O PL/SQL, que é otimizado para acesso ao banco de dados, armazena em cache silenciosamente as instruções. No PL/SQL, quando você fecha um cursor, ele é fechado de sua perspectiva, ou seja, não é possível usá-lo onde um cursor aberto é necessário, mas o PL/SQL realmente mantém o cursor aberto e armazena em cache sua instrução.

Se você usar a instrução em cache novamente, o PL/SQL usará o mesmo cursor, evitando assim uma análise. (O PL/SQL fecha instruções armazenadas em cache, se necessário, por exemplo, se o programa tiver que abrir outro cursor, mas isso excederá a definição init.ora de OPEN_CURSORS.)

O código PL/SQL pode armazenar em cache silenciosamente apenas instruções SQL que não podem ser alteradas no runtime.

Sobre a instrução EXECUTE IMMEDIATE

A instrução EXECUTE IMMEDIATE cria e executa uma instrução SQL dinâmica em uma única operação.

A sintaxe básica da instrução EXECUTE IMMEDIATE é:

EXECUTE IMMEDIATE sql_statement

sql_statement é uma string que representa uma instrução SQL. Se sql_statement tiver o mesmo valor toda vez que a instrução EXECUTE IMMEDIATE for executada, o PL/SQL poderá armazenar em cache a instrução EXECUTE IMMEDIATE. Se o sql_statement puder ser diferente toda vez que a instrução EXECUTE IMMEDIATE for executada, o PL/SQL não poderá armazenar em cache a instrução EXECUTE IMMEDIATE.

Consulte também:

Sobre Instruções OPEN FOR

A instrução OPEN FOR tem a seguinte sintaxe básica.

A sintaxe básica da instrução OPEN FOR é:

OPEN cursor_variable FOR query

Seu aplicativo pode abrir cursor_variable para várias consultas diferentes antes de fechá-lo. Como o código PL/SQL não pode determinar o número de consultas diferentes até o runtime, o código PL/SQL não pode armazenar em cache a instrução OPEN FOR.

Se você não precisar usar uma variável de cursor, use um cursor declarado para obter melhor desempenho e facilidade de programação. Para obter detalhes, consulte o Oracle Database Development Guide.

Consulte também:

Sobre o Pacote DBMS_SQL

O pacote DBMS_SQL é uma API para criar, executar e descrever instruções SQL dinâmicas. Você deve usar o pacote DBMS_SQL em vez da instrução EXECUTE IMMEDIATE se o compilador PL/SQL não puder determinar no momento da compilação o número ou os tipos de variáveis de host de saída (selecionar itens de lista) ou variáveis de bind de entrada.

O pacote DBMS_SQL é uma API para criar, executar e descrever instruções SQL dinâmicas. O uso do pacote DBMS_SQL exige mais esforço do que o uso da instrução EXECUTE IMMEDIATE, mas você deverá usar o pacote DBMS_SQL se o compilador PL/SQL não puder determinar no momento da compilação o número ou os tipos de variáveis de host de saída (selecionar itens de lista) ou variáveis de bind de entrada.

Consulte também:

Sobre SQL em Massa

O uso de SQL em massa reduz o número de "round trips" entre o PL/SQL e o SQL, usando menos recursos.

Sem SQL em massa, você recupera uma linha por vez do banco de dados (SQL), processa-a (PL/SQL) e a retorna ao banco de dados (SQL). Com SQL em massa, você recupera um conjunto de linhas do banco de dados, processa o conjunto de linhas e retorna o conjunto inteiro para o banco de dados.

A Oracle recomenda o uso de SQL em Massa quando você recupera várias linhas do banco de dados e as retorna ao banco de dados, como no Exemplo 8-2. Você não precisará de SQL em massa se recuperar várias linhas, mas não as retornar; por exemplo:

FOR x IN (SELECT * FROM t WHERE ... )  -- Retrieve row set (implicit array fetch)
  LOOP
    DBMS_OUTPUT.PUT_LINE(t.x);          -- Process rows but do not return them
  END LOOP;

Exemplo 8-2 faz loops em uma tabela t com uma coluna object_name, recuperando conjuntos de 100 linhas, processando-as e retornando-as ao banco de dados. (Limitar a instrução FETCH em massa a 100 linhas requer um cursor explícito.)

O Exemplo 8-3 faz o mesmo trabalho do Exemplo 8-2, sem SQL em massa.

Como esses relatórios TKPROF para o Exemplo 8-2 e o Exemplo 8-3 são mostrados, o uso de SQL em massa para esse job usa quase 50% menos tempo de CPU:

SELECT ROWID RID, OBJECT_NAME FROM T T_BULK

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      721      0.17       0.17          0      22582          0       71825
********************************************************************************
UPDATE T SET OBJECT_NAME = :B1 WHERE ROWID = :B2

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    719     12.83      13.77          0      71853      74185       71825
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      720     12.83      13.77          0      71853      74185       71825


SELECT ROWID RID, OBJECT_NAME FROM T T_SLOW_BY_SLOW

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      721      0.17       0.17          0      22582          0       71825
********************************************************************************
UPDATE T SET OBJECT_NAME = :B2 WHERE ROWID = :B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  71824     21.25      22.25          0      71836      73950       71824
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    71825     21.25      22.25          0      71836      73950       71824

No entanto, o uso de SQL em massa para este job usa mais tempo de CPU e mais código do que o uso de uma única instrução SQL, pois este relatório TKPROF mostra:

UPDATE T SET OBJECT_NAME = SUBSTR(OBJECT_NAME,2) || SUBSTR(OBJECT_NAME,1,1)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      1.30       1.44          0       2166      75736       71825
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      1.30       1.44          0       2166      75736       71825

Exemplo 8-2 SQL em Massa

CREATE OR REPLACE PROCEDURE bulk AS
  TYPE ridArray IS TABLE OF ROWID;
  TYPE onameArray IS TABLE OF t.object_name%TYPE;

  CURSOR c is SELECT ROWID rid, object_name  -- explicit cursor
              FROM t t_bulk;

  l_rids    ridArray;
  l_onames  onameArray;
  N         NUMBER := 100;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT
    INTO l_rids, l_onames LIMIT N;   -- retrieve N rows from t

    FOR i in 1 .. l_rids.COUNT
      LOOP                           -- process N rows
        l_onames(i) := substr(l_onames(i),2) || substr(l_onames(i),1,1);
      END LOOP;

      FORALL i in 1 .. l_rids.count  -- return processed rows to t
        UPDATE t
        SET object_name = l_onames(i)
        WHERE ROWID = l_rids(i);
        EXIT WHEN c%NOTFOUND;
  END LOOP;
  CLOSE c;
END;
/

Exemplo 8-3 Sem SQL em Massa

CREATE OR REPLACE PROCEDURE slow_by_slow AS
BEGIN
  FOR x IN (SELECT rowid rid, object_name FROM t t_slow_by_slow)
    LOOP
      x.object_name := substr(x.object_name,2) || substr(x.object_name,1,1);

      UPDATE t
      SET object_name = x.object_name
      WHERE rowid = x.rid;
    END LOOP;
END;

Consulte também:

Sobre Simultaneidade e Escalabilidade

Concorrência é a execução simultânea de várias transações. Quanto melhor seu aplicativo lidar com a simultaneidade, mais escalável ele será. Um aplicativo escalável pode processar uma carga de trabalho maior com um aumento proporcional no uso de recursos do sistema.

Concorrência é a execução simultânea de várias transações. Os demonstrativos em transações simultâneas podem atualizar os mesmos dados. Quanto melhor seu aplicativo lidar com a simultaneidade, mais escalável ele será. Um aplicativo escalável pode processar uma carga de trabalho maior com um aumento proporcional no uso de recursos do sistema. Por exemplo, se você duplicar sua carga de trabalho, um aplicativo escalável usará o dobro de recursos do sistema.

As transações simultâneas devem produzir resultados significativos e consistentes. Portanto, um banco de dados multiusuário deve fornecer o seguinte:

O Oracle Database mantém a consistência dos dados usando um modelo de consistência multiversão e vários tipos de bloqueios e níveis de isolamento de transações. Para obter uma visão geral do mecanismo de bloqueio do Oracle Database, consulte Oracle Database Concepts. Para obter uma visão geral dos níveis de isolamento de transações do Oracle Database, consulte Oracle Database Concepts.

Para descrever o comportamento consistente da transação quando as transações são executadas simultaneamente, os pesquisadores de banco de dados definiram uma categoria de isolamento de transação chamada serializável. Uma transação serializável opera em um ambiente que parece ser um banco de dados de usuário único. Transações serializáveis são desejáveis em casos específicos, mas para 99% da carga de trabalho, o isolamento de leitura comprometida é perfeito.

O Oracle Database tem recursos que melhoram a simultaneidade e a escalabilidade, por exemplo, sequências, latches, leituras e gravações sem bloqueio e SQL compartilhado.

Consulte Também: Oracle Database Concepts para obter mais informações sobre simultaneidade e consistência dos dados

Sobre sequências e simultaneidade

As sequências eliminam a serialização, melhorando assim a simultaneidade e a escalabilidade do seu aplicativo.

sequência é um objeto de esquema do qual vários usuários podem gerar números inteiros exclusivos, o que é muito útil quando você precisa de chaves primárias exclusivas.

Sem sequências, valores exclusivos de chave primária devem ser produzidos de forma programática. Um usuário obtém um novo valor de chave primária selecionando o valor produzido mais recentemente e incrementando-o. Essa técnica exige um bloqueio durante a transação e faz com que vários usuários aguardem o próximo valor de chave primária, ou seja, as transações são serializadas. As sequências eliminam a serialização, melhorando assim a simultaneidade e a escalabilidade do seu aplicativo.

Consulte também:

Sobre Latches e simultaneidade

Um aumento nas travas significa mais esperas baseadas em simultaneidade e, portanto, uma diminuição na escalabilidade.

Um latch é um mecanismo de serialização simples e de baixo nível que coordena o acesso de vários usuários a estruturas de dados compartilhadas. As travas protegem os recursos de memória compartilhada contra danos quando acessados por vários processos.

Um aumento nas travas significa mais esperas baseadas em simultaneidade e, portanto, uma diminuição na escalabilidade. Se você puder usar uma abordagem que seja um pouco mais rápida durante o desenvolvimento ou que use menos travas, use a última.

Consulte também:

Sobre Leituras e Gravações sem Bloqueio e Concorrência

No Oracle Database, leituras e gravações sem bloqueio permitem que as consultas sejam executadas simultaneamente com alterações nos dados que estão lendo, sem bloqueio ou interrupção. Leituras e gravações sem bloqueio permitem que uma sessão leia dados enquanto outra sessão está alterando esses dados.

Sobre SQL Compartilhado e Simultaneidade

O Oracle Database compila uma instrução SQL em um objeto executável uma vez e, em seguida, outras sessões podem reutilizar o objeto enquanto ele existir. Esse recurso do Oracle Database, chamado SQL compartilhado, permite que o banco de dados execute operações muito intensivas em recursos, compilando e otimizando instruções SQL apenas uma vez, em vez de toda vez que uma sessão usa a mesma instrução SQL.

Consulte Também: Conceitos do Oracle Database para obter mais informações sobre SQL compartilhado

Limitando o Número de Sessões Simultâneas

Quanto mais sessões simultâneas você tiver, mais esperas baseadas em simultaneidade você terá e menor será o tempo de resposta.

Se o seu computador tiver n núcleos de CPU, no máximo n sessões poderão estar ativas simultaneamente. Cada sessão "concorrente" adicional deve aguardar que um núcleo de CPU esteja disponível para que possa se tornar ativo. Se algumas sessões em espera estiverem aguardando apenas E/S, aumentar o número de sessões simultâneas para um pouco mais de n poderá melhorar um pouco o desempenho do runtime. No entanto, aumentar demais o número de sessões simultâneas reduzirá significativamente o desempenho do runtime.

O parâmetro de inicialização SESSIONS determina o número máximo de usuários simultâneos no sistema. Para obter detalhes, consulte o Oracle Database Reference.

Veja Também: http://www.youtube.com/watch?v=xNDnVOCdvQ0 para um vídeo que mostra o efeito de reduzir o número de sessões simultâneas em um computador com 12 núcleos de CPU de milhares para 96

Comparando Técnicas de Programação com Runstats

A ferramenta Runstats permite comparar o desempenho de duas técnicas de programação para ver qual é melhor.

Sobre Runstats

A ferramenta Runstats permite comparar o desempenho de duas técnicas de programação para ver qual é melhor.

Runstats mede os seguintes valores:

Das medições anteriores, a mais importante é a trava (consulte "Sobre Travas e Simultaneidade").

Consulte Também: Exemplo 8-1, que usa Runstats

Definindo Runstats

A ferramenta Runstats é implementada como um pacote que usa uma view e uma tabela temporária.

Observação: Para a etapa 1 do seguinte procedimento, você precisa do privilégio SELECT nas exibições de desempenho dinâmico V$STATNAME, V$MYSTAT e V$LATCH. Se você não puder obter esse privilégio, tenha alguém que tenha o privilégio para criar a view na etapa 1 e conceder a você o privilégio SELECT nela.

Etapas para configurar a ferramenta Runstats:

  1. Crie a view que os Runstats usam:

     CREATE OR REPLACE VIEW stats
 AS SELECT 'STAT...' || a.name name, b.value
 FROM V$STATNAME a, V$MYSTAT b
 WHERE a.statistic# = b.statistic#
 UNION ALL
 SELECT 'LATCH.' || name, gets
 FROM V$LATCH;

  2. Crie a tabela temporária que o Runstats utiliza:

     DROP TABLE run_stats;

 CREATE GLOBAL TEMPORARY TABLE run_stats
 ( runid VARCHAR2(15),
   name VARCHAR2(80),
   value INT )
 ON COMMIT PRESERVE ROWS;

  3. Crie esta especificação do pacote:

     CREATE OR REPLACE PACKAGE runstats_pkg
 AS
   PROCEDURE rs_start;
   PROCEDURE rs_middle;
   PROCEDURE rs_stop( p_difference_threshold IN NUMBER DEFAULT 0 );
 end;
 /

    O parâmetro p_difference_threshold controla a quantidade de estatísticas e os dados de trava que os Runstats exibem. Runstats exibe dados somente quando a diferença para as duas técnicas é maior que p_difference_threshold. Por padrão, Runstats exibe todos os dados.

  4. Crie o seguinte corpo de pacote:

     CREATE OR REPLACE PACKAGE BODY runstats_pkg
 AS
   g_start NUMBER;
   g_run1 NUMBER;
   g_run2 NUMBER;

   PROCEDURE rs_start
   IS
   BEGIN
     DELETE FROM run_stats;

     INSERT INTO run_stats
     SELECT 'before', stats.* FROM stats;

     g_start := DBMS_UTILITY.GET_TIME;
   END rs_start;

   PROCEDURE rs_middle
   IS
   BEGIN
     g_run1 := (DBMS_UTILITY.GET_TIME - g_start);

     INSERT INTO run_stats
     SELECT 'after 1', stats.* FROM stats;

     g_start := DBMS_UTILITY.GET_TIME;
   END rs_middle;

   PROCEDURE rs_stop( p_difference_threshold IN NUMBER DEFAULT 0 )
   IS
   BEGIN
     g_run2 := (DBMS_UTILITY.GET_TIME - g_start);

     DBMS_OUTPUT.PUT_LINE
       ('Run 1 ran in ' || g_run1 || ' hsec');

     DBMS_OUTPUT.PUT_LINE
       ('Run 2 ran in ' || g_run2 || ' hsec');

     DBMS_OUTPUT.PUT_LINE
       ('Run 1 ran in ' || round(g_run1/g_run2*100, 2) || '% of the time of run 2');

     DBMS_OUTPUT.PUT_LINE( CHR(9) );

     INSERT INTO run_stats
     SELECT 'after 2', stats.* FROM stats;

     DBMS_OUTPUT.PUT_LINE
       ( RPAD( 'Name', 30 ) ||
         LPAD( 'Run 1', 14) ||

         LPAD( 'Run 2', 14) ||

         LPAD( 'Difference', 14)
       );

     FOR x IN
     ( SELECT RPAD( a.name, 30 ) ||
             TO_CHAR( b.value - a.value, '9,999,999,999' ) ||
             TO_CHAR( c.value - b.value, '9,999,999,999' ) ||
             TO_CHAR( ( (c.value - b.value) - (b.value - a.value)),
               '9,999,999,999' ) data
       FROM run_stats a, run_stats b, run_stats c
       WHERE a.name = b.name
         AND b.name = c.name
         AND a.runid = 'before'
         AND b.runid = 'after 1'
         AND c.runid = 'after 2'
         AND (c.value - a.value) > 0
         AND abs((c.value - b.value) - (b.value - a.value)) >
           p_difference_threshold
     ORDER BY ABS((c.value - b.value) - (b.value - a.value))
     ) LOOP
         DBMS_OUTPUT.PUT_LINE( x.data );
     END LOOP;

     DBMS_OUTPUT.PUT_LINE( CHR(9) );

     DBMS_OUTPUT.PUT_LINE(
       'Run 1 latches total compared to run 2 -- difference and percentage' );

     DBMS_OUTPUT.PUT_LINE
       ( LPAD( 'Run 1', 14) ||

         LPAD( 'Run 2', 14) ||

         LPAD( 'Diff', 14) ||

         LPAD( 'Pct', 10)
       );

     FOR x IN
     ( SELECT TO_CHAR( run1, '9,999,999,999' ) ||
             TO_CHAR( run2, '9,999,999,999' ) ||
             TO_CHAR( diff, '9,999,999,999' ) ||
             TO_CHAR( ROUND( g_run1/g_run2*100, 2), '99,999.99' ) || '%' data
       FROM ( SELECT SUM (b.value - a.value) run1,
                     SUM (c.value - b.value) run2,
                     SUM ( (c.value - b.value) - (b.value - a.value)) diff
             FROM run_stats a, run_stats b, run_stats c
             WHERE a.name = b.name
               AND b.name = c.name
               AND a.runid = 'before'
               AND b.runid = 'after 1'
               AND c.runid = 'after 2'
               AND a.name like 'LATCH%'
           )
     ) LOOP
         DBMS_OUTPUT.PUT_LINE( x.data );
     END LOOP;

   END rs_stop;

 END;
 /

Consulte também:

Usando Runstats

Este tópico fornece a sintaxe para usar a ferramenta Runstats.

Para usar Runstats a fim de comparar duas técnicas de programação, chame os procedimentos runstats_pkg a partir de um bloco anônimo, usando esta sintaxe:

[ DECLARE local_declarations ]
BEGIN
  runstats_pkg.rs_start;
  code_for_first_technique
  runstats_pkg.rs_middle;
  code_for_second_technique
  runstats_pkg.rs_stop(n);
END;
/

Consulte Também: Exemplo 8-1, que usa Runstats

Técnicas de Processamento de Dados e Desempenho do Mundo Real

Uma tarefa comum em aplicativos de banco de dados em um ambiente de data warehouse é consultar ou modificar um grande conjunto de dados. O problema para os desenvolvedores de aplicativos é como obter alto desempenho ao processar grandes conjuntos de dados.

As técnicas de processamento se enquadram em duas categorias: iterativa e baseada em conjuntos. Ao longo de anos de testes, o grupo Real-World Performance descobriu que técnicas de processamento baseadas em conjuntos têm um desempenho de ordens de magnitude melhor para aplicações de banco de dados que processam grandes conjuntos de dados.

Este tópico inclui os seguintes subtópicos principais:

Sobre o Processamento de Dados Iterativos

No processamento iterativo, os aplicativos usam lógica condicional para percorrer um conjunto de linhas.

Normalmente, embora não necessariamente, o processamento iterativo usa um modelo cliente/servidor da seguinte forma:

  1. Transfira um grupo de linhas do servidor de banco de dados para o aplicativo cliente.

  2. Processe o grupo dentro do aplicativo cliente.

  3. Transfira o grupo processado de volta para o servidor de banco de dados.

Você pode implementar algoritmos iterativos usando três técnicas principais: processamento linha por linha, processamento de matriz e paralelismo manual.

Processamento Iterativo: Linha por Linha

No processamento linha por linha, um único processo percorre um conjunto de dados e opera em uma única linha por vez. Em uma implementação típica, o aplicativo recupera cada linha do banco de dados, a processa na camada intermediária e, em seguida, a envia de volta ao banco de dados, que executa DML e commits.

Suponha que seu requisito funcional seja consultar uma tabela externa chamada ext_scan_events e, em seguida, inserir suas linhas em uma tabela temporária organizada por heap chamada stage1_scan_events. O seguinte bloco PL/SQL usa uma técnica linha a linha para atender a esse requisito:

declare
  cursor c is select s.* from ext_scan_events s;
  r c%rowtype;
begin
  open c;
  loop
    fetch c into r;
    exit when c%notfound;
    insert into stage1_scan_events d values r;
    commit;
  end loop;
  close c;
end;

A técnica file-by-row tem as seguintes vantagens:

A técnica file-by-row tem as seguintes desvantagens:

Consulte Também: RWP #7 Processamento Baseado em Conjunto

Processamento Iterativo: Matrizes

O processamento de matrizes é idêntico ao processamento linha a linha, exceto pelo fato de processar um grupo de linhas em cada iteração em vez de uma única linha.

Suponha que sua exigência funcional seja a mesma do Exemplo X-X: consulte uma tabela externa chamada ext_scan_events e insira suas linhas em uma tabela temporária organizada por heap chamada stage1_scan_events. O seguinte bloco PL/SQL usa uma técnica de matriz para atender a esse requisito:

declare
  cursor c is select s.* from ext_scan_events s;
  type t is table of c%rowtype index by binary_integer;
  a t;
  rows binary_integer := 0;
begin
  open c;
  loop
    fetch c bulk collect into a limit array_size;
    exit when a.count = 0;
    forall i in 1..a.count
      insert into stage1_scan_events d values a(i);
    commit;
  end loop;
  close c;
end;

O código anterior difere do código linha por linha equivalente ao usar um operador BULK COLLECT no FETCH STATEMENT, que é limitado pelo valor array_size do tipo PLS_INTEGER. Por exemplo, se array_size estiver definido como 100, o aplicativo extrairá linhas em grupos de 100.

A técnica de matriz tem as seguintes vantagens sobre a técnica linha a linha:

As desvantagens dessa técnica são as mesmas do processamento linha a linha. O tempo de processamento pode ser inaceitável para grandes conjuntos de dados. Além disso, o aplicativo deve ser executado em série em um único núcleo de CPU e, portanto, não pode explorar o paralelismo nativo do Oracle Database.

Processamento Iterativo: Paralelismo Manual

O paralelismo manual usa o mesmo algoritmo iterativo que o processamento linha por linha e matriz, mas permite que vários processos do servidor dividam o trabalho e sejam executados em paralelo.

Suponha que a exigência funcional seja a mesma dos exemplos de linha por linha e matriz. As principais diferenças são as seguintes:

O seguinte código PL/SQL usa paralelismo manual:

declare
  sqlstmt varchar2(1024) := q'[
-- BEGIN embedded anonymous block
  cursor c is select s.* from ext_scan_events_${thr} s;
  type t is table of c%rowtype index by binary_integer;
  a t;
  rows binary_integer := 0;
begin
  for r in (select ext_file_name from ext_scan_events_dets where ora_hash(file_seq_nbr,${thrs}) = ${thr})
  loop
    execute immediate
      'alter table ext_scan_events_${thr} location' || '(' || r.ext_file_name || ')';
    open c;
    loop
      fetch c bulk collect into a limit ${array_size};
      exit when a.count = 0;
      forall i in 1..a.count
        insert into stage1_scan_events d values a(i);
      commit;
--  demo instrumentation
      rows := rows + a.count; if rows > 1e3 then exit when not sd_control.p_progress('loading','userdefined',rows); rows := 0; end if;
    end loop;
    close c;
  end loop;
end;
-- END   embedded anonymous block
]';

begin
  sqlstmt := replace(sqlstmt, '${array_size}', to_char(array_size));
  sqlstmt := replace(sqlstmt, '${thr}', thr);
  sqlstmt := replace(sqlstmt, '${thrs}', thrs);
  execute immediate sqlstmt;
end;

A função ORA_HASH divide a tabela ext_scan_events_dets em 32 buckets distribuídos uniformemente e, em seguida, a instrução SELECT recupera os nomes de arquivo do bucket 0. Para cada nome de arquivo no bloco, o programa define o local da tabela externa para esse nome de arquivo. Em seguida, o programa utiliza o processamento em batch para consultar a tabela externa, inserir na tabela temporária e, em seguida, efetuar commit.

Enquanto o job 1 está sendo executado, os outros 31 jobs do Oracle Scheduler são executados em paralelo. Dessa forma, cada job lê simultaneamente um subconjunto diferente dos arquivos de evento de varredura e insere os registros de seu subconjunto na mesma tabela intermediária.

A técnica de paralelismo manual tem as seguintes vantagens sobre as técnicas iterativas alternativas:

A técnica de paralelismo manual tem as seguintes desvantagens:

Veja também: RWP #8: Processamento Paralelo Baseado em Conjunto

Sobre o Processamento Baseado em Conjunto

O processamento baseado em conjunto é uma técnica SQL que processa um conjunto de dados dentro do banco de dados.

Em um modelo baseado em conjunto, a instrução SQL define o resultado e permite que o banco de dados determine a maneira mais eficiente de obtê-lo. Por outro lado, os algoritmos iterativos usam a lógica condicional para extrair cada linha ou grupo de linhas do banco de dados para o aplicativo cliente, processar os dados no cliente e enviar os dados de volta para o banco de dados. O processamento baseado em conjunto elimina a sobrecarga da API de rede e de banco de dados porque os dados nunca saem do banco de dados.

Considere a mesma exigência funcional dos exemplos anteriores. As seguintes instruções SQL atendem a esse requisito usando um algoritmo baseado em conjunto:

alter session enable parallel dml;
insert /*+ APPEND */ into stage1_scan_events d
  select s.* from ext_scan_events s;
commit;

Como a instrução INSERT contém uma subconsulta da tabela ext_scan_events, uma instrução SQL única lê e grava todas as linhas. Além disso, o aplicativo executa um único COMMIT depois que o banco de dados insere todas as linhas. Por outro lado, os aplicativos iterativos executam um COMMIT depois da inserção de cada linha ou de cada grupo de linhas.

A técnica baseada em conjuntos tem vantagens significativas sobre as técnicas iterativas:

O processamento baseado em conjunto tem algumas desvantagens potenciais:

Consulte Também: RWP #7 Processamento Baseado em Conjunto, RWP #8: Processamento Paralelo Baseado em Conjunto, RWP #9: Processamento Baseado em Conjunto – Desduplicação de Dados, RWP #10: Processamento Baseado em Conjunto – Transformações de Dados e RWP #11: Processamento Baseado em Conjunto – Agregação de Dados