PL/SQL is faster than SQL – Just ask Mitch.

Oracle Community

PL/SQL is faster than SQL – Just ask Mitch.

Follow / 1.19.2014 at 3:47pm

After their comprehensive defeat at Lord’s back in June, some experts were confidently predicting that Australia would be on the wrong-end of a clean sweep in both of the back-to-back Ashes series.
Mitchell Johnson, if he was mentioned at all, was written off by all and sundry. After all, not only did he not hand homework in on time, he couldn’t be relied upon to hit a barn door, let alone a set of stumps.
Fast-forward a few months and you can see that conventional wisdom has held…to the extent that no barn doors have been dented.
Unfortunately, the same cannot be said of English pride.
Mitch and his mates have a bit of time on their hands before Australia visit South Africa next month – that nice Mr Lehman has let the class off homework – so they’re free to assist in contradicting another of those things that “everyone knows” – SQL is always faster than PL/SQL.

What we’re going to cover here (among other things) is :

  • a quick overview of the LOG ERRORS mechanism (Mitch doesn’t do any other speed)
  • a recap of the older PL/SQL SAVE EXCEPTIONS
  • performance comparison between the two with errors present
  • Explore the limits of LIMIT
  • performance comparison when no errors are present

Mitch is standing at the top of his run. A random English batsmen is quaking at the crease, so let’s get started…

Test Data

These are the tables and records that will be used in the examples that follow.
First of all, because I want to vary the number of records being processed throughout these examples, I’ll just put some seed records in a table :

CREATE TABLE seed_records
(
    player_name VARCHAR2(50),
    overs VARCHAR2(5),
    maidens NUMBER(2),
    runs NUMBER(3),
    wkts NUMBER(2),
    best VARCHAR2(6),
    five_fors NUMBER(1)
)
/

INSERT INTO seed_records
(
    player_name, overs, maidens,
    runs, wkts, best, five_fors
)
VALUES
(
    'MG JOHNSON', '188.4', 51, 
    517, 37, '7-40', 3
)
/

INSERT INTO seed_records
(
    player_name, overs, maidens,
    runs, wkts, best,five_fors
)
VALUES
(
    'RJ HARRIS', '166.2', 50, 
    425, 22, '5-25', 1
)
/

INSERT INTO seed_records
(
    player_name, overs, maidens,
    runs, wkts, best,five_fors
)
VALUES
(
    'PM SIDDLE', '156.4', 48, 
    386, 16, '4-57', NULL
)
/

INSERT INTO seed_records
(
    player_name, overs, maidens,
    runs, wkts, best,five_fors
)
VALUES
(
    'NM LYON', '176.2', 42, 
    558, 19, '5-50', 1
)
/ 
COMMIT;

Now for a staging table – where records are initially loaded into the system :

CREATE TABLE ashes_bowling_stg
(
    player_name VARCHAR2(50),
    overs VARCHAR2(5),
    maidens NUMBER(2),
    runs NUMBER(3),
    wkts NUMBER(2),
    best VARCHAR2(6),
    five_fors NUMBER(1)
)
/

Finally, our target application table :

CREATE TABLE ashes_bowling_figures
(
    player_name VARCHAR2(50),
    overs VARCHAR2(5),
    maidens NUMBER(2),
    runs NUMBER(3),
    wkts NUMBER(2),
    best VARCHAR2(6),
    five_fors NUMBER(1) NOT NULL -- ooh, that's new
)
/

Notice that, in the target table, the five_fors column is mandatory. This will ensure that we get some errors during our load.

Multi Record Insert Error Logging – a brief history

Once upon a time, inserting a bunch of records in a single statement was an all-or-nothing deal. If one record caused an error, the entire transaction failed.
If you wanted to get around this, you either had to insert each record one at a time or use something like SQL*Loader.
That was until the SAVE EXCEPTIONS clause was added to the PL/SQL Bulk Collect command in 9i.
Even better, in 10g Release 2, Oracle introduced a method of doing this in straight SQL – the LOG ERRORS clause.
Perfect. After all, as every Oracle Developer knows, doing something in SQL is always quicker than doing it in PL/SQL…

SQL Log Errors

