Oracle Internals: An Introduction

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Why are people so intensely interested in Oracle internals? Partly because internals information can be useful for tuning and troubleshooting. But also because Oracle Corporation has kept most of the internals secret, while revealing just enough to tantalize.

In fact, Oracle internals information is needed only for advanced performance tuning. It's true that basic application tuning is the kind of tuning that's most often needed, and the kind that has the biggest impact. Nevertheless, there are times when advanced performance tuning is necessary, and that is when you need a deep understanding of how Oracle works. This book provides some of the foundations for that understanding.

To appreciate the contribution that this book makes, and to put it in context, you need to have a basic understanding of the layers of the Oracle kernel.

The Oracle kernel is comprised of layers; the main layers are shown in Figure 1.1. Each layer depends upon the services of the layers below it, and may call any of them directly, in any order. However, control is never passed up the stack, except when returning from a call.

The one apparent exception to this rule is that the data layer and the transaction layer sometimes need to perform recursive transactions for tasks such as index block splits or extent space management, and recursive calls are needed for tasks such as trigger execution or SQL statement execution from within stored program units. However, instead of calling back to the kernel execution or compilation layer from within the same session or call context, a separate context is established and the stack is reentered from the top layer.

Figure 1.1: The Oracle kernel layers

Each layer has a short name, or abbreviation, that is used as a prefix to the names of its modules. For example, KC is the short name for the kernel cache layer. These short names are shown in Figure 1.1 and in the following list. Similarly, each of the modules that comprise the layers has a short name too. For example, KCR is the redo management module within the cache layer. These module names are prefixed to the names of their data structures and function calls. For example, KCRFAL is the redo allocation latch. This naming convention makes Oracle's names seem rather cryptic and formidable at first, but they soon become surprisingly easy to recognize and a great aid to understanding. Nevertheless, you will be pleased to know that this book uses the verbose names in preference to their somewhat cryptic alternatives.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The Oracle kernel is comprised of layers; the main layers are shown in Figure 1.1. Each layer depends upon the services of the layers below it, and may call any of them directly, in any order. However, control is never passed up the stack, except when returning from a call.

The one apparent exception to this rule is that the data layer and the transaction layer sometimes need to perform recursive transactions for tasks such as index block splits or extent space management, and recursive calls are needed for tasks such as trigger execution or SQL statement execution from within stored program units. However, instead of calling back to the kernel execution or compilation layer from within the same session or call context, a separate context is established and the stack is reentered from the top layer.

Figure 1.1: The Oracle kernel layers

Each layer has a short name, or abbreviation, that is used as a prefix to the names of its modules. For example, KC is the short name for the kernel cache layer. These short names are shown in Figure 1.1 and in the following list. Similarly, each of the modules that comprise the layers has a short name too. For example, KCR is the redo management module within the cache layer. These module names are prefixed to the names of their data structures and function calls. For example, KCRFAL is the redo allocation latch. This naming convention makes Oracle's names seem rather cryptic and formidable at first, but they soon become surprisingly easy to recognize and a great aid to understanding. Nevertheless, you will be pleased to know that this book uses the verbose names in preference to their somewhat cryptic alternatives.

The Oracle call interface (OCI): The Oracle call interface is the lowest level at which client programs are intended to interact with Oracle. This interface is well documented and provides access to most of the functionality of Oracle, including advanced features such as object navigation, and sophisticated transaction and session control. Applications with advanced requirements have to use OCI directly, in order to access the features that are not available in Oracle's other development tools.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This book covers the kernel services for waits, latches, locks, and memory. Although there is relatively little you can do to tune these services themselves, you will need to understand them when you tune any other part of Oracle.

Chapter 2: The wait statistics are the most important Oracle statistics for advanced performance tuning. This chapter explains how to gather and use these statistics.
Chapter 3: Oracle makes extensive use of latches, and advanced performance tuning often involves the prevention of latch contention. This chapter provides a foundation for such tuning by explaining how latches are used.
Chapter 4: Oracle uses many types of locks. This chapter explains how locks are used, and how to diagnose locking problems.
Chapter 5: Oracle parallel server technology adds an extra dimension to Oracle tuning. This chapter explains how parallel server locking is implemented, and what the statistics mean.
Chapter 6: This chapter explains how Oracle's internal memory management works. I pay particular attention to the inner workings of the shared pool, and to assessing whether it is sized correctly.

