TRUE, FALSE and NULL

24 May 1999

Nevertheless, in practice we have three truth-values (TRUE, FALSE and NULL) in Oracle and this is enough to implement any value system you like. If you want 2-valued logic, you can just make the columns not nullable. If you want 3-valued logic without NULLs, you just use default clauses on the columns to ensure that absent "values" are assigned a special constant value outside the domain of the field. If you want 4-valued logic or higher, you can define multiple such special constants, or allow NULLs and just add a metadata column to distinguish between the meanings of NULL.

Of course, from a performance point of view this really starts to get interesting when you consider the treatment of NULLs in indexes ... but that's another topic.

Copying a table

8 October 1999

If the table is prone to index range scans, it is worth taking the trouble to ensure that the rows are ordered by that index key. In recent releases, you can include an ORDER BY clause in the query. In older releases, you need to use an INDEX hint, which inhibits parallelism. You can then use the NOSORT option when creating that index, but more importantly, the index will have an optimal clustering factor, and that will give your index range scans a big boost.

Sequence of statements

2 November 1999

ASTs

5 November 1999

MTU size

19 November 1999

Sequence performance

30 November 1999

Performance of objects

22 December 1999

oerr on NT

24 December 1999

Releasable hashed locking

27 December 1999

Creating an ordered table

29 December 1999

listener.log

20 January 2000

mv listener.log listener.log.prev
lsnrctl stop
lsnrctl start

Update mechanism

20 January 2000

State object free lists

25 January 2000

One of the members of each of these structures is a pointer to the next free slot in the array. If a process, session or transaction state object is free, it has a valid pointer to another free state object in the same array (unless it is at the end of the free list). These pointers implement a free list though the state object arrays. They are modified under the protection of the process allocation, session allocation and transaction allocation latches respectively. State objects are always added to and removed from the heads of their free lists.

The transaction state object free list is a totally different thing to transaction freelists. Transaction freelists are linked lists of data blocks in a particular segment. When a transaction frees space in a data block so that the space used falls below PCTUSED, then that block is linked to a transaction freelist.

Sequence problem

29 January 2000

alter sequence your_seq increment by &the_difference;
select your_seq.next_val from dual;
alter sequence your_seq increment by 1;

Best basic tuning book

1 February 2000

By the way, I don't regard my book as a basic tuning book. It is an advanced internals book, and has very little to say about basic tuning.

oiconnect

5 February 2000

USERENV

5 February 2000

