Oracle Scratchpad

May 15, 2007

Slow Code

Filed under: Troubleshooting — Jonathan Lewis @ 10:38 am BST May 15,2007

No, this isn’t going to be about dealing with slow code – it’s about how you can slow your code down when you need to. This may seem a little strange, but for testing purposes you might (for example) want to create a long-running query which didn’t actually do very much work – all you need is a small query with my ‘slow down’ function built in.


create or replace function wait_row(
	i_v	in	varchar2,
	i_sec	in	number default 5
) return varchar2
deterministic
parallel_enable
as
begin
	sys.dbms_lock.sleep(i_sec);
	return i_v;
end;
/

All I’ve done is create a function that accepts and returns an input varchar2 value, but also has a second parameter that tells it how many seconds (to the hundredth) to wait before returning the value.

I’ve created the function as deterministic so that it can be called from SQL, and parallel-enabled it. Here’s a simple example of use – note the elapsed time particularly:


SQL> set timing on
SQL> select table_name from user_tables;   

TABLE_NAME
--------------------
MIN_MAX
T1
T2
EMP
Elapsed: 00:00:00.00
SQL> select wait_row(table_name,2.22) from user_tables;   

WAIT_ROW(TABLE_NAME,2.22)
---------------------------------------------------------------------
MIN_MAX
T1
T2
EMP
Elapsed: 00:00:09.00
SQL>

There are a few oddities of course: most significantly, Oracle now implements (though not 100% successfully) a caching mechanism to minimise the number of times a deterministic function is called. (See this URL for a note I wrote for the Dizwell Wiki some time ago).

You willl have noticed from the output formatting in the test that the return value from function wait_row() is longer than the normal length of a table_name. A varchar2 is implicitly 4,000 bytes when returned from a function – so you have may have to fiddle around a little bit to tidy things up when using this function. Partly for this reason, I actually have a little package with overloaded versions of the function to handles character, date, and numeric inputs.

You will, of course, need the directly granted privilege to run package dbms_lock before you can create a function that calls the sleep() function.

A couple of examples of usage:

11 Comments »

  1. A third parameter defaulted to 0 would allow to – effectively – bypass or allow the caching mechanism by passing or not passing in the rownum. Would it not?

    Comment by Gabe — May 15, 2007 @ 3:36 pm BST May 15,2007 | Reply

  2. Every time I’ve tried to use dbms_lock.sleep(), I’ve run into weird latching problems. This was in the 9.2.0.4. days. I remember that a lot of other things would slow down when I invoked sleep(). Maybe things are different now-would you be willing to run some tests?

    Comment by neil kodner — May 15, 2007 @ 5:39 pm BST May 15,2007 | Reply

  3. gabe,

    I can’t follow you
    Could you please explain ?

    Sounds very interesting, can I really switch the caching mechanism on / off ?

    Comment by matthew — May 16, 2007 @ 8:30 am BST May 16,2007 | Reply

  4. [...] of the first things I did with the wait_row() function that I described in my last blog, was to set up a simple way to emulate this view in 8i and 9i. The code, which has to be run under [...]

    Pingback by v$session_wait_history « Oracle Scratchpad — May 16, 2007 @ 10:14 am BST May 16,2007 | Reply

  5. Matthew,

    Well, you’re not switching off anything really. You just make it so there is nothing to cache: the cache is based on the input … add something unique, like rownum, and then you’re sleeping on every row. The function remains deterministic.

    create or replace function wait_row(
    i_v in varchar2,
    i_sec in number default 5,
    i_rnm in number default 0
    ) return varchar2
    deterministic
    parallel_enable
    as
    begin
    sys.dbms_lock.sleep(i_sec);
    return i_v;
    end;
    /

    SQL> select wait_row(n,1) from x;

    WAIT_ROW(N,1)
    ————————————————

    0001
    0000
    0001
    0000
    0001
    0000
    0001
    0000
    0001
    0000
    0001

    11 rows selected.

    Elapsed: 00:00:03.01
    SQL> select wait_row(n,1,rownum) from x;

    WAIT_ROW(N,1,ROWNUM)
    ————————————————

    0001
    0000
    0001
    0000
    0001
    0000
    0001
    0000
    0001
    0000
    0001

    11 rows selected.

    Elapsed: 00:00:11.03

    Caveats?

    Comment by Gabe — May 16, 2007 @ 4:24 pm BST May 16,2007 | Reply

  6. Gabe,

    for your case – why not just get rid of the deterministic clause?

    That way you don’t need to play with a rownum’s and such.

    Comment by Alexander Fatkulin — May 16, 2007 @ 11:46 pm BST May 16,2007 | Reply

  7. Alexander,

    Quite true.
    I just kept “building on” … taking the deterministic clause out is indeed all it takes to avoid the caching.

    Thanks.

    Comment by Gabe — May 17, 2007 @ 6:03 pm BST May 17,2007 | Reply

  8. gabe

    thank you, I just didn’t get it at first

    Comment by matthew — May 18, 2007 @ 7:06 am BST May 18,2007 | Reply

  9. Gabe, Alexander: My error. If you want to use a pl/sql function to build a ‘function-based index’ it has to be deterministic. This has got me into the habit of declaring any function I call from SQL a deterministic function, even though it’s not necessary.

    Comment by Jonathan Lewis — May 18, 2007 @ 9:54 am BST May 18,2007 | Reply

  10. [...] — Jonathan Lewis @ 8:13 pm UTC Jun 15,2008 Some time ago I wrote a note about using dbms_lock() to slow down your SQL, and followed up with an example of using this type of code to help with [...]

    Pingback by Event snapshots « Oracle Scratchpad — June 15, 2008 @ 8:13 pm BST Jun 15,2008 | Reply

  11. […] use of “slowdown” functions in the past, but with 12c I don’t need to create a separate row_wait function – I can define it on the fly, and use it […]

    Pingback by 12c Funny | Oracle Scratchpad — June 30, 2013 @ 7:11 pm BST Jun 30,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,013 other followers