Tài liệu ORACLE8i- P22 docx

836 CHAPTER 17 • MONITORING AND TUNING LATCHES, LOCKS, AND WAITS

If your system administrator manages these systems, you may not be privy to the

mapping of physical disks. Perhaps, in part, the administrator doesn’t understand the

importance of such mapping or doesn’t know how to create such a map. Either way,

it’s imperative that you make clear to your system administrator that you need to

know where this data resides, and with what other systems it interacts.

Correcting I/O problems can make a huge difference in performance. This action

alone can transform you into a hero. Trust me. I’ve been there.

How’s the Shared Pool Doing?

After tuning I/O, you may still be seeing a good deal of latch contention. Now’s the

time to look at the shared pool and take advantage of the scripts provided in Chap￾ter 15. A poorly tuned shared pool can cause all sorts of latch issues. Run the various

scripts that monitor the hit ratios of the shared pool. Are your hit ratios low? If so,

you must add memory.

WARNING Any time you add memory to any Oracle memory structure such as the

shared pool, make sure you don’t add so much that you cause the system to start thrash￾ing memory pages between the swap disks and memory. This will inevitably result in per￾formance problems. You’ll only wind up worse off than before.

If the hit ratios are not low, and you see thrashing, make sure you have not over￾allocated memory to the point that you are paging or excessively swapping memory

to and from the disk. If you are, you must correct this problem immediately. You’ll

need to enlist the help of your system administrator to determine if you’re having

system memory contention issues.

In keeping with the theme of reducing I/O is the idea that the fewer numbers of

blocks you have to deal with, the less work the database will have to do, and latch con￾tention will be minimized. Do everything you can to reduce I/Os during queries. Make

sure your tables are allocating block storage correctly (look at PCTUSED/PCTFREE).

Making sure tables load in order of the primary key (or most often used) index columns,

and tuning your SQL to return the result set in the fewest number of block I/Os (logical

or physical) will result in a reduction in latch contention—-we guarantee it.

Finally, don’t just throw CPUs, disks, and memory at the problem: that’s the wrong

kind of lazy solution and often doing so prolongs the problem. As your database sys￾tem grows, even the faster hardware will not be able to handle the load. The only

exception to this rule is if you simply do not have enough disk space to properly dis￾tribute the I/O of the database. If this is the case then you simply have to buy more

disk pronto.

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com

837

NOTE In some cases, lack of memory in the shared pool or the database buffer cache

actually is the problem. This can be true if you see low hit ratios in any of the memory

areas.

Fragmentation of the shared pool can also be a problem. Consider changing the

shared_pool_reserved_size

parameter, which is associated with the parameter

_shared_pool_reserved_min_alloc

These parameters affect the location in the shared pool for storing PL/SQL code. If the

code is of a size greater than _SHARED_POOL_RESERVED_MIN_ALLOC, it will be

stored in an area of the shared pool set aside by the parameter SHARED_POOL_

RESERVED_SIZE. If there isn’t enough memory available for that chunk to be stored

in reserved memory, it will be stored in the normal memory area of the shared pool.

You can positively affect shared pool fragmentation by increasing the

SHARED_POOL_RESERVED_MIN_ALLOC parameter so that your largest PL/SQL pro￾grams are loaded there. This approach will eliminate fragmentation issues.

Another method that can be used to limit fragmentation of the shared pool is the

use of the DBMS_SHARED_POOL.KEEP procedure to pin often used PL/SQL objects in

the shared pool. (See Chapter 20 for more on DBMS_SHARED_POOL.) You might con￾sider pinning commonly used objects in the SGA every time the database starts up.

Doing so will help improve performance, and will go a long way toward reducing per￾formance problems.

Tune Up Your SQL

If everything looks hunky-dory with the shared pool then make sure you are using re￾usable SQL statements with bind variables as much as possible. If you aren’t, you can

cause all sorts of problems, including latching contention. See Chapter 16 for more

information on how to write reusable SQL and how to determine if SQL needs to be

rewritten. You may also want to take advantage of cursor sharing in Oracle8i, which is

also discussed in Chapter 16.

General Tuning for Latch Contention

Because there are various levels of latches, contention for one latch can cause contention

against other, lower-level latches. A perfect example is the attempt to acquire a redo

copy latch to quickly allocate memory in the redo log buffer. Depending on the size

of the redo to be written, Oracle will often opt to use one of the several redo allocation

