shareding分表oracle,shared_pool_size过大到底有什么坏处？

WHAT ARE LATCHES AND WHAT CAUSES LATCH  CONTENTION

The Oracle RDBMS makes use of different types of locking mechanisms.

They are mainly latches, enqueues, distributed locks and global locks

(used in parallel instance implementations).

This bulletin focuses on latches. It attempts to give a clear understanding

of how latches are implemented in the Oracle RDBMS and what causes latch

contention. The information provided can be used in tuning the various

kinds of latches discussed.

1. What is a latch?

Latches are low level serialization mechanisms used to protect shared

data structures in the SGA. The implementation of latches is operating

system dependent, particularly in regard to whether a process will wait

for a latch and for how long.

A latch is a type  of a lock that can be very quickly acquired and freed.

Latches are typically used to prevent more than one process from

executing the same  piece of  code at  a given time. Associated with each

latch is a cleanup procedure that will be called if a process  dies while

holding  the latch.  Latches  have an  associated level  that  is used to

prevent deadlocks.  Once a process acquires a latch at a certain level it

cannot subsequently acquire a latch at a  level that is equal to  or less

than that level (unless it acquires it nowait).

2. Latches vs Enqueues

Enqueues are another type of locking mechanism used in Oracle.

An enqueue is a more sophisticated mechanism which permits several concurrent

processes to have varying degree of sharing of "known" resources. Any object

which can be concurrently used, can be protected with enqueues. A good example

is of locks on tables. We allow varying levels of sharing on tables e.g.

two processes can lock a table in share mode or in share update mode etc.

One difference is that the enqueue is obtained using an OS specific

locking mechanism. An enqueue allows the user to store a value in the lock,

i.e the mode in which we are requesting it. The OS lock manager keeps track

of the resources locked. If a process cannot be granted the lock because it

is incompatible with the mode requested and the lock is requested with wait,

the OS puts the requesting process on a wait queue which is serviced in FIFO.

Another difference between latches and enqueues is that

in latches there is no ordered queue of waiters like in enqueues. Latch

waiters may either use timers to wakeup and retry or spin (only in

multiprocessors). Since all waiters are concurrently retrying (depending on

the scheduler), anyone might get the latch and conceivably the first one to

try might be the last one to get.

3. When do we need to obtain a latch?

A process acquires a latch when working with a structure in the SGA

(System Global Area). It continues to hold the latch for the period

of time it works with the structure. The latch is dropped when the

process is finished with the structure. Each latch protects a different

set of data, identified by the name of the latch.

Oracle uses atomic instructions like "test and set" for operating on latches.

Processes waiting to execute a part of code for which a latch has

already been obtained by some other process will wait until the

latch is released. Examples are redo allocation latches, copy

latches, archive control latch etc. The basic idea is to block concurrent

access to shared data structures. Since the instructions to

set and free latches are atomic, the OS guarantees that only one process gets

it. Since it is only one instruction, it is quite fast. Latches are held

for short periods of time and provide a mechanism for cleanup in case

a holder dies abnormally while holding it. This cleaning is done using

the services of PMON.

4. Latches request modes?

Latches request can be made in two modes: "willing-to-wait" or "no wait". Normally,

latches will be requested in "willing-to-wait" mode. A request in "willing-to-wait"  mode

will loop, wait, and request again until the latch is obtained.  In "no wait" mode the process

request the latch. If one is not available, instead of waiting, another one is requested. Only

when all fail does the server process have to wait.

Examples of "willing-to-wait" latches are: shared pool and library cache latches

A example of "no wait" latches is the redo copy latch.

5. What causes latch contention?

If a required latch is busy, the process requesting it spins, tries again

and if still not available, spins again. The loop is repeated up to a maximum

number of times determined by the initialization parameter _SPIN_COUNT.

If after this entire loop, the latch is still not available, the process must yield

the CPU and go to sleep. Initially is sleeps for one centisecond. This time is

doubled in every subsequent sleep.

This causes a slowdown to occur and results in additional CPU usage,

until a latch is available. The CPU usage is a consequence of the

"spinning" of the process. "Spinning" means that the process continues to

look for the availability of the latch after certain intervals of time,

during which it sleeps.

6. How to identify contention for internal latches?

Relevant data dictionary views to query

---------------------------------------------

V$LATCH

V$LATCHHOLDER

V$LATCHNAME

Each row in the V$LATCH table contains statistics for a different type

of latch. The columns of the table reflect activity for different types

of latch requests. The distinction between these types of requests is

whether the requesting process continues to request a latch if it

is unavailable:

willing-to-wait        If the latch requested with a willing-to-wait

request is not available, the requesting process

waits a short time and requests the latch again.

The process continues waiting and requesting until

the latch is available.

no wait                    If the latch requested with an immediate request is

not available, the requesting process does not

wait, but continues processing.

V$LATCHNAME key information:

---------------------------------------

