I really did not have anything I thought was interesting for this week's tip, so I had been avoiding even starting to put something together. But while doing some research on the Internet on regular expressions (yes, I do lead an exciting life), I came across something from Tom Kyte that not only was interesting, but I could apply to a real world problem (and had nothing at all to do with regular expressions).

The Tom Kyte article is available at http://www.oracle.com/technology/oramag/oracle/06-jul/o46asktom.html, and the point he is making is that the perception of performance is frequently as important as performance. If the user has to wait for something to happen, performance is bad. If the user does not have to wait, then performance is good, even if the amount of time the process takes is the same. Tom's example is about executing the command DROP USER username CASCADE. Executing this command interactively could take some time if the user schema contains a large number of objects, and the perception is that performance is bad because the user has to wait. But by using DBMS_JOB to run the same command in the background, control is returned to the user immediately, so performance is good. Take a look at the article for details.

In our case, we developed an application into which data is loaded from text files. The load process is complex, and it is not uncommon for files to contain several hundred thousand records. The application allows the user to enter information about processing the file: format, layout, default values, etc., and to load the data into the database immediately in the foreground, immediately in the background, or to schedule the process to run at a specific time. The user also has the ability to delete the results of a load, if, for example, the layout was defined incorrectly. Since the delete process is relatively simple, the procedure runs interactively and can take several minutes for large amounts of data. Performance is not acceptable.

This was the perfect opportunity to apply this technique. The application provides a way to mark a set of data as "deleted," and the user interface calls a stored procedure to execute the delete of the data. So the user interface required no changes. I split the processing of the current stored procedure into two procedures. The original stored procedure that was deleting the data now marks the data set as deleted and submits the second stored procedure as a job that completes deleting the data. Control is returned immediately to the user, who sees dramatically improved performance.

The original stored procedure, in package pkg_mypkg, was structured like this (code sanitized to protect the innocent).

PROCEDURE p_del_data (
   a_arg1   IN   iINTEGER,
   a_arg2   IN   VARCHAR2
IS
-- Define variables

.

BEGIN
-- Do some validation

.

   UPDATE tab WHERE ...;

   DELETE tab4 WHERE ...;

   DELETE tab3 WHERE ...;

   DELETE tab2 WHERE ...;

   DELETE tabl WHERE ...;

   COMMIT;

EXCEPTION

-- Handle errors

END p_del_data;

The new stored procedures look like this.

PROCEDURE p_del_data (
   a_arg1   IN   iINTEGER,
   a_arg2   IN   VARCHAR2
IS
-- Define variables

   l_job   INTEGER;

.

BEGIN
-- Do some validation

.

   UPDATE tab WHERE ...;

   COMMIT;

   DBMS_JOB.SUBMIT (l_job, 'pkg_mypkg.p_del_data_job (' || a_arg1 || ', ''' || arg2 || ''');');

   COMMIT;

EXCEPTION

-- Handle errors

END p_del_data;

PROCEDURE p_del_data_job (
   a_arg1   IN   iINTEGER,
   a_arg2   IN   VARCHAR2
IS
-- Define variables

.

BEGIN
   DELETE tab4 WHERE ...;

   DELETE tab3 WHERE ...;

   DELETE tab2 WHERE ...;

   DELETE tabl WHERE ...;

   COMMIT;

EXCEPTION

-- Handle errors

END p_del_data_job;

All procedures need to be defined in the package header.

The results, as seen through SQL*Plus, are the following. Using the original procedure, the user waited one minute, 40 seconds.

exec pkg_mypkg.p_del_data (1170, 'N')
pkg_mypkg.p_del_data start 28-JUN-2006 15:47:12
pkg_mypkg.p_del_data end 28-JUN-2006 15:48:52

Using the new procedure, the user waited just one second.

exec pkg_mypkg.p_del_data (1171, 'N')
pkg_mypkg.p_del_data start 28-JUN-2006 15:52:31
pkg_mypkg.p_del_data end 28-JUN-2006 15:52:32

Perception is reality.

Note: This tip was tested on Oracle9i.

Was this tip useful? Did you find any errors? Do you have any suggestions? Do you care? Click here for the tip feedback page. Thank you.