Creazione di applicazioni scalabili

Progetta le tue applicazioni per utilizzare le stesse risorse, indipendentemente dalle popolazioni di utenti e dai volumi di dati, e non per sovraccaricare le risorse di sistema.

Informazioni sulle applicazioni scalabili

Un'applicazione scalabile può elaborare un carico di lavoro più ampio con un aumento proporzionale dell'uso delle risorse di sistema.

Un'applicazione scalabile può elaborare un carico di lavoro più ampio con un aumento proporzionale dell'uso delle risorse di sistema. Ad esempio, se si raddoppia il carico di lavoro, un'applicazione scalabile utilizza il doppio delle risorse di sistema.

Un'applicazione non scalabile esaurisce una risorsa di sistema; pertanto, se si aumenta il carico di lavoro dell'applicazione, non è possibile aumentare il throughput. Le applicazioni scalabili generano throughput fissi e tempi di risposta scadenti.

Esempi di esaurimento delle risorse sono:

Progetta le tue applicazioni per utilizzare le stesse risorse, indipendentemente dalle popolazioni di utenti e dai volumi di dati, e non per sovraccaricare le risorse di sistema.

Utilizzo di variabili di binding per migliorare la scalabilità

Le variabili di binding, utilizzate correttamente, consentono di sviluppare applicazioni efficienti e scalabili.

Una variabile di associazione è un segnaposto in un'istruzione SQL che deve essere sostituita con un valore o un indirizzo di valore valido affinché l'esecuzione dell'istruzione riesca. Utilizzando le bind variable, è possibile scrivere un'istruzione SQL che accetta input o parametri in fase di esecuzione.

Un sottoprogramma può avere parametri i cui valori sono forniti dall'utente chiamante, così come un'istruzione SQL può avere segnaposto di bind variable i cui valori (denominati bind variable) vengono forniti in runtime. Proprio come un sottoprogramma viene compilato una volta e quindi eseguito più volte con parametri diversi, un'istruzione SQL con segnaposto bind variable viene analizzata una volta e quindi analizzata in modo soft con bind variable diverse.

Un hard parse, che include l'ottimizzazione e la generazione dell'origine delle righe, è un'operazione che richiede molta CPU. Un soft parse, che salta l'ottimizzazione e la generazione dell'origine delle righe e procede direttamente all'esecuzione, è in genere molto più veloce di un hard parse della stessa istruzione. Per una panoramica dell'elaborazione SQL, che include la differenza tra hard parse e soft parse, vedere Oracle Database Concepts.

Non solo è un hard parse un'operazione ad alta intensità di CPU, ma è un'operazione non scalabile, perché non può essere eseguita contemporaneamente a molte altre operazioni. Per ulteriori informazioni su concorrenza e scalabilità, vedere "Informazioni su concorrenza e scalabilità".

L'Esempio 8-1 mostra la differenza di prestazioni tra una query senza bind variable e una query semanticamente equivalente con una bind variable. Il primo è più lento e utilizza molti più blocchi (per informazioni su come i latch influenzano la scalabilità, vedere). Per raccogliere e visualizzare le statistiche sulle prestazioni, l'esempio utilizza lo strumento Statistiche di esecuzione, descritto in "Confronto delle tecniche di programmazione con le statistiche di esecuzione".

Nota:

Nota:

Sebbene l'analisi soft sia più efficiente dell'analisi hard, il costo dell'analisi soft di un'affermazione molte volte è ancora molto elevato. Per massimizzare l'efficienza e la scalabilità dell'applicazione, ridurre al minimo l'analisi. Il modo più semplice per ridurre al minimo l'analisi è utilizzare PL/SQL.

Esempio 8-1 Variabile di binding migliora le prestazioni

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

Il risultato è simile al testo seguente:

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.

Utilizzo di PL/SQL per migliorare la scalabilità

Alcune funzioni PL/SQL possono aiutarti a migliorare la scalabilità delle applicazioni.

Modalità di riduzione dell'analisi PL/SQL

PL/SQL, ottimizzato per l'accesso al database, inserisce nella cache le istruzioni in modo silenzioso. In PL/SQL, quando si chiude un cursore, il cursore si chiude dal punto di vista, ovvero non è possibile utilizzarlo dove è richiesto un cursore aperto, ma PL/SQL mantiene effettivamente il cursore aperto e memorizza nella cache la relativa istruzione.

