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
GRANT EXECUTE ON DBMS_LOCK TO PUBLIC
Code examples
BEGIN
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 );
END;
/
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
WHERE name = 'DBMS_LOCK'
UNION
SELECT name
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,
decode(type,
'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,
decode(lmode,
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,
decode(request,
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,
decode(block,
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
DBA_LOCK
Source: $ORACLE_HOME/rdbms/admin/catblock.sql
Related Parameters:
_enqueue_locks
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:
ALTER TABLE DISABLE TABLE LOCK
Related Views:
V$_LOCK
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.
No comments:
Post a Comment