In this chapter, we’ll take a detailed look at how Oracle locks both data (e.g., rows in tables) and shared data structures (such as those found in the SGA). We’ll investigate the granularity to which Oracle locks data and what that means to you, the developer. When appropriate, I’ll contrast Oracle’s locking scheme with other popular implementations, mostly to dispel the myth that row-level locking adds overhead; in reality, it adds overhead only if the implementation adds overhead. In the next chapter, we’ll continue this discussion and investigate Oracle’s multiversioning techniques and how locking strategies interact with them.

The three general classes of locks in Oracle are as follows:

• DML locks: DML stands for Data Manipulation Language. In general this means SELECT, INSERT, UPDATE, MERGE, and DELETE statements. DML locks are the mechanism that allows for concurrent data modifications. DML locks will be, for example, locks on a specific row of data or a lock at the table level that locks every row in the table.
• DDL locks: DDL stands for Data Definition Language, (CREATE and ALTER statements, and so on). DDL locks protect the definition of the structure of objects.
• Internal locks and latches: Oracle uses these locks to protect its internal data structures. For example, when Oracle parses a query and generates an optimized query plan, it will latch the library cache to put that plan in there for other sessions to use. A latch is a lightweight, low-level serialization device employed by Oracle, similar in function to a lock. Do not confuse or be misled by the term lightweight; latches are a common cause of contention in the database, as you will see. They are lightweight in their implementation, but not their effect.

We will now take a more detailed look at the specific types of locks within each of these general classes and the implications of their use. There are more lock types than I can cover here. The ones I cover in the sections that follow are the most common and are held for a long duration. The other types of locks are generally held for very short periods of time.

DML Locks

DML locks are used to ensure that only one person at a time modifies a row and that no one can drop a table upon which you are working. Oracle will place these locks for you, more or less transparently, as you do work.

TX (Transaction) Locks

A TX lock is acquired when a transaction initiates its first change. The transaction is automatically initiated at this point (you don’t explicitly start a transaction in Oracle). The lock is held until the transaction performs a COMMIT or ROLLBACK. It is used as a queuing mechanism so that other sessions can wait for the transaction to complete. Each and every row you modify or SELECT FOR UPDATE in a transaction will point to an associated TX lock for that transaction. While this sounds expensive, it is not. To understand why this is, you need a conceptual understanding of where locks live and how they are managed. In Oracle, locks are stored as an attribute of the data. Oracle does not have a traditional lock manager that keeps a long list of every row that is locked in the system. Many other databases do it that way because, for them, locks are a scarce resource, the use of which needs to be monitored. The more locks are in use, the more these systems have to manage, so it is a concern in these systems if too many locks are being used.

In a database with a traditional memory-based lock manager, the process of locking a row would resemble the following:

1. Find the address of the row you want to lock.
2. Get in line at the lock manager (which must be serialized, as it is a common in-memory structure).
3. Lock the list.
4. Search through the list to see if anyone else has locked this row.
5. Create a new entry in the list to establish the fact that you have locked the row.
6. Unlock the list.

Now that you have the row locked, you can modify it. Later, as you commit your changes, you must continue the procedure as follows:

1. Get in line again.
2. Lock the list of locks.
3. Search through the list and release all of your locks.
4. Unlock the list.

As you can see, the more locks acquired, the more time spent on this operation, both before and after modifying the data. Oracle does not do it that way. Oracle’s process looks like this:

1. Find the address of the row you want to lock.
2. Go to the row.
3. Lock the row right there, right then—at the location of the row, not in a big list somewhere (waiting for the transaction that has it locked to end if it is already locked, unless you are using the NOWAIT option).

That’s it. Since the lock is stored as an attribute of the data, Oracle does not need a traditional lock manager. The transaction will simply go to the data and lock it (if it is not locked already). The interesting thing is that the data may appear locked when you get to it, even if it’s not. When you lock rows of data in Oracle, the row points to a copy of the transaction ID that is stored with the block containing the data, and when the lock is released that transaction ID is left behind. This transaction ID is unique to your transaction and represents the undo segment number, slot, and sequence number. You leave that on the block that contains your row to tell other sessions that you own this data (not all of the data on the block—just the one row you are modifying). When another session comes along, it sees the transaction ID and, using the fact that it represents a transaction, it can quickly see if the transaction holding the lock is still active. If the lock is not active, the session is allowed access to the data. If the lock is still active, that session will ask to be notified as soon as the lock is released. Hence, you have a queuing mechanism: the session requesting the lock will be queued up waiting for that transaction to complete, and then it will get the data.

Here is a small example showing how this happens, using three V$ tables:

• V$TRANSACTION, which contains an entry for every active transaction.
• V$SESSION, which shows the sessions logged in.
• V$LOCK, which contains an entry for all enqueue locks being held as well as for sessions that are waiting on locks. You will not see a row in this view for each row locked by a session. As stated earlier, that master list of locks at the row level doesn’t exist. If a session has one row in the EMP table locked, there will be one row in this view for that session indicating that fact. If a session has millions of rows in the EMP table locked, there will still be just one row in this view. This view shows what enqueue locks individual sessions have.

First, let’s get a copy of the EMP and DEPT tables. If you already have them in your schema, replace them with the following definitions:

EODA@ORA12CR1> create table dept
  2  as select * from scott.dept;
Table created.

EODA@ORA12CR1> create table emp
  2  as select * from scott.emp;
Table created.

EODA@ORA12CR1> alter table dept
  2  add constraint dept_pk
  3  primary key(deptno);
Table altered.

EODA@ORA12CR1> alter table emp
  2  add constraint emp_pk
  3  primary key(empno);
Table altered.

EODA@ORA12CR1> alter table emp
  2  add constraint emp_fk_dept
  3  foreign key (deptno)
  4  references dept(deptno);
Table altered.

EODA@ORA12CR1> create index emp_deptno_idx
  2  on emp(deptno);
Index created.

Let’s start a transaction now:

EODA@ORA12CR1> update dept
  2     set dname = initcap(dname);
4 rows updated.

Now, let’s look at the state of the system at this point. This example assumes a single-user system; otherwise, you may see many rows in V$TRANSACTION. Even in a single-user system, do not be surprised to see more than one row in V$TRANSACTION, as many of the background Oracle processes may be performing a transaction as well.

EODA@ORA12CR1> select username,
  2         v$lock.sid,
  3         trunc(id1/power(2,16)) rbs,
  4         bitand(id1,to_number('ffff','xxxx'))+0 slot,
  5         id2 seq,
  6         lmode,
  7         request
  8  from v$lock, v$session
  9  where v$lock.type = 'TX'
 10    and v$lock.sid = v$session.sid
 11    and v$session.username = USER;

USERNAME               SID        RBS       SLOT        SEQ      LMODE    REQUEST
--------------- ---------- ---------- ---------- ---------- ---------- ----------
EODA                    22          2         27      21201          6          0

