Wednesday, June 03, 2015

PL/SQL lock timer

I was looking through V$SYSTEM_EVENT on an Oracle 11.2 production database (as you do) and I noticed that it had waited for exactly 1 second on the PL/SQL lock timer event. Apparently this is the amount of time a database has been waiting for sessions which have been told to sleep by their application. I decided to check this out so I started a new session, slept for 6.7 seconds then looked to see how long my session had been waiting on this event:

SQL> conn /
Connected.
SQL> exec dbms_lock.sleep(6.7);
 
PL/SQL procedure successfully completed.
 
SQL> select time_waited/100
  2  from v$session_event
  3  where event = 'PL/SQL lock timer'
  4  and sid = (select distinct sid from v$mystat)
  5  /
 
TIME_WAITED/100
---------------
           6.71
 
SQL>
 

No comments: