I have a million row table in Oracle 11g Express and want to run a slow SQL select query so I can test stopping agents in various ways and observe the results on the database server.
However no matter what I do, like self joins against a non-indexed column, selecting random rows using dbms_random, where/order by statements using non-indexed columns, the results all finish within a few seconds.
Is there a query I can write that will make it take a few minutes?
I'm not quite sure what you mean by "stopping agents" in this context. The only "agent" I can think of in this context would be an Enterprise Manager agent but I doubt that's what you're talking about and I don't see why you'd need a long-running query for that.
The simplest way to force a query to run for a long time is to have it sleep for a bit using the dbms_lock.sleep procedure. Something like
CREATE OR REPLACE FUNCTION make_me_slow( p_seconds in number )
RETURN number
IS
BEGIN
dbms_lock.sleep( p_seconds );
RETURN 1;
END;
which you can call in your query
SELECT st.*, make_me_slow( 0.01 )
FROM some_table st
That will call make_me_slow once for every row in some_table. Each call to make_me_slow will take at least 0.01 seconds. If some_table has, say, 10,000 rows, that would take at least 100 seconds. If it has 100,000 rows, that would take 1,000 seconds (16.67 minutes).
If you don't care about the results of the query, you can use the dual table to generate the rows so that you don't need a table with materialized rows. Something like
SELECT make_me_slow( 0.01 )
FROM dual
CONNECT BY level <= 20000
will generate 20,000 rows of data and take at least 200 seconds.
If you want a pure SQL query (which gives you less ability to control exactly how long it's going to run),
select count(*)
from million_row_table a
cross join million_row_table b
will generate a 1 million x 1 million = 1 trillion row result set. That's likely to run long enough to blow out whatever TEMP tablespace you have defined.
From Oracle12c you could use:
WITH FUNCTION my_sleep(t NUMBER) RETURN NUMBER
AS
BEGIN
DBMS_LOCK.SLEEP(t);
RETURN t;
END;
SELECT my_sleep(2)
FROM dual;
-- 2 after two seconds
This approach is nice because:
In most situations, the developer will not have the permission to create a FUNCTION or will not have access to DBMS_LOCK and will need GRANT execute on Schema. The not so subtle way is to change the query (temporarily) to lock a record by using "for update".
select * from employee where empId = 1 for update;
Now you can run your code and query will wait for lock to be released. You will have to manually unlock the record after the test is complete.