LABEL		(in Trusted Oracle only)
USERMODE	(boring)
COMMITSCN	(see Jonathan Lewis' article on this)
SCHEMAID	(equivalent to V$SESSION.SCHEMA#)
CLIENT_INFO	(documented in 8.1)

DSI seminars

14 February 2000

As to whether you should go, depends on whether you want/need a really deep understanding of Oracle. Probably only 1% of DBAs need to know this stuff.

Encrypted passwords

16 March 2000

Here documents in shell scripts

19 March 2000

if $var=1; then
  sqlplus -s apps/$APPS_PASS <<EOF1
  -- sql statements
  EOF1
fi

((var == 1)) &&
print '
-- sql statements
' |
sqlplus -s apps/$APPS_PASS

SP2-0556: Invalid file name

29 March 2000

Oracle processes

18 April 2000

Dedicated Servers

Each of these processes is dedicated to serving a single client process. They parse and execute its SQL statements, read database blocks from the data files, make changes to buffered database blocks, and return results to the client process. Dedicated server processes are also called shadow processes, particularly when their client process is running on the same computer.

Shared server processes are used in MTS configurations. They perform the same tasks as dedicated server processes, however, they are not associated with any particular client process. Instead they service requests from the call request queue and return results to a result queue.

Dispatcher processes mediate between MTS client processes and the shared servers. Incoming calls are placed in the request queue for processing by a shared server, and outgoing responses are taken from the response queue and transmitted to the clients. Each dispatcher process can host multiple connections if they share the same network transport protocol.

The job queue processes perform scheduled tasks such as executing jobs scheduled with the DBMS_JOB package, refreshing local snapshots, and propagating advanced queuing messages and replicated transactions to other databases. Up to 36 of these processes may be configured.

Up to 10 of these processes may be used by the Advanced Queuing facility for message delay, expiration and retry, and to collect interval statistics.

This process is also associated with Advanced Queuing. It is responsible for the asynchronous notification of subscribers that have registered a callback request for particular messages.

An instance may have up to 256 parallel execution slaves. These slaves are used for parallel SQL execution, parallel recovery, and parallel propagation of replicated transactions. When one of these operations is performed in parallel, the driving dedicated server, shared server or job queue process acts as the coordinator for a set of parallel execution slaves.

Whenever a change is made to the database and whenever a transaction ends, the low-level database change operations and transaction markers are recorded in the log buffer. The LGWR process writes this information from the log buffer to disk in order to safeguard recovery.

In ARCHIVELOG mode, used log files must be archived to another location before they can be reused. The ARCn processes are responsible for automatic archiving. Up to 10 archive processes may be used.

When a change is made to the database, modified database blocks are seldom written immediately to disk. Instead they are retained in the database buffer cache and written to disk in the background by the DBWn processes. Up to 10 database writer processes may be used.

In a checkpoint the DBWn processes write out all database blocks that were modified before the checkpoint SCN. This is intended to limit the redo that must be applied in the event of instance recovery. For all checkpoints other than incremental checkpoints, the last step in checkpoint processing is to update the checkpoint SCN in headers of all affected data files. This task is performed by the CKPT process.

When necessary, the system monitor process performs crash recovery in single instance Oracle and instance recovery in parallel server configurations. SMON is also responsible for a range of space management housekeeping functions such as shrinking rollback segments that have extended beyond their optimal size, coalescing contiguous free extents in the free extent table of the data dictionary, cleaning up obsolete temporary segments, and allocating space in sort segments.

Oracle server processes are sometimes killed from the operating system, or die unexpectedly due to unrecoverable errors. The instance is aborted if any of the essential processes dies or is killed. Otherwise, the PMON process frees database resources that were being used by the dead process, rolls back any uncommitted transactions, and restarts the process if possible.

Oracle automatically uses a two-phase commit mechanism for distributed transactions to ensure that all the transaction branches either commit or roll back together. Local transaction branches are said to be in-doubt between the two phases of a two-phase commit. If the second commit phase cannot be completed due a network or instance failure, then it is uncertain as to whether the transaction is to be committed or rolled back. In this case RECO waits until the connection can be restored, and then automatically complete the transaction.

On operating systems that do not support asynchronous I/O, each process can only perform one I/O operation at a time. This constraint is untenable for processes that require high I/O throughput. Oracle allows multiple I/O slaves to be configured to achieve parallelism in such cases.

In parallel server environments Oracle synchronizes access to resources that are shared between instances using instance locks. Each instance manages a subset of the instance locks. The LMDn processes service requests for locally managed locks directly, and forward requests for remotely managed locks to the remote node. The LMDn processes are also responsible for the detection of distributed deadlocks. Multiple lock manager processes can be configured for each instance, but one is normally sufficient because the messages are passed asynchronously.

The instance locks that protect globally cached resources are held by the lock processes on behalf of their instance. Other instance locks are held directly by the processes with an interest in the resource. Up to 10 lock processes may be configured, but one is normally sufficient, as these processes also operate asynchronously. If there are multiple lock processes, then the responsibility for PCM locks is evenly distributed between them.

The lock monitor process handles the redistribution of instance locks as instances are started up and shut down, and the recovery of instance lock information prior to instance recovery. LMON also cooperates with PMON in the recovery of dead processes holding instance locks, and has a role in the establishment of communication paths with remote parallel execution slaves.

If a process requires a consistent read image of a buffer for which its instance does not have the require instance lock, then from release 8.1 onwards it requests that buffer from the instance holding the blocking instance lock. A buffer server processes in that instance receives the request, constructs the required CR buffer, and sends it back to the requesting process directly over the interconnect. Up to 10 of buffer server processes may be started dynamically in each instance depending on the load.

When an Oracle process sleeps, waiting for an event, the sleep ends either when the process is posted, indicating that the event has occurred, or when the sleep times out. The alarm() system call is used to set the time out. However, prior to release 8.1, if the wakeup monitor process was enabled, then most sleeps would never time out. Instead, the sleeping process was posted by WMON.

The wakeup monitor can be enabled by setting the reduce_alarm parameter to TRUE. The interval at which it checks for sleeping processes to wakeup is set by the _wakeup_timeout parameter. This parameter is set in centiseconds and defaults to 100 centiseconds, that is one second. This information is included here merely for completeness. Do not attempt to enable the wakeup monitor in a busy production environment. The effect on performance would almost certainly be disastrous.

TRWR is the most obscure Oracle process. It writes out trace files from an internal tracing facility that is seldom used. This facility is controlled by a set of hidden parameters, and by the ALTER TRACING command. Trace messages are stored in a circular buffer in memory, and written to disk asynchronously by TRWR.