CODEWITHSUNDEEP
sqloracle-databaseplsql
ErikL
ErikL

Reputation: 2041

Finding out what a PL/SQL script is waiting for

Lets say a user is running a huge PL/SQL script, consisting of 2000 lines, and somewhere in there, there's a statement that is "waiting" for some condition to become true. For that a construction like this example is used:

BEGIN

  --Wail until 2 o clock
  WHILE (to_date('06-01-2014 02:00','dd-mm-yyyy hh24:mi') > sysdate)
  LOOP
    DBMS_LOCK.SLEEP(60);
  END LOOP;

  --code to execute after 2 o clock

END;

Now there are several constructions of this kind in the script, and the script seems to be running forever, so the user asked me where it's at. The difficulty is that I can see in the dynamic performance views that it is running, for instance using this query:

SELECT S.USERNAME,
  s.sid,
  s.osuser,
  t.sql_id,
  sql_text
FROM v$sqltext_with_newlines t,
  V$SESSION s
WHERE t.address  =s.sql_address
AND t.hash_value = s.sql_hash_value
AND s.status     = 'ACTIVE'
AND s.username  <> 'SYSTEM'
ORDER BY s.sid,
  t.piece;

But that will return the entire script, not the statement it's currently at. Is there any way to figure this out?

Upvotes: 3

Views: 2047

Answers (2)

haki
haki

Reputation: 9779

What @Kombajn zbożowy suggested is the best way to track plsql execution at runtime - if you prepared for that and set up the calls in your script.

If you got stuck with code that's not using dbms_application_info :

  1. v$session has several helpful columns

    v$session.sql_id will give you the current running sql statement - even when executed in a plsql block.

    v$session.event - gives you the wait event. i think that dbms_lock is considered idle (check for your self).

    v$session.ROW_WAIT_OBJ# is the object_id (dba_objects.object_id) of the current object in the wait event.

  2. v$active_session_info - snapshot of v$session. you can trace back the session since a given time and query the columns mentioned above.

P.S one other thing, instead of looping every 60 seconds and check if the date has passed - isn't it simpler to calculate the number of seconds till the desired time and just wait ?

Upvotes: 1

Kombajn zbożowy
Kombajn zbożowy

Reputation: 10703

I'm not aware of any way to get currently executing line of a PL/SQL program.

But you can use dbms_application_info.set_module/set_action before the sleep statement and it will be visible in v$session.module/action columns.

SQL> select sys_context('USERENV', 'SID') from dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
273


SQL> exec dbms_application_info.set_module('My program', 'Waiting for the hell to freeze');

PL/SQL procedure successfully completed.


SQL> exec dbms_lock.sleep(60);

....

In another session:

SQL> select sid, module, action from v$session where sid = 273;

       SID MODULE                 ACTION
---------- ---------------------- ----------------------------------
       273 My program             Waiting for the hell to freeze

Upvotes: 6

Related Questions