13-3. Speeding Up Read/Write Loops
Problem
You have identified a loop that reads and writes large batches of data. You want to speed it up.
Solution
Use a BULK COLLECT statement to fetch the target data records, and then use a FORALL loop to update the local database. For example, suppose you want to speed up the sync_hr_data procedure demonstrated in Chapter 11:
CREATE OR REPLACE PROCEDURE sync_hr_data AS
CURSOR driver IS
SELECT *
FROM employees@hr_data;
TYPE recs_type IS TABLE OF driver%ROWTYPE INDEX BY BINARY_INTEGER;
recs recs_type;
BEGIN
OPEN driver;
FETCH driver BULK COLLECT INTO recs;
CLOSE driver;
FORALL i IN 1..recs.COUNT
UPDATE employees
SET first_name = recs(i).first_name
WHERE employee_id = recs(i).employee_id;
END sync_hr_data;
Run the profiler procedures to collect additional statistics:
EXEC DBMS_PROFILER.START_PROFILER ('Test2', 'Testing Two'),
EXEC sync_hr_data;
EXEC DBMS_PROFILER.FLUSH_DATA;
EXEC DBMS_PROFILER.STOP_PROFILER;
Query the underlying tables to see the results of the analysis:
COL line# FORMAT 999
COL hundreth FORMAT A6
SELECT d.line#,
TO_CHAR (d.total_time/10000000, '999.00') hundreths,
s.text
FROM user_source s,
plsql_profiler_data d,
plsql_profiler_units u,
plsql_profiler_runs r
WHERE r.run_comment = 'Test2'
AND u.runid = r.runid
AND u.unit_owner = r.run_owner
AND d.runid = r.runid
AND d.unit_number = u.unit_number
AND s.name = u.unit_name
AND s.line = d.line#
ORDER BY d.line#;
1 .00 PROCEDURE sync_hr_data AS
3 .00 CURSOR driver is
4 11.54 SELECT *
5 .00 FROM employees@hr_data;
12 .00 OPEN driver;
13 1.61 FETCH driver BULK COLLECT INTO recs;
14 .01 CLOSE driver;
16 1.15 FORALL i IN 1..recs.COUNT
21 .00 END sync_hr_data;
How It Works
The procedure is updated from the previous recipe to use a BULK COLLECT statement to gather the data into a collection. The update statement uses the FORALL command to pass the entire collection of data to the Oracle engine for processing rather than updating one row at a time. BULK COLLECT and FORALL loops pass the entire dataset of the collections to the database engine for processing, unlike the loop in recipe 13-2, where each iteration passes only one record at a time from the collection to the database. The constant switching back and forth between PL/SQL and the database engine creates unnecessary overhead.
Perform the following steps to collect statistics on the update procedure:
- Run the
DBMS_PROFILER.START_PROFILERroutine to begin the process of collecting statistics. You use the two parameters of the routine to give the run a name and to post a comment. Unique names are not required, but doing so will make it easier to query the results later. - Run the
sync_hr_dataprogram to collect statistics. - Run the
DBMS_PROFILER.FLUSH_DATAprocedure to write the data collected to the tables. - Run the
DBMS_PROFILER.STOP_PROFILERroutine to, as the name implies, stop the collection of statistics.
The query joins the profiler data, using the run name of Test2, with the source code lines to display executable lines and the execution time, in hundredths of a second. The raw data stores time in nanoseconds. The query results show three lines of code with actual execution time.
Comparing these results with the previous recipe, we note a 28 percent improvement, 2.25 to 1.61, in fetching the records via the BULK COLLECT statement, and a 30 percent improvement, 1.64 to 1.15, in the writing of the records via the FORALL statements. This improvement is realized while processing only 107 records. Greater gains can be realized with larger data sets, especially when selecting records via a remote database link as there are fewer context switches between PL/SQL and the Oracle engine.