TUNING LOCKS, LATCHES, AND WAITS

Beyond Simple

Database Management

PART

III

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com

838 CHAPTER 17 • MONITORING AND TUNING LATCHES, LOCKS, AND WAITS

latches rather than use the one redo copy latch. Having acquired the redo allocation

latch, Oracle will then quickly try to acquire the level-six redo copy latch. Oracle

needs this latch only long enough to allocate space in the redo log buffer for the

entries it needs to write; then it releases the latch for other processes to use. Unfortu￾nately, a delay in getting the redo copy latch can keep other processes from acquiring

the available redo allocation latches. The bottom line is that you must always deal with

latch contention level by level, tuning from the highest level (15) to the lowest (0).

Consider increasing the _SPIN_COUNT parameter if you are seeing excessive sleeps

on a latch. On many systems it defaults to 2000, but yours might be different. If

you’re seeing problems with redo copy latches or other latch sleeps, see what you can

do by playing with this parameter.

You can use the ALTER SYSTEM command to reset the spin count as well, which

means you don’t have to shut down the database. Here’s the syntax for this:

ALTER SYSTEM SET “_SPIN_COUNT” = 4000;

After you have reset the spin count, let the system run normally for a few minutes

and then check to see if the number of spins has dropped. Also, has there been any

change in the number of sleeps? (Note that sleeps for the redo copy latch are not

unusual.)

WARNING Remember that hidden or undocumented parameters are not sup￾ported by Oracle in most cases. That includes _SPIN_COUNT (though it was a docu￾mented parameter until Oracle8). With this in mind, test all hidden parameters before

you decide to use them in production, and find out about any bugs by checking Oracle’s

registered information.

TIP In conjunction with your latch contention tuning, keep in mind that tuning bad SQL

statements can have a huge impact on latching overall. So by all means tune the instance

as best you can—but often your best results will come from SQL tuning.

Tuning Redo Copy Latch Problems

Oracle’s multiple redo copy latches are designed to relieve the hard-pressed single

redo allocation latch. When using the redo copy latch, Oracle acquires the redo allo￾cation latch only long enough to get memory in the redo log buffer allocated. Once

that operation is complete, it releases the redo allocation latch and writes the redo log

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com

839

entry through the redo copy latch. Note that sleeps for the redo copy latches are nor￾mal and unique to this latch.

The process will sleep if it fails to acquire one of the redo copy latches. When it

wakes up, it tries to acquire the next redo copy latch in order, trying one at a time

until it is successful. Oracle executes a sleep operation between each acquisition

attempt, so you see the increases in the SLEEP columns of the V$LOCK data dictio￾nary view. That being the case, if you get multiple processes fighting for this latch,

you are going to get contention. You can do a couple of things to try to correct this

problem.

Increase the number of redo copy latches by increasing the default value of the

parameters LOG_SIMULTANEOUS_COPIES and LOG_ENTRY_PREBUILD_THRESHOLD.

Check your operating system documentation for restrictions on increasing these

values.

Tuning Redo Allocation Latch Problems

Oracle’s lone redo allocation latch serializes access to the redo log buffer, allocating

space to it for the server processes. Sometimes this latch is held for the entire period

of the redo write, and sometimes just long enough for the allocation of memory in

the redo log buffer. The parameter LOG_SMALL_ENTRY_MAX_SIZE sets a threshold

for whether the redo allocation latch will be acquired for the duration of the redo log

buffer write. If the size of the redo is smaller (in bytes) than LOG_SMALL_ENTRY_

MAX_SIZE, the redo allocation latch will be used. If the redo is larger, a redo copy

latch will be used. So if you see latch contention in the form of sleeps or spins on the

redo allocation latch, consider reducing LOG_SMALL_ENTRY_MAX_SIZE.

NOTE There is a school of opinion for setting LOG_SMALL_ENTRY_MAX_SIZE to 0 and

always using the redo copy latches. We contend that things Oracle are rarely so black and

white. Always test a setting like this, and always be willing to accept that something else

will work better.

Other Shared Pool Latching Problems

Latching issues in the shared pool are usually caused by insufficient memory alloca￾tion. As far as database parameters go, there isn’t a lot to tune with respect to the

shared pool beyond memory. Of course, maintain your typical vigilance over I/O dis￾tribution and bad SQL.

TUNING LOCKS, LATCHES, AND WAITS

Beyond Simple

Database Management

PART

III

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com