PL/SQL, which is optimized for database access, silently caches statements. In PL/SQL, when you close a cursor, the cursor closes from your perspective—that is, you cannot use it where an open cursor is required—but PL/SQL actually keeps the cursor open and caches its statement.

If you use the cached statement again, PL/SQL uses the same cursor, thereby avoiding a parse. (PL/SQL closes cached statements if necessary—for example, if your program must open another cursor but doing so would exceed the init.ora setting of OPEN_CURSORS.)

PL/SQL can silently cache only SQL statements that cannot change at runtime.

The EXECUTE IMMEDIATE statement builds and runs a dynamic SQL statement in a single operation.

The basic syntax of the EXECUTE IMMEDIATE statement is:

EXECUTE IMMEDIATE sql_statement

sql_statement is a string that represents a SQL statement. If sql_statement has the same value every time the EXECUTE IMMEDIATE statement runs, then PL/SQL can cache the EXECUTE IMMEDIATE statement. If sql_statement can be different every time the EXECUTE IMMEDIATE statement runs, then PL/SQL cannot cache the EXECUTE IMMEDIATE statement.

The OPEN FOR statement has the following basic syntax.

The basic syntax of the OPEN FOR statement is:

OPEN cursor_variable FOR query

Your application can open cursor_variable for several different queries before closing it. Because PL/SQL cannot determine the number of different queries until runtime, PL/SQL cannot cache the OPEN FOR statement.

If you do not need to use a cursor variable, then use a declared cursor, for both better performance and ease of programming. For details, see Oracle Database Development Guide.

The DBMS_SQL package is an API for building, running, and describing dynamic SQL statements. You must use the DBMS_SQL package instead of the EXECUTE IMMEDIATE statement if the PL/SQL compiler cannot determine at compile time the number or types of output host variables (select list items) or input bind variables.

The DBMS_SQL package is an API for building, running, and describing dynamic SQL statements. Using the DBMS_SQL package takes more effort than using the EXECUTE IMMEDIATE statement, but you must use the DBMS_SQL package if the PL/SQL compiler cannot determine at compile time the number or types of output host variables (select list items) or input bind variables.

Bulk SQL reduces the number of "round trips" between PL/SQL and SQL, thereby using fewer resources.

Without bulk SQL, you retrieve one row at a time from the database (SQL), process it (PL/SQL), and return it to the database (SQL). With bulk SQL, you retrieve a set of rows from the database, process the set of rows, and then return the whole set to the database.

Oracle recommends using Bulk SQL when you retrieve multiple rows from the database and return them to the database, as in Example 8-2. You do not need bulk SQL if you retrieve multiple rows but do not return them; for example:

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;

Example 8-2 loops through a table t with a column object_name, retrieving sets of 100 rows, processing them, and returning them to the database. (Limiting the bulk FETCH statement to 100 rows requires an explicit cursor.)

Example 8-3 does the same job as Example 8-2, without bulk SQL.

As these TKPROF reports for Example 8-2 and Example 8-3 show, using bulk SQL for this job uses almost 50% less CPU time:

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

However, using bulk SQL for this job uses more CPU time—and more code—than using a single SQL statement, as this TKPROF report shows:

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

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

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;

Sequences eliminate serialization, thereby improving the concurrency and scalability of your application.

A sequence is a schema object from which multiple users can generate unique integers, which is very useful when you need unique primary keys.

Without sequences, unique primary key values must be produced programmatically. A user gets a new primary key value by selecting the most recently produced value and incrementing it. This technique requires a lock during the transaction and causes multiple users to wait for the next primary key value—that is, the transactions serialize. Sequences eliminate serialization, thereby improving the concurrency and scalability of your application.

An increase in latches means more concurrency-based waits, and therefore a decrease in scalability.

A latch is a simple, low-level serialization mechanism that coordinates multiuser access to shared data structures. Latches protect shared memory resources from corruption when accessed by multiple processes.

An increase in latches means more concurrency-based waits, and therefore a decrease in scalability. If you can use either an approach that runs slightly faster during development or one that uses fewer latches, use the latter.

In Oracle Database, nonblocking reads and writes let queries execute concurrently with changes to the data they are reading, without blocking or stopping. Nonblocking reads and writes let one session read data while another session is changing that data.

Oracle Database compiles a SQL statement into an executable object once, and then other sessions can reuse the object for as long as it exists. This Oracle Database feature, called shared SQL, lets the database do very resource-intensive operations—compiling and optimizing SQL statements—only once, instead of every time a session uses the same SQL statement.

The Runstats tool lets you compare the performance of two programming techniques to see which is better.

Runstats measures:

• Elapsed time for each technique in hundredths of seconds (hsec)

• Elapsed time for the first technique as a percentage of that of the second technique

• System statistics for the two techniques (for example, parse calls)

• Latching for the two techniques

Of the preceding measurements, the most important is latching (see "About Latches and Concurrency").

The Runstats tool is implemented as a package that uses a view and a temporary table.

1. Create the view that Runstats uses:

   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. Create the temporary table that Runstats uses:

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

3. Create this package specification:

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

   The parameter p_difference_threshold controls the amount of statistics and latching data that Runstats displays. Runstats displays data only when the difference for the two techniques is greater than p_difference_threshold. By default, Runstats displays all data.

4. Create this package body:

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

This topic gives the syntax for using the Runstats tool.

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

In iterative processing, applications use conditional logic to loop through a set of rows.

Typically, although not necessarily, iterative processing uses a client/server model as follows:

1. Transfer a group of rows from the database server to the client application.

2. Process the group within the client application.

3. Transfer the processed group back to the database server.

You can implement iterative algorithms using three main techniques: row-by-row processing, array processing, and manual parallelism.

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;

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;

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;

Set-based processing is a SQL technique that processes a data set inside the database.

In a set-based model, the SQL statement defines the result, and allows the database to determine the most efficient way to obtain it. In contrast, iterative algorithms use conditional logic to pull each each row or group of rows from the database to the client application, process the data on the client, and then send the data back to the database. Set-based processing eliminates the network round-trip and database API overhead because the data never leaves the database.

Assume the same functional requirement as in the previous examples. The following SQL statements meet this requirement using a set-based algorithm:

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

Because the INSERT statement contains a subquery of the ext_scan_events table, a single SQL statement reads and writes all rows. Also, the application executes a single COMMIT after the database has inserted all rows. In contrast, iterative applications execute a COMMIT after the insert of each row or each group of rows.

The set-based technique has significant advantages over iterative techniques:

• As demonstrated in Real-World Performance demonstrations and classes, the performance on large data sets is orders of magnitude faster. It is not unusual for the run time of a program to drop from several hours to several seconds.

• A side-effect of the orders of magnitude increase in processing speed is that DBAs can eliminate long-running and error-prone batch jobs, and innvoate business processes in real time.

• The length of the code is significantly shorter, a short as two or three lines of code, because SQL defines the result and not the access method.

• In contrast to manual parallelism, parallel DML is optimized for performance because the database, rather than the application, manages the processes.

• When joining data sets, the database automatically uses highly efficient hash joins instead of relatively inefficient application-level loops.

• The APPEND hint forces a direct-path load, which means that the database creates no redo and undo, thereby avoiding the waste of I/O and CPU.

Set-based processing does have some potential disadvantages:

• The techniques are unfamiliar to many database developers, so they may be more difficult.

• Because a set-based model is completely different from an iterative model, changing it requires completely rewriting the source code.

See Also: 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, and RWP #11: Set-Based Processing--Data Aggregation