Although there is much more to Oracle internals than this small book covers, these chapters provide the foundation that you need for advanced performance tuning.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In an Oracle instance many processes (or threads of a single process) work together. To work together, they must communicate, and one of main ways that they communicate is via semaphores. A semaphore is a signal. It is somewhat like a railway signal that tells trains whether to stop and wait, and when to go. Oracle server processes often need to stop and wait:

Sometimes because a resource is not available
Sometimes because they have no work to do
Sometimes because they need to wait for another server process to perform a prerequisite task

Semaphores allow Oracle server processes to stop and wait, and then to be notified when they should resume processing.

There is a semaphore for every Oracle server process. Processes wait on their semaphore when they need to wait for a resource, or need work to do, or need work to be done. When the resource has been freed, or when there is work to do, or when the prerequisite work has been done, then their semaphore is posted as a signal to stop waiting.

For example, LGWR (the Log Writer process) may be waiting on its semaphore for work to do, while a user process may be copying redo information into the redo log buffer. When the user commits, LGWR must write the redo and commit marker to the log file while the user waits. To achieve this, the user process posts LGWR's semaphore to signal that it can stop waiting for work to do, as some work is now available. The user process then waits on its own semaphore. When the log file I/O has completed, LGWR posts the semaphore of the user process to signal that it can now begin its next transaction, because the commit operation has completed. LGWR then waits on its own semaphore again, because it has no more work to do.

For another example, process A may need to update a row, but find that process B has not yet committed an earlier update to the same row. Process A must wait for process B to commit. To achieve this, process A will wait on its semaphore. When process B commits, it will post process A's semaphore to signal that it can now proceed with its update.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

There is a semaphore for every Oracle server process. Processes wait on their semaphore when they need to wait for a resource, or need work to do, or need work to be done. When the resource has been freed, or when there is work to do, or when the prerequisite work has been done, then their semaphore is posted as a signal to stop waiting.

For example, LGWR (the Log Writer process) may be waiting on its semaphore for work to do, while a user process may be copying redo information into the redo log buffer. When the user commits, LGWR must write the redo and commit marker to the log file while the user waits. To achieve this, the user process posts LGWR's semaphore to signal that it can stop waiting for work to do, as some work is now available. The user process then waits on its own semaphore. When the log file I/O has completed, LGWR posts the semaphore of the user process to signal that it can now begin its next transaction, because the commit operation has completed. LGWR then waits on its own semaphore again, because it has no more work to do.

For another example, process A may need to update a row, but find that process B has not yet committed an earlier update to the same row. Process A must wait for process B to commit. To achieve this, process A will wait on its semaphore. When process B commits, it will post process A's semaphore to signal that it can now proceed with its update.

Semaphores are an operating system facility. When an Oracle process is waiting on its semaphore, the operating system will not schedule it to run on a CPU. In operating system terms, it is blocked, not runnable. When the semaphore is posted, the operating system status of the process is changed from blocked to runnable, and the process will be scheduled to run as soon as possible.

Some operating systems support more than one type of semaphore. System V semaphores are the most common. The semaphore data structures for System V semaphores form a fixed array in kernel memory sized by the SEMMNS kernel parameter. To post a semaphore or wait on a semaphore, processes must use the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The Oracle wait statistics are pure gold—but not to be overvalued. Many types of performance problems are easy to identify from the wait statistics. If Oracle is waiting extensively for resources such as latches, free cache buffers, enqueue locks, and so on, then the wait statistics can both identify and quantify the problem. With experience, you may also be able to use the wait statistics to identify network and disk performance problems. The wait statistics also provide valuable feedback on attempts to resolve such problems.

But if your application is doing more parsing, or more disk I/O than necessary for its workload, then the wait statistics cannot help you. They will appear to give your instance a clean bill of health, and rightly so. The wait statistics are only able to reveal inefficiencies at the database server level and below. So they are silent about application-level performance problems that increase the load on the database server but do not cause it to work inefficiently.

However, you should already have addressed all the application performance issues before considering database server tuning in detail. If so, the wait statistics can have full value for database server tuning. But they can only have full value if the waits are timed.

Waits are timed if and only if the TIMED_STATISTICS parameter is set to TRUE. Let me endorse what others have said before, that the overhead of timed statistics is negligible. If you need to convince yourself, use the SET TIMING ON command in SQL*Plus to measure the elapsed time of a benchmark query. Use an otherwise idle system and take ten or more measurements with and without timed statistics. You will be hard pressed to discern any significant difference.