EODA@ORA12CR1> select XIDUSN, XIDSLOT, XIDSQN from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
         2         27      21201

The interesting points to note here are as follows:

• The LMODE is 6 in the V$LOCK table and the REQUEST is 0. If you refer to the definition of the V$LOCK table in the Oracle Database Reference manual, you will find that LMODE=6 is an exclusive lock. A value of 0 in the request means you are not making a request; you have the lock.
• There is only one row in this table. This V$LOCK table is more of a queuing table than a lock table. Many people expect four rows in V$LOCK since we have four rows locked. Remember, however, that Oracle does not store a master list of every row locked anywhere. To find out if a row is locked, we must go to that row.
• I took the ID1 and ID2 columns and performed some manipulation on them. Oracle needed to save three 16-bit numbers, but only had two columns in order to do it. So, the first column ID1 holds two of these numbers. By dividing by 2^16 with trunc(id1/power(2,16)) rbs, and by masking out the high bits with bitand(id1,to_number('ffff','xxxx'))+0 slot, I am able to get back the two numbers that are hiding in that one number.
• The RBS, SLOT, and SEQ values match the V$TRANSACTION information. This is my transaction ID.

Now we’ll start another session using the same username, update some rows in EMP, and then try to update DEPT:

EODA@ORA12CR1> update emp set ename = upper(ename);
14 rows updated.

EODA@ORA12CR1> update dept set deptno = deptno-10;

We’re now blocked in this session. If we run the V$ queries again, we see the following:

EODA@ORA12CR1> select username,
  2         v$lock.sid,
  3         trunc(id1/power(2,16)) rbs,
  4         bitand(id1,to_number('ffff','xxxx'))+0 slot,
  5         id2 seq,
  6         lmode,
  7         request
  8  from v$lock, v$session
  9  where v$lock.type = 'TX'
 10    and v$lock.sid = v$session.sid
 11    and v$session.username = USER;

USERNAME               SID        RBS       SLOT        SEQ      LMODE    REQUEST
--------------- ---------- ---------- ---------- ---------- ---------- ----------
EODA                    17          2         27      21201          0          6
EODA                    22          2         27      21201          6          0
EODA                    17          8         17      21403          6          0

EODA@ORA12CR1> select XIDUSN, XIDSLOT, XIDSQN from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
         2         27      21201
         8         17      21403

What we see here is that a new transaction has begun, with a transaction ID of (8,17,21403). Our new session, SID=17, has two rows in V$LOCK this time. One row represents the locks that it owns (where LMODE=6). It also has a row that shows a REQUEST with a value of 6. This is a request for an exclusive lock. The interesting thing to note here is that the RBS/SLOT/SEQ values of this request row are the transaction ID of the holder of the lock. The transaction with SID=22 is blocking the transaction with SID=17. We can see this more explicitly simply by doing a self-join of V$LOCK:

EODA@ORA12CR1> select
  2        (select username from v$session where sid=a.sid) blocker,
  3         a.sid,
  4        ' is blocking ',
  5         (select username from v$session where sid=b.sid) blockee,
  6             b.sid
  7    from v$lock a, v$lock b
  8   where a.block = 1
  9     and b.request > 0
 10     and a.id1 = b.id1
 11     and a.id2 = b.id2;

BLOCKER                SID 'ISBLOCKING'  BLOCKEE                SID
--------------- ---------- ------------- --------------- ----------
EODA                    22  is blocking  EODA                    17

Now, if we commit our original transaction, SID=22, and rerun our lock query, we find that the request row has gone:

EODA@ORA12CR1> select username,
  2         v$lock.sid,
  3         trunc(id1/power(2,16)) rbs,
  4         bitand(id1,to_number('ffff','xxxx'))+0 slot,
  5         id2 seq,
  6         lmode,
  7         request
  8  from v$lock, v$session
  9  where v$lock.type = 'TX'
 10    and v$lock.sid = v$session.sid
 11    and v$session.username = USER;

USERNAME               SID        RBS       SLOT        SEQ      LMODE    REQUEST
--------------- ---------- ---------- ---------- ---------- ---------- ----------
EODA                    17          8         17      21403          6          0

EODA@ORA12CR1> select XIDUSN, XIDSLOT, XIDSQN from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
         8         17      21403

The request row disappeared the instant the other session gave up its lock. That request row was the queuing mechanism. The database is able to wake up the blocked sessions the instant the transaction is completed. There are prettier displays with various GUI tools, but in a pinch, having knowledge of the tables you need to look at is very useful.

However, before we can say that we have a good understanding of how the row locking in Oracle works, we must look at one last topic: how the locking and transaction information is managed with the data itself. It is part of the block overhead. At the top of a database block is some leading overhead space in which to store a transaction table for that block. This transaction table contains an entry for each real transaction that has locked some data in that block. The size of this structure is controlled by two physical attribute parameters on the CREATE statement for an object:

• INITRANS: The initial, preallocated size of this structure. This defaults to 2 for indexes and tables.
• MAXTRANS: In Oracle 10g and above, MAXTRANS is always 255.

Each block starts life with, by default, two transaction slots. The number of simultaneous active transactions that a block can ever have is constrained by the value of MAXTRANS and by the availability of space on the block. You may not be able to achieve 255 concurrent transactions on the block if there is not sufficient space to grow this structure.

We can artificially demonstrate how this works by creating a table with lots of rows packed into a single block such that the block is very full from the start; there will be very little room left on the block after we initially load our data. The presence of these rows will limit how large the transaction table can grow, due to the lack of space. I was using an 8KB block size and I tested this particular example in all versions of Oracle from 9i Release 2 through 12c Release 1 with the same results (so, if you have an 8KB blocksize, you should be able to reproduce this). We’ll start by creating our packed table. I played around with different lengths of data until I arrived at this very special size:

EODA@ORA12CR1> create table t
  2  (x int primary key,
  3    y varchar2(4000)
  4  )
  5  /
Table created.

EODA@ORA12CR1> insert into t (x,y)
  2  select rownum, rpad('*',148,'*')
  3    from dual
  4  connect by level <= 46;
46 rows created.

EODA@ORA12CR1> select length(y),
  2         dbms_rowid.rowid_block_number(rowid) blk,
  3         count(*), min(x), max(x)
  4    from t
  5   group by length(y), dbms_rowid.rowid_block_number(rowid);

LENGTH(Y)        BLK   COUNT(*)     MIN(X)     MAX(X)
--------- ---------- ---------- ---------- ----------
      148      23470         46          1         46