Se si utilizza di nuovo l'istruzione inserita nella cache, PL/SQL utilizza lo stesso cursore, evitando così un'analisi. (Se necessario, PL/SQL chiude le istruzioni inserite nella cache, ad esempio se il programma deve aprire un altro cursore, ma questa operazione supererebbe l'impostazione init.ora di OPEN_CURSORS.)

PL/SQL può inserire nella cache solo istruzioni SQL che non possono essere modificate in runtime.

Informazioni sull'istruzione EXECUTE IMMEDIATE

L'istruzione EXECUTE IMMEDIATE crea ed esegue un'istruzione SQL dinamica in una singola operazione.

La sintassi di base dell'istruzione EXECUTE IMMEDIATE è:

EXECUTE IMMEDIATE sql_statement

sql_statement è una stringa che rappresenta un'istruzione SQL. Se sql_statement ha lo stesso valore ogni volta che viene eseguita l'istruzione EXECUTE IMMEDIATE, PL/SQL può inserire nella cache l'istruzione EXECUTE IMMEDIATE. Se sql_statement può essere diverso ogni volta che viene eseguita l'istruzione EXECUTE IMMEDIATE, PL/SQL non può inserire nella cache l'istruzione EXECUTE IMMEDIATE.

Vedere anche:

Informazioni su OPEN FOR Statement

L'istruzione OPEN FOR ha la seguente sintassi di base.

La sintassi di base dell'istruzione OPEN FOR è:

OPEN cursor_variable FOR query

L'applicazione può aprire cursor_variable per diverse query prima di chiuderla. Poiché PL/SQL non è in grado di determinare il numero di query diverse fino al runtime, PL/SQL non può inserire nella cache l'istruzione OPEN FOR.

Se non è necessario utilizzare una variabile del cursore, utilizzare un cursore dichiarato, sia per prestazioni migliori e facilità di programmazione. Per i dettagli, vedere Oracle Database Development Guide.

Vedere anche:

Informazioni sul package DBMS_SQL

Il package DBMS_SQL è un'API per la creazione, l'esecuzione e la descrizione di istruzioni SQL dinamiche. È necessario utilizzare il package DBMS_SQL anziché l'istruzione EXECUTE IMMEDIATE se il compilatore PL/SQL non è in grado di determinare al momento della compilazione il numero o i tipi di variabili host di output (selezionare le voci di lista) o bind variable di input.

Il package DBMS_SQL è un'API per la creazione, l'esecuzione e la descrizione di istruzioni SQL dinamiche. L'uso del package DBMS_SQL richiede più impegno rispetto all'uso dell'istruzione EXECUTE IMMEDIATE, ma è necessario utilizzare il package DBMS_SQL se il compilatore PL/SQL non è in grado di determinare al momento della compilazione il numero o i tipi di variabili host di output (selezionare gli elementi della lista) o le bind variable di input.

Vedere anche:

Informazioni su Bulk SQL

Bulk SQL riduce il numero di "round trip" tra PL/SQL e SQL, utilizzando così meno risorse.

Senza SQL in blocco, puoi recuperare una riga alla volta dal database (SQL), elaborarlo (PL/SQL) e restituirlo al database (SQL). Con SQL in blocco, si recupera un set di righe dal database, si elabora il set di righe e quindi si restituisce l'intero set al database.

Oracle consiglia di utilizzare Bulk SQL quando si recuperano più righe dal database e le restituiscono al database, come nell'Esempio 8-2. Non è necessario eseguire SQL in blocco se vengono recuperate più righe ma non vengono restituite, ad esempio:

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;

Esempio 8-2 esegue il loop di una tabella t con un object_name di colonna, recuperando set di 100 righe, elaborandole e restituendole al database. (Limitare l'istruzione FETCH di massa a 100 righe richiede un cursore esplicito.)

L'Esempio 8-3 esegue lo stesso job dell'Esempio 8-2, senza SQL in blocco.

Come mostrano i report TKPROF per l'Esempio 8-2 e l'Esempio 8-3, l'utilizzo di SQL in blocco per questo job utilizza quasi il 50% di tempo CPU in meno:

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

Tuttavia, l'utilizzo di SQL di massa per questo job utilizza più tempo CPU e più codice rispetto a un'unica istruzione SQL, come mostra questo report TKPROF:

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

Esempio di SQL in blocco 8-2

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

Esempio 8-3 senza SQL in blocco

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;

Vedere anche:

Concorrenza e scalabilità

La valuta è l'esecuzione simultanea di più transazioni. Migliore è la gestione della concorrenza da parte dell'applicazione, più scalabile è. Un'applicazione scalabile può elaborare un carico di lavoro più ampio con un aumento proporzionale dell'uso delle risorse di sistema.

La valuta è l'esecuzione simultanea di più transazioni. I rendiconti all'interno delle transazioni concorrenti possono aggiornare gli stessi dati. Migliore è la gestione della concorrenza da parte dell'applicazione, più scalabile è. Un'applicazione scalabile può elaborare un carico di lavoro più ampio con un aumento proporzionale dell'uso delle risorse di sistema. Ad esempio, se si raddoppia il carico di lavoro, un'applicazione scalabile utilizza il doppio delle risorse di sistema.

Le transazioni concorrenti devono produrre risultati significativi e coerenti. Di conseguenza, un database multiutente deve fornire quanto segue:

Oracle Database mantiene la coerenza dei dati utilizzando un modello di coerenza multiversione e vari tipi di blocchi e livelli di isolamento delle transazioni. Per una panoramica del meccanismo di blocco di Oracle Database, vedere Oracle Database Concepts. Per una panoramica dei livelli di isolamento delle transazioni di Oracle Database, vedere Oracle Database Concepts.

Per descrivere un funzionamento coerente delle transazioni quando le transazioni vengono eseguite contemporaneamente, i ricercatori del database hanno definito una categoria di isolamento delle transazioni denominata serializzabile. Una transazione serializzabile opera in un ambiente che sembra essere un database a utente singolo. Le transazioni serializzabili sono desiderabili in casi specifici, ma per il 99% del carico di lavoro, l'isolamento confermato dalla lettura è perfetto.

Oracle Database dispone di funzioni che migliorano la concorrenza e la scalabilità, ad esempio sequenze, blocchi, letture e scritture non bloccanti e SQL condiviso.

Vedere anche: Oracle Database Concepts per ulteriori informazioni sulla concorrenza e la coerenza dei dati

Informazioni su sequenze e concorrenza

Le sequenze eliminano la serializzazione, migliorando così la concorrenza e la scalabilità dell'applicazione.

Una sequenza è un oggetto schema da cui più utenti possono generare numeri interi univoci, molto utile quando sono necessarie chiavi primarie univoche.

Senza sequenze, i valori di chiave primaria univoci devono essere prodotti a livello di programmazione. Un utente ottiene un nuovo valore di chiave primaria selezionando il valore prodotto più di recente e incrementandolo. Questa tecnica richiede un blocco durante la transazione e fa attendere a più utenti il valore della chiave primaria successiva, ovvero la serializzazione delle transazioni. Le sequenze eliminano la serializzazione, migliorando così la concorrenza e la scalabilità dell'applicazione.

Vedere anche:

Informazioni su blocchi e concorrenza

Un aumento dei latch significa attese più basate sulla concorrenza e quindi una diminuzione della scalabilità.

Un latch è un meccanismo di serializzazione semplice e di basso livello che coordina l'accesso multiutente alle strutture dati condivise. I blocchi proteggono le risorse di memoria condivisa dal danneggiamento quando vi si accede da più processi.

Un aumento dei latch significa attese più basate sulla concorrenza e quindi una diminuzione della scalabilità. Se è possibile utilizzare un approccio che funziona leggermente più velocemente durante lo sviluppo o uno che utilizza meno fermi, utilizzare quest'ultimo.

Vedere anche:

Informazioni su letture, scritture e concorrenza non bloccanti

In Oracle Database, le letture e scritture di non blocco consentono l'esecuzione delle query contemporaneamente alle modifiche ai dati che stanno leggendo, senza bloccare o arrestare. Le letture e le scritture non bloccanti consentono a una sessione di leggere i dati mentre un'altra sessione sta modificando tali dati.

Informazioni su SQL condiviso e concorrenza

Oracle Database compila un'istruzione SQL in un oggetto eseguibile una sola volta e quindi altre sessioni possono riutilizzare l'oggetto per tutto il tempo in cui esiste. Questa funzione di Oracle Database, denominata SQL condiviso, consente al database di eseguire operazioni a uso intensivo di risorse per la compilazione e l'ottimizzazione delle istruzioni SQL una sola volta, anziché ogni volta che una sessione utilizza la stessa istruzione SQL.

Per ulteriori informazioni su SQL condiviso, vedere anche: Oracle Database Concepts

Limitazione del numero di sessioni concorrenti

Più sessioni concorrenti hai, più attese basate sulla concorrenza hai e più lento è il tuo tempo di risposta.

Se nel computer sono presenti n memorie centrali CPU, al massimo n sessioni possono essere attive contemporaneamente. Ogni sessione "concorrente" aggiuntiva deve attendere che una memoria centrale CPU sia disponibile prima di poter diventare attiva. Se alcune sessioni di attesa sono in attesa solo di I/O, l'aumento del numero di sessioni concorrenti a poco più di n potrebbe migliorare leggermente le prestazioni di runtime. Tuttavia, l'aumento eccessivo del numero di sessioni concorrenti ridurrà in modo significativo le prestazioni di runtime.

Il parametro di inizializzazione SESSIONS determina il numero massimo di utenti concorrenti nel sistema. Per i dettagli, consulta Oracle Database Reference.

Vedere anche: http://www.youtube.com/watch?v=xNDnVOCdvQ0 per un video che mostra l'effetto della riduzione del numero di sessioni simultanee su un computer con 12 memorie centrali CPU da migliaia a 96

Confronto delle tecniche di programmazione con le statistiche di esecuzione

Lo strumento Runstats consente di confrontare le prestazioni di due tecniche di programmazione per vedere quale è meglio.

Informazioni su Runstats

Lo strumento Runstats consente di confrontare le prestazioni di due tecniche di programmazione per vedere quale è meglio.

Le statistiche di esecuzione misurano i valori seguenti:

Tra le misurazioni precedenti, la più importante è la chiusura (vedere "Informazioni su chiusure e concorrenza").

Vedere anche: Esempio 8-1, che utilizza le statistiche di esecuzione

Impostazione delle statistiche di esecuzione

Lo strumento Statistiche di esecuzione viene implementato come pacchetto che utilizza una vista e una tabella temporanea.

Nota: per il passo 1 della procedura riportata di seguito, è necessario disporre del privilegio SELECT nelle viste prestazioni dinamiche V$STATNAME, V$MYSTAT e V$LATCH. Se non è possibile ottenere questo privilegio, chiedere a qualcuno che dispone del privilegio di creare la vista nel passo 1 e concedere il privilegio SELECT su di essa.

Passi per impostare lo strumento Statistiche di esecuzione:

  1. Creare la vista utilizzata da Runstats:

     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. Creare la tabella temporanea utilizzata da Runstats:

     DROP TABLE run_stats;

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

  3. Creare la specifica package:

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

    Il parametro p_difference_threshold controlla la quantità di statistiche e dati di blocco visualizzati da Runstats. Le statistiche di esecuzione visualizzano i dati solo quando la differenza per le due tecniche è maggiore di p_difference_threshold. Per impostazione predefinita, Runstats visualizza tutti i dati.

  4. Creare il seguente corpo del pacchetto:

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

Vedere anche:

Utilizzo delle runstat

Questo argomento fornisce la sintassi per l'utilizzo dello strumento Statistiche di esecuzione.

Per utilizzare Runstats per confrontare due tecniche di programmazione, richiamare le procedure runstats_pkg da un blocco anonimo, utilizzando la seguente sintassi:

[ 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;
/

Vedere anche: Esempio 8-1, che utilizza le statistiche di esecuzione

Tecniche di elaborazione dei dati e delle prestazioni nel mondo reale

Un task comune nelle applicazioni di database in un ambiente di data warehouse è l'esecuzione di query o la modifica di un data set di grandi dimensioni. Il problema per gli sviluppatori di applicazioni è come ottenere prestazioni elevate durante l'elaborazione di set di dati di grandi dimensioni.

Le tecniche di elaborazione rientrano in due categorie: iterative e basate su set. Nel corso di anni di test, il gruppo Real-World Performance ha scoperto che le tecniche di elaborazione basate su set eseguono ordini di grandezza migliori per le applicazioni di database che elaborano set di dati di grandi dimensioni.

Questo argomento include i seguenti principali argomenti secondari:

Informazioni sull'elaborazione dei dati iterativi

Nell'elaborazione iterativa, le applicazioni utilizzano la logica condizionale per eseguire il loop in un set di righe.

In genere, sebbene non necessariamente, l'elaborazione iterativa utilizza un modello client/server come indicato di seguito.

  1. Trasferire un gruppo di righe dal database server all'applicazione client.

  2. Elaborare il gruppo all'interno dell'applicazione client.

  3. Trasferire di nuovo il gruppo elaborato al database server.

Puoi implementare algoritmi iterativi utilizzando tre tecniche principali: elaborazione riga per riga, elaborazione degli array e parallelismo manuale.

Elaborazione iterativa: riga per riga

Nell'elaborazione riga per riga, un singolo processo esegue il loop attraverso un set di dati e opera su una singola riga alla volta. In un'implementazione tipica, l'applicazione recupera ogni riga dal database, la elabora nel livello intermedio e quindi invia la riga al database, che esegue DML e commit.

Si supponga che il requisito funzionale sia quello di eseguire una query su una tabella esterna denominata ext_scan_events, quindi di inserire le relative righe in una tabella intermedia organizzata dall'heap denominata stage1_scan_events. Il seguente blocco PL/SQL utilizza una tecnica riga per riga per soddisfare questo 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;

La tecnica row-by-row presenta i seguenti vantaggi:

La tecnica row-by-row presenta i seguenti svantaggi:

Vedi anche: RWP #7 Elaborazione basata su set

Elaborazione iterativa: array

L'elaborazione degli array è identica all'elaborazione riga per riga, ad eccezione del fatto che elabora un gruppo di righe in ogni iterazione anziché in una singola riga.

Si supponga che il requisito funzionale sia lo stesso dell'esempio X-X: eseguire una query su una tabella esterna denominata ext_scan_events, quindi inserire le righe in una tabella intermedia organizzata dall'heap denominata stage1_scan_events. Il seguente blocco PL/SQL utilizza una tecnica di array per soddisfare questo 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;

Il codice precedente differisce dal codice riga per riga equivalente nell'utilizzo di un operatore BULK COLLECT in FETCH STATEMENT, che è limitato dal valore array_size di tipo PLS_INTEGER. Ad esempio, se array_size è impostato su 100, l'applicazione recupera le righe in gruppi di 100.

La tecnica dell'array presenta i seguenti vantaggi rispetto alla tecnica riga per riga:

Gli svantaggi di questa tecnica sono gli stessi dell'elaborazione riga per riga. I tempi di elaborazione possono essere inaccettabili per set di dati di grandi dimensioni. Inoltre, l'applicazione deve essere eseguita in serie su una singola memoria centrale CPU e pertanto non può sfruttare il parallelismo nativo di Oracle Database.

Elaborazione iterativa: parallelismo manuale

Il parallelismo manuale utilizza lo stesso algoritmo iterativo dell'elaborazione riga per riga e array, ma consente a più processi server di dividere il lavoro ed eseguirlo in parallelo.

Si supponga che i requisiti funzionali siano uguali a quelli degli esempi riga per riga e array. Le differenze principali sono le seguenti:

Il seguente codice PL/SQL utilizza il parallelismo manuale:

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;

La funzione ORA_HASH divide la tabella ext_scan_events_dets in 32 bucket distribuiti in modo uniforme, quindi l'istruzione SELECT recupera i nomi file per il bucket 0. Per ogni nome file nel bucket, il programma imposta la posizione della tabella esterna su questo nome file. Il programma utilizza quindi l'elaborazione batch per eseguire query sulla tabella esterna, inserirla nella tabella intermedia e quindi eseguire il commit.

Durante l'esecuzione del job 1, gli altri 31 job di Oracle Scheduler vengono eseguiti in parallelo. In questo modo, ogni job legge contemporaneamente un sottoinsieme diverso dei file degli eventi di scansione e inserisce i record dal relativo sottoinsieme nella stessa tabella intermedia.

La tecnica di parallelismo manuale presenta i seguenti vantaggi rispetto alle tecniche iterative alternative:

La tecnica di parallelismo manuale presenta i seguenti svantaggi:

Vedi anche: RWP #8: Elaborazione parallela basata su set

Informazioni sull'elaborazione basata su set

L'elaborazione basata su set è una tecnica SQL che elabora un data set all'interno del database.

In un modello basato su set, l'istruzione SQL definisce il risultato e consente al database di determinare il modo più efficiente per ottenerlo. Al contrario, gli algoritmi iterativi utilizzano la logica condizionale per estrarre ogni riga o gruppo di righe dal database all'applicazione client, elaborare i dati sul client e quindi inviare i dati al database. L'elaborazione basata su set elimina l'overhead delle API di round-trip e database di rete perché i dati non lasciano mai il database.

Assumere lo stesso requisito funzionale degli esempi precedenti. Le seguenti istruzioni SQL soddisfano questo requisito utilizzando un algoritmo basato su set:

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

Poiché l'istruzione INSERT contiene una subquery della tabella ext_scan_events, un'istruzione SQL singola legge e scrive tutte le righe. Inoltre, l'applicazione esegue un COMMIT singolo dopo che il database ha inserito tutte le righe. Al contrario, le applicazioni iterative eseguono un COMMIT dopo l'inserimento di ogni riga o di ogni gruppo di righe.

La tecnica basata sul set presenta vantaggi significativi rispetto alle tecniche iterative:

L'elaborazione basata su set presenta alcuni potenziali svantaggi:

Vedi anche: RWP #7 Set-Based Processing, RWP #8: Set-Based Parallel Processing, RWP #9: Set-Based Processing-Data Deduplication, RWP #10: Set-Based Processing-Data Transformations e RWP #11: Set-Based Processing-Data Aggregation