Without timed statistics, Oracle records the reason for each wait before it begins to wait, and when the wait is over, it records whether it timed out. But with timed statistics enabled, Oracle checks the time just before and after each wait, and also records the time waited. The time waited is recorded in hundredths of a second—that is, centiseconds.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This section contains a quick reference to the parameters, events, statistics, and APT scripts mentioned in Chapter 2.

Parameter	Description
_USE_VECTOR_POSTS	Vector posts enable multiple waiting processes to be posted in a single semaphore operation.
POST_WAIT_DEVICE	The post-wait driver is a pseudo device driver. Its functions are invoked when operations are performed against a device special file of that device type. Where this parameter is used, it specifies the path to the device file for the post-wait driver.
TIMED_STATISTICS	Should be set to TRUE whenever timing information may be required for tuning purposes, which is always.
USE_POST_WAIT_DRIVER	If this parameter exists, it should be set to TRUE in order to use the post-wait driver, instead of regular semaphore operations.

Event	Description
10046	This is the event used to implement the DBMS_SUPPORT trace, which is a superset of Oracle's SQL_TRACE facility. At level 4, bind calls are included in the trace output; at level 8, wait events are included, which is the default level for DBMS_SUPPORT; and at level 12, both binds and waits are included. See the excellent Oracle Note 39817.1 for a detailed explanation of the raw information in the trace file.

Event

Description

10046

This is the event used to implement the DBMS_SUPPORT trace, which is a superset of Oracle's SQL_TRACE facility. At level 4, bind calls are included in the trace output; at level 8, wait events are included, which is the default level for DBMS_SUPPORT; and at level 12, both binds and waits are included. See the excellent Oracle Note 39817.1 for a detailed explanation of the raw information in the trace file.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

There are numerous data structures in Oracle's System Global Area (SGA) that need to be accessed concurrently by many different database processes. It is essential that only one process be able to modify any particular data structure at one time, and that the data structure cannot be modified while it is being inspected. Oracle makes sure this does not happen by protecting all SGA data structures with either locks or latches. (See Chapter 6, for a description of the contents of the SGA and other memory areas.)

Latches are the more restrictive mechanism, because they do not allow multiple processes to inspect the protected data structure at the same time—they provide for exclusive access only. Locks allow for better concurrency, because they may be held in a shared mode when the data structure is simply being inspected.

Another significant difference between locks and latches is request queuing. Requests for locks are queued if necessary and serviced in order, whereas latches do not support request queuing. If a request to get a latch fails because the latch is busy, the process just continues to retry until it succeeds. So latch requests are not necessarily serviced in order.

Because a latch can only be held by one process at a time, and because there is no inherent concept of queuing, the latch data structure itself is very simple—essentially just a single location in memory representing the state of the latch. And because the latch data structure is so simple, the functions to get and release a latch have very little work to do. By contrast, the data structures for locks are much more sophisticated because of their support for queuing and concurrency. So the functions to get, convert, and release locks have correspondingly more work to do.

Of course, it is necessary for Oracle to ensure that only one process at a time can modify the latch and lock data structures themselves. For latches this is easy. Because each latch is just a single location in memory, Oracle is able to use the TEST AND SET, LOAD AND CLEAR, or COMPARE AND SWAP instructions of the underlying hardware's instruction set for its latch get operations. Because these are simple machine instructions that are guaranteed to be atomic, no other locking mechanism is needed. This simplicity makes latch gets very efficient.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Latches are the more restrictive mechanism, because they do not allow multiple processes to inspect the protected data structure at the same time—they provide for exclusive access only. Locks allow for better concurrency, because they may be held in a shared mode when the data structure is simply being inspected.

Another significant difference between locks and latches is request queuing. Requests for locks are queued if necessary and serviced in order, whereas latches do not support request queuing. If a request to get a latch fails because the latch is busy, the process just continues to retry until it succeeds. So latch requests are not necessarily serviced in order.

Because a latch can only be held by one process at a time, and because there is no inherent concept of queuing, the latch data structure itself is very simple—essentially just a single location in memory representing the state of the latch. And because the latch data structure is so simple, the functions to get and release a latch have very little work to do. By contrast, the data structures for locks are much more sophisticated because of their support for queuing and concurrency. So the functions to get, convert, and release locks have correspondingly more work to do.

