Latches and Tuning:Buffer Cache

2010/12/26 by Leave a Comment

1.  The buffer pool is structured into pools; RECYCLE, KEEP, and DEFAULT.  Unless otherwise specified at create time or altered, an object will reside in the DEFAULT pool.

a. Suppose you have a table that is frequently updated, but is a relatively small table.  You would want to ensure the blocks for this table are stored in the KEEP pool of the buffer cache.  Execute the following command to create the table TEST1 in the KEEP pool.> create table TEST1 (n number) storage (buffer_pool KEEP);

b.  Suppose you have a large table that is randomly accessed.  You want to ensure the blocks for this table are stored in the RECYCLE pool.  Execute the following command to create the table TEST2 in the RECYCLE pool:

> create table TEST2 (n nmber) storage (buffer_pool RECYCLE);

c.  Suppose you have a table that is subjected to normal access. You want to ensure the blocks for this table are stored in the DEFAULT pool. Execute the following command to create the table TEST3 in the DEFAULT pool:

> create table TEST3 (n number) storage (buffer_pool DEFAULT);

d.  Query the USER_TABLES view to see the table names and the pools to which they are assigned:

> select table_name, buffer_pool from user_tables where table_name like ‘TEST%’;

You can use the ALTER TABLE command to change the buffer pool for a table.

2. Various statistics are incremented during buffer management.
a.  Execute the following query to view the relevants statistics:

> select * from v$sysstat where name in (‘dirty buffers inspected’,’free buffer inspected’);

– dirty buffers inspected: incremented when dirty buffers are read and moved to the LRUW list.
– free buffers inspected: incremented when free buffers are detected during the search for a free buffer on the LRU list.

3. The parameter DB_BLOCK_LRU_LATCHES defines the number of working sets for the instance.

a.   Determine the value of DB_BLOCK_LRU_LATCHES by executing the following query:> select name,value from v$system_parameter where name = ‘db_block_lru_latches’;

4.  There is one working set per DB_BLOCK_LRU_LATCHES.

a.  Query the X$KCBWDS view to determine the number of working sets for the instance.> select count(*) from x$kcbwds;

The returned values should be the same as the value for DB_BLOCK_LRU_LATCHES.

5.  Each working set also has its own checkpoint latch.

a.  Execute the following query to view the checkpoint latch and set id’s for the instance:> select addr,set_id,ckpt_latch,set_latch,dbwr_num from x$kcbwds;

b.  Using one of the ckpt_latch values, execute the following query:

> select * from v$latch_children where addr = <address from query>;

With this you can see that there is one child latch for each checkpoint latch.

6.  The maximum number of DBWR cannot be more than the number of working sets.
a.  Set the number of DBWR processes to a value greater than DB_BLOCK_LRU_LATCHES.  Do this by changing the DB_WRITER_PROCESSES value in the init.ora.

b.  Bounce your database instance.

c.  Execute the following query:

> select dbwr_num from x$kcbwds;

You will see that the number of DBWR processes is equal to the number DB_BLOCK_LRU_LATCHES.

7. The number of DBWR processes are set using the DB_WRITER_PROCESSES parameter.

a.  View your init.ora file and determine if the number of DB_WRITER_PROCESSES has been explicitly set.

8.  Two parameters effect the way DBWR scans for purposes of making free buffers.

a  Execute the following query:

> select ksppinm from x$ksppi where ksppinm like ‘_db%scan%’;

_DB_WRITER_SCAN_DEPTH – Sets the scan depth for DBWR
_DB_BLOCK_MAX_SCAN_CNT – Sets the foregrond scan depth