So, our table has 46 rows, all on the same block. I chose 148 characters because if it was one character more, we’d need two blocks to hold these same 46 records. Now, we need a way to see what happens when many transactions try to lock data on this single block simultaneously. For that, we’ll use an AUTONOMOUS_TRANSACTION again, just so we can use a single session and not have to run lots of concurrent SQL*Plus sessions. Our stored procedure will lock a row in the table by the primary key starting with a primary key value of 1 (the first record inserted). If our procedure gets the lock on this row without having to wait (without getting blocked), it will simply increase the primary key value by 1 and, using recursion, do it all over again. So, the second call will try to lock record 2, the third call record 3, and so on. If the procedure is made to wait, it will raise an ORA-54 resource busy error and we’ll print out “locked out trying to select row <primary key value>”. That will indicate we ran out of transaction slots on this block before we ran out of rows to lock. On the other hand, if we find no row to lock, that means we’ve already locked every row on this block and we print out success (meaning, the transaction table in the block header was able to grow to accommodate all of the transactions). Here is that stored procedure:

EODA@ORA12CR1> create or replace procedure do_update(p_n in number )
  2  as
  3      pragma autonomous_transaction;
  4      l_rec t%rowtype;
  5      resource_busy exception;
  6      pragma exception_init(resource_busy, -54 );
  7  begin
  8      select *
  9        into l_rec
 10        from t
 11       where x = p_n
 12         for update NOWAIT;
 13
 14      do_update(p_n+1 );
 15      commit;
 16  exception
 17  when resource_busy
 18  then
 19      dbms_output.put_line('locked out trying to select row ' || p_n );
 20      commit;
 21  when no_data_found
 22  then
 23      dbms_output.put_line('we finished - no problems' );
 24      commit;
 25  end;
 26  /
Procedure created.

The magic is on line 14 where we recursively call ourselves with a new primary key value to lock over and over. If you run the procedure after populating the table with 148 character strings, you should observe:

EODA@ORA12CR1> exec do_update(1);
locked out trying to select row 38
PL/SQL procedure successfully completed.

This output shows that we were able to lock 37 rows but ran out of transaction slots for the 38th row. For this given block, a maximum of 37 transactions can concurrently access it. If we redo the example with a slightly smaller string we’ll see that if finishes with no problems:

EODA@ORA12CR1> truncate table t;
Table truncated.

EODA@ORA12CR1> insert into t (x,y)
  2  select rownum, rpad('*',147,'*')
  3    from dual
  4  connect by level <= 46;
46 rows created.

EODA@ORA12CR1> select length(y),
  2         dbms_rowid.rowid_block_number(rowid) blk,
  3         count(*), min(x), max(x)
  4    from t
  5   group by length(y), dbms_rowid.rowid_block_number(rowid);

LENGTH(Y)        BLK   COUNT(*)     MIN(X)     MAX(X)
--------- ---------- ---------- ---------- ----------
      147      23470         46          1         46

EODA@ORA12CR1> exec do_update(1);
we finished - no problems
PL/SQL procedure successfully completed..

This time we completed successfully—the difference a single byte makes! In this case, having the extra 46 bytes of space free on the block (each of the 46 strings was just one byte smaller) allowed us to have at least 9 more transactions active on the block.

This example demonstrates what happens when many transactions attempt to access the same block simultaneously—a wait on the transaction table may occur if there is an extremely high number of concurrent transactions. Blocking may occur if the INITRANS is set low and there is not enough space on a block to dynamically expand the transaction. In most cases, the default of 2 for INITRANS is sufficient, as the transaction table will dynamically grow (space permitting), but in some environments you may need to increase this setting (to reserve more room for slots) to increase concurrency and decrease waits.

An example of when you might need to increase the setting would be on a table or, even more frequently, on an index (since index blocks can get many more rows on them than a table can typically hold) that is frequently modified and has a lot of rows per block on average. You may need to increase either PCTFREE or INITRANS to set aside ahead of time sufficient space on the block for the number of expected concurrent transactions. This is especially true if you anticipate the blocks will be nearly full to begin with, meaning there is no room for the dynamic expansion of the transaction structure on the block.

One last note on INITRANS. A couple of times I’ve stated that the default value for this attribute is 2. However, if you examine the data dictionary after creating a table, you’ll notice that INITRANS displays a value of 1:

EODA@ORA12CR1> create table t (x int );
EODA@ORA12CR1> select ini_trans from user_tables where table_name = 'T';

 INI_TRANS
----------
         1

So is the default number of transaction slots 1 or 2? Even though the data dictionary is showing a value of 1, we can demonstrate that it really is 2. Consider this experiment. First generate one transaction for table T by inserting a single record:

EODA@ORA12CR1> insert into t values ( 1 );

Now verify that one block is consumed by table T:

EODA@ORA12CR1> select dbms_rowid.ROWID_BLOCK_NUMBER(rowid)  from t;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                               57715

Next, place into the variables—B and F—the block number and the data file number of the block used by table T:

EODA@ORA12CR1> column b new_val B
EODA@ORA12CR1> column f new_val F
EODA@ORA12CR1> select dbms_rowid.ROWID_BLOCK_NUMBER(rowid) B,
  2         dbms_rowid.ROWID_TO_ABSOLUTE_FNO(rowid, user, 'T' ) F
  3    from t;

Now dump the block being used by table T:

EODA@ORA12CR1> alter system dump datafile &F block &B;

Next, place into a variable named TRACE the location and name of the trace file containing the dump information for the block:

EODA@ORA12CR1> column trace new_val TRACE

EODA@ORA12CR1> select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
  2    from v$process a, v$session b, v$parameter c, v$instance d
  3   where a.addr = b.paddr
  4     and b.audsid = userenv('sessionid')
  5     and c.name = 'user_dump_dest';

Now terminate the session and edit the trace file:

EODA@ORA12CR1> disconnect
EODA@ORA12CR1> edit &TRACE

Searching the trace file for the value of Itl, we see there are two transaction slots that have been initialized (even though there has only been one transaction issued for this table):

Itl           Xid                  Uba          Flag  Lck        Scn/Fsc
0x01   0x0013.00e.000024be  0x00c000bf.039e.2d  --U-    1  fsc 0x0000.01cfa56a
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

The INITRANS value of 1 reported in the data dictionary is most likely a legacy value and it really should display a value of 2 for more current versions of Oracle.

TM (DML Enqueue) Locks

TM locks are used to ensure that the structure of a table is not altered while you are modifying its contents. For example, if you have updated a table, you will acquire a TM lock on that table. This will prevent another user from executing DROP or ALTER commands on that table. If another user attempts to perform DDL on the table while you have a TM lock on it, he’ll receive the following error message:

drop table dept
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

The ORA-00054 message is a confusing message at first, since there is no direct method to specify NOWAIT or WAIT on a DROP TABLE at all. It is just the generic message you get when you attempt to perform an operation that would be blocked, but the operation does not permit blocking. As you’ve seen before, it’s the same message you get if you issue a SELECT FOR UPDATE NOWAIT against a locked row.

The following shows how these locks would appear in the V$LOCK table:

EODA@ORA12CR1> create table t1 ( x int );
Table created.

EODA@ORA12CR1> create table t2 ( x int );
Table created.

EODA@ORA12CR1> insert into t1 values ( 1 );
1 row created.