Of course, it is necessary for Oracle to ensure that only one process at a time can modify the latch and lock data structures themselves. For latches this is easy. Because each latch is just a single location in memory, Oracle is able to use the TEST AND SET, LOAD AND CLEAR, or COMPARE AND SWAP instructions of the underlying hardware's instruction set for its latch get operations. Because these are simple machine instructions that are guaranteed to be atomic, no other locking mechanism is needed. This simplicity makes latch gets very efficient.

Oracle's lock data structures, on the other hand, have several parts, and therefore cannot be modified atomically. For this reason, Oracle actually protects operations on locks with latches. The type of latch used varies depending on the type of lock. For example, the cache buffer locks are indirectly protected by the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Most internal Oracle data structures that are protected by latches are protected by only one latch. However, in some cases more than one latch may be used. For example, there may be a number of library cache latches protecting different groups of objects in the library cache, and separate cache buffers chains latches are used to protect each of the database buffer cache hash chains.

Whenever a number of latches may be used to protect different parts of a structure, or different equivalent structures, these latches are called child latches. For each set of child latches of the same type there is one parent latch. In general, both the parent and child latches may be taken. In practice, however, the library cache parent latch is the only parent latch you are likely to see being taken, and even then this is a relatively rare occurrence by comparison with the activity against its child latches.

Somewhat confusingly, Oracle also refers to solitary latches that have no children as parent latches. So the V$LATCH_PARENT view contains one row for each of the solitary latches, as well as one row for each of the genuine parent latches. V$LATCH_CHILDREN has a row for each child latch. Thus, the union of these two views represents all latches.

The types of latches used by Oracle, and whether they are solitary latches or parent and child sets, varies with different releases of Oracle and operating system ports. The APT script latch_types.sql can be used to see what latch types are in use in your database, whether they are parent and child sets, and if so, how many child latches there are. Example 3.1 shows an extract of the output of this script.

Example 3.1. Sample Output from latch_types.sql

SQL> @latch_types
------ ------------------------------ ------ -------
     0 latch wait list                     1       1
     1 process allocation                  1
     2 session allocation                  1
     3 session switching                   1
     4 session idle bit                    1       1
...

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

When an Oracle process needs to access a data structure protected by a latch, it can request to get the latch in one of two modes—willing-to-wait mode or no-wait mode (also called immediate mode).

Oracle expects latches to be held briefly and intermittently. So if a process attempts to get a latch in willing-to-wait mode and finds that the latch is not available, it will spin briefly and then try again. When a process spins, it executes a simple series of instructions a number of times, as a way of waiting before trying again. This is sometimes called an active wait because from the operating system's perspective, the process is still actively consuming CPU cycles, although it is really just waiting a while.

The amount of CPU time that a process will burn before trying to get the latch once again is very small and fixed (although it was tunable in Oracle7 using the _LATCH_SPIN_COUNT parameter). If the next attempt to get the latch fails again, the procedure will be repeated up to the number of times specified by the _SPIN_COUNT parameter. This parameter normally defaults to 2000 iterations in multi-processor environments.

Section 3.3.1.1: Why spin?

The idea of spinning is that another process executing on another CPU may release the latch, thereby allowing the spinning process to proceed. Of course, it makes no sense to spin on a machine with just one CPU, and so Oracle does not.

The alternative to spinning is to relinquish the CPU and allow another process to use it. At first glance, this may seem like a good idea. However, for a CPU to stop executing one process and begin executing another, it must perform a context switch . That is, it must save the context of the first process, determine which process to schedule next, and then resume the context of the next process. The context of a process is essentially a set of CPU register values that describes the exact state of the process.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Some operating systems support a facility called multi-processing control. This enables an authorized user process to influence its CPU scheduling in a variety of ways. Where available, Oracle can use certain multi-processing control features. The following features affect the latching mechanism.

Preemption control enables Oracle to suspend the operation of the normal operating system process preemption mechanism during performance-critical operations—in particular, when holding a latch. This means that the Oracle process can continue to run on its CPU until it explicitly enables preemption again, or until it blocks on an operating system event such as an I/O request, semaphore operation, or page fault. The process will not be pre-empted at the end of its time-slice by a higher priority process of the time-sharing priority class. This means that operations protected by latches complete as quickly as possible, and so the risk of latch contention is greatly reduced. If preemption control is available to Oracle, it is used by default unless disabled using the _NO_PREEMPT parameter.

