Archive for the ‘PL/SQL Lock Timer’ Category

Oracle’s PL/SQL Lock Timer

December 1, 2009 Leave a comment

The amount of time a user or application has slept through the USER_LOCK.SLEEP or DBMS_LOCK.SLEEP procedures.
When investigating Oracle Wait-Events we often look at those events where applications consume precious system resources or are in direct contention with other users or applications. These resource waits revolve around excessive CPU, memory, or disk usage. Events that spin for Oracle internal structures such as latches or enquires can also be of concern. As their name suggests, Wait-Events DO ‘wait’ on some event to complete before a user or application can continue with its work. Contention for system resources will overwhelm Oracle’s ability to immediately process the SQL and ultimately cause applications to accumulate time in resource specific Wait-Events.

On the flip-side of resource waits there are what are called idle events. These events do not wait for any specific resource but record the time Oracle is waiting for a work request from the application. Many practitioners of Oracle performance will not even look at these idle events because they do not consume resources and are not limiting what Oracle can do at any given point in time. Some will even go so far as to state that the idle events have no meaningful information. This is not necessarily true. Take for instance the idle event ‘SQL*Net message from client’. This idle event is not inhibiting the database server from performing work but, as many agree, is an indication of poor response from client to database server. While idle events are not the result of direct resource contention, they are an accurate measure of accumulated delays in the application imposed by Oracle.

The ‘idle’ event ‘PL/SQL lock timer’ is worth watching because it points us in the direction of application response, throughput, and possible coding issues. The PL/SQL lock timer event is, as the command that issues it states, a sleep mandated by the application code. The application is idle and doing nothing. This means that if the application sleeps for a combined interval of 5 minutes, the response to the user or complete run of the application will take at least 5 minutes longer.

While this event does not require resources and thus is not an Oracle resource contention issue, it is our job to provide the quickest response to users and push applications through our system to increase the user’s perceived performance. Most often, sleeps are put into an application for serialization of transactional events or spinning on queues until something happens. When the event occurs or a queue is populated, then the application continues its work. We should ask ourselves why an application is sleeping for any amount of time and provide alternatives if necessary to reduce this idle event. You will gain quicker responses if you trigger an action instead of the action waiting for an event. How to look at the PL/SQL lock timer event To initiate a sleep for the current session for five seconds.

Oracle has four separate ways to induce a sleep into PL/SQL.

Source : {ORACLE_HOME}/rdbms/admin/dbmslock.sql

Name             Description

nl_mode          NuLL Value 1

ss_mode          Sub Shared: used on an aggregate object to indicate that share locks are being acquired on subparts of the object value 2

sx_mode          Sub eXclusive: used on an aggregate object to indicate that exclusive locks are being acquired on sub-parts of the object value 3

s_mode            Shared: indicates that the entire aggregate object has a share lock, but some of the sub-parts may additionally have exclusive locks value 4

ssx_mod          Shared SubeXclusive value 5

x_mode            eXclusive value 6

Grant access DBMS_LOCK to all users

Code examples

  dbms_output.put_line( 'Start Time: ' || dbms_utility.get_time );
  dbms_lock.sleep(seconds => 10);
  dbms_output.put_line( 'End Time:   ' || dbms_utility.get_time );
Start Time: 1119218
End Time:   1120218

SQL>  execute sys.dbms_lock.sleep(5);

To take a look at current sessions that are using the SLEEP command. Notice that the column P1 in V$SESSION does not represent the amount of time the session has slept but in fact represents the duration this session will sleep. Also note that this column is in centiseconds and as such the five seconds issued for a sleep has been translated in to 500 centiseconds.

SQL>  select osuser,event,p1
       from v$session
      where event = 'PL/SQL lock timer'

OSUSER       EVENT             P1
------------ ----------------- ---
oracle       PL/SQL lock timer 500

If you wanted to take a look at the total amount of time that has accumulated for this type of wait event, you can look at the V$SYSTEM_EVENT view. Also note that in this particular view the time waited is also in centiseconds but as you can see the time actually waited may be more that expected. On our test system this equated to about 1.024 seconds to 1 second requested.

SQL>  select time_waited
      from v$system_event
      where event = 'PL/SQL lock timer';

SQL>  select package_name, argument_name,
             data_type, data_length,
       data_precision, pls_type
from  all_arguments
where object_name = 'SLEEP'
order by 1;

To find the Dependencies

SQL>  SELECT referenced_name
FROM dba_dependencies
FROM dba_dependencies
WHERE referenced_name = 'DBMS_LOCK';

Oracle 11g’s data dictionary defines the DBA_LOCK view using the following SQL

SQL> select
sid session_id,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
type) lock_type,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(lmode)) mode_held,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(request)) mode_requested,
to_char(id1) lock_id1, to_char(id2) lock_id2,
ctime last_convert,
0, 'Not Blocking', /* Not blocking any other processes */
1, 'Blocking', /* This lock blocks other processes */
2, 'Global', /* This lock is global, so we can't tell */
to_char(block)) blocking_others
from v$lock

Data Dictionary Views of LOCK


Source: $ORACLE_HOME/rdbms/admin/catblock.sql

Related Parameters:

dml_locks                         Specifies the maximum number of DML locks–one for each table modified in a transaction.
enqueue_resources      Sets the number of resources that can be concurrently locked by the lock manager.

Related Commands:


Related Views:


V$ACCESS       Displays objects in the database that are currently locked and the sessions that are accessing them.

V$ENQUEUE_LOCK Displays all locks owned by enqueue state objects.

V$ENQUEUE_STAT Contains detailed enqueue statistics for each enqueue.

V$GLOBAL_BLOCKED_LOCKS Displays global blocked locks.

V$LOCK        Lists the locks currently held by the Oracle database server and outstanding requests for a lock or latch.

V$LOCKED_OBJECT    Lists all locks acquired by every transaction on the system.

V$RESOURCE         Contains resource name and address information

V$RESOURCE_LIMIT   Displays information about current and maximum global resource utilization for some system resources.

V$TRANSACTION_ENQUEUE   Displays locks owned by transaction state objects.

DBA_BLOCKERS       Shows all sessions holding a lock which is required by another process.

DBA_DDL_LOCKS      Lists all DDL locks held in the database and all outstanding requests for a DDL lock.

DBA_DML_LOCKS      Displays a session if it is not waiting for a locked object but is holding a lock on an object for which another session is waiting.

DBA_KGLLOCK        Lists all the locks and pins held on KGL objects (objects in the Kernel Generic Library cache).

DBA_LOCK_INTERNAL Displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch.

DBA_WAITERS        Shows all sessions waiting for locks and the session that holds the lock.

DBMS_LOCK_ALLOCATED     Describes user-allocated locks.