EODA@ORA12CR1> insert into t2 values ( 1 );
1 row created.

EODA@ORA12CR1> select (select username
  2            from v$session
  3           where sid = v$lock.sid) username,
  4         sid,
  5         id1,
  6         id2,
  7         lmode,
  8         request, block, v$lock.type
  9    from v$lock
 10   where sid = sys_context('userenv','sid'),

USERNAME               SID        ID1        ID2      LMODE    REQUEST      BLOCK TY
--------------- ---------- ---------- ---------- ---------- ---------- ---------- --
EODA                    22        133          0          4          0          0 AE
EODA                    22     244271          0          3          0          0 TM
EODA                    22     244270          0          3          0          0 TM
EODA                    22    1966095        152          6          0          0 TX

EODA@ORA12CR1> select object_name, object_id
  2    from user_objects
  3    where object_id in (244271,244270);

OBJECT_NAME  OBJECT_ID
----------- ----------
T2              244271
T1              244270

Whereas we get only one TX lock per transaction, we can get as many TM locks as the objects we modify. Here, the interesting thing is that the ID1 column for the TM lock is the object ID of the DML-locked object, so it is easy to find the object on which the lock is being held.

An interesting aside to the TM lock: the total number of TM locks allowed in the system is configurable by you (for details, see the DML_LOCKS parameter definition in the Oracle Database Reference manual). It may, in fact, be set to zero. This does not mean that your database becomes a read-only database (no locks), but rather that DDL is not permitted. This is useful in very specialized applications, such as RAC implementations, to reduce the amount of intra-instance coordination that would otherwise take place. You can also remove the ability to gain TM locks on an object-by-object basis using the ALTER TABLE <TABLENAME> DISABLE TABLE LOCK command. This is a quick way to make it harder to accidentally drop a table, as you will have to reenable the table lock before dropping the table. It can also be used to detect a full table lock as a result of the unindexed foreign key we discussed previously.

DDL Locks

DDL locks are automatically placed against objects during a DDL operation to protect them from changes by other sessions. For example, if I perform the DDL operation ALTER TABLE T, the table T will in general have an exclusive DDL lock placed against it, preventing other sessions from getting DDL locks and TM locks on this table.

DDL locks are held for the duration of the DDL statement and are released immediately afterward. This is done, in effect, by always wrapping DDL statements in implicit commits (or a commit/rollback pair). For this reason, DDL always commits in Oracle. Every CREATE, ALTER, and so on statement is really executed as shown in this pseudo-code:

Begin
   Commit;
   DDL-STATEMENT
   Commit;
Exception
   When others then rollback;
End;

So, DDL will always commit, even if it is unsuccessful. DDL starts by committing; be aware of this. It commits first so that if it has to roll back, it will not roll back your transaction. If you execute DDL, it will make permanent any outstanding work you have performed, even if the DDL is not successful. If you need to execute DDL, but you do not want it to commit your existing transaction, you may use an autonomous transaction.

There are three types of DDL locks:

• Exclusive DDL locks: These prevent other sessions from gaining a DDL lock or TM (DML) lock themselves. This means that you may query a table during a DDL operation, but you may not modify it in any way.
• Share DDL locks: These protect the structure of the referenced object against modification by other sessions, but allow modifications to the data.
• Breakable parse locks: These allow an object, such as a query plan cached in the shared pool, to register its reliance on some other object. If you perform DDL against that object, Oracle will review the list of objects that have registered their dependence and invalidate them. Hence, these locks are breakable—they do not prevent the DDL from occurring.

Most DDL takes an exclusive DDL lock. If you issue a statement such as

Alter table t move;

the table T will be unavailable for modifications during the execution of that statement. The table may be queried using SELECT during this time, but most other operations will be prevented, including all other DDL statements. In Oracle, some DDL operations may now take place without DDL locks. For example, I can issue the following:

Create index t_idx on t(x) ONLINE;

The ONLINE keyword modifies the method by which the index is actually built. Instead of taking an exclusive DDL lock, preventing modifications of data, Oracle will only attempt to acquire a low-level (mode 2) TM lock on the table. This will effectively prevent other DDL from taking place, but it will allow DML to occur normally. Oracle accomplishes this feat by keeping a record of modifications made to the table during the DDL statement and applying these changes to the new index as it finishes the CREATE action. This greatly increases the availability of data. To see this for yourself, you could create a table of some size:

EODA@ORA12CR1> create table t as select * from all_objects;
Table created.

EODA@ORA12CR1> select object_id from user_objects where object_name = 'T';

 OBJECT_ID
----------
    244277

And then run the create index against that table:

EODA@ORA12CR1> create index t_idx on t(owner,object_type,object_name) ONLINE;

While at the same time running this query in another session to see the locks taken against that newly created table (remember, ID1=244277 is specific to my example, you’ll want to use your object ID).

EODA@ORA12CR1> select (select username
  2            from v$session
  3           where sid = v$lock.sid) username,
  4         sid,
  5         id1,
  6         id2,
  7         lmode,
  8         request, block, v$lock.type
  9    from v$lock
 10   where id1 = 244277
 11  /

USERNAME               SID        ID1        ID2      LMODE    REQUEST      BLOCK TY
--------------- ---------- ---------- ---------- ---------- ---------- ---------- --
EODA                    22     244277          0          3          0          0 DL
EODA                    22     244277          0          3          0          0 DL
EODA                    22     244277          0          2          0          0 TM
EODA                    22     244277          0          4          0          0 OD

So, here we see four locks taken out against our object. The two DL locks are direct load locks. They are used to prevent a direct path load into our base table while the index creation is taking place (which implies, of course, that you cannot directly path load the table AND create the index simultaneously). The OD lock is a lock type first appeared with Oracle 11g (you would not see that lock in 10g or 9i) that permits truly online DDL. In the past (10g and before), online DDL such as CREATE INDEX ONLINE was not 100 percent online. It would take a lock at the beginning and end of the CREATE statement—preventing other concurrent activities (modifications of the base table data). It was mostly online but not completely online. Starting with 11g, the CREATE INDEX ONLINE command is completely online; it does not require exclusionary locks at the beginning/end of the command. Part of the implementation to accomplish this feat was the introduction of the OD (Online DDL) lock; it is used internally to allow truly online DDL operations.

Other types of DDL take share DDL locks. These are taken out against dependent objects when you create stored, compiled objects, such as procedures and views. For example, if you execute the following, share DDL locks will be placed against both EMP and DEPT while the CREATE VIEW command is being processed:

Create view MyView
as
select emp.empno, emp.ename, dept.deptno, dept.dname
  from emp, dept
 where emp.deptno = dept.deptno;

You can modify the contents of these tables, but you cannot modify their structure.

The last type of DDL lock is a breakable parse lock. When your session parses a statement, a parse lock is taken against every object referenced by that statement. These locks are taken in order to allow the parsed, cached statement to be invalidated (flushed) in the shared pool if a referenced object is dropped or altered in some way.