CPU yielding enables Oracle processes to offer to yield the CPU during a spin. If there is another runnable process of higher priority able to use the CPU, that process is scheduled, and the yielding process is placed at the end of its run queue, but it remains runnable. Otherwise, if there are no other higher-priority processes able to use the CPU, then the process will continue to spin for its latch. The frequency with which Oracle will offer to yield the CPU while spinning is controlled by the _SPIN_YIELD_CPU_FREQ parameter, which defaults to the default value of the _SPIN_COUNT parameter. If CPU yielding is available, and if these two parameters have the same value, the effect is that the process will begin a new spin without sleeping if there is no other process available to use the CPU. Thus, CPU yielding enables Oracle processes to obtain latches as quickly as possible without consuming otherwise usable CPU time.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This section contains a quick reference to the parameters, statistics, waits, and APT scripts mentioned in Chapter 3.

Parameter	Description
_LATCH_WAIT_POSTING	Latch wait posting is a mechanism whereby a process can be woken (posted) when the latch that it requires becomes available. If this parameter is set to 0, latch wait posting is disabled. If this parameter is set to 1 (the default), latch wait posting is enabled for the library cache and shared pool latches only. Any other setting results in latch wait posting being enabled for all latches.
_MAX_EXPONENTIAL_SLEEP	Consecutive sleeps during a single attempt to acquire a latch become progressively longer, under an exponential backoff algorithm, up to the limit specified by this parameter. Defaults to 200 centiseconds in Oracle8.
_MAX_SLEEP_HOLDING_LATCH	The maximum sleep allowed under the exponential backoff algorithm when the sleeping process is holding another latch. Defaults to 4 centiseconds.
_NO_PREEMPT	If this parameter is set to TRUE (the default) Oracle will use the operating system's preemption control mechanism, if available, to minimize the risk of processes sleeping while holding a latch.
_SPIN_COUNT	The number of iterations to perform before sleeping when spinning to acquire a latch. Defaults to 1 on single CPU systems, and 2000 on multi-processor machines.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Oracle uses latches to protect data structures that are accessed briefly and intermittently. However, latches are not suitable for protecting resources that may be needed for a relatively long time, such as database tables. In such cases, a lock must be used instead. Locks allow sessions to join a queue for a resource that is not immediately available. This avoids spinning. Locks also allow multiple sessions to share a resource if their activities are compatible.

Oracle uses locks for many different purposes. The following are the most important ones to understand for performance tuning.

Oracle's much vaunted row-level locks are subtle. When a transaction modifies a row, its transaction identifier is recorded in an entry in the interested transaction list (ITL) in the header of the data block itself, and the row header is modified to point to that ITL entry. Once these changes have been made, no lock is retained. The ITL entry for the uncommitted transaction, together with the row header that references it, constitutes an implicit lock on the row.

When another transaction wants to modify the same row, and sees that an uncommitted transaction has modified that row, that transaction waits, not on a row-level lock, but on the transaction lock for the blocking transaction.

When the blocking transaction commits or rolls back, its transaction lock will be released. Its implicit row-level locks are thereby released, and so the blocked transaction can then proceed. Note that rolling back to a savepoint does not free previously blocked transactions that were waiting for a row-level lock.

Row-level locks protect data integrity at the lowest feasible level of granularity, and remain in force for the duration of a transaction. However, Oracle also needs short-term block-level locks to be in force while accessing or modifying blocks in its cache.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Oracle uses locks for many different purposes. The following are the most important ones to understand for performance tuning.

Oracle's much vaunted row-level locks are subtle. When a transaction modifies a row, its transaction identifier is recorded in an entry in the interested transaction list (ITL) in the header of the data block itself, and the row header is modified to point to that ITL entry. Once these changes have been made, no lock is retained. The ITL entry for the uncommitted transaction, together with the row header that references it, constitutes an implicit lock on the row.

When another transaction wants to modify the same row, and sees that an uncommitted transaction has modified that row, that transaction waits, not on a row-level lock, but on the transaction lock for the blocking transaction.

When the blocking transaction commits or rolls back, its transaction lock will be released. Its implicit row-level locks are thereby released, and so the blocked transaction can then proceed. Note that rolling back to a savepoint does not free previously blocked transactions that were waiting for a row-level lock.

Row-level locks protect data integrity at the lowest feasible level of granularity, and remain in force for the duration of a transaction. However, Oracle also needs short-term block-level locks to be in force while accessing or modifying blocks in its cache.

Buffer locks are used to provide simple read/write locking for blocks in the database buffer cache. Although they are often taken for granted and seldom mentioned, buffer locks are essential to data integrity, and can feature prominently in certain performance tuning scenarios.