The LOG ERRORS clause causes any error records to be written to an errors table associated with the primary target table.
So, the first step is to create an errors table.
To do this, we can use DBMS_ERRLOG.CREATE_ERROR_LOG.

The procedure accepts the following parameters :

  • dml_table_name – name of the table you want to log errors against
  • err_log_table_name – name of the table to hold the errors. The default is err$_first-25-characters-of-the-dml-table
  • err_log_table_owner
  • err_log_table_space
  • skip_unsupported – LONG and LOB columns are not supported – defaults to FALSE

We’re going to keep things fairly simple and create an error log table for ASHES_BOWLING_FIGURES called ASHES_BOWLING_FIGS_ERR :

BEGIN
    DBMS_ERRLOG.CREATE_ERROR_LOG
    (
        dml_table_name => 'ASHES_BOWLING_FIGURES',
        err_log_table_name => 'ASHES_BOWLING_FIGS_ERR'
    );
END;
/

If we now take a look at our new table, we can see that it has the same columns as the DML table, plus additional columns for error logging purposes :

SQL> desc ashes_bowling_figs_err
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORA_ERR_NUMBER$				    NUMBER
 ORA_ERR_MESG$					    VARCHAR2(2000)
 ORA_ERR_ROWID$ 				    ROWID
 ORA_ERR_OPTYP$ 				    VARCHAR2(2)
 ORA_ERR_TAG$					    VARCHAR2(2000)
 PLAYER_NAME					    VARCHAR2(4000)
 OVERS						    VARCHAR2(4000)
 MAIDENS					    VARCHAR2(4000)
 RUNS						    VARCHAR2(4000)
 WKTS						    VARCHAR2(4000)
 BEST						    VARCHAR2(4000)
 FIVE_FORS					    VARCHAR2(4000)

SQL> 

To demonstrate, we can populate our staging table with the seed records :

INSERT INTO ashes_bowling_stg
SELECT * FROM seed_records;
/
COMMIT;

Now we can insert these four records into our target table, with any errors being written to the error table…

INSERT INTO ashes_bowling_figures
(
    player_name, overs, maidens,
    runs, wkts, best, five_fors
)
SELECT player_name, overs, maidens,
    runs, wkts, best, five_fors
FROM ashes_bowling_stg
LOG ERRORS INTO ashes_bowling_figs_err ('TOO FAST') REJECT LIMIT UNLIMITED
/

The LOG ERRORS clause in this instance contains two optional elements. You don’t need to specify the table name to insert the errors into if it has been created using the DBMS_ERRLOG.CREATE_ERROR_LOG procedure. Also, you don’t need to specify a value to be written to the ORA_ERR_TAG$ column ( in this case, ‘TOO FAST’).

If we run this we get :

3 rows created.

SQL> 

As we can see, only three of the four rows have been inserted into our target table. If we look in the errors table…

SELECT ora_err_mesg$, player_name
FROM ashes_bowling_figs_err
WHERE ora_err_tag$ = 'TOO FAST';

ORA_ERR_MESG$											     PLAYER_NAME
---------------------------------------------------------------------------------------------------- --------------------
ORA-01400: cannot insert NULL into ("MIKE"."ASHES_BOWLING_FIGURES"."FIVE_FORS") 		     PM SIDDLE

PL/SQL SAVE EXCEPTIONS

By contrast, well, the older PL/SQL method of doing this even looks slower :

DECLARE
   
    TYPE typ_bowlers IS TABLE OF ashes_bowling_stg%ROWTYPE;
    tbl_bowlers typ_bowlers;
    
    --
    -- With the SAVE EXCEPTIONS clause, a single ORA-24381 is raised at the
    -- end of the DML action, irrespective of how many rows actually error
    --
    e_dml_err EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_dml_err, -24381);
    
    CURSOR c_stg IS
        SELECT player_name, overs, maidens,
            runs, wkts, best, five_fors    
        FROM ashes_bowling_stg;

    l_idx NUMBER;
    l_err_code NUMBER;
    l_err_msg VARCHAR2(4000);