A view that is invaluable for looking at this information is DBA_DDL_LOCKS. There is no V$ view. The DBA_DDL_LOCKS view is built on the more mysterious X$ tables and, by default, it might not be installed in your database. You can install this and other locking views by running the catblock.sql script found in the directory [ORACLE_HOME]/rdbms/admin. This script must be executed as the user SYS in order to succeed. Once you have executed this script, you can run a query against the view. For example, in a freshly connected session, I might see the following:

EODA@ORA12CR1> connect eoda/foo
Connected.
EODA@ORA12CR1> set linesize 1000
EODA@ORA12CR1> select session_id sid, owner, name, type,
  2         mode_held held, mode_requested request
  3    from dba_ddl_locks
  4   where session_id = (select sid from v$mystat where rownum=1)
  5  /

   SID OWNER    NAME                  TYPE                 HELD       REQUEST
------ -------- --------------------- -------------------- ---------- --------
    22 SYS      DBMS_OUTPUT           Body                 Null       None
    22 SYS      DBMS_OUTPUT           Table/Procedure/Type Null       None
    22 EODA     EODA                  18                   Null       None
    22 SYS      DBMS_APPLICATION_INFO Body                 Null       None
    22 SYS      PLITBLM               Table/Procedure/Type Null       None
    22 SYS      DBMS_APPLICATION_INFO Table/Procedure/Type Null       None
    22          EODA                  73                   Share      None
    22 SYS      DATABASE              18                   Null       None

8 rows selected.

These are all the objects that my session is locking. I have breakable parse locks on a couple of the DBMS_* packages. These are a side effect of using SQL*Plus; it might call DBMS_APPLICATION_INFO, for example, when you initially log in (to enable/disable DBMS_OUTPUT via the SET SERVEROUTPUT command). I may see more than one copy of various objects here; this is normal, and it just means I have more than one thing I’m using in the shared pool that references these objects. Note that in the view, the OWNER column is not the owner of the lock; rather, it is the owner of the object being locked. This is why you see many SYS rows. SYS owns these packages, but they all belong to my session.

To see a breakable parse lock in action, let’s first create and run a stored procedure, P:

EODA@ORA12CR1> create or replace procedure p
  2  as
  3  begin
  4   null;
  5  end;
  6  /
Procedure created.

EODA@ORA12CR1> exec p
PL/SQL procedure successfully completed.

The procedure, P, will now show up in the DBA_DDL_LOCKS view. We have a parse lock on it:

EODA@ORA12CR1> select session_id sid, owner, name, type,
  2         mode_held held, mode_requested request
  3    from dba_ddl_locks
  4   where session_id = (select sid from v$mystat where rownum=1)
  5  /

  SID  OWNER    NAME                  TYPE                 HELD       REQUEST
------ -------- --------------------- -------------------- ---------- --------
    22 EODA     P                     Table/Procedure/Type Null       None
...
    22 SYS      DATABASE              18                   Null       None
9 rows selected.

We then recompile our procedure and query the view again:

EODA@ORA12CR1> alter procedure p compile;
Procedure altered.

EODA@ORA12CR1> select session_id sid, owner, name, type,
  2         mode_held held, mode_requested request
  3    from dba_ddl_locks
  4   where session_id = (select sid from v$mystat where rownum=1)
  5  /

   SID OWNER    NAME                  TYPE                 HELD       REQUEST
------ -------- --------------------- -------------------- ---------- --------
    22 SYS      DBMS_OUTPUT           Body                 Null       None
    22 SYS      DBMS_OUTPUT           Table/Procedure/Type Null       None
    22 EODA     EODA                  18                   Null       None
    22 SYS      DBMS_APPLICATION_INFO Body                 Null       None
    22 SYS      PLITBLM               Table/Procedure/Type Null       None
    22 SYS      DBMS_APPLICATION_INFO Table/Procedure/Type Null       None
    22          EODA                  73                   Share      None
    22 SYS      DATABASE              18                   Null       None
8 rows selected.

We find that P is now missing from the view. Our parse lock has been broken.

This view is useful to you, as a developer, when it is found that some piece of code won’t compile in the test or development system—it hangs and eventually times out. This indicates that someone else is using it (actually running it), and you can use this view to see who that might be. The same will happen with GRANT statements and other types of DDL against the object. You cannot grant EXECUTE on a procedure that is running, for example. You can use the same method to discover the potential blockers and waiters.

Latches

Latches are lightweight serialization devices used to coordinate multiuser access to shared data structures, objects, and files. Latches are locks designed to be held for extremely short periods of time—for example, the time it takes to modify an in-memory data structure. They are used to protect certain memory structures, such as the database block buffer cache or the library cache in the shared pool. Latches are typically requested internally in a willing to wait mode. This means that if the latch is not available, the requesting session will sleep for a short period of time and retry the operation later. Other latches may be requested in an immediate mode, which is similar in concept to a SELECT FOR UPDATE NOWAIT, meaning that the process will go do something else, such as try to grab an equivalent sibling latch that may be free, rather than sit and wait for this latch to become available. Since many requestors may be waiting for a latch at the same time, you may see some processes waiting longer than others. Latches are assigned rather randomly, based on the luck of the draw, if you will. Whichever session asks for a latch right after it was released will get it. There is no line of latch waiters—just a mob of waiters constantly retrying.

Oracle uses atomic instructions like “test and set” and “compare and swap” for operating on latches. Since the instructions to set and free latches are atomic, the operating system itself guarantees that only one process gets to test and set the latch even though many processes may be going for it simultaneously. Since the instruction is only one instruction, it can be quite fast (but the overall latching algorithm itself is many CPU instructions). Latches are held for short periods of time and provide a mechanism for cleanup in case a latch holder dies abnormally while holding it. This cleanup process would be performed by PMON.

Enqueues, which we discussed earlier, are another, more sophisticated serialization device used when updating rows in a database table, for example. They differ from latches in that they allow the requestor to queue up and wait for the resource. With a latch request, the requestor session is told right away whether or not it got the latch. With an enqueue lock, the requestor session will be blocked until it can actually attain it.

As such, an enqueue is not as fast as a latch can be, but it does provide functionality over and above what a latch can offer. Enqueues may be obtained at various levels, so you can have many share locks and locks with various degrees of shareability.

Latch “Spinning”

One thing I’d like to drive home with regard to latches is this: latches are a type of lock, locks are serialization devices, and serialization devices inhibit scalability. If your goal is to construct an application that scales well in an Oracle environment, you must look for approaches and solutions that minimize the amount of latching you need to perform.

Even seemingly simple activities, such as parsing a SQL statement, acquire and release hundreds or thousands of latches on the library cache and related structures in the shared pool. If we have a latch, then someone else might be waiting for it. When we go to get a latch, we may well have to wait for it ourselves.

