Use PL to improve the performance

Use PL to reduce parses

Procedural Language (PL) has been optimized for database access and can cache statements. For example, in PL, if a cursor is closed, it is no longer usable, but the open state and statement of the cursor are still stored. When the cached statement is needed again, PL will use the same cursor, thus avoiding another parse.

PL can cache only SQL statements that do not change during runtime.

EXECUTE IMMEDIATE statement

The EXECUTE IMMEDIATE statement builds and executes a dynamic SQL statement in a single operation. The basic syntax of this statement is as follows:

EXECUTE IMMEDIATE sql_statement 

The sql_statement string represents the SQL statement. If the value of sql_statement is the same for all EXECUTE IMMEDIATE executions, PL can cache the EXECUTE IMMEDIATE statement. If the value varies each execution, PL cannot cache this EXECUTE IMMEDIATE statement.

OPEN FOR statement

The simple syntax of the OPEN FOR statement is as follows:

OPEN cursor_variable FOR query

An application can open cursor variables for different queries and then close the cursors after use. PL can determine the numbers of different queries only at runtime, and thus cannot cache OPEN FOR statements.

If you do not need cursor variables, we recommend that you use declared cursors for better performance and program convenience.

Bulk SQL

Bulk SQL reduces the number of data interactions between PL and SQL, thus reducing required resources.

If Bulk SQL is not used, each time one row of data is obtained from the database through the SQL engine, processed in PL, and then returned to the SQL engine of the database. When Bulk SQL is used, a batch of records can be retrieved from the database, and then processed and returned to the database.

Therefore, we recommend that you use Bulk SQL when you need to retrieve many rows of data from the database and return them to the database after processing. If you do not need to return data to the database, Bulk SQL is not required.

In the following example, data is cyclically retrieved from the table ware, with 100 rows retrieved each time. You can add the LIMIT statement after the Bulk FETCH statement to limit the number of row numbers, which requires an explicit cursor. Then, these records are processed and returned to the database.

delimiter /
CREATE OR REPLACE PROCEDURE sp_bulk_sql_test
AS 
    TYPE T_IDS  IS TABLE OF number ;
    TYPE T_NAMES IS TABLE OF varchar2(50);
    l_id    T_IDS;
    l_first T_NAMES;
    l_last  T_NAMES;
    
    CURSOR c1 IS SELECT c_id, c_last, c_first FROM cust ;

    N   NUMBER := 100 ;
BEGIN
    OPEN c1 ;
    LOOP
        FETCH c1 BULK COLLECT INTO l_id, l_first, l_last LIMIT N;
    
        FOR i IN 1..l_id.COUNT
        LOOP 
            l_first(i)  := 'F-' || LPAD(to_char(l_id(i)),5,'0');
            l_last(i)   := 'L-' || LPAD(to_char(l_id(i)),5,'0');
        END LOOP;
    
        FORALL i IN 1..l_id.COUNT
            UPDATE cust SET c_first = l_first(i), c_last = l_last(i) WHERE c_id = l_id(i);
        EXIT WHEN c1%NOTFOUND;
        
        COMMIT;
    END LOOP;
    CLOSE c1;
END;
/
delimiter ;

obclient> call sp_bulk_sql_test();
Query OK, 0 rows affected (8.87 sec)

obclient>