BEGIN
    OPEN c_stg;
    --
    -- To make sure we limit the amount of PGA we use, we need to process
    -- the results in batches. We'll choose a limit of 100 here because that's
    -- about normal.
    --
    LOOP
        FETCH c_stg BULK COLLECT INTO tbl_bowlers LIMIT 100;
        EXIT WHEN tbl_bowlers.COUNT = 0;
        BEGIN
            FORALL i IN 1..tbl_bowlers.COUNT SAVE EXCEPTIONS
                INSERT INTO ashes_bowling_figures
                VALUES tbl_bowlers(i);
        EXCEPTION
            WHEN e_dml_err THEN
                --
                -- Loop through the error records and insert them into the
                -- error table one-by-one...
                --      
                FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
                    l_idx := SQL%BULK_EXCEPTIONS(j).error_index;
                    l_err_code := SQL%BULK_EXCEPTIONS(j).error_code;
                    l_err_msg := SQLERRM( l_err_code * -1);
                    INSERT INTO ashes_bowling_figs_err
                    (
                        ora_err_number$,
                        ora_err_mesg$,
                        ora_err_optyp$,
                        ora_err_tag$, 
                        player_name,
                        overs,
                        maidens,
                        runs,
                        wkts,
                        best,
                        five_fors
                    )
                    VALUES
                    (
                        l_err_code,
                        l_err_msg,
                        'I',
                        'TRADITIONAL',
                        tbl_bowlers(l_idx).player_name,
                        tbl_bowlers(l_idx).overs,
                        tbl_bowlers(l_idx).maidens,
                        tbl_bowlers(l_idx).runs,
                        tbl_bowlers(l_idx).wkts,
                        tbl_bowlers(l_idx).best,
                        tbl_bowlers(l_idx).five_fors
                    );
                END LOOP;
        END;
    END LOOP;
    CLOSE c_stg;
END;
/   

We’re using the same error table as we created for the LOG ERRORS example and the functionality is more-or-less the same.
The one difference is that the error message you get using the PL/SQL approach is a bit less helpful as SQLERRM is required to lookup the standard error message for the given error code :

SELECT ora_err_mesg$, player_name
FROM ashes_bowling_figs_err
WHERE ora_err_tag$ = 'TRADITIONAL'
/

ORA_ERR_MESG$											     PLAYER_NAME
---------------------------------------------------------------------------------------------------- --------------------
ORA-01400: cannot insert NULL into ()								     PM SIDDLE

Just for fun, let’s do a comparison on a more substantial run.

Performance Comparison – 40000 records

For these comparisons, I’m running on Oracle 11g R2 XE 64-bit.
The machine I’m running on has 8GB of physical RAM.
Before each run the following steps are taken :

  1. The staging, target and error tables are truncated
  2. The staging table is re-populated with the number of records required for the run
  3. The database is bounced between runs to prevent the results being skewed by anything lying around in the cache

As this example is supposed to mimic a batch run, I’ve just recorded the first execution time for each load.
In order to do some more in-depth analysis, each of the sessions is traced.

The truncation commands are saved in the script teardown.sql :

TRUNCATE TABLE ashes_bowling_figs_err
/

TRUNCATE TABLE ashes_bowling_figures
/

TRUNCATE TABLE ashes_bowling_stg
/

To re-populate the staging table, I’m using the following script – pop_staging_tab.sql :

accept recs_reqd prompt 'Enter number of records required : '
DECLARE
    l_iterations NUMBER;
BEGIN
    l_iterations := TO_NUMBER('&recs_reqd')/4; -- set iterations for required number of records
    FOR i IN 1..l_iterations
    LOOP
        INSERT INTO ashes_bowling_stg
        SELECT * FROM seed_records;
        --
        -- Commit every 10000 rows
        --
        IF MOD(i, 100000) = 0 THEN
            COMMIT;
        END IF;
    END LOOP;
END;
/

Setting up for 40000 records then…

SQL> @teardown.sql

Table truncated.


Table truncated.


Table truncated.

SQL> @pop_staging_tab.sql
Enter number of records required : 40000
old   4:     l_iterations := TO_NUMBER('&recs_reqd')/4; -- set iterations for required number of records
new   4:     l_iterations := TO_NUMBER('40000')/4; -- set iterations for required number of records

PL/SQL procedure successfully completed.

SQL> select count(*) from ashes_bowling_stg;

  COUNT(*)
----------
     40000

SQL> 