Waiting for a latch can be an expensive operation. If the latch is not available immediately and we are willing to wait for it, as we likely are most of the time, then on a multi-CPU machine our session will spin, trying over and over, in a loop, to get the latch. The reasoning behind this is that context switching (i.e., getting kicked off the CPU and having to get back on the CPU) is expensive. So, if the process cannot get a latch immediately, we’ll stay on the CPU and try again immediately rather than just going to sleep, giving up the CPU, and trying later when we’ll have to get scheduled back on the CPU. The hope is that the holder of the latch is busy processing on the other CPU (and since latches are designed to be held for very short periods of time, this is likely) and will give it up soon. If after spinning and constantly trying to get the latch, we still fail to obtain it, only then will our process sleep, or take itself off of the CPU, and let some other work take place. This sleep action is usually the result of many sessions concurrently requesting the same latch; it is not that a single session is holding it for a long time, but rather that so many sessions want it at the same time and each hold it for a short duration. If you do something short (fast) often enough, it adds up! The pseudo-code for a latch get might look like this:

Loop
        for i in 1 .. 2000
        loop
                try to get latch
                if got latch, return
                if i = 1 then misses=misses+1
        end loop
        INCREMENT WAIT COUNT
        sleep
        Add WAIT TIME
End loop;

The logic is to try to get the latch and, failing that, to increment the miss count, a statistic we can see in a Statspack report or by querying the V$LATCH view directly. Once the process misses, it will loop some number of times (an undocumented parameter controls the number of times and is typically set to 2,000), attempting to get the latch over and over. If one of these get attempts succeeds, then it returns and we continue processing. If they all fail, the process will go to sleep for a short duration of time, after incrementing the sleep count for that latch. Upon waking up, the process begins all over again. This implies that the cost of getting a latch is not just the “test and set”-type operation that takes place, but also a considerable amount of CPU while we try to get the latch. Our system will appear to be very busy (with much CPU being consumed), but not much work is getting done.

Measuring the Cost of Latching a Shared Resource

As an example, we’ll study the cost of latching the shared pool. We’ll compare a well-written program (one that uses bind variables) and a program that is not so well written (it uses literal SQL, or unique SQL for each statement). To do this, we’ll use a very small Java program that simply logs into Oracle, turns off auto-commit (as all Java programs should do immediately after connecting to a database), and executes 25,000 unique INSERT statements in a loop. We’ll perform two sets of tests: our program will not use bind variables in the first set, and in the second set it will.

To evaluate these programs and their behavior in a multiuser environment, I opted to use Statspack to gather the metrics, as follows:

1. Execute a Statspack snapshot to gather the current state of the system.
2. Run N copies of the program, having each program INSERT into its own database table so as to avoid the contention associated with having all programs trying to insert into a single table.
3. Take another snapshot immediately after the last copy of the program finishes.

Then it is a simple matter of printing out the Statspack report and finding out how long it took N copies of the program to complete, how much CPU was used, what major wait events occurred, and so on.

These tests were performed on a dual-CPU machine with hyperthreading enabled (making it appear as if there were four CPUs). Given that there were two physical CPUs, you might expect very linear scaling here—that is, if one user uses 1 unit of CPU to process her inserts, then you might expect that two users would require 2 units of CPU. You’ll discover that this premise, while sounding plausible, may well be inaccurate (just how inaccurate depends on your programming technique, as you’ll see). It would be correct if the processing we were performing needed no shared resource, but our process will use a shared resource, namely the shared pool. We need to latch the shared pool to parse SQL statements, and we need to latch the shared pool because it is a shared data structure, and we cannot modify it while others are reading it and we cannot read it while it is being modified.

Setting Up for the Test

In order to test, we’ll need a schema (set of tables) to work with. We’ll be testing with multiple users and want to measure the contention due to latching most of all, meaning that we’re not interested in measuring the contention you might observe due to multiple sessions inserting into the same database table. So, we’ll want a table per user to be created and we’ll name these tables T1 . . . T10. For example:

SCOTT@ORA12CR1> connect scott/tiger
Connected.

SCOTT@ORA12CR1> begin
  2      for i in 1 .. 10
  3      loop
  4          for x in (select * from user_tables where table_name = 'T'||i )
  5          loop
  6              execute immediate 'drop table ' || x.table_name;
  7          end loop;
  8          execute immediate 'create table t' || i || ' ( x int )';
  9      end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

We’ll run this script before each iteration of the test to follow in order to reset our schema and to force hard parsing to take place if we run a test more than once. During our testing, we’ll follow these steps:

1. Run statspack.snap.
2. Immediately start N of our Java routines, where N will vary from 1 to 10, representing 1 to 10 concurrent users.
3. Wait for all N to complete.
4. Run statspack.snap.
5. Generate the Statspack report for the last two Statspack IDs.

The numbers presented for the following test runs were collected using this technique.

Without Bind Variables

In the first instance, our program will not use bind variables, but rather will use string concatenation to insert data (you will obviously have to use your own connect string for your system):

import java.sql.*;
public class instest
{
   static public void main(String args[]) throws Exception
   {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      Connection
         conn = DriverManager.getConnection
         ("jdbc:oracle:thin:@heesta:1521:ORA12CR1","scott","tiger");
      conn.setAutoCommit(false );
      Statement stmt = conn.createStatement();
      for(int i = 0; i < 25000; i++ )
      {
        stmt.execute
        ("insert into "+ args[0] +
          " (x) values(" + i + ")" );
      }
      conn.commit();
      conn.close();
   }
}

I ran the test in single user mode (that is, by itself with no other active database sessions), and the Statspack report came back with this information:

   Elapsed:       0.25 (mins) Av Act Sess:       0.9
   DB time:       0.22 (mins)      DB CPU:       0.20 (mins)

Cache Sizes            Begin        End
~~~~~~~~~~~       ---------- ----------
    Buffer Cache:     2,656M              Std Block Size:         8K
     Shared Pool:       640M                  Log Buffer:    14,808K

Load Profile              Per Second    Per Transaction    Per Exec    Per Call
~~~~~~~~~~~~      ------------------  ----------------- ----------- -----------
...
          Parses:            3,342.1           25,066.0
     Hard parses:            1,667.2           12,504.0
...
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:   99.99  Optimal W/A Exec %:  100.00
            Library Hit   %:   60.05        Soft Parse %:   50.12
         Execute to Parse %:    0.11         Latch Hit %:  100.00
Parse CPU to Parse Elapsd %:  108.72     % Non-Parse CPU:   16.29
...
Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
AQPC idle                                            1          30  30010   36.1
heartbeat redo informer                             15          15   1004   18.1
LGWR worker group idle                              14          15   1055   17.8
lreg timer                                           4          12   3001   14.4
CPU time                                                        11          13.4

I included the SGA configuration for reference, but the relevant statistics are as follows:

• Elapsed (DB time) time of approximately 15 seconds (0.25 of a minute)
• 1,667 hard parses per second
• 11 CPU seconds used

