Oracle Scratchpad

March 29, 2011


Filed under: Oracle,Performance,Troubleshooting — Jonathan Lewis @ 9:42 pm BST Mar 29,2011

One of the problems of building models of Oracle activity is that it’s easy to build the wrong model. One of the commonest issues appears with repetitive actions – how do you write code that repeats a simple action many times in a row. Writing a simple PL/SQL loop is often an adequate approach, but there are cases where a PL/SQL  loop behaves very differently from a long list of individual SQL statements – which is why I’ve occasionally used a very simple-minded approach to avoid that particular trap.

If you checked the directory of my database work area, you’d find an interesting pattern of names made by five (subsequently grown to 7) very short scripts:

q:> dir start*.sql
 Volume in drive Q has no label.
 Volume Serial Number is A8BA-79AA

 Directory of q:\

28/03/2011  08:56               350 start_1.sql
13/01/2005  13:38               110 start_10.sql
13/01/2005  13:39               120 start_100.sql
13/01/2005  13:39               130 start_1000.sql
13/01/2005  13:40               140 start_10000.sql
11/02/2012  01:01               190 start_100000.sql
11/02/2012  01:01               202 start_1000000.sql
               7 File(s)            930 bytes
               0 Dir(s)   8,238,260,224 bytes free

The script start_10000.sql contains just 10 lines:

@ start_1000
@ start_1000
@ start_1000
@ start_1000
@ start_1000
@ start_1000
@ start_1000
@ start_1000
@ start_1000
@ start_1000

and you won’t be surprised to find that the script start_1000.sql also contains just 10 lines:

@ start_100
@ start_100
@ start_100
@ start_100
@ start_100
@ start_100
@ start_100
@ start_100
@ start_100
@ start_100

and so on down the chain, until the script start_1.sql contains the piece of code that I actually want to run many times.

Obviously there are cases where you need a more subtle framework – but it’s very convenient to be able to write a small script holding the piece of code that you want to keep repeating and then run it 10,000 times with just a single call to: @start_10000.sql.


  1. I wonder why I did not think about this approach before :)

    thanks for saving big loads of time, this is really helpful

    Comment by coskan — March 30, 2011 @ 10:57 am BST Mar 30,2011 | Reply

  2. […] cycle as a completely independant SQL script using the “start_1000.sql” script from my previous note […]

    Pingback by ASSM wreck « Oracle Scratchpad — March 30, 2011 @ 9:06 pm BST Mar 30,2011 | Reply

  3. Thanks Jonathan for such a brilliant idea to run some piece of code again and again.

    Jagdeep Sangwan

    Comment by jagdeepsangwan — January 14, 2014 @ 11:59 am BST Jan 14,2014 | Reply

  4. […] comparison here’s the effect of the same data load when operated as 10,000 separate insert statements called from […]

    Pingback by ASSM Help | Oracle Scratchpad — February 1, 2017 @ 10:16 am BST Feb 1,2017 | Reply

  5. […] mechanism of the script start_1000 is something I published a few years ago, and essentially it executes a script called start_1.sql 1,000 times which, for this test, contains […]

    Pingback by Histogram Threat | Oracle Scratchpad — January 30, 2018 @ 8:07 am BST Jan 30,2018 | Reply

  6. […] I ran an SQL*Plus test that executed “set role test_role;” 1,000 times in a row (using my start_1000 trick) with extended tracing enabled, then ran tkprof against the trace file with the option […]

    Pingback by Set Role | Oracle Scratchpad — March 17, 2019 @ 5:55 pm BST Mar 17,2019 | Reply

  7. […] start_10000 script is my mechanism for running a simple piece of code many times, and as the comment following […]

    Pingback by Describe Upgrade | Oracle Scratchpad — April 8, 2019 @ 11:02 am BST Apr 8,2019 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by