Previous | Table of Contents | Next
Page 40
example is to split a long-running query to find a sum into two pieces, run them
on separate CPUs, and then add their subtotals to get the final result. This is, in fact,
what Oracle's Parallel Query capability provides.
- Preallocate and
precompile. Static allocation and fixed allocation mean the same thing
as preallocation. In other words, allocate your resources ahead of time, rather than let
the software do it for you on-the-fly, or dynamically. This typically results in
additional computational and I/O overhead, which is nearly always undesirable.
Precompiled programs will save substantial time over interpreted programs. DBMS caching handles
a lot, but the DBA should be on the lookout for what he or she can also do as a
supplement. For example, write generic, reusable procedures and pin them in
memory. Oracle's KEEP operation does the latter. The KEEP operation is discussed further
in Chapter 31, "Tuning Memory."
- Be
proactive. Anticipate the major problems. Follow the Pareto rule: fix the 20 percent
of the problems that might cause 80 percent of the trouble. This is also referred to as
the 20/80 rule. A statistical way of saying this is that
performance problems aren't uniformly distributed among
causes. We want to try to predict the most major problems and
either design them out of the system or at least compensate and design around them.
Consider a large batch system that runs only a few major programs serially, each one
inserting, updating, or deleting large amounts of data. The potential problem here is with
the transaction log, especially the undo log, growing extremely large and perhaps
running out of room. With Oracle, the undo log is the set of available
rollback segments. As a DBA, the proper design would to be have at least one very large, non-SYSTEM
rollback segment capable of handling the maximum amount of undo data generated.
- Bulk, block, and
batch. Use mass transit. Batch things together that make sense to
be batched. What this means is that for things such as disk and network I/O, often the
best route to take is mass transitgroup together I/O operations with the same origins
and destinations. This works most often for DSS and batch systems. For example, users
may frequently select a large number of rows from an extremely large table. Since
these selects return so many rows, they never use any available indexing. If this is the
case, and without the benefit of any parallelizing hardware or software, the table should
exist contiguously on a single, large disk. Then we increase the database logical buffer
sizes, and read many physical data blocks at once. In Oracle, for example, we would
set DB_BLOCK_SIZE to the highest amount for our platform; for example, 8K on a
Windows NT machine. We're reading as many blocks as we can with one read request.
This all stems from the simple fact that in most all-electromechanical systems, startup
costs are expensive. An analogous network situation is to send as much in one packet
as possible, because this is more cost effective.
Page 41
- Segment the application
appropriately. This could be considered a subheading
under divide and conquer. But it is somewhat different in that what we are emphasizing is
the entire environment and the application, not just the database behind it (the back
end). Consider the relative performance capabilities of the client, the network, and the
server before distributing the application. Put functionality where functionality is
performed logically. For example, display and presentation duties clearly belong to the client in
an interactive application, whereas database events that act on database objects should
be handled at the database (by the DBMS) and not by some front-end piece of software.
In Oracle, use triggers and stored procedures for this design.
The major objective of physical design and tuning is to eliminate, or at least minimize,
con-tention. Contention is when two or more pieces of software compete for the same
resource. Common sense only tells us that something has to wait. To combat this, practice the
following layout recommendations:
- Separate tables and indexes.
- Place large tables and indexes on disks of their own.
- Place frequently joined tables on either separate disks (or cluster them).
- Place infrequently joined tables on the same disks if necessary (that is, if you're short
on disks).
- Separate DBMS software from tables/indexes.
- Separate the data dictionary from tables/indexes.
- Separate the undo (rollback) logs and redo logs onto their own disks if possible.
- Use RAID 1 (mirroring) for undo or redo logs.
- Use RAID 3 or 5 (striping with parity) for table data.
- Use RAID 0 (striping without parity) for indexes.
As we discussed regarding RAID, manual striping can suffice in certain cases if RAID is
not available. However, in general it is not as flexible, safe, or fast. Also, when we say to
separate something, we mean put them on separate disks, but it can further mean to put them on
separate disk controllers. The more controllers, the more ideal the performance and safety.l
Page 42
Previous | Table of Contents | Next
|