GETS                      Number of successful willing-to-wait requests for

a latch.

MISSES                  Number of times an initial willing-to-wait request

was unsuccessful.

SLEEPS                  Number of times a process waited a requested a latch

after an initial wiling-to-wait request.

IMMEDIATE_GETS              Number of successful immediate requests for each latch.

IMMEDIATE_MISSES          Number of unsuccessful immediate requests for each latch.

Calculating latch hit ratio

----------------------------

To get the Hit ratio for  latches apply the following formula:

"willing-to-wait" Hit Ratio=(GETS-MISSES)/GETS

"no wait" Hit Ratio=(IMMEDIATE_GETS-IMMEDIATE_MISSES)/IMMEDIATE_GETS

This number should be close to 1. If not, tune according to the latch name

7. Useful SQL scripts to get latch information

/*

** Display System-wide latch statistics.

*/

column name format A32 truncate heading "LATCH NAME"

column pid heading "HOLDER PID"

select c.name,a.addr,a.gets,a.misses,a.sleeps,

a.immediate_gets,a.immediate_misses,b.pid

from v$latch a, v$latchholder b, v$latchname c

where a.addr = b.laddr(+)

and a.latch# = c.latch#

order by a.latch#;

/*

** Given a latch address, find out the latch name.

*/

column name format a64 heading 'Name'

select name from v$latchname a, v$latch b

where b.addr = '&addr'

and b.latch#=a.latch#;

/*

** Display latch statistics by latch name.

*/

column name format a32 heading 'LATCH NAME'

column pid heading 'HOLDER PID'

select c.name,a.addr,a.gets,a.misses,a.sleeps,

a.immediate_gets,a.immediate_misses,b.pid

from v$latch a, v$latchholder b, v$latchname c

where a.addr   = b.laddr(+) and a.latch# = c.latch#

and c.name like '&latch_name%' order by a.latch#;

8. List of all the latches

Oracle versions might differ in the latch# assigned to the existing latches.

The following query will help you to identify all latches and the number assigned.

column name format a40 heading 'LATCH NAME'

select latch#, name from v$latchname;

The following output is for Oracle Release 8.1.7:

LATCH#     NAME

---------- ------------------

0 latch wait list

1 process allocation

2 session allocation

3 session switching

4 process group creation

5 session idle bit

6 longop free list

7 cached attr list

8 GDS latch

9 shared java pool

10 event group latch

11 messages

12 enqueues

13 enqueue hash chains

14 trace latch

15 KSFQ

16 X$KSFQP

17 i/o slave adaptor

18 ksfv messages

19 msg queue latch

20 done queue latch

21 session queue latch

22 direct msg latch

23 vecio buf des

24 ksfv subheap

25 resmgr group change latch

26 channel handle pool latch

27 channel operations parent latch

28 message pool operations parent latch

29 first spare latch

30 second spare latch

31 dlm process table freelist

32 process parent latch

33 dlm process hash list

34 dlm resource table freelist

35 dlm resource hash list

36 dlm resource scan list

37 dlm lock table freelist

38 dlm cr info freelist latch

39 dlm timeout list

40 dlm deadlock list

41 dlm statistic table latch

42 dlm synchronous data latch

43 dlm shared communication latch

44 dlm recovery domain table latch

45 dlm recovery domain record latch

46 dlm domain table latch

47 dlm domain lock latch

48 dlm domain lock table latch

49 dlm group table freelist

50 dlm group lock latch

51 dlm group lock table latch

52 name-service entry

53 name-service request queue

54 name-service pending queue

55 name-service namespace bucket

56 name-service memory objects

57 name-service request

58 dlm cr request queue latch

59 file number translation table

60 mostly latch-free SCN

61 batching SCNs

62 cache buffers lru chain

63 multiple dbwriter suspend

64 active checkpoint queue latch

65 checkpoint queue latch

66 cache buffers chains

67 cache buffer handles

68 multiblock read objects

69 cache protection latch

70 large memory latch

71 simulator lru latch

72 simulator hash latch

73 archive control

74 archive process latch

75 redo allocation

76 redo copy

77 redo writing

78 KCL instance latch

79 KCL lock element parent latch

80 KCL name table latch

81 KCL freelist latch

82 loader state object freelist

83 begin backup scn array

84 dml lock allocation

85 list of block allocation

86 transaction allocation

87 transaction branch allocation

88 sort extent pool

89 undo global data

90 ktm global data

91 parallel txn reco latch

92 intra txn parallel recovery

93 sequence cache

94 temp lob duration state obj allocation

95 row cache objects

96 dictionary lookup

97 cost function

98 user lock

99 comparison bit cache

100 instance information

101 policy information

102 global tx free list

103 global transaction

104 global tx hash mapping

105 shared pool

106 library cache

107 library cache load lock

108 Token Manager

109 Direct I/O Adaptor

110 cas latch

111 resmgr:runnable lists

112 resmgr:actses change state

