How to Make a Long Running Query

An article by -Matt-    No Comments

Recently a colleague sent me a quick instant message asking if I knew of any way in Oracle to make a query run long enough to test a “cancel query” function he was writing in Java.  He had tried several queries that usually produced the results he was looking for, however there wasn’t enough data in his test environment to reproduce the wait time he needed.  I thought about it for a minute and then proposed the following solution.

Oracle provides the DBMS_LOCK package which allows users to manage their own locks.  One handy subprogram in this package in the SLEEP method, which suspends the current session for the specified number of seconds.  I have used DBMS_LOCK.SLEEP a number of times in the past so I knew this would do the trick.

The first thing needed was the ability to execute this package method, which is not something granted to all users by default.  I had my colleague issue the following grant as the SYS user in his test database:

GRANT EXECUTE ON DBMS_LOCK TO public;

Once that was done, all that was needed was a quick function to make his query run long.  Hence, the following:

CREATE OR REPLACE FUNCTION query_wait
(
   i_wait_secs IN PLS_INTEGER
)
   RETURN NUMBER
IS
BEGIN
   DBMS_LOCK.SLEEP(i_wait_secs);
   RETURN i_wait_secs;
END;
/

Here’s our new helper function in action:

matt:~>sqlplus scott/tiger@local

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 24 05:55:54 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SET TIMING ON

SQL> SELECT query_wait(10)
  2    FROM dual
  3  /

QUERY_WAIT(10)
--------------
            10

Elapsed: 00:00:10.01

And that’s all there is to it. Instant, long running queries. Enjoy!