The definitions of database objects in the data dictionary must be protected while they are being referenced. This is necessary to prevent those objects from being dropped, and to prevent their definitions from being changed, while they are being used. Dictionary locks must be held while dependent SQL statements are being parsed or executed, and must be retained for the duration of dependent transactions.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Locks are applied to both compound and simple objects. The classic example of a compound object and its component parts is a table and its rows. A cache buffer is an example of a simple object. Simple objects may only be locked in the following modes:

Exclusive: If a session needs to modify a simple object, then an exclusive lock is required on the resource to prevent any concurrent access.
Shared: If a session needs to inspect a simple object, then a shared lock on the resource is sufficient to ensure that the data structure will not be modified by another session, while allowing concurrent shared access.
Null: If a session has some information cached about an object, then a null mode lock may be held as a placeholder, even when the resource is not actively being used. A null mode lock does not inhibit any concurrent access, but if the resource is invalidated, the null mode lock acts as a trigger for the session to invalidate its private cached information. There is an important difference between holding a null mode lock, and not holding a lock at all.

In addition to the modes above, compound objects may also be locked in the following modes:

Sub-shared: If a session needs shared access to part of a compound object, then a shared lock on the entire compound resource would be unduly restrictive, because it would prevent exclusive access to other parts of the compound resource. In such cases, a sub-shared lock is used instead.
Sub-exclusive: If a session needs exclusive access to part of a compound resource, then a sub-exclusive lock is sufficiently restrictive.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Many of Oracle's locks are called enqueue locks. To enqueue a lock request is to place that request on the queue for its resource. So although the word "enqueue" is strictly speaking a verb, it is used adjectivally in the term enqueue lock. It is also used as a noun when referring to a particular enqueue resource, such as the CF (control file) enqueue.

Oracle uses two classes of local locks—those for which the lock and resource data structures are dynamically allocated in the shared pool, and those that use fixed arrays for the lock and resource data structures. Although almost all types of lock requests may be enqueued, the term enqueue should be taken to refer exclusively to those locks that use the fixed arrays for the lock and resource data structures, unless otherwise qualified.

The fixed array for enqueue resources is sized by the ENQUEUE_RESOURCES parameter. The number of slots in this array that are in use varies from time to time, and these can be seen in V$RESOURCE . Each row in V$RESOURCE represents a resource that is currently locked in any mode by one or more sessions. These resources are not persistent in that they are no longer defined once all locks on the resource have been released.

Rows in V$RESOURCE are identified by a two-character code representing the type of resource, and two numeric fields used to encode either the resource identity or the activities protected by locks on the resource, depending on the resource type. For example, resources of type TX represent entries in the transaction table of a rollback segment. The high-order two bytes of the first identifier contain the rollback segment number, and the low-order two bytes contain the transaction table slot number, while the second identifier contains the rollback segment wrap or sequence number.

All enqueue operations access the enqueue resource structure via a hash table. The hash value is based on the resource type and the numeric identifiers. The length of the enqueue hash table is set by the _ENQUEUE_HASH parameter. The default value of this parameter is derived directly from the PROCESSES parameter, as follows:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

A cache of rows from the data dictionary is kept in the shared pool. This cache serves not only to reduce physical access to the data dictionary tables in the SYSTEM tablespace, but also enables fine-grained locking of individual data dictionary rows. The need for data dictionary locking was introduced at the start of this chapter (see Section 4.1.3).

The locks on the data dictionary rows themselves are called row cache enqueue locks. These locks are implemented in much the same way as general enqueue locks. The cached data dictionary row acts as the resource structure, and enqueue lock structures are dynamically allocated from the shared pool as required. Locks can be requested, converted, and released, and requests can wait and time out, just like the general enqueue locks. However, row cache enqueue locks are not included in V$LOCK. In fact, they are not visible anywhere except in system and process state dumps.

Depending on the operation, some row cache enqueue locks are requested in no-wait mode and an ORA-54 error is returned if the lock is not immediately available. Otherwise, row cache lock requests are enqueued if necessary, and the process waits on a row cache lock wait. The parameters for this wait are shown in Table 4.4.