Next, we bounce the database.
When it comes back, we need to trace the session :

ALTER SESSION SET TRACEFILE_IDENTIFIER='old_way_1';
BEGIN
    DBMS_MONITOR.SESSION_TRACE_ENABLE(null,null, true, true, 'FIRST_EXECUTION');
END;
/

Now we find the tracefile location :

SELECT value
FROM v$diag_info
WHERE name = 'Default Trace File'
/

This will make it easier for us to identify the correct file should we feel the need to use tkprof.

The PL/SQL Approach

Running the script then (saved as old_way_limit_errtab.sql) …

SQL> @old_way_limit_errtab.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.31
SQL> select count(*) from ashes_bowling_figures;

  COUNT(*)
----------
     30000

SQL> select count(*) from ashes_bowling_figs_err;

  COUNT(*)
----------
     10000

SQL> BEGIN
  2      DBMS_MONITOR.SESSION_TRACE_DISABLE;
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> 

The SQL Approach

Next, we repeat the setup steps, bounce the database, and run the SQL script…

SQL> @load_bowlers_le.sql

30000 rows created.

Elapsed: 00:00:05.47

Commit complete.

Elapsed: 00:00:00.01
SQL> select count(*) from ashes_bowling_figures;

  COUNT(*)
----------
     30000

Elapsed: 00:00:00.02
SQL> select count(*) from ashes_bowling_figs_err;

  COUNT(*)
----------
     10000

Elapsed: 00:00:00.00
SQL> 

That’s a bit of a surprise. The SQL script took 5.47 seconds, over two seconds longer than the PL/SQL approach.
Let’s take a look at tkprof…

tkprof XE_ora_4978_sql_way.trc sql_way.prf explain=uid/pwd@db sys=no

Looking through the trace, the interesting bit is the insert into the error table…


INSERT INTO "ASHES_BOWLING_FIGS_ERR" (ORA_ERR_NUMBER$, ORA_ERR_MESG$, 
  ORA_ERR_ROWID$, ORA_ERR_OPTYP$, ORA_ERR_TAG$, "PLAYER_NAME", "OVERS", 
  "MAIDENS", "RUNS", "WKTS", "BEST", "FIVE_FORS") 
VALUES
 (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      3.37       3.55          5        460      22183       10000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10001      3.37       3.55          5        460      22183       10000

So, this insert was executed 10,000 times, once for each error record. Not exactly the set-based approach we’d expect from a SQL statement.
There is another revealing difference between the two runs, which we’ll come onto in a bit.

Before that though it’s worth looking again at our PL/SQL script.

Using FORALL on SQL%BULK_EXCEPTIONS

At the moment, the error record processing in the PL/SQL script is row-by-row. Wouldn’t it be better if we could somehow get this to run as a FORALL statement ?
Let’s have a look :

set timing on
--
-- Now amended to use a FORALL to populate the error table
--
DECLARE
    TYPE typ_bowlers IS TABLE OF ashes_bowling_stg%ROWTYPE;
    tbl_bowlers typ_bowlers;
    
    e_dml_err EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_dml_err, -24381);
    
    CURSOR c_stg IS
        SELECT player_name, overs, maidens,
            runs, wkts, best, five_fors    
        FROM ashes_bowling_stg;

    --
    -- Array for error handling
    --
    TYPE typ_err IS TABLE OF ashes_bowling_figs_err%ROWTYPE
        INDEX BY PLS_INTEGER;
    tbl_err typ_err;
    tbl_err_empty typ_err;
    l_idx NUMBER;
