OEM Database Control Versus OEM Grid Control

Enterprise Manager provides two separate consoles that you can use to monitor your database: Database Control and Grid Control.

Database Control is the Enterprise Manager Web-based application for managing Oracle Database 11g Release 1 (11.1) and later. Database Control is installed and available with every Oracle Database 11g installation. From Database Control, you can monitor and administer a single Oracle Database instance or a clustered database.


Grid Control is the Enterprise Manager console you use to centrally manage your entire Oracle environment. Within Grid Control, you access the multiple database targets using the Targets tab, then Databases.

How to manage locks from OEM Grid Control

We can use Oracle Enterprise Manager (OEM) grid control to identify and resolve locking situations. We can find all current locks in the instance, including the blocking and the blocked sessions. We can also kill the blocking session from OEM Grid control.

We can view blocking session details by clicking the Performance tab in the Home page. Click Blocking Sessions under the Additional Monitoring Links section to go to the Blocking Sessions page. The Blocking Sessions page contains details for both the blocking as well as the blocked sessions. We can see the exact wait event, which will be enq: TX row lock contention when one session blocks another. We can find out the exact SQL statement that’s involved in blocking sessions, by clicking the SQL ID link on this page. We can kill the blocking session from this page by clicking the Kill Session button at the top left side of the page.

In the Additional Monitoring Links section is another link named Instance Locks, which takes us to the Instance Locks page. The Instance Locks page shows the session details for both the blocking and blocked sessions. We can click the SQL ID link to view the current SQL that’s being executed by the blocker and the blocked sessions. We can also find out the name of the object that’s locked. You can kill the blocking session by clicking the Kill Session button.

How to find the locked object in Oracle

We can find the locked object’s identity by looking at the value of the ID1 (LockIdentifier) column in the V$LOCK view. The value of the ID1 column where the TYPE column is TM (DML enqueue) identifies the locked object. Let's say we've ascertained that the value of the ID1 column is 99999. We can then issue the following query to identify the locked table:

SQL> select object_name from dba_objects where object_id=99999;

OBJECT_NAME
----------------------
TEST
SQL>

An even easier way is to use the V$LOCKED_OBJECT view to find out the locked object, the object type, and the owner of the object.

SQL> select lpad(' ',decode(l.xidusn,0,3,0)) || l.oracle_username "User",
o.owner, o.object_name, o.object_type
from v$locked_object l, dba_objects o
where l.object_id = o.object_id
order by o.object_id, 1 desc;

User OWNER      OBJECT_NAME OBJECT_TYPE
-------- -----------       ---------------------- ----------------------
HR            HR TEST           TABLE
SH            HR TEST           TABLE


Note that the query shows both the blocking and the blocked users.

We can also use Oracle Enterprise Manager to quickly identify a locked object, the ROWID of the object involved in the lock, and the SQL statement that’s responsible for the locks. However, it’s always important to understand the underlying Oracle views that contain the locking information, and that’s what this post demonstrates. Using the queries shown above, we can easily identify a locked object without recourse to a monitoring tool such as Oracle Enterprise Manager, for example.

How to identify blocked and blocking Sessions in Oracle

When you notice enqueue locks in your database and suspect that a blocking lock may be holding up other sessions. You’d like to identify the blocking and the blocked sessions.

So when you see an enqueue wait event in an Oracle database, chances are that it’s a locking phenomenon that’s holding up some sessions from executing their SQL statements. When a session waits on an “enqueue” wait event, that session is waiting for a lock that’s held by a different session. The blocking session is holding the lock in a mode that’s incompatible with the lock mode that’s being requested by the blocked session. You can issue the following command to view information about the blocked and the blocking sessions:

SQL> select decode(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type from v$lock
where (id1, id2, type) in
(select id1, id2, type from v$lock where request>0)
order by id1, request;

The V$LOCK view shows if there are any blocking locks in the instance. If there are blocking locks, it also shows the blocking session(s) and the blocked session(s). Note that a blocking session can block multiple sessions simultaneously, if all of them need the same object that’s being blocked. Here’s an example that shows there are locks present:

The key column to watch is the BLOCK column—the blocking session will have the value 1 for this column. In our example, session 68 is the blocking session, because it shows the value 1 under the BLOCK column. The blocking session, with a SID of 68, also shows a lock mode 6 under the LMODE column, indicating that it’s holding this lock in the exclusive mode—this is the reason session 81 is “hanging,” unable to perform its update operation. The blocked session, of course, is the victim—so it shows a value of 0 in the BLOCK column. It also shows a value of 6 under the REQUEST column, because it’s requesting a lock in the exclusive mode to perform its update of the column. The blocking session, in turn, will show a value of 0 for the REQUEST column, because it isn’t requesting any locks—it’s already holding it.

If you want to find out the wait class and for how long a blocking session has been blocking others, you can do so by querying the V$SESSION view, as shown here:

The query shows that the session with SID=68 is blocking the session with SID=81, and the block started 7,069 seconds ago.

How It Works

The following are the most common types of enqueue locks you’ll see in an Oracle database:

• TX: These are due to a transaction lock and usually caused by faulty application logic.
• TM: These are table-level DML locks, and the most common cause is that you haven’t indexed foreign key constraints in a child table.

In addition, you are also likely to notice ST enqueue locks on occasion. These indicate sessions that are waiting while Oracle is performing space management operations, such as the allocation of temporary segments for performing a sort.

How to find out who is holding a blocking lock on the database

This is the very regular task of a DBA.Your users are complaining that some of their sessions are very slow. You suspect that those sessions may be locked by Oracle for some reason, and would like to find the best way to go about figuring out who is holding up these sessions.

A blocking lock could “slow” a session down—in fact, the session is merely waiting on another session that is holding a lock on an object (such as a row or a set of rows, or even an entire table). Or, in a development scenario, a developer might have started multiple sessions, some of which are blocking each other.

When analyzing Oracle locks, some of the key database views you must examine are the V$LOCK and the V$SESSION views. The V$LOCKED_OBJECT and the DBA_OBJECTS views are also very useful in identifying the locked objects.

In order to find out whether a session is being blocked by the locks being applied by
another session, you can execute the following query:

In RAC:

sql> select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ') is blocking'
        || s2.username || '@' || s2.machine || '( SID=' || s2.sid || ')' from
       gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
       where s1.sid=l1.sid and s2.sid=l2.sid
       and l1.block=1 and l2.request > 0
       and l1.id1=l2.id1
       and l2.id2=l2.id2;

In Standalone:

sql>select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ') is blocking'
       || s2.username || '@' || s2.machine || '( SID=' || s2.sid || ')' from
       v$lock l1, v$session s1, v$lock l2, v$session s2
       where s1.sid=l1.sid and s2.sid=l2.sid
       and l1.block=1 and l2.request > 0
       and l1.id1=l2.id1
       and l2.id2=l2.id2;

Query Output:

S1.USERNAME||'@'||S1.MACHINE||'(SID='||S1.SID||')ISBLOCKING'||S2.USERNAME||'@'||
----------------------------------------------------------------------------------------
SYS@host1.abc.com ( SID=229) is blockingSYS@host1.abc.com( SID=226)

A quick way to find out if you have any blocking locks in your instance at all, for any user, is to simply run the following query:

SQL> select * from V$lock where block > 0;

If you don’t get any rows back from this query—good—you don’t have any blocking locks in the instance right now!


How It Works

Oracle uses two types of locks to prevent destructive behavior: exclusive and shared locks. Only one transaction can obtain an exclusive lock on a row or a table, while multiple shared locks can be obtained on the same object. Oracle uses locks at two levels—row and table levels. Row locks, indicated by the symbol TX, lock just a single row of a table for each row that’ll be modified by a DML statement such as INSERT, UPDATE, and DELETE. This is true also for a MERGE or a SELECT … FOR UPDATE statement.

The transaction that includes one of these statements grabs an exclusive row lock as well as a row share table lock. The transaction (and the session) will hold these locks until it commits or rolls back the statement. Until it does one of these two things, all other sessions that intend to modify that particular row are blocked. Note that each time a transaction intends to modify a row or rows of a table, it holds a table lock (TM) as well on that table, to prevent the database from allowing any DDL operations (such as DROP
TABLE) on that table while the transaction is trying to modify some of its rows.

In an Oracle database, locking works this way:

• A reader won’t block another reader.
• A reader won’t block a writer.
• A writer won’t block a reader of the same data.
• A writer will block another writer that wants to modify the same data.

It’s the last case in the list, where two sessions intend to modify the same data in a table, that Oracle’s automatic locking kicks in, to prevent destructive behavior. The first transaction that contains the statement that updates an existing row will get an exclusive lock on that row. While the first session that locks a row continues to hold that lock (until it issues a COMMIT or ROLLBACK statement), other sessions can modify any other rows in that table other than the locked row. The concomitant table lock held by the first session is merely intended to prevent any other sessions from issuing a DDL statement to alter the table’s structure. Oracle uses a sophisticated locking mechanism whereby a row-level lock isn’t automatically escalated to the table, or even the block level.

What is locking, advantage of locking and types of locking in Oracle

Locking is a mechanism to ensure data integrity while allowing maximum concurrent access to data. It is used to implement concurrency control when multiple users access table to manipulate its data at the same time.

Advantage of locking:

a. Avoids deadlock conditions
b. Avoids clashes in capturing the resources

Types of Locks Within Oracle
Oracle provides the following three main kinds of locks:

• DML locks
• DDL locks
• Internal locks and latches

DML Locks

DML locks or data locks guarantee the integrity of data being accessed concurrently by multiple users. DML locks help to prevent damage caused by interference from simultaneous conflicting DML or DDL operations. By default, DML statements acquire both table-level locks and row-level locks.

The reference for each type of lock or lock mode is the abbreviation used in the Locks Monitor from Oracle Enterprise Manager (OEM). For example, OEM might display TM for any table lock within Oracle rather than show an indicator for the mode of table lock (RS or SRX).

Row Locks (TX)

Row-level locks serve a primary function to prevent multiple transactions from modifying the same row. Whenever a transaction needs to modify a row, a row lock is acquired by Oracle.

There is no hard limit on the exact number of row locks held by a statement or transaction. Also, unlike other database platforms, Oracle will never escalate a lock from the row level to a coarser granular level. This row locking ability provides the DBA with the finest granular level of locking possible and, as such, provides the best possible data concurrency and performance for transactions.

The mixing of multiple concurrency levels of control and row level locking means that users face contention for data only whenever the same rows are accessed at the same time.  Furthermore, readers of data will never have to wait for writers of the same data rows. Writers of data are not required to wait for readers of these same data rows except in the case of when a SELECT... FOR UPDATE is used.

Writers will only wait on other writers if they try to update the same rows at the same point in time. In a few special cases, readers of data may need to wait for writers of the same data. For example, concerning certain unique issues with pending transactions in distributed database environments with Oracle.

Transactions will acquire exclusive row locks for individual rows that are using modified INSERT, UPDATE, and DELETE statements and also for the SELECT with the FOR UPDATE clause.

Modified rows are always locked in exclusive mode with Oracle so that other transactions do not modify the row until the transaction which holds the lock issues a commit or is rolled back. In the event that the Oracle database transaction does fail to complete successfully due to an instance failure, then Oracle database block level recovery will make a row available before the entire transaction is recovered. The Oracle database provides the mechanism by which row locks acquire automatically for the DML statements mentioned above.

Whenever a transaction obtains row locks for a row, it also acquires a table lock for the corresponding table. Table locks prevent conflicts with DDL operations that would cause an override of data changes in the current transaction.

Table Locks (TM)

What are table locks in Oracle? Table locks perform concurrency control for simultaneous DDL operations so that a table is not dropped in the middle of a DML operation, for example. When Oracle issues a DDL or DML statement on a table, a table lock is then acquired. As a rule, table locks do not affect concurrency of DML operations. Locks can be acquired at both the table and sub-partition level with partitioned tables in Oracle.

A transaction acquires a table lock when a table is modified in the following DML statements: INSERT, UPDATE, DELETE, SELECT with the FOR UPDATE clause, and LOCK TABLE. These DML operations require table locks for two purposes: to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction.

Any table lock prevents the acquisition of an exclusive DDL lock on the same table, and thereby prevents DDL operations that require such locks. For example, a table cannot be altered or dropped if an uncommitted transaction holds a table lock for it.

A table lock can be held in any of several modes: row share (RS), row exclusive (RX), share (S), share row exclusive (SRX), and exclusive (X). The restrictiveness of a table lock's mode determines the modes in which other table locks on the same table can be obtained and held.

How to resolve network - database connectivity issue

DBA generally face this issue many times that a user has reported that he or she can’t connect to a database. You know there are many components involved with network connectivity and want to figure out the root cause of the problem.

Use the below steps as guidelines when diagnosing Oracle database network connectivity issues:

1. Use the operating system ping utility to determine whether the remote box is accessible—for example:
$ ping dwdb
dwdb is alive

If ping doesn’t work, work with your system or network administrator to ensure you have server-to-server connectivity in place.

2. Use telnet to see if you can connect to the remote server and port (that the listener is listening on)—for example:
$ telnet ora03 1521
Trying 127.0.0.1...
Connected to ora03.
Escape character is '^]'.

The prior output indicates that connectivity to a server and port is okay. If the
prior command hangs, then contact your SA or network administrator for
further assistance.

3. Use tnsping to determine whether Oracle Net is working. This utility will verify that an Oracle Net connection can be made to a database via the network—for example:

$ tnsping dwrep
..........
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
(HOST = dwdb1.us.farm.com)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DWREP)))
OK (500 msec)

If tnsping can’t contact the remote database, verify that the remote listener and database are both up and running. On the remote box, use the lsnrctl status command to verify that the listener is up. Verify that the remote database is available by establishing a local connection as a non-SYS account (SYS can often connect to a troubled database when other schemas will not work).


4. Verify that the TNS information is correct. If the remote listener and database are working, then ensure that the mechanism for determining TNS information (like the tnsnames.ora file) contains the correct information.

Sometimes the client machine will have multiple TNS_ADMIN locations and
tnsnames.ora files. One way to verify whether a particular tnsnames.ora file is
being used is to rename it and see whether you get a different error when
attempting to connect to the remote database.


Network connectivity issues can be troublesome to diagnose because there are several architectural components that have to be in place for it to work correctly. You need to have the following in place:

• A functional network
• Open ports from point to point
• Oracle Net correctly installed and configured
• Target database and listener up and running
• Correct navigational information from the client to the target database

If you’re still having issues, examine the client sqlnet.log file and the remote server listener.log file. Sometimes these log files will show additional information that will pinpoint the issue.