113 resmgr:actses change group

114 resmgr:session queuing

115 resmgr:actses active list

116 resmgr:schema config

117 resmgr:gang list

118 resmgr:queued list

119 resmgr:running actses count

120 resmgr:method mem alloc latch

121 resmgr

lan CPU method

122 resmgr:resource group CPU method

123 dispatcher configuration

124 session timer

125 parameter list

126 presentation list

127 address list

128 end-point list

129 virtual circuit buffers

130 virtual circuit queues

131 virtual circuits

132 ncodef allocation latch

133 NLS data objects

134 JOX SGA heap latch

135 job_queue_processes parameter latch

136 query server process

137 query server freelists

138 error message lists

139 process queue

140 process queue reference

141 parallel query stats

142 parallel query alloc buffer

143 temp table ageout allocation latch

144 hash table modification latch

145 constraint object allocation

146 device information

147 temporary table state object allocation

148 kwqit: protect wakeup time

149 AQ Propagation Scheduling Proc Table

150 AQ Propagation Scheduling System Load

151 fixed table rows for x$hs_session

9. List of latches that are of most concern to a DBA

BUFFER CACHE LATCHES

=====================

Cache buffers chains latch:

...........................

This latch is needed when user processes try to scan

the SGA for database cache buffers.

Cache buffers LRU chain latch:

..............................

This latch is needed when user processes try to scan

the LRU (least recently used) chain containing all the dirty blocks

in the buffer cache.

REDOLOG BUFFER LATCHES

========================

Redo allocation latch:

......................

This latch controls the allocation of space for redo

entries in the redo log buffer. There is one redo allocation

latch per instance.

Redo copy latch:

................

This latch is used to write redo records into the redolog buffer.

SHARED POOL LATCHES

====================

Row cache objects latch:

........................

This latch comes into play when user processes are attempting to

access the cached data dictionary values.

Library cache latch

..............................

It serialize access to the objects in the library cache. Every time a SQL statements, PL/SQL

blocks or a stored objects (Procedures, packages, functions, triggers) is executed this latch

is acquired.

Shared pool latch

............................

This latch protects the allocation of memory in the library cache. There is just one latch to

the entire database.

10. How can we reduce contention for internal latches?

We can reduce contention for these latches and tune them by adjusting

certain init.ora parameters.

Cache buffers chain latch:

..........................

Contention in this latch might be related with the Buffer cache size, but it might be present

due to a "hot block" (meaning a block highly accessed). Before of incrementing the parameter

DB_BLOCK_BUFFER check that specific blocks are not causing the contention avoiding

memory wasting.

Cache buffers LRU chain latch:

..............................

Multiple Buffer pools and adjusting the parameter DB_BLOCK_LRU_LATCHES to have multiple

LRU latches will help on reducing latch contention.

Redo Allocation Latch:

......................

Contention for this latch in Oracle7 can be reduced by decreasing the value of

LOG_SMALL_ENTRY_MAX_SIZE on multi-cpu systems to force the use of the

redo copy latch. In Oracle8i this parameter is obsolete, so you need to consider

to increase the size of the LOG_BUFFER or reduce the load of the log buffer using

NOLOGGING features when possible.

Redo copy latch:

................

This latch is waited for on both single and multi-cpu systems.

On multi-cpu systems, contention can be reduced by increasing the

value of LOG_SIMULTANEOUS_COPIES (Hidden in Oracle8i) and/or increasing

LOG_ENTRY_PREBUILD_THRESHOLD (undocumented in Oracle7).

Row cache objects latch:

........................

In order to reduce contention for this latch, we need to

tune the data dictionary cache. In Oracle7 this basically means

increasing the size of the shared pool (SHARED_POOL_SIZE)

as the dictionary cache is a part of the shared pool.

Library cache and Shared pool latches

..............................

The first resource to reduce contention on this latch is to ensure that the application

is reusing as much as possible SQL statement representation. If the application is already

tuned the SHARED_POOL_SIZE can be increased. Be aware that if the application is not

using appropriately the library cache the contention might be worst with a larger structure

to be handled.

11. Tuning _SPIN_COUNT (_LATCH_SPIN_COUNT in Oracle7)

SPIN_COUNT controls how many times the process will re-try to obtain

the latch before backing off and going to sleep. This basically

means the process is in a tight CPU loop continually trying to get

the latch for SPIN_COUNT attempts.

On a single CPU system if an Oracle process tries to acquire a latch

but it is held by someone else the process will release the CPU and go

to sleep for a short period before trying again.

However, on a multi processor system (SMP) it is possible that the

process holding the latch is running on one of the other CPUs

and so will potentially release the latch in the next few instructions

(latches are usually held for only very short periods of time).

Performance can be adjusted by changing the value of SPIN_COUNT.

If a high value is used, the latch will be attained sooner than if

you use a low value. However, you may use more CPU time spinning to

get the latch if you use a high value for SPIN_COUNT.