BEGIN
    OPEN c_stg;
    LOOP
        FETCH c_stg BULK COLLECT INTO tbl_bowlers LIMIT 100;
        EXIT WHEN tbl_bowlers.COUNT = 0;
        BEGIN
            FORALL i IN 1..tbl_bowlers.COUNT SAVE EXCEPTIONS
                INSERT INTO ashes_bowling_figures
                VALUES tbl_bowlers(i);
        EXCEPTION
            WHEN e_dml_err THEN
                FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
                    --
                    -- Loop through and populate the error array
                    --
                    l_idx := SQL%BULK_EXCEPTIONS(j).error_index;
                    tbl_err(j).ora_err_number$ := SQL%BULK_EXCEPTIONS(j).error_code;
                    tbl_err(j).ora_err_mesg$ := SQLERRM( SQL%BULK_EXCEPTIONS(j).error_code * -1);
                    tbl_err(j).ora_err_rowid$ := NULL; -- need to specify the NULL value in this example
                    tbl_err(j).ora_err_tag$ := 'FORALL_EXCEPTION';
                    tbl_err(j).ora_err_optyp$ := 'I';
                    tbl_err(j).player_name := tbl_bowlers(l_idx).player_name;
                    tbl_err(j).overs := tbl_bowlers(l_idx).overs;
                    tbl_err(j).maidens := tbl_bowlers(l_idx).maidens;
                    tbl_err(j).runs := tbl_bowlers(l_idx).runs;
                    tbl_err(j).wkts := tbl_bowlers(l_idx).wkts;
                    tbl_err(j).best := tbl_bowlers(l_idx).best;
                    tbl_err(j).five_fors := tbl_bowlers(l_idx).five_fors;
                END LOOP;
                --
                -- ...and populate the error table in one fell swoop ( per iteration)
                --
                FORALL k IN 1..tbl_err.COUNT 
                    INSERT INTO ashes_bowling_figs_err
                    VALUES tbl_err(k);
                --
                -- Cleardown the error array
                --
                tbl_err := tbl_err_empty;
        END;
    END LOOP;
    CLOSE c_stg;
END;
/   
COMMIT;

Once we’ve setup the test again, bounced the database etc, we can re-execute (still on 40000 records) :

SQL> @old_way_limit_forall.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.10

Commit complete.

Elapsed: 00:00:00.01
SQL> select count(*) from ashes_bowling_figures;

  COUNT(*)
----------
     30000

Elapsed: 00:00:00.02
SQL> select count(*) from ashes_bowling_figs_err;

  COUNT(*)
----------
     10000

Elapsed: 00:00:00.01
SQL> 

So, in this scenario, making this change results in the script executing in one third of the time.

We could make this script run faster still…

The sky is the Limit ?

The thing about PL/SQL collections is that they exist in the Process Global Area (PGA) memory of a session.
What’s more, they exist in the “untunable” area of PGA that Oracle has no control over ( up to 11g that is).
Therefore, it’s perfectly possible for a PL/SQL table to hog all of the available memory on the server with, as they say, hilarious results.
Apparently there is a new parameter in Database 12c to stop this happening. You can see details of this in this article by Peter Haeusler.
Making generalisations about what you should do in order to avoid chewing up all of the available RAM in your PGA is somewhat problematic. A lot depends on how much RAM you have to start with, how much is allocated to the OS, and how much to Oracle.
Rather than go too deeply into that particular rabbit hole, I’m simply going to run some tests to see how high it is possible to set the LIMIT clause when processing FORALL inserts with the SAVE EXCEPTIONS clause.

Incidentally, if you do want to check how much of a PGA hog your session is being, the following query may help :

SELECT sn.name, round(ms.value/1024/1024, 2) as MB
FROM v$mystat ms, v$statname sn
WHERE sn.statistic# = ms.statistic#
AND ms.statistic# in (33,34)
/

Anyway, back to our LIMIT question.
We’re going to try a load containing, to take a completely random number, 65535 erroring records ( 262140 in total).
We’ll do this without a LIMIT clause at all on the BULK COLLECT.

The code we’re running now is :

DECLARE
--
-- Forall and NO Limits...I like to live *on the edge* 
--

    TYPE typ_bowlers IS TABLE OF ashes_bowling_stg%ROWTYPE;
    tbl_bowlers typ_bowlers;
    
    e_dml_err EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_dml_err, -24381);
    --
    -- Array for error handling
    --
    TYPE typ_err IS TABLE OF ashes_bowling_figs_err%ROWTYPE
        INDEX BY PLS_INTEGER;
    tbl_err typ_err;
    tbl_err_empty typ_err;
    l_idx NUMBER;
BEGIN
    --
    -- Just use an implicit cursor now we don't have to loop...
    --
    SELECT player_name, overs, maidens,
        runs, wkts, best, five_fors
    BULK COLLECT INTO tbl_bowlers
    FROM ashes_bowling_stg;

    FORALL i IN 1..tbl_bowlers.COUNT SAVE EXCEPTIONS
        INSERT INTO ashes_bowling_figures
        VALUES tbl_bowlers(i);
