Parallel By ANY

Previous posts in this category introduced a way to perform DML in a SELECT using the following technology:

  • Table Functions
  • Autonomous Transactions

The following concepts were included:

  • A REF CURSOR as a parameter
  • Row by row (aka slow by slow) processing of the REF CURSOR
  • Raw parallel query processing to try to overcome the slow by slow processing

This post makes the parallel processing less raw by specifying that one instance of the function will run on each parallel process.

To keep this demo simple I’ll let Oracle decide how many parallel processes to use. This wont break anything because all I’m after is extra processing power.

SQL> -- Create the table
SQL> CREATE TABLE demo
 2   ( col1 NUMBER,
 3     col2 NUMBER );
Table created.

SQL> -- create 1000 demo records
SQL> BEGIN
 2     FOR counter IN 1..1000 LOOP
 3       INSERT INTO demo
 4       VALUES(counter,
 5              counter);
 6     END LOOP;
 7   END;
 8 /
PL/SQL procedure successfully completed.

SQL> COMMIT;
Commit complete.

SQL> -- create a return type for the function we will write later
SQL> CREATE OR REPLACE TYPE v_n_t AS TABLE OF NUMBER;
 2 /
Type created.

SQL> CREATE OR REPLACE FUNCTION delete_demo ( p_curs SYS_REFCURSOR )
 2                     RETURN v_n_t
 3                     --
 4                     -- Here is the parallel clause. The BY ANY clause tells Oracle to
 5                     -- simply use whatever parallelism it sees fit
 6                     --
 7                    PARALLEL_ENABLE ( PARTITION p_curs BY ANY ) AS
 8                    --
 9                    PRAGMA AUTONOMOUS_TRANSACTION;
 10                   v_ret_val v_n_t := v_n_t();
 11                   v_number NUMBER;
 12 BEGIN
 13   LOOP
 14     FETCH p_curs INTO v_number;
 15     EXIT WHEN p_curs%NOTFOUND;
 16     v_ret_val.EXTEND;
 17     DELETE demo
 18     WHERE col1 = v_number;
 19     v_ret_val(1) := SQL%ROWCOUNT;
 20   END LOOP;
 21   COMMIT;
 22   RETURN(v_ret_val);
 23 END;
 24 /
Function created.

SQL> ALTER SESSION FORCE PARALLEL QUERY;
Session altered.

SQL> SELECT COUNT(*)
 2     FROM TABLE(delete_demo(CURSOR(SELECT col1
 3                                     FROM demo)))
 4 /

COUNT(*)
----------
      1000

SQL> ALTER SESSION DISABLE PARALLEL QUERY;
Session altered.

SQL> SELECT COUNT(*)
 2     FROM demo
 3 /

COUNT(*)
----------
         0

SQL> SELECT statistic,
 2          value
 3     FROM v$pq_sysstat
 4    WHERE value > 0
 5   ORDER BY value DESC;

STATISTIC            VALUE
-------------------- -----
Local Msgs Recv'd       30
Local Msgs Sent         30
Servers Idle            12
Servers Started         12
Servers Highwater        4
Server Sessions          4
Queries Initiated        1
DFO Trees                1

Note the much more efficient use of PQ processing. Only 30 messages!

The next post will how parallelism can be controlled to provide raw power and functionality. It’s Here.

Thanks for reading!