Page 793
NOTE |
Guidelines: This is where Tuning Locks overlaps with Tuning the Application. When coding, ensure (1) hold locks only when necessary, (2) hold locks at the lowest possible level, (3) keep transactions short as possible, (4) commit (or rollback) as frequently as possible, and (5) chop client/server transactions where possible. n |
TIP |
Create indexes on all your foreign keys to eliminate unnecessary parent-child locking. |
To monitor locking, run the $ORACLE_HOME/rdbms/admin/catblock.sql as SYS to create locking views. Then you may gather session wait information from the tables DBA_OBJECTS, DBA_WAITERS, DBA_BLOCKERS, V$SESSION, and V$LOCK. What you can also do is run the $ORACLE_HOME/rdbms/admin/utllockt.sql script to get most of this same information. Examine Count for Event Name = `enqueue' under System wide wait events. If any of these sources of information suggest that locking is unnecessarily high, apply the above guidelines. Use the ALTER SESSION KILL syntax to kill sessions if necessary. If deadlock occurs, a trace file is dumped. Examine it to determine which sessions and what types of locks caused the deadlock. Then eliminate unnecessary locking and reorder your processing if necessary.
If it appears you need to increase the number of locks to alleviate lock waits or because you have already hit the default ceiling, then you may need to increase ENQUEUE_RESOURCES. ENQUEUE_RESOURCES is a function of DML_LOCKS, DDL_LOCKS, other parameters, and platform specifics. You can ALTER TABLE DISABLE LOCKS to speed up certain guaranteed exclusive runs, but I wouldn't normally recommend it. Also, you can set DML_LOCKS = 0 to help speed up an overall instance, but this may have undesirable side effects. Oracle apparently still somehow manages concurrency without the use of heavyweight locks, but the mechanism may be subject to integrity problems; therefore, I again would not recommend this.
TIP |
Set DML_LOCKS = (the maximum number of concurrent users) x (the number of tables). You can also explicitly set ENQUEUE_RESOURCES, but this will reset upon resetting DML_LOCKS anyway. For example, if you have U users and T tables, you should set DML_LOCKS = (U x T), plus perhaps some additional percentage, such as 10 percent. |
Without getting too platform-specific, let's look briefly at some of the common operating system memory issues associated with tuning memory in Oracle. Platform specifics may be found in Appendix A, "Oracle on UNIX," and Appendix B, "Oracle on Windows NT." The major operating system integration memory issues are
Page 794
Shared memory is a mechanism used by virtually all RDBMS vendors, particularly on UNIX. Shared memory permits multithreading and memory sharing among processes. Oracle uses the latter approach with its SGA, by sharing it among all sessions for that instance. The MTS also depends heavily on this resource to simulate multithreading at the interprocess level.
CAUTION |
The Oracle SGA should fit comfortably well within the shared memory given to it by the operating system. Otherwise, you will have unnecessary paging and swapping, which can cripple a system. |
Semaphores are true locking mechanisms. Oracle uses them as the basis for enqueue resources, such as DML locks. Again, they are made up of a (global memory) gate and a queue, along with a set of queuing operations. Oracle's locking operations map to operating system low-level semaphore operations.
Interprocess communication refers to the native operating system communication protocols that allow processes to communicate. These may be implemented as sockets, streams, named pipes, or other mechanisms. Because Oracle is not fully multithreaded, it depends heavily on the operating system interprocess operations. Oracle's intersession communications are mapped to these low-level operations. When using SQL*Net, the IPC protocol refers to and uses the default operating system interprocess communication method.
Virtual memory is a special type of cache. It is an extension of real memory to the disk. The hit ratio of virtual memory can be calculated just as with any cache. Virtual memory must be sufficient to handle all the total memory needs of the operating system itself and all applications, including Oracle. When real memory is paged or swapped, it is sent to virtual memory (disk). The actual disk supporting virtual memory is called the backing store. Again, you don't want the Oracle SGA to be paged or swapped there, nor any of the major Oracle background processes to be swapped out.
Memory file systems are file systems held entirely in real or virtual memory. Sometimes these are called RAM disks (as with DOS) or temporary file systems (as with UNIX). In any case, they, of course, outperform ordinary disk file systems by some orders of magnitude. Because they can be at least partly held in memory and take advantage of virtual memory caching, at least some, if not most, of their operations are to and from memory. Oracle can sometimes use these as an exotic performance tuning solution. For example, you could create an Oracle temporary tablespace (or permanent tablespace) on an operating system memory file system. They could also be used, for example, to store lookup tables.l