EXCEPTION
    WHEN e_dml_err THEN
        FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
            l_idx := SQL%BULK_EXCEPTIONS(j).error_index;
            --
            -- Populate the error array
            --
            tbl_err(j).ora_err_number$ := SQL%BULK_EXCEPTIONS(j).error_code;
            tbl_err(j).ora_err_mesg$ := SQLERRM( SQL%BULK_EXCEPTIONS(j).error_code * -1);
            tbl_err(j).ora_err_rowid$ := NULL; -- need to specify this value in this example
            tbl_err(j).ora_err_tag$ := 'NOLIMIT';
            tbl_err(j).ora_err_optyp$ := 'I';
            tbl_err(j).player_name := tbl_bowlers(l_idx).player_name;
            tbl_err(j).overs := tbl_bowlers(l_idx).overs;
            tbl_err(j).maidens := tbl_bowlers(l_idx).maidens;
            tbl_err(j).runs := tbl_bowlers(l_idx).runs;
            tbl_err(j).wkts := tbl_bowlers(l_idx).wkts;
            tbl_err(j).best := tbl_bowlers(l_idx).best;
            tbl_err(j).five_fors := tbl_bowlers(l_idx).five_fors;
        END LOOP;
        --
        -- ...and populate the error table in one fell swoop ( per iteration)
        --
        FORALL k IN 1..tbl_err.COUNT 
            INSERT INTO ashes_bowling_figs_err
            VALUES tbl_err(k);
        --
        -- Cleardown the error array
        --
        tbl_err := tbl_err_empty;
END;
/   

So, reset the staging data, bounce the database and run…

SQL> @forall_no_limit.sql

PL/SQL procedure successfully completed.

SQL> select count(*) from ashes_bowling_figures;

  COUNT(*)
----------
    196605

SQL> select count(*) from ashes_bowling_figs_err;

  COUNT(*)
----------
     65535

SQL> 

That all looks OK ( leaving aside the aforementioned PGA issues). However, what happens when we add just a single additional error record and re-run ?
So…with 262144 records in the staging table, of which 65536 will error…

SQL> @forall_no_limit.sql

PL/SQL procedure successfully completed.

SQL> select count(*) from ashes_bowling_figures;

  COUNT(*)
----------
    196608

SQL> select count(*) from ashes_bowling_figs_err;

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

SQL> 

Hmmm, it seems we’re missing some errors.
What we can deduce from this is that, unlike a normal PL/SQL collection, SQL%BULK_EXCEPTIONS can hold a maximum 65535 records.
Therefore, this is the maximum value at which the limit clause can be set if you’re using SAVE EXCEPTIONS.
You may regard all of this as somewhat academic. After all, you’d never let something that takes up so much PGA into production, would you ?
Well, it depends. Of course, a process hogging resources from other database sessions will be an issue. However, in this particular example, we’re doing a bulk load of records. This is the sort of thing you’d expect to find on an overnight batch.
If there are other batch jobs dependent on the successful completion of this one, and it’s the only thing running on the system at the time, then you might consider the potential saving in execution time worth the additional system resources taken up.

Comparison on 1 million records

We’re going to tweak our PL/SQL routine one more time and then do a comparison with the SQL on one million records.
Then, just to restore some semblance of sanity, we’ll run against a record set of the same size but which will contain no errors.

The final PL/SQL code then, saved as pga_gobbler.sql :

DECLARE
    --
    -- Super fast, super hungry PL/SQL version.
    -- Mmmm PGA, pile it on !
    --
    TYPE typ_bowlers IS TABLE OF ashes_bowling_stg%ROWTYPE;
    tbl_bowlers typ_bowlers;
    
    e_dml_err EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_dml_err, -24381);
    
    CURSOR c_stg IS
        SELECT player_name, overs, maidens,
            runs, wkts, best, five_fors    
        FROM ashes_bowling_stg;

    --
    -- Array for error handling
    --
    TYPE typ_err IS TABLE OF ashes_bowling_figs_err%ROWTYPE
        INDEX BY PLS_INTEGER;
    tbl_err typ_err;
    tbl_err_empty typ_err;
    l_idx NUMBER;
