How to create a slow SQL query?

Question

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?

Bandeau

I'm sure there is a query that will do what you want, but without a schema it's going to be very difficult to suggest one. – Infracostal

How about a CROSS JOIN? – Infamous

Reset the memory config to extra small so it has to use on-disk scratch space – Coal

I don't agree with the "off-topic" vote - we have many questions on SQL on SO, and an "agent" is a general enough topic to include custom programs. For example, being able to execute slowly on demand could be useful for testing UI interactions. – Psi

Answer 1

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.

Littlejohn

By agents I mean running a query from various clients and cancelling the query there to see whether the database query itself is stopped. I also would like to run a SQL query without any functions involved. Just a plain (inefficient) SELECT statement. – Bandeau

When I tried dbms_lock.sleep in a select and stop the query from the client I get the following error: "ORA-03127: no new operations allowed until the active operation ends". I see that a CANCEL_CURSOR is issued before this appears and thought maybe it's due to the dbms_lock.sleep function and that maybe it would behave differently using a select without this function. – Bandeau

@user3533880 - You should expect to get the same behavior whether your query includes a function or not. I did update my answer with an example of a query that should run long enough to exhaust your TEMP tablespace. – Littlejohn

The function that contained dbms_lock.sleep that I was using only returned 1 row and that was the problem. If it returns a lot of rows then it works great. The reason is that when I observe the Oracle process initially the state is 'active' and it cannot be stopped. After a while it rotates between active/inactive state (maybe when it's returning data to the client) and a CANCEL_CURSOR request stops the Oracle process while in an inactive state. – Bandeau

@user3533880 - The function returns a single value, the query will return however many rows are returned by the underlying query. Rows will generally be returned to the client in small batches as they are materialized. The client can certainly stop requesting additional batches which cause Oracle not to materialize more results. When most people talk about long-running queries, though, they are talking about queries that take a long time for Oracle to generate a single batch in which case the client cannot stop the execution. – Littlejohn

I see, so if the long running query is processing the request (and not just returning data to the client) it is not possible to stop the process from a client since it's in an 'active' state. However, I was able to stop the process when I clicked the stop icon when running the request in SQL Developer. How was that possible? – Bandeau

@user3533880 - Assuming that no rows had been returned and that the client was waiting for the database, the client can simply stop waiting for the database to respond. The database will continue running the query but the client will be free to issue more queries. – Littlejohn

Answer 2

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:

• you don't need to use separate PL/SQL block (BEGIN ... END;)
• it is fully contained query
• does not "pollute" your schema (no need for creation object privilege)
• it could be used for Time-Based Blind SQL Injection testing. More info: www.owasp.org/index.php/Blind_SQL_Injection

Weathers

@DNA Starting from Oracle 18c: https://mcmap.net/q/360089/-sleep-function-in-oracle – Weathers

Answer 3

BEGIN
DBMS_LOCK.sleep(14);
END;
/
select * from table_c;

Acyl

Answer 4

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.

Giorgia