Table 4.4: Wait Parameters (row cache lock waits)
Parameter	Description
p1	A number corresponding to the CACHE# column of V$ROWCACHE representing the data dictionary table for which a row lock is needed
p2	The mode in which the lock is already held
p3	The mode in which the lock is needed

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The library cache is not one cache, but many. It contains the pseudo code for PL/SQL program units. It contains parse trees and execution plans for shareable SQL statements. It also contains abstract representations in a form called DIANA of the database objects referenced by the SQL statements. The information is needed in this form for PL/SQL program unit compilation and SQL statement parsing and execution, despite the fact that the dictionary cache contains the same information in a different form. The library cache also contains control structures such as synonym translations, dependency tracking information, and library cache locks and pins.

Library cache locks are referred to as breakable parse locks in the Oracle documentation. They are applied to the library cache objects for SQL statements and PL/SQL program units, and recursively to the library cache objects for the database objects on which they depend. Library cache locks are held in shared mode during parse operations and are converted to null mode thereafter. If a DDL statement later modifies the definition of a database object, then the library cache information for that database object and all dependent library cache objects is invalidated by breaking the library cache locks.

Library cache locks can only be broken, however, when the library cache object is not also pinned. A pin is applied to the library cache object for a PL/SQL program unit or SQL statement while it is being compiled, parsed, or executed. Pins are normally held in shared mode, but are also held in exclusive mode while the library cache information for the object is being changed. The library cache objects for pipes and sequences are most subject to change. When a library cache object is pinned, pins are applied to all referenced objects in turn. When a pin is applied to the library cache object for a database object, then a corresponding row cache enqueue lock is acquired on the underlying data dictionary row, thereby preventing conflicting DDL.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Library cache pins and the associated row cache enqueue locks protect object definitions for the duration of parse and execute calls. However, for transactions that consist of a series of statements, equivalent locks need to be held for the duration of the transaction.

More than that, the lock mode may need to be raised partway through the transaction. For example, a table may first be queried, and then updated. This, of course, is why lock conversions are necessary. If the existing lock were to be released, even momentarily, it would be possible for the referenced object to be dropped or changed, and the transaction would then be unable to either proceed or roll back.

The possibility of rollback, particularly rollback to a savepoint, adds another dimension of complexity to dictionary locking. Namely, if a transaction is rolled back beyond the point at which a lock was upgraded, then the lock must be downgraded correspondingly, as part of the rollback operation, in order to reduce the risk of artificial deadlocks.

The requirements of dictionary locking for transactions and, in particular, the maintenance of a history of lock conversions, is provided by DML locks in conjunction with TM enqueues. Every transaction holding a DML lock also holds a TM enqueue lock. The basic locking functionality is provided by the enqueue, and the DML lock adds the maintenance of the conversion history.

The fixed array of DML lock structures is sized by the DML_LOCKS parameter. Its free list is protected by the dml lock allocation latch , and the active slots are visible in V$LOCKED_OBJECT . As with enqueue resources and locks, the number of slots in the DML locks fixed array is unimportant to performance, as long as you don't run out of free slots and get an ORA-55 error. Once again, V$RESOURCE_LIMIT can be used to adjust your setting for DML_LOCKS to ensure that this does not happen. Each slot only takes on the order of 116 bytes, so having a generous number of slots is not a problem.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

A form of enqueue locking is used to protect cached database blocks. For each buffer in the database buffer cache, there is a buffer header. The buffer headers constitute a fixed array in the permanent memory part of the shared pool. These buffer headers act as the resource structures for buffer locks. Sessions manipulate buffer headers, and thus buffers, via dynamically allocated structures known as buffer handles. The buffer handles act as the lock structures for buffer locks.

Buffer locks are taken only in shared and exclusive modes. The buffer headers implement a two-way linked list of the buffer handles for sessions that are using the buffer, and another for the buffer handles of sessions waiting for the buffer. Sessions waiting for a buffer lock report either buffer busy waits, or buffer busy due to global cache waits, or write complete waits. The parameters for buffer busy waits are shown in Table 4.6.

Table 4.6: Wait Parameters (buffer busy waits)
Parameter	Description
p1	The file number of the database block.
p2	The block number of the database block in its file.
p3	The reason for the wait. A or 1014 indicates that the buffer is locked exclusively by a session that is busy reading a block from disk into the buffer, and that the read has not yet completed. A reason of is used for consistent gets, whereas 1014 is used for current mode block gets. Any other number indicates that the buffer is locked exclusively for modification by another session.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Sort locks apply to the disk space being used for disk sort operations. There are two types of sort locks: temporary table locks and sort segment locks. These correspond to temporary segments in PERMANENT tablespaces and TEMPORARY tablespaces respectively. There are fixed arrays in the SGA for each type of sort lock. Both arrays are sized by the SESSIONS parameter, which allows for the maximum possible usage of sort locks.