BEGIN
    OPEN c_stg;
    LOOP
        FETCH c_stg BULK COLLECT INTO tbl_bowlers LIMIT 65535;
        EXIT WHEN tbl_bowlers.COUNT = 0;
        BEGIN
            FORALL i IN 1..tbl_bowlers.COUNT SAVE EXCEPTIONS
                INSERT INTO ashes_bowling_figures
                VALUES tbl_bowlers(i);
        EXCEPTION
            WHEN e_dml_err THEN
                FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
                    --
                    -- Loop through and populate the error array
                    --
                    l_idx := SQL%BULK_EXCEPTIONS(j).error_index;
                    tbl_err(j).ora_err_number$ := SQL%BULK_EXCEPTIONS(j).error_code;
                    tbl_err(j).ora_err_mesg$ := SQLERRM( SQL%BULK_EXCEPTIONS(j).error_code * -1);
                    tbl_err(j).ora_err_rowid$ := NULL; -- need to specify the NULL value in this example
                    tbl_err(j).ora_err_tag$ := 'FORALL_EXCEPTION';
                    tbl_err(j).ora_err_optyp$ := 'I';
                    tbl_err(j).player_name := tbl_bowlers(l_idx).player_name;
                    tbl_err(j).overs := tbl_bowlers(l_idx).overs;
                    tbl_err(j).maidens := tbl_bowlers(l_idx).maidens;
                    tbl_err(j).runs := tbl_bowlers(l_idx).runs;
                    tbl_err(j).wkts := tbl_bowlers(l_idx).wkts;
                    tbl_err(j).best := tbl_bowlers(l_idx).best;
                    tbl_err(j).five_fors := tbl_bowlers(l_idx).five_fors;
                END LOOP;
                --
                -- ...and populate the error table in one fell swoop ( per iteration)
                --
                FORALL k IN 1..tbl_err.COUNT 
                    INSERT INTO ashes_bowling_figs_err
                    VALUES tbl_err(k);
                --
                -- Cleardown the error array
                --
                tbl_err := tbl_err_empty;
        END;
    END LOOP;
    CLOSE c_stg;
END;
/   
COMMIT;

So, having setup the test…

SQL> @pga_gobbler.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:15.00

Commit complete.

Elapsed: 00:00:00.15
SQL> 

Just out of idle curiosity, let’s see how much PGA we’ve munched our way through :

NAME									 MB
---------------------------------------------------------------- ----------
session pga memory						       1.62
session pga memory max							 58

SQL> 

The size of the staging table itself ?

SELECT ROUND(bytes/1024/1024,2) as MB
FROM user_segments
WHERE segment_name = 'ASHES_BOWLING_STG'
/

	MB
----------
	42
	
SQL>

So, we seem to have used an extra 58MB, but the amount of data we used to populate the array is only 42MB. I must confess that I’m not entirely sure why it’s grabbed that extra 16MB.

Now for the SQL…

SQL> @load_bowlers_le.sql

750000 rows created.

Elapsed: 00:00:55.84

Commit complete.

Elapsed: 00:00:00.01
SQL> 

As you might expect, the increased number of error records has simply exacerbated the comparatively slow performance.
On this record set, the SQL takes almost 56 seconds as opposed to 15 seconds with the PL/SQL approach.

Having said that, it’s worth considering that the error rate in this load ( as well as the consistent distribution of error records throughout the data set) is somewhat unusual.
Often, you’ll want to employ the error logging method of inserting records on the off-chance that the odd record fails.

Sunny day test – with no errors

In this test, we’ll have the same number of records, but we’ll tweak things a little by ensuring that they all succeed.
The easiest way to do this is to remove the Not Null constraint from the column on our target table :

ALTER TABLE ashes_bowling_figures
    MODIFY five_fors NULL
/

Now all of our records should be successfully loaded.

Running the PL/SQL …

SQL> @pga_gobbler.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.52

Commit complete.

Elapsed: 00:00:00.02

…and now for the SQL…

SQL> @load_bowlers_le.sql

1000000 rows created.

Elapsed: 00:00:23.99

Commit complete.