Now, if we were to run two of these programs simultaneously, we might expect the hard parsing to jump to about 3,300 per second (we have two CPUs available, after all) and the CPU time to double to perhaps 22 CPU seconds. Let’s take a look:

   Elapsed:       0.27 (mins) Av Act Sess:       1.6
   DB time:       0.44 (mins)      DB CPU:       0.41 (mins)
...
Load Profile              Per Second    Per Transaction    Per Exec    Per Call
~~~~~~~~~~~~      ------------------  ----------------- ----------- -----------
...
          Parses:            6,259.8           33,385.3
     Hard parses:            3,125.6           16,669.7
...
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:   99.99  Optimal W/A Exec %:  100.00
            Library Hit   %:   60.03        Soft Parse %:   50.07
         Execute to Parse %:    0.06         Latch Hit %:   98.41
Parse CPU to Parse Elapsd %:   96.28     % Non-Parse CPU:   15.06
...
Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
CPU time                                                        23          32.7
LGWR worker group idle                              18          16    876   22.8
heartbeat redo informer                             15          15   1005   21.8
lreg timer                                           5          15   3001   21.7
latch: shared pool                              15,076           0      0     .6

What we discover is that the hard parsing goes up a little bit, but the CPU time more than doubles. How could that be? The answer lies in Oracle’s implementation of latching. On this multi-CPU machine, when we could not immediately get a latch, we spun. The act of spinning itself consumes CPU. Process 1 attempted many times to get a latch onto the shared pool only to discover that process 2 held that latch, so process 1 had to spin and wait for it (consuming CPU). The converse would be true for process 2; many times it would find that process 1 was holding the latch to the resource it needed. So, much of our processing time was spent not doing real work, but waiting for a resource to become available. If we page down through the Statspack report to the “Latch Sleep Breakdown” report, we discover the following:

Latch Name                        Requests       Misses      Sleeps        Gets
-------------------------- --------------- ------------ ----------- -----------
shared pool                      2,296,041       75,240      15,267      60,165

Note how the number 15,267 appears in the SLEEPS column here? That number corresponds very closely to the number of WAITS reported in the preceding “Top 5 Timed Events” report.

Our “Latch Sleep Breakdown” report shows us the number of times we tried to get a latch and failed in the spin loop. That means the Top 5 report is showing us only the tip of the iceberg with regard to latching issues—the 75,240 misses (which means we spun trying to get the latch) are not revealed in the Top 5 report for us. After examination of the Top 5 report, we might not be inclined to think we have a hard parse problem here, even though we have a very serious one. To perform 2 units of work, we needed to use more than 2 units of CPU. This was due entirely to the fact that we need that shared resource, the shared pool. Such is the nature of latching.

You can see that it can be very hard to diagnose a latching-related issue, unless you understand the mechanics of how they are implemented. A quick glance at a Statspack report, using the Top 5 section, might cause us to miss the fact that we have a fairly bad scaling issue on our hands. Only by deeper investigation in the latching section of the Statspack report will we see the problem at hand.

Additionally, it is not normally possible to determine how much of the CPU time used by the system is due to this spinning—all we know in looking at the two-user test is that we used 23 seconds of CPU time and that we missed getting a latch on the shared pool 75,240 times. We don’t know how many times we spun trying to get the latch each time we missed, so we have no real way of gauging how much of the CPU time was spent spinning and how much was spent processing. We need multiple data points to derive that information.

In our tests, because we have the single-user example for comparison, we can conclude that about 1 CPU seconds or so was spent spinning on the latch, waiting for that resource. We can come to this conclusion because we know that a single user needs only 11 seconds of CPU time so two single users would need 22 seconds, and 23 (total CPU seconds) minus 22 is 1.

With Bind Variables

Now I’d like to look at the same situation as presented in the previous section, but this time using a program that uses significantly less latches during its processing. We’ll take that Java program and code it using bind variables. To accomplish this, we’ll change the Statement into a PreparedStatement, parse a single INSERT statement, and then bind and execute that PreparedStatement repeatedly in the loop:

import java.sql.*;
public class instest
{
   static public void main(String args[]) throws Exception
   {
      System.out.println("start" );
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      Connection
         conn = DriverManager.getConnection
                ("jdbc:oracle:thin:@heesta:1521:ORA12CR1", "scott","tiger");
      conn.setAutoCommit(false );
      PreparedStatement pstmt =
          conn.prepareStatement
          ("insert into "+ args[0] + " (x) values(?)" );
      for(int i = 0; i < 25000; i++ )
      {
        pstmt.setInt(1, i );
        pstmt.executeUpdate();
      }
      conn.commit();
      conn.close();
      System.out.println("done" );
   }
}

Let’s look at the single and dual user Statspack reports, as we did for the no bind variable example. We’ll see dramatic differences here. Here is the single-user report:

   Elapsed:       0.07 (mins) Av Act Sess:       0.6
   DB time:       0.04 (mins)      DB CPU:       0.03 (mins)

Cache Sizes            Begin        End
~~~~~~~~~~~       ---------- ----------
    Buffer Cache:     2,656M              Std Block Size:         8K
     Shared Pool:       640M                  Log Buffer:    14,808K

Load Profile              Per Second    Per Transaction    Per Exec    Per Call
~~~~~~~~~~~~      ------------------  ----------------- ----------- -----------
...
          Parses:              158.5              317.0
     Hard parses:               29.8               59.5
...
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:   98.99  Optimal W/A Exec %:  100.00
            Library Hit   %:   96.14        Soft Parse %:   81.23
         Execute to Parse %:   97.72         Latch Hit %:  100.00
Parse CPU to Parse Elapsd %:   87.10     % Non-Parse CPU:   71.58
...
Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
AQPC idle                                            1          30  30004   66.6
lreg timer                                           2           6   3004   13.3
heartbeat redo informer                              4           4   1006    8.9
LGWR worker group idle                              12           4    331    8.8
CPU time                                                         1           2.1

That is quite dramatic: from 11 CPU seconds in the no bind variables example to 1 CPU second here. From 1,667 hard parses per second to about 29 per second (and based on my knowledge of how Statspack works, most of those were from running Statspack). Even the elapsed time was dramatically reduced from about 15 seconds down to 4 seconds (0.07 minutes). When not using bind variables, we spent ten-elevenths of our CPU time parsing SQL (1 second versus 11). This was not entirely latch related, as much of the CPU time incurred without bind variables was spent parsing and optimizing the SQL. Parsing SQL is very CPU intensive, but to expend ten-elevenths of our CPU doing something (parsing) that doesn’t really do useful work for us—work we didn’t need to perform—is pretty expensive.

When we get to the two-user test, the results continue to look better:

   Elapsed:       0.08 (mins) Av Act Sess:       0.9
   DB time:       0.07 (mins)      DB CPU:       0.07 (mins)
...
Load Profile              Per Second    Per Transaction    Per Exec    Per Call
~~~~~~~~~~~~      ------------------  ----------------- ----------- -----------
...
          Parses:               25.6               42.7
     Hard parses:                0.8                1.3