The maximum number of dirty buffers in the write list, which will not cause DBWR to write is
max(DB_BLOCK_BUFFERS/8, 2*write-batch-size

9. A target value for the buffer cache hit% is 90.

a.  Determine the buffer cache % by executing the following query:> select 1 – (phy.value / (log.value + con.value )) “Buffer Cache Hit%”
from v$sysstat phy, v$sysstat log, v$sysstat con
where phy.name = ‘physical reads’ and
log.name = ‘db block gets’ and
con.name = ‘consistent gets’;

Descriptions of the buffer cache statistics used are:

– db block gets: tracks the number of blocks obtained in CURRENT mode.
– physical reads: stores the number of physical blocks when the OS retrieves a database block from disk
– consistent gets: number of times a consistent read was requested for a block.

Other buffer cache statistics include:

– physical writes
– db block changes
– free buffer requested
– dirty buffers inspected
– free buffer inspected
– CR blocks created

10.  Numerous statistics are associated with the DBWR process.

a.  Execute the following query to view the statistics associated with the DBWR process:> select name from v$sysstat where name like ‘DBWR%’;

Of the statistics listed two of the most important are DBWR checkpoints and DBWR make free requests.

11.  The number of cache buffers chains latches is equal to prime(db_block_buffers/4).  The number of cache buffers
lru chain latches is equal to the value of DB_BLOCK_LRU_LATCHES.

a.  Execute the following query to determine the approximate number of cache buffers chains latches:> select (value/4) from v$system_parameter where name = ‘db_block_buffers’;

b.  The value from the above should be approximately equal to the row count returned from the following
query:

> select count(*) from v$latch_children where name = ‘cache buffers chains’;

c.  Execute the following query to determine the number of cache buffers lru chain latches:

> select count(*) from v$latch_children where name = ‘cache buffers lru chain’;

d.  Check the value of DB_BLOCK_LRU_LATCHES and it should equal the row count returned from the first query.

12.  Wait events are also important for diagnosing buffer cache problems.  It’s the time_waited for each
event that is used for diagnostics.
a.  Execute the following query to view the two primary events associated with tuning the buffer cache:

> select * from v$system_event
where name in (‘buffer busy waits’,’free buffer waits’)
order by time_waited;

You may not see free buffer waits in the result set if there has been no wait time for this event.  It is listed in V$EVENT_NAME.

b.  Additional wait information can be derived from the views V$SESSION_WAIT and V$WAITSTAT views, and the X$KCBFWAIT table.  Query the V$WAITSTAT view to list the different types of
blocks:

> select class from v$waitstat;

Some of the most likely reasons for each block type are:

Segment Header – not enough free lists and high number of inserts
Data Blocks – too many rows in each block
Freelist Blocks – not enough free lists
Undo Header – too many transactions per rollback segment
Undo Blocks – not enough rollback segment blocks

Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: , ,

Oracle常用的几个父栓

2010/09/07 by 1 Comment

Oracle中的父闩大致可以分成2类：有子闩的父闩或者独居的父闩，我们来看看这些父闩的属性:

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

SQL> select count(distinct name) from v$latch_children;
COUNT(DISTINCTNAME)
-------------------
                 82

/* 10.2.0.4下共有82种不同子闩，同比11.2.0.1是75种，要比10g中少一些，因为一部分闩在11g中被mutex替代了 */

[Read more…]

Filed Under: Oracle Tagged With: , ,

【书籍推荐】Oracle 8i Internal Services

2010/07/26 by Leave a Comment

直到今天仍是经典，无可替代，绝不落伍。想了解Oracle internal 必读的一本书，每次翻开影印本都会有所收获，感谢作者的无私贡献！
[gview file=”http://askmac.cn/wp-content/uploads/resource/oracle8i.internal.services.for.waits.latches.locks.pdf”]

Filed Under: Oracle Tagged With: , , ,

kgl simulator，共享池simulator以及缓冲simulator相关解释

2009/10/05 by 2 Comments

从9i开始学习的朋友一定习惯了v$sgastat视图中 一系列的sim项，主要包括各种 kglsim,sim cache segment trace等等。如下例：

SQL> select * from v$sgastat where lower(name) like '%sim%' order by name;

shared pool  kglsim count of pinned he        1920
shared pool  kglsim free heap list              96
shared pool  kglsim free obj list               96
shared pool  kglsim hash table                8208
shared pool  kglsim hash table bkts        4194304
shared pool  kglsim heap                    782208
shared pool  kglsim main lru count           76800
shared pool  kglsim main lru size           151040
shared pool  kglsim object batch           1163520
shared pool  kglsim pin list arr               288
shared pool  kglsim recovery area             1920
shared pool  kglsim sga                      30552
shared pool  kglsim size of pinned mem        3776

shared pool  ksim client list                  168
shared pool  log_simultaneous_copies           800
shared pool  sim cache nbufs                   640
shared pool  sim cache sizes                   640
shared pool  sim kghx free lists                 8
shared pool  sim lru segments                 1280
shared pool  sim segment hits                 1280
shared pool  sim segment num bufs              640
shared pool  sim state object                   40
shared pool  sim trace buf                    5144
shared pool  sim trace buf context             200
shared pool  sim_knlasg                       1280
shared pool  simulator hash buckets         131328

shared pool  simulator hash latch             5120
shared pool  simulator latch/bucket st        2816

28 rows selected.

有时你也会发现存在sim相关的latch,

SQL> select name from v$latch where name like '%sim%';

sim partition latch
simulator hash latch
simulator lru latch

显然Oracle实例中存在着这许多的模拟器(simulator),他们是 干啥的？

这些模拟器帮助Oracle实例获知当拥有更大的内存容量（涉及buffer_cache,shared_pool_size等)时可以节省多少时间和功。这些信息被汇总到几个常用视图中，如 V$SHARED_POOL_ADVICE, V$MTTR_TARGET_ADVICE, V$DB_CACHE_ADVICE等等。模拟器特性从9i开始被引入，也就是这些视图第一次出现的时候。

MMAN进程也利用这些信息作为SGA动态调整的基本依据（在sga_target或memory_target启用的情况下）。

KGL simulator会跟踪共享池内存分配并扮演模拟器引擎的角色。

KGL是 kernel general Library cache manager,它管理library cache中的对象 包括游标，PL/SQL存储过程，表的定义等等。

KGL simulator 评估当缓存大于目前情况的获益。 其通过将flush out出library cache的object的hash code保存至kgl simulator的 hash 列表中，以造成object似乎仍在缓存中的假象，方便其统计相关的数据。

当有对象被读入library cache中时，Oracle检查kgl simulator中的hash列表，若命中说明历史hash列表中的对象对当前实例仍有用，即更大的内存空间对实例来说是有益的。

Filed Under: Oracle Tagged With: , , ,

Know more about redo log buffer and latches

2009/06/19 by Leave a Comment

1.
The Total size of the log buffer is determined by LOG_BUFFER parameter.

2.
Only Server process may pin a data block in exclusive mode.

3.
LGWR writes to the redo log files when:

  • The redo log buffer is 1/3 full.
  • 1 MB of data have been written to the redo log buffer.
  • A 3-second time-out occurs.
  • commit!
  • before dbwr write out dirty buffers
  • A thread is closed

 

4.
the correct sequence for the LGWR algorithm is :

  1. Acquire the redo writing and redo allocation latches
  2. determine the buffer to write out
  3. Release the redo allocation latch
  4. determine how many writes are required

 

5.
In Oracle8i, the redo copy latch is always acquired regardless of the redo size.

Because the parameter LOG_SMALL_ENTRY_MAX_SIZE is obsolete, a redo copy latch is always acquired.

6.
Three most relevant redo events under normal operations:

  • Log file parallel write
  • Log buffer space
  • Log file sync

 

7.
relevant redo statistics and their purpose

  • REDO writes                                                 === Number of times the log buffer is written
  • redo blocks written                                === Number of times the log buffer is written
  • redo write time                                          === Total time required to write all the redos to disk
  • redo buffer allocation retires         === Total number of retries necessary to allocate space in the redo buffer

 

8.
Log buffer contention is typically indicated by Redo buffer allocation retries .

The buffer allocation retries indicates that a process has to continually try to allocate buffers that are not available

9.
Redo buffer allocation retries can be gathered by querying the name and value columns from V$ dynamic views:
V$sesstat
V$sysstat

10.
Stripe the redo log files across physical disks will help flush the redo buffer faster.

The redo log file write batch is 128K, so striping the redo logs across 8 disks with a stripe size of 16K could improve the write time by as much as 75%.

11.
The number of redo copy latches can be defined by setting _Log_simultaneous_copies .

The parameter begins with an underscore because in 8i it is a hidden parameter.

12.
The purpose of the redo writing latch is to: Prevent multiple processes from posting
LGWR when there is no space in the buffer .
The redo writing latch frees space in log buffer and does not want
processes writing to the buffer while space is being freed

13.
_log_io_size is set to reduce contention for the redo writing latch.

But notice that it is a hidden parameter. This is the number of used redo blocks that will automatically initiate a log write.

14.
A standby database must be refreshed following a nologging operation.

When nologging occurs, the redo logs of the standby database are not written to.

15.
log_small_entry_max_size is used to determine if a redo copy latch is required based upon the redo entry size,

however, this parameter is obsolete in Oracle8i, as a copy latch is always acquired.

_log_simultaneous_copies determines the number of redo copy latches to allocate.

Note that this is a hidden parameter in Oracle8i because it is not recommended to have more than one copy latch.

log_checkpoint_interval determines the need for a checkpoint.

Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: , ,

Most of library cache latches have gone in 11g

2009/05/20 by 1 Comment

10g

SELECT t1.ksllasnam "parent_name",
t2.ksllwnam "location"
FROM x$ksllw t2, x$kslwsc t1
WHERE t2.indx = t1.indx
AND ksllasnam like '%library cache%'

parent_name location
-------------------------------------------------- ----------------------------------------------------------------
library cache kqlftc
library cache kqlmbfre: parent
library cache kqlmbpil: parent
library cache kqlmbinv: parent
library cache kqlmbfre: child: in loop
library cache kqlmbfre: child: no obj to free
library cache pin kqlmbpil: child
library cache pin kqlmbpil: child: free and continue
library cache pin kqlmbp: child
library cache pin kqlmbpil: child: post parent after timeout
library cache kgluka: parent
library cache kglScanDependency
library cache kglscn: parent
library cache kglsca: parent
library cache kglLockCursor
library cache kgldrp: child
library cache kgldrx: child
library cache kgldmc: parent
library cache kglhdiv: child
library cache kglivl: parent
library cache kglidp: child
library cache kgldtld: parent
library cache kgldte: parent
library cache kglilf: parent
library cache kglmvsca: parent
library cache kgldte: child 0
library cache kglget: KGLDSBYA
library cache kglget: set reserved lock
library cache kglget: hash collision
library cache kglget: unpin heap 0
library cache kglpin: child: KGLMX
library cache kglupi: child: outside loop
library cache kglkep: child
library cache kglukp: child
library cache kglupc: child
library cache kglgfc: child
library cache kglalt: child
library cache kglupd: child
library cache kglsts: child
library cache kglpur: child
library cache kglprg: child
library cache kglini: child
library cache kglver: child
library cache kglivl: child
library cache kglivr: child
library cache kgldte: child
library cache kglhdbrnl: child
library cache kglobr: child
library cache kglpnc: child
library cache kglati
library cache kglpim
library cache kglupkp
library cache kgllfr
library cache kglnti
library cache kglic
library cache kglpnp: child
library cache kglhfr: child
library cache kglrtl
library cache kglpin
library cache kglswp
library cache kgldti: 2child
library cache kgldtld: 2child
library cache kgl 2child pad56
library cache kgllkal: parent
library cache kglhdal: parent
library cache kglhdunp: parent: flush
library cache kglhdiv0: parent: invalidate
library cache kglhdbr0: parent: invalidate
library cache kgllldl: child:
library cache kglhdbr: child
library cache kgl parent pad2132
library cache kglhdgn: child:
library cache kglhdgc: child:
library cache kglhdgh: child:
library cache kglobpn: child:
library cache kglpndl: child: before processing
library cache kglhdiv0: child
library cache kglpndl: child: after processing
library cache kglobld
library cache kglpin: child: heap processing
library cache kglpnal: 2child
library cache load lock kglllal: llatch
library cache lock kglget: un-set reserved lock
library cache lock kgllkal: child: multiinstance
library cache lock kgllkal: child: in loop
library cache lock kgllkal: child: deadlk det: error
library cache lock kgllkdl: free heap 0
library cache lock kgllkdl: child: cleanup
library cache lock kgllkdl: child: multiinstance
library cache lock kgllkdl: child: no lock handle
library cache lock kgllkc: child
library cache lock kglpsl: child
library cache lock kglhdbrnl: child
library cache lock kglhdcb: child
library cache lock ksucallcbksafely: kqllhng
library cache pin ksucallcbksafely: kqlphng
library cache load lock ksucallcbksafely: kqldlhn
library cache pin kglobld: child:
library cache pin kgllkdl: child: free pin
library cache pin kglpnal: child: alloc space
library cache pin kglpnal: child: check granted
library cache pin kglpndl: child: multiinstance
library cache pin kglpnck
library cache pin kglupc
library cache pin kglpin: child: KGLMS
library cache pin kglupi: child: within loop
library cache pin kgldnp: child
library cache pin kglpnc: child
library cache pin kglpnp: child
library cache pin kglobf0
library cache pin kglpndl
library cache pin dummy
library cache pin kglivl0
library cache hash chains kglpnrel
library cache hash chains kglpnget
library cache hash chains spare
library cache pin allocation kglpnrel
library cache pin allocation kglpnget
library cache pin allocation spare1
library cache pin allocation spare2
library cache lock allocation kgllkrel
library cache lock allocation kgllkget
library cache lock allocation spare1
library cache lock allocation spare2
library cache load lock kglllal: llatch: in loop
library cache load lock kgllldl: llatch
library cache kgl 2child pad2137
library cache kgl 2child pad2138
library cache kksftc

11g

SELECT t1.ksllasnam "parent_name",
t2.ksllwnam "location"
FROM x$ksllw t2, x$kslwsc t1
WHERE t2.indx = t1.indx
AND ksllasnam like '%library cache%'

parent_name location
--------------------------------------- ---------------------------------------------
library cache load lock kglllal: llatch
library cache load lock ksucallcbksafely: kqldlhn
library cache load lock kglllal: llatch: in loop
library cache load lock kglllal: llatch: in loop 2
library cache load lock kgllldl: llatch

latch:
library cache lock
library cache lock allocation
library cache pin
library cache pin allocation
library cache

ALL above latches have gone(latch library cache load lock left), library cache lock/pin don't need acquire library cache any more in 11g ;

library cache lock/pin structure now are protected by Library cache type mutex also.
But We can not find out how kglpin/kgllkdl and else function are utlized with mutex from any V$/X$ dynamic views.
Mutex is more black-box than Latch.

SQL> select mutex_type ,location from v$mutex_sleep_history group by mutex_type,location;

MUTEX_TYPE LOCATION
-------------------------------- ---------------------------------------------
Library Cache kglhdgn2 106
Library Cache kglget1 1
Cursor Pin kksfbc [KKSCHLCREA]
Cursor Parent kksfbc [KKSPRTLOC1]
Library Cache kglrfcl1 79
Library Cache kgllkal1 80
Library Cache kgllkdl1 85
Cursor Pin kkslce [KKSCHLPIN2]
Library Cache kglhdgh1 64
Library Cache kglpin1 4
Library Cache kglpndl1 95
Cursor Pin kksfbc [KKSCHLPIN1]
Library Cache kglhdgn1 62
Library Cache kglpur1 28
Library Cache kglpnal1 90
Library Cache kglobpn1 71
Library Cache kglpnal2 91

Filed Under: Oracle Tagged With: , ,

Know more about Oracle Latches

2007/06/30 by Leave a Comment

Two purposes of latches were:Serialize Execution,Serialize Access

Match the characteristics with latches and enqueues.

Several modes of access                   Enqueue
FIFO queue acquisition                    Enqueue
Mostly exclusive access                   Latch
Non-deterministic acquisition             Latch
No atomicity                              Enqueue
Atomicity                                 Latch

Most latches have a level between 0 and 8.

Usually a process can request a latch X only:After obtaining latch Y and if level X > level Y.
Remember that the level of X, which is child latch, must be greater than level Y, which is most likely a parent latch.

The structure for parent and child latches can be seen by querying X$KSLLT .
There is one KSLLT structure for each parent and child latch in an instance.

The characteristic of latch acquisition in NO-WAIT mode is:
One attempt to get a latch, no spin or sleep .
This is typically for latches where there are many of the same type. The process will attempt to acquire another latch.

The sleep time on latches approximately doubles with each wait until upper limit is reached. This is an exponential backoff.

The statistic waiters_woken counts the number of times a posting for a latch has occurred.
It can be found in V$LATCH.This view contains aggregate statistics for latches

PMON invokes a clean up function for releasing latches if the holding process dies.PMON will initiate the clean up function.

_MAX_SLEEP_HOLDING_LATCH The maximum exponential backoff when waiting for a latch and already holding anothers
_MAX_EXPONENTIAL_SLEEP Controls the maximum time a process has to sleep before requesting the latch again
_SPIN_COUNT Designates the number of times a process will spin while trying to acquire a latchs

V$SESSION_WAIT dynamic performance view gives real time information regarding what sessions are currently waiting or have just waited for an event.

When diagnosing latch contention, we should typically look for Latches with the most impact.

We should use below formula  to calculate the total cost of latch spinning:

_spin_count * sleeps/misses

Statistics from V$LATCH with their associated description:

IMMEDIATE_MISSES Number of times the fast get failed when requested in “no-wait” mode

MISSES Number of times the fast get call failed when requested in “willing-to-wait” modes

SPIN_GETS Number of times where the fast get failed but the latch was acquired during the first spin before having to yield to the CPU

GETS Number of times the latch was obtained when requested in “willing-to-wait” mode

SLEEPi Number of times a “willing-to-wait” request had to sleep I times

IMMEDIATE_GETS Number of times the latch was obtained when requested in “no-wait” mode

We can set event 10005 to perform latch tracing, or oradebug dump latches 3 ;

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmac.cn

[oracle@rh2 ~]$ oerr ora 10005
10005, 00000, "trace latch operations for debugging"
// *Document: NO
// *Cause:
// *Action: Enable tracing for various latch operations
// *Comment:
//    level 1 - trace latch gets and frees
//    level 4 - trace multiple posts by processes when latch is freed
//

Wait and latch (KSL) layer (10005)

We can set events 10005 (trace latch gets and frees) and 10073 (have PMON
dump info before latch cleanup).  Level does not matter for these events.
We can also set event="600 trace name LATCHES level 1" to dump latch info
when PMON hits the error (I'm not so sure this one is going to work).

 event = "600 trace name latches level 10"
 event = "10005 trace name context forever, level 1"

10005 event has been removed above 9i , try dump KSTDUMPCURPROC 1

SQL> oradebug setmypid;
SQL> oradebug unlimit;
SQL> oradebug dump KSTDUMPCURPROC 1
SQL> oradebug tracefile_name;

KSTDUMP: In-memory trace dump
TIME(usecs):SEQ# ORAPID   SID EVENT  OP DATA
========================================================================
D9396B13:000044F8    14     0 10005   1 KSL WAIT BEG [PX Idle Wait] 200/0xc8 146/0x92 0/0x0
D9573C3D:00004568    14     0 10005   2 KSL WAIT END [PX Idle Wait] 200/0xc8 146/0x92 0/0x0 time=1954087
D9573C4F:00004569    14     0 10005   1 KSL WAIT BEG [PX Idle Wait] 200/0xc8 147/0x93 0/0x0
D9750D82:000045FC    14     0 10005   2 KSL WAIT END [PX Idle Wait] 200/0xc8 147/0x93 0/0x0 time=1954097
D9750D8F:000045FD    14     0 10005   1 KSL WAIT BEG [PX Idle Wait] 200/0xc8 148/0x94 0/0x0
D992DEA2:00004639    14     0 10005   2 KSL WAIT END [PX Idle Wait] 200/0xc8 148/0x94 0/0x0 time=1954065
D992DEB2:0000463A    14     0 10005   1 KSL WAIT BEG [PX Idle Wait] 200/0xc8 149/0x95 0/0x0
D9B0AFD8:000046A9    14     0 10005   2 KSL WAIT END [PX Idle Wait] 200/0xc8 149/0x95 0/0x0 time=1954084
D9B0AFEB:000046AA    14     0 10005   1 KSL WAIT BEG [PX Idle Wait] 200/0xc8 150/0x96 0/0x0
D9CE8110:00004742    14     0 10005   2 KSL WAIT END [PX Idle Wait] 200/0xc8 150/0x96 0/0x0 time=1954083
D9CE811E:00004743    14     0 10005   1 KSL WAIT BEG [PX Idle Wait] 200/0xc8 151/0x97 0/0x0
D9EC5246:00004790    14     0 10005   2 KSL WAIT END [PX Idle Wait] 200/0xc8 151/0x97 0/0x0 time=1954086
D9ECA80D:00004792    14     0 10280   8 kst: process state object about to be deleted
E308018E:00006780    14     0 10280   1 kst: process state object created on 06-30 22:11:24.211
E308018F:00006781    14     0 10280   3 kst: process info: ospid=10380 pso_num=14 pso_serial#=2
E30801D0:00006782    14     0 10420   1 kso: new process: pid=10380 (legacy spawn)
E4ACF279:00006D4A    14     0 10280   8 kst: process state object about to be deleted
E4B2CD42:00006D4B    14     0 10280   1 kst: process state object created on 06-30 22:11:52.853
E4B2CD43:00006D4C    14     0 10280   3 kst: process info: ospid=10382 pso_num=14 pso_serial#=3
E4B2CDA3:00006D4D    14     0 10420   1 kso: new process: pid=10382 (legacy spawn)
KSTDUMP: End of in-memory trace dump


**** LGWR trace
05A46022:0000DEB0     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[58] comment=[Redo writer log switch operations]
05A46025:0000DEB1     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[59] comment=[Redo writer generate offline immed marker]
05A46026:0000DEB2     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[170] comment=[kfr Poke LGWR]
05A46027:0000DEB3     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[171] comment=[kfr Incr Ckpt]
05A46029:0000DEB4     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[172] comment=[kfr ACD relocation]
05A4602A:0000DEB5     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[1] comment=[KSB action for X-instance calls]
05A4602A:0000DEB6     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[63] comment=[Scumnt mount lock]
05A4602B:0000DEB7     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[64] comment=[Poll system events broadcast channel]
05A4602E:0000DEB8     6   221 10005   1 KSL WAIT BEG [rdbms ipc message] 300/0x12c 0/0x0 0/0x0
05D117DB:0000DF45     6   221 10005   2 KSL WAIT END [rdbms ipc message] 300/0x12c 0/0x0 0/0x0 time=2930603
05D117E6:0000DF46     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[1] comment=[KSB action for X-instance calls]
05D117E7:0000DF47     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[63] comment=[Scumnt mount lock]
05D117E8:0000DF48     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[64] comment=[Poll system events broadcast channel]
05D117ED:0000DF49     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[0] comment=[Monitor Cleanup]
05D117EE:0000DF4A     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[56] comment=[Redo writer IO's]
05D117FE:0000DF4B     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[57] comment=[Redo writer post action]
05D117FF:0000DF4C     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[58] comment=[Redo writer log switch operations]
05D11803:0000DF4D     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[59] comment=[Redo writer generate offline immed marker]
05D11804:0000DF4E     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[170] comment=[kfr Poke LGWR]
05D11805:0000DF4F     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[171] comment=[kfr Incr Ckpt]
05D11807:0000DF50     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[172] comment=[kfr ACD relocation]
05D11808:0000DF51     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[1] comment=[KSB action for X-instance calls]
05D11808:0000DF52     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[63] comment=[Scumnt mount lock]
05D11809:0000DF53     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[64] comment=[Poll system events broadcast channel]
05D1180C:0000DF54     6   221 10005   1 KSL WAIT BEG [rdbms ipc message] 300/0x12c 0/0x0 0/0x0
05FDCFC0:0000DFE1     6   221 10005   2 KSL WAIT END [rdbms ipc message] 300/0x12c 0/0x0 0/0x0 time=2930610
05FDCFCB:0000DFE2     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[1] comment=[KSB action for X-instance calls]
05FDCFCC:0000DFE3     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[63] comment=[Scumnt mount lock]
05FDCFCD:0000DFE4     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[64] comment=[Poll system events broadcast channel]
05FDCFD1:0000DFE5     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[0] comment=[Monitor Cleanup]
05FDCFD2:0000DFE6     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[56] comment=[Redo writer IO's]
05FDCFEF:0000DFE7     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[57] comment=[Redo writer post action]
05FDCFF0:0000DFE8     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[58] comment=[Redo writer log switch operations]
05FDCFF3:0000DFE9     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[59] comment=[Redo writer generate offline immed marker]
05FDCFF4:0000DFEA     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[170] comment=[kfr Poke LGWR]
05FDCFF5:0000DFEB     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[171] comment=[kfr Incr Ckpt]
05FDCFF7:0000DFEC     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[172] comment=[kfr ACD relocation]
Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: ,

Know more about Buffer Cache and Latch

2007/06/30 by Leave a Comment

We can examine X$BH table to obtain buffer header information,The BH stands for buffer header.

Structures that maintain a list of data buffer headers are called:Hash buckets.

Hash buckets are grouped by relative DBA and class number.
Hash chains list data buffer headers in one hash bucket.

Cache Recycle Pool For randomly accessed large tables

Default Pool For normally accessed tables

Keep Pool for frequently updated small tables

Set DB_BLOCK_LRU_LATCHES parameter to create multiple LRU lists.This parameter sets the number of latches,
and it is best used on a multi-CPU system.

DB_BLOCK_BUFFERS sets the number of buffers to allocate in the buffer cache.
DB_BLOCK_LRU_EXTENDED_STATISTICS is obsolete in Oracle8i.
DB_WRITER_PROCESSES sets the number of database writer processes.

Cache buffers LRU chain protects LRU lists.

The total number of working sets per instance is defined by DB_BLOCK_LRU_LATCHES. There is one latch per set.

The number of DBWR processes cannot be greater than the number of working sets.

Oracle get buffers as below steps:

  1. get a buffer descriptor
  2. specify a mode in which a buffer should be obtained
  3. scan the appropriate hash chain
  4. find the appropriate buffer in the chain or read from disk
  5. attach a state object to the buffer header

Buffers are initially hashed to LRU_AUX.This contains buffers that are candidates for reuse.
LRU_MAIN List houses buffers that are in use (pinned or dirty).
LRU_W List is the write list for dirty buffers.
LRU_XR List is the reuse range list for buffers that are to be written for reuse.

DBWR processes operate on working sets that are assigned to them in a cyclical manner.

There are three reasons may cause DBWR write buffers to disk:

  • To provide checkpoints
  • To do free requests
  • To do ping writes(obsolete)

The write batch size controls the number of asynchronous writes slots allocated to each DBWR.The write batch size controls the number of asynchronous writes slots allocated to each DBWR.

Two Most important DBWR statistics: DBWR make free request,DBWR lru scans 。

Two circumstances where DBWR purges dirty buffers whose DBA falls between the lowest and highest DBA of the datafiles:

  • ALTER TABLESPACE…BEGIN BACKUP
  • Make a tablespace read only

Of the nine latches that protect the buffer cache,the three that are most important:

  • Cache buffers lru chain
  • Cache buffers chains
  • Checkpoint queue latch

each of the fixed tables below with its purpose:

  • X$KCBWAIT         Wait statistics by block class
  • X$KCBFWAIT      Wait statistics by file id
  • X$KCBBHS              DBWR histogram statistics
  • X$KCBWBPD         Buffer pool descriptors

A target buffer cache hit ratio is above 90%.
But 60% could be a valid hit ratio for a DSS or Data Warehouse application.

Two primary events that relate to the buffer cache:

  • Buffer busy waits
  • Free buffer waits

We can use the views and table:V$SYSTEM_EVENT, V$WAITSTAT, and X$KCBFWAIT to determine the reason and block class for buffer busy waits.

Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: , ,

Know More About Libarary Cache and Latches

2007/06/29 by 2 Comments

Stored objects And Transient objects are stored in the library cache, neither Temporary objects nor Permanent objects.

The library cache is structured as a Hash table .But Library cache objects are composed of data heaps.
Oracle access hash tables through hash buckets.

SHARED_POOL_SIZE does the Oracle server determine the number of hash tables. When the the shared pool is larger, it can accommodate more object handles.

_KGL_BUCKET_CNT can be used to set the number of hash buckets and deprecated.
The minimum size of the hash table is 509 buckets in ORACLE 7.

For performance reasons, when the linked list has an average depth of 2 the Oracle server doubles the size of the hash table.

Locks manage concurrency whereas pins manage cache coherency.

There are two valid library cache pin modes: Share and Exclusive

An object handle is protected by a latch determined by the bucket it hashes into using the formula latch# = mod(bucket#, #latches) .

The hidden parameter _KGL_LATCH_COUNT is used to determine the number of child latches.”The default value should be adequate, but if contention for the library cache latch cant be resolved, it may be advisable to increase this value. The default value for _KGL_LATCH_COUNT is the next prime number after CPU_COUNT. This value cannot exceed 66 (See: <>). ”

Begin 10.2.0.2, mutex take place cursor pin latch.To avoid using Mutex latches, you can set _kks_use_mutex_pin=false .

CURSOR_SPACE_FOR_TIME has been deprecated in 10.2.0.5 and 11.1.0.7.

CURSOR_SPACE_FOR_TIME was originally introduced to try and help reduce latch contention by keeping cursors in memory in the SGA rather than allowing their data to be flushed from the shared pool. Such latch contention is avoided in current releases by the use of cursor mutexes and so this parameter is no longer relevant.

v$open_cursor lists kinds of library cache lock,x$kgllk – Details about Object locks


v$open_cursor

select inst_id,
       kgllkuse,
       kgllksnm,
       user_name,
       kglhdpar,
       kglnahsh,
       kgllksqlid,
       kglnaobj,
       kgllkest,
       decode(kgllkexc, 0, to_number(NULL), kgllkexc),
       kgllkctp
  from x$kgllk
 where kglhdnsp = 0
   and kglhdpar != kgllkhdl


SQL> select distinct kgllkctp from x$kgllk ;

KGLLKCTP
--------------------------------------------
SESSION CURSOR CACHED
PL/SQL CURSOR CACHED
OPEN
OPEN-RECURSIVE
DICTIONARY LOOKUP CURSOR CACHED
Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: ,

沪ICP备14014813号-2

沪公网安备 31010802001379号