Page 736
If at any stage of a database's life cycle the design comes into question, consider redesigning it if resources and management permit. Sometimes the best way to correct a database without extensive tuning efforts is a re-analysis and redesign. For example, consider redesign when there is no initial design, when there is a hurried design, or when multiple databases might need to be integrated.
A program will only run as fast as the algorithms of which it's composed. If some SQL code uses an inefficient search or sort routine, despite the best efforts of the Oracle's optimizer, the application will run slowly through no fault of the database itself. If you come into a job, and the code is already written but has been written poorly, consider the next step rather than trying to fix pieces at a time.
If application code efficiency comes into question, and if resources and management permit, re-analyze and rewrite code more efficiently. Be aware of the Oracle optimizer and Oracle SQL rewriting rules for more efficient code. A database application is no different than any ordinary piece of software. It can be written well or written poorly. After design issues, the application is the single largest tuning opportunity for return on investment. Without a doubt, time and effort should be spent here.
In Chapter 6, "The Oracle Database Architecture," you saw that the SGA houses all the tunable, shared memory structures. When deemed necessary, Oracle can offer substantial improvements through the tuning of its database buffer cache. This cache buffers all database blocks that are accessed by Oracle, on the way to it (read) or from it (write). Also, the shared pool caches SQL code via the library cache component, and caches the data dictionary through the aptly named data dictionary cache. Further rollback is cached via the aforementioned database buffer cache, although it's not separately tunable. But the redo log buffer is a separately defined, tunable area in the SGA. Sufficiently sizing all of these memory structures reduces contention and combats the electromechanical disadvantage.
In Windows NT, there isn't as much to tune by way of memory usage as what must be tuned in larger, multiuser OSs such as VMS and UNIX. In UNIX, for example, the named Oracle user (usually "oracle") must typically have unlimited shell process limits. This must be done manually or within a login script. The UNIX swap area functions as an area for OS temporary storage, user temporary storage, and the OS virtual memory backing store. Hence, in UNIX, the swap area can be a bottleneck, especially on a database server.
Page 737
In Oracle on UNIX, the SA and DBA must work together so that UNIX provides enough shared memory and semaphores to give the Oracle processes enough breathing room to operate efficiently. See Appendix A, "Oracle on UNIX," and Appendix B, "Oracle on Windows NT," for further discussion of these vital topics.
Database I/O is, of course, affected by both RDBMS and OS memory structures. But what step 8 means is tuning the I/O by relocating database logical and physical structures to reduce contention. Of course, a DBA will do both, especially if he or she follows these steps in proper order.
For example, if you reach this point in tuning, you will have already tuned the database buffer cache. Now you simply continue by essentially refocusing on the physical design. That is, you physically do more redesigning, if necessary, with database I/O exclusively in mind. Your physical redesign will be far more specific than that done previously. You want to improve I/O time estimates in the direction desired, such as reducing the wall time of applications with heavy inserts.
The OS fulfills all read and write requests by all processes, including Oracle background processes such as DBWR and LGWR. An OS typically buffers these requests, performs the reads or writes, and then returns the acknowledgment and data back to the process upon completion. File systems are data structures that contain metadata about the files they manage, such as the location of each file's starting sector address, its sector length, its directory tree location, its attributes (such as permissions, size, timestamps, and so on), and other information.
In UNIX, file systems also have their own logical block sizes, which correspond to something greater than or equal to a physical block size (512 bytes), usually 8KB by default. The Oracle block size should be at least 8KB or a multiple of it, such as 16KB.
Other important OS and Oracle I/O tuning issues include read-ahead capabilities, asynchronous I/O, multiblock reads, RAID stripe sizes, disk geometry issues, controller issues, and much more. If the DBA is not also the SA, which is more often the case than not, he or she must work cooperatively with the SA to appropriately integrate Oracle and OS I/O parameters. We'll discuss as many of these as possible in Chapter 32.
As discussed in the "Understanding Why You Tune" section, a saturated network can cancel out improvements made by database tuning. A DBA must ensure, through proactive means such as the use of network monitoring software if necessary, that his or her client/server system's overall application performance is not suffering unduly because of network load or other network ailments. Network administrators, like SAs, must work closely with DBAs to resolve these issues.
Page 738
Local Area Network (LAN) administrators and general-purpose network administrators (including their WAN responsibilities) typically are responsible for the hardware and software of networked clients, or PCs. These administrators, along with the DBAs, must correctly size and configure the clients, upgrading if necessary, to enable client/server database applications to function within acceptable performance criteria. For example, when Oracle has been tuned and can service a query (once received across a network from a client) in subsecond time, it makes no difference if the network delay is on the order of several seconds from client to server, and vice versa.
"Exotic solutions" include Oracle's MultiThreaded Server (MTS), transaction processing (TP) monitors, Oracle's Parallel Query and other parallel capabilities, Oracle's clustering capability, Oracle's bitmapped indexing, MPP machines, solid state disks, memory-resident (RAM) disks, hardware accelerators, and queuing systems.
Let's briefly cover some of these solutions now, because they won't be covered in later chapters.
Oracle's MultiThreaded Server (MTS) As discussed in Chapter 3, Oracle's MTS is a pseudo-multithreaded solution to help counter a high number of concurrent users or programs accessing a single database. MTS is essentially a low-cost form of TP monitor whose goal is to allow for a high number of concurrent users and to service them all, thereby maximizing throughput (not response time). However, MTS moves as much Process Global Area (PGA) memory into the SGA as possible, thereby sharing more memory and reducing the overhead (its state information) in maintaining each individual process.
Transaction Processing (TP) Monitors In contrast, a TP monitor goes one step further by not maintaining the full process state information and persistent connections, as is done by an ordinary OS or RDBMS, including MTS. Overhead per process is further reduced. Typical concurrency thresholds for OSs and RDBMSs are around 200 or so users. This means that after most operating and database systems reach this number, their performance drops precipitously. This is known as thrashing and is symptomatic of too high a degree of multiprogramming or too many users.
TP monitors can help by permitting 1,000 or so concurrent users where only a few hundred were possible before. This is why TP monitors typically sit in front of an RDBMS. They are said to live in the middle tier between the back end (the RDBMS) and the front end (the client, or PC). Two major commercial offerings at the time of this writing are Novell/BEA's Tuxedo and IBM/TransArc's Encina.
Oracle's Parallel Query Option (PQO) As mentioned briefly in Chapter 3, Oracle's Parallel Query (PQ) can speed up queries by the divide-and-conquer principle. However, Oracle also permits parallel index creation, parallel SQL*Loader loads, and parallel import/exports. All of these capabilities cut actual wall time by several factors, which is always a boon to DBA work.