CODEWITHSUNDEEP
sqloracle-databaseasynchronous
L Lawliet
L Lawliet

Reputation: 2635

oracle sql how to do asynchronous queries

I have multiple select queries which I want to execute asynchronously. How can I do this in oracle sql ?

I basically want to test something and so want to simulate workload so I don't really care about the result and I know I can do this in multiple threads but this is specific and so would prefer if I can do this entirely in sql. procedures are fine though. NOTE: there are no update queries only select.

I read about nowait but am not sure how to use it in oracle.

I tried something like -

select * from foo with(nowait) where col1="something";

This is the error I got -

with(nowait)
*
ERROR at line 3:
ORA-00933: SQL command not properly ended

Upvotes: 3

Views: 3930

Answers (2)

Emu
Emu

Reputation: 504

I think you could use DBMS_SCHEDULER to schedule some sql or procs that execute SQL.

However this is probably not the best way to do this

There are tools for this. The best way maybe to write a procedure you can call from the web and then you can use any performance testing tool that can make a web call...its worked for me before.

You may also consider:

http://sqlmag.com/database-performance-tuning/testing-heavy-load-simulating-multiple-concurrent-operations

Upvotes: 0

Nick.Mc
Nick.Mc

Reputation: 19184

The Oracle info on NOWAIT says:

Specify NOWAIT if you want the database to return control to you immediately if the specified table, partition, or table subpartition is already locked by another user. In this case, the database returns a message indicating that the table, partition, or subpartition is already locked by another user.

This will not do what you want.

Asynchronous queries are an application thing, not a SQL thing. For example I can open TOAD and open a dozen windows and run long queries in all of them and still open another window and run another query. I could open a dozen instances of SQLPLUS and do the same thing. Nothing in the query lets me do this, it's in the application.

Upvotes: 6

Related Questions