oracle sql how to do asynchronous queries

Question

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

Nick.Mc · Accepted Answer · 2014-07-29 02:09:03Z

6

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.

answered Jul 29, 2014 at 2:09

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Emu · Accepted Answer · 2014-07-29 12:40:02Z

0

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

answered Jul 29, 2014 at 12:40

Emu

4947 silver badges16 bronze badges

Collectives™ on Stack Overflow

oracle sql how to do asynchronous queries

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related