Elapsed: 00:00:00.15

That was unexpected. Even with no row-by-row error processing, the SQL is still taking much longer (24 seconds as opposed to around 4.5).
I questioned this result to the extent that I re-booted the machine (not just re-starting the database) to verify this result.
It would be interesting to compare this to a straight insert/select, that doesn’t log errors…

set timing on
INSERT INTO ashes_bowling_figures
(
    player_name, overs, maidens,
    runs, wkts, best, five_fors
)
SELECT player_name, overs, maidens,
    runs, wkts, best, five_fors
FROM ashes_bowling_stg
/
COMMIT;

Running this reveals something closer to our expectations :

SQL> @just_ins.sql

1000000 rows created.

Elapsed: 00:00:02.66

Commit complete.

Elapsed: 00:00:00.01

At this point, the most interesting comparison is between this vanilla insert/select statement and the log errors statement.
Looking at the tkprof output we can see the following for the straight insert :

INSERT INTO ashes_bowling_figures
(
    player_name, overs, maidens,
    runs, wkts, best, five_fors
)
SELECT player_name, overs, maidens,
    runs, wkts, best, five_fors
FROM ashes_bowling_stg

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      1.19       2.49       5289      11732      47997     1000000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      1.20       2.49       5289      11732      47997     1000000

The current gets figure here is pretty much in line with what you see for the PL/SQL script. The Log Errors statement however, looks a bit different :

INSERT INTO ashes_bowling_figures
(
    player_name, overs, maidens,
    runs, wkts, best, five_fors
)
SELECT player_name, overs, maidens,
    runs, wkts, best, five_fors
FROM ashes_bowling_stg
LOG ERRORS INTO ashes_bowling_figs_err ('TOO FAST') REJECT LIMIT UNLIMITED

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      8.46      23.85       5291       9125    1057798     1000000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      8.48      23.86       5291       9125    1057798     1000000

Looking at the current column, we can see that the Log Errors statement is performing twenty times the number of consistent gets. That should explain why it’s not as quick as the other statements.

I should really give you some detailed hypothesis as to why this is happening, but the truth is, I have no idea.
All I do know is that, in this test at least, using PL/SQL instead of SQL is around 5 times faster.
You may well want to check this out yourself, but, in my testing at least, these results are pretty consistent.

What can we conclude from all of this ?

  1. The PL/SQL SQL%BULKEXCEPTIONS collection holds a maximum of 65535 records
  2. The SQL LOG ERRORS statement does a row-by-row insert of any error records it encounters
  3. It also performs a much higher number of db block gets than a conventional insert statement, even when there are no error records to process

The reasons for this behaviour are not obvious (to me, at least). What is apparent is that, in this particular instance, PL/SQL is faster than SQL.
Of course, what is true right on 11g may well not be on 12c and subsequent releases of Oracle.
The optimum approach to a given problem will change over time as the technology advances.
Because of this, it’s probably a good idea to verify anything Mr Google tells you (including what I’ve said in this post) on your own hardware/database/application.
As for Mitch and the guys, they’re off to take Urn Polishing classes as it seems they’ll be looking after it for quite a while.


Filed under: Oracle, PL/SQL, SQL Tagged: dbms_errlog.create_error_log, dbms_monitor, dbms_monitor.session_trace_disable, dbms_monitor.session_trace_enable, find the tracefile for the current session, limit, log errors, maximum number of records in sql%bulk_exceptions, ora-24381, save exceptions, sql%bulk_exceptions, tkprof, v$diag_info
811 /
Follow / 20 Jan 2014 at 5:53pm

MIke - this is an impressive analysis! I must confess that I have only read through it lately and will go back over it in more detail when I have more time. But I did want to make one point - and perhaps you would consider addressing it in the post:

LOG ERRORS and SAVE EXCEPTIONS are similar, but not the same, in several ways, but most importantly in this:

LOG ERRORS suppresses errors in the SQL layer at the ROW LEVEL.

SAVE EXCEPTIONS suppresses exceptions in the PL/SQL layer at the STATEMENT LEVEL.

For individual inserts, there is no difference - each statement = one (potentially) new row.

But for SQL statements that may affect more than one row, you will see very different behavior between these two mechanisms.