...
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:   99.93  Optimal W/A Exec %:  100.00
            Library Hit   %:   99.97        Soft Parse %:   96.88
         Execute to Parse %:   99.74         Latch Hit %:   99.99
Parse CPU to Parse Elapsd %:  100.00     % Non-Parse CPU:   99.66
...
Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
AQPC idle                                            1          30  30012   74.6
heartbeat redo informer                              4           4   1010   10.0
lreg timer                                           1           3   3000    7.5
CPU time                                                         3           7.3
log file parallel write                             22           0      5     .3

The amount of CPU time is about 2 to 3 times the amount reported by the single-user test case.

Further, the amount of CPU used by two users with bind variables is far less than half the amount of CPU a single user not using bind variables required! When I looked at the latch report in this Statspack report, I found there was so little contention for the shared pool and library cache that it was not even worth reporting. In fact, digging deeper turned up the fact that the shared pool latch was requested 50,511 times versus well over 2.2 million times in the preceding two-user test without binds:

Latch Name                        Requests       Misses      Sleeps        Gets
-------------------------- --------------- ------------ ----------- -----------
shared pool                         50,511           48           1          47

Performance/Scalability Comparison

Table 3-1 summarizes the CPU usage by each implementation, as well as the latching results as we increase the number of users beyond two. As you can see, the solution using fewer latches (binds) will scale much better as the user load goes up.

*Note: Latch wait count is in thousands for No Binds, but not for Binds.

The interesting observation is that 10 users using bind variables (and very few latch requests as a result) use the same amount of hardware resources (CPU) as 1 user that does not use bind variables (i.e., that overuse a latch or process more than they need to). When you examine the results for 10 users, you see that nonuse of bind variables results in the use of over 14 times the CPU and takes almost 9 times the execution time when compared to the bind variable solution. The more users are added over time, the longer each user spends waiting for these latches. We went from an average of 0.6 seconds/session (3 seconds of wait/5 sessions) of wait time for latches with 5 users to an average of 1.7 seconds/session of wait time with 10 users. However, the implementation that avoided overuse of the latch suffered no ill effects as it scaled up.

Mutexes

A mutex is a serialization device much like a latch is, in fact, the name mutex stands for mutual exclusion. It is another serialization tool used by the database; it was introduced in Oracle 10g Release 1 and is used in place of traditional latches in many places in the server. A mutex differs from a latch in that it is even more lightweight in its implementation. It requires less code to implement, approximately one-fifth of the instructions (which results in less CPU to request in general) and it requires less memory, approximately one-seventh of the size, to implement. A mutex, in addition to being lighter weight, is a little less functional in some respects. Just like an enqueue lock is much heavier than a latch, a latch is heavier than a mutex. But, like the enqueue to latch comparison, the latch can do more than a mutex in some cases (like an enqueue can do more than a latch in some cases). This means that not every latch will be, or should be, replaced by a mutex, just as every enqueue lock will not be, or should not be, replaced by a latch.

When reading about mutexes in various reports, just remember that they are lighter-weight serialization devices. They enable possibly more scalability than a latch (just as latches are more scalable than enqueues), but they are still a serialization device. If you can avoid doing something that requires a mutex, in general, you should, for the same reason you would avoid requesting a latch if possible.

Manual Locking and User-Defined Locks

So far, we have looked mostly at locks that Oracle places for us transparently. When we update a table, Oracle places a TM lock on it to prevent other sessions from dropping that table (or performing most DDL, in fact). We have TX locks that are left on the various blocks we modify so others can tell what data we own. The database employs DDL locks to protect objects from change while we ourselves are changing them. It uses latches and locks internally to protect its own structure.

Next, let’s take a look at how we can get involved in some of this locking action. Our options are as follows:

• Manually lock data via a SQL statement.
• Create our own locks via the DBMS_LOCK package.

The following sections briefly discuss why you might want to do each of these.

Manual Locking

We have, in fact, already seen a couple of cases where we might want to use manual locking. The SELECT...FOR UPDATE statement is the predominant method of manually locking data. We used it in previous examples to avoid the lost update issue whereby one session would overwrite another session’s changes. We’ve seen it used as a method to serialize access to detail records to enforce business rules.

We can also manually lock data using the LOCK TABLE statement. This statement is used rarely, because of the coarseness of the lock. It simply locks the table, not the rows in the table. If you start modifying the rows, they will be locked as normal. So, this is not a method to save on resources (as it might be in other RDBMSs). You might use the LOCK TABLE IN EXCLUSIVE MODE statement if you were writing a large batch update that would affect most of the rows in a given table and you wanted to be sure that no one would block you. By locking the table in this manner, you can be assured that your update will be able to do all of its work without getting blocked by other transactions. It would be the rare application, however, that has a LOCK TABLE statement in it.

Creating Your Own Locks

Oracle actually exposes to developers the enqueue lock mechanism that it uses internally, via the DBMS_LOCK package. You might be wondering why you would want to create your own locks. The answer is typically application specific. For example, you might use this package to serialize access to some resource external to Oracle. Say you are using the UTL_FILE routine that allows you to write to a file on the server’s file system. You might have developed a common message routine that every application calls to record messages. Since the file is external, Oracle won’t coordinate the many users trying to modify it simultaneously. In comes the DBMS_LOCK package. Now, before you open, write, and close the file, you will request a lock named after the file in exclusive mode, and after you close the file, you will manually release the lock. In this fashion, only one person at a time will be able to write a message to this file. Everyone else will queue up. The DBMS_LOCK package allows you to manually release a lock when you are done with it, or to give it up automatically when you commit, or even to keep it as long as you are logged in.

Summary

In this chapter we looked at how Oracle manages concurrency through locks on rows, objects, and memory structures. Oracle uses TX locks to ensure that only one transaction is modifying a row at a given time. Oracle uses DM locks to ensure that the structure of an object isn’t changed while its contents are being modified. We examined helpful queries that show the transaction details and identify sessions holding the lock and also show the sessions blocked from obtaining a lock.

We also discussed latches, which are lightweight serialization devices used to coordinate multiuser access to shared data structures, objects, and files. Latches are locks designed to be held for very short amount of time. They are used to protect memory structures in the block buffer cache, library cache, and the shared pool. We also talked about a mutex, which is a serialization device similar to a latch. A mutex is a much more efficient serialization device and is used in place of traditional latches in many places in the server (starting with Oracle 10g and above).

Lastly we briefly looked at manual locking methods. The most common manual locking method is the SELECT...FOR UPDATE statement. Other than that, for the most part you’ll never need to implement manual locking methods (such as LOCK TABLE).

In the next chapter we’ll investigate multiversioning (Oracle’s ability to simultaneously materialize multiple versions of the data). This is the mechanism by which Oracle provides read-consistent views of data. We’ll also take a detailed look at how Oracle implements multiversioning in regard to transaction isolation levels.