Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Prasad.

Asked: February 07, 2022 - 5:05 am UTC

Last updated: February 10, 2022 - 5:15 am UTC

Version: 19c

Viewed 1000+ times

You Asked

Hi Tom,

I would like to do a performance testing on particular SQL before I release it to PRODUCTION, performance testing i mean, executing same SQL simultaneously from different sessions (may be from 20, 30, 50 sessions), purpose of this is to ensure how SQL will perform when no.of users who uses the query from application are increased.

Thank you.

and Connor said...

Easy is to use the scheduler, which can now run SQL Plus scripts, eg

https://connor-mcdonald.com/2018/06/20/the-death-of-utl_file/

If you want control over the launching of SQL Plus, check out this video I did which uses a simple mechanism to do that



Here's a quick primer

1) create a table T
2) Start a main sqlplus session that does "lock table T in exclusive mode"
3) start as many SQL Plus sessions as you want

Windows

start sqlplus user/pass @myscript

Unix

sqlplus user/pass @myscript &

4) the first thing in 'myscript' is

lock table T in share mode

Now they will all sit there doing nothing until you return to your main sqlplus and enter "commit"


Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.