Sort locks are used merely to track disk sort space usage, and do not suffer from lock conflicts, waits, or deadlocks. However, you should not confuse sort locks with the ST (space transaction) enqueue , which is extremely prone to lock conflicts, waits, and even deadlocks. Contention for the ST enqueue is often associated with disk sorts, because it is needed for the creation, extension, and deallocation of temporary segments.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This section contains a quick reference to the parameters, events, statistics, waits, and APT scripts mentioned in Chapter 4.

Parameter	Description
_DISTRIBUTED_LOCK_TIMEOUT	Timeout for assumed deadlocks on distributed transactions. Defaults to 60 seconds.
_ENQUEUE_HASH	The size of the enqueue hash table.
_ENQUEUE_HASH_CHAIN_LATCHES	The number of latches used for access to the enqueue hash table. Defaults to the CPU count.
_ENQUEUE_LOCKS	The number of enqueue lock structures.
DML_LOCKS	The size of the DML locks fixed array. Where possible, DML locking should be disabled to reduce locking overheads and the risk of blocking locks.
ENQUEUE_RESOURCES	The size of the enqueue resources array.
PARALLEL_TRANSACTION_RESOURCE_TIMEOUT	Timeout for assumed deadlocks between the branches of a parallel transaction and another transaction in release 8.0.
TEMPORARY_TABLE_LOCKS	This parameter is obsolete in Oracle8. It does still exist in release 8.0, but setting it has no effect.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Instance locks are used for inter-instance locking and communication between the instances of an Oracle parallel server database. Although instance locks are scarcely used in single-instance Oracle, I encourage all readers to browse this chapter anyway. Single-instance Oracle is really just a special case of parallel server, and there are some aspects of its operation that you will find difficult to grasp unless you understand the general case. If nothing else, reading this chapter will deepen your appreciation for the simplicity of single-instance Oracle.

The part of Oracle that manages instance locks is called the lock manager. The lock manager is a layer of functionality that affects the operation of all processes. However, its most obvious manifestations are the presence of a set of lock management processes, and an in-memory database of instance lock information in each instance.

The lock manager is said to be distributed. There is no central point of control. Each instance only maintains information about the instance locks in which it has an interest. The lock manager is also said to be integrated. This is because, prior to Oracle8, a separate product provided by the operating system vendors was required for lock management. In Oracle8, release 8.0, this functionality was incorporated into the Oracle kernel.

The lock and resource structures for instance locks reside in a dedicated area within the SGA called the instance lock database. The lock and resource arrays are dimensioned by the LM_LOCKS and LM_RESS parameters. A third parameter, LM_PROCS, dimensions the array of processes and instances that can own the locks. This array needs one slot for each local process and one slot for each remote instance.

The instance lock database also includes an array of process groups. In some cases, instance locks may be owned by a group of processes, rather than a single process. Group lock ownership allows Multi-Threaded Server sessions to migrate between shared server processes, and allows OCI transactions to be detached from one process and resumed by a different process. All lock acquisition requests can specify either process or group ownership. The group membership of processes is inferred and tracked automatically in the instance lock database. Exchanges of group-owned instance locks within the process group do not require any further lock acquisition or conversion. The size of the process groups array is set by the _LM_GROUPS parameter, which defaults to 20.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The part of Oracle that manages instance locks is called the lock manager. The lock manager is a layer of functionality that affects the operation of all processes. However, its most obvious manifestations are the presence of a set of lock management processes, and an in-memory database of instance lock information in each instance.

The lock manager is said to be distributed. There is no central point of control. Each instance only maintains information about the instance locks in which it has an interest. The lock manager is also said to be integrated. This is because, prior to Oracle8, a separate product provided by the operating system vendors was required for lock management. In Oracle8, release 8.0, this functionality was incorporated into the Oracle kernel.

The lock and resource structures for instance locks reside in a dedicated area within the SGA called the instance lock database. The lock and resource arrays are dimensioned by the LM_LOCKS and LM_RESS parameters. A third parameter, LM_PROCS, dimensions the array of processes and instances that can own the locks. This array needs one slot for each local process and one slot for each remote instance.

The instance lock database also includes an array of process groups. In some cases, instance locks may be owned by a group of processes, rather than a single process. Group lock ownership allows Multi-Threaded Server sessions

Table of Contents

Section 3.3.1.1: Why spin?