在Buffer Cache 中，Oracle 通过几个链表进行内存管理，其中最为人熟知的就是LRU list and Dirty list （也通常被成为write list，从Oracle 8i开始，因为算法变化，也被称为checkpoint queue），各种list存放的是指向具体的Buffer的指针。
数据库初始化时，所有的Buffer都被Hash到LRU list上，当需要从数据文件读数据的时，首先在LRU list 寻找Free 的Buffer，然后读取数据到Buffer Cache 中。当数据被修改后，状态变为dirty，就可以被移至dirty list（checkpoint queue）。Dirty list 上的都是候选的可以被DBWR写出到数据文件的Buffer。一个Buffer要么在LRU list 上，要么在dirty list上。
1. 当一个server 进程需要读数据到Buffer Cache中时候，首先需要判断该数据在Buffer中是否存在。如果存在且可用，则获取该数据。根据LRU算法在LRU list上移动该Block。如果Buffer中不存在该数据，则需要从数据文件中读取。
2.在读取数据之前，SErver进程需要扫描LRU list寻找free 的Buffer，扫描过程中server进程会发现的已有的被修改过的Buffer 移至Checkpoint Queue 上。 这些Dirty buffer 随后可以被写出到数据文件。
3. 如果checkpoint queue 超过了阈值，Server进程就会通知DBWn去写出脏数据，这也是触发DBWn写的一个条件。这个阈值是25%。也就是说，当检查点队列超过25%满就会触发DBWn的写操作。
select kvittag,kvitval,kvitdsc from x$kvit where kvittag = 'kcbldq';
KVITTAG KVITVAL
---------------------------------------------------------------- ----------
KVITDSC
----------------------------------------------------------------
kcbldq 25
large dirty queue if kcbclw reaches this
如果SErver进程扫描LRU超过一定的阈值仍然不能找到足够的Free Buffer，将停止寻找，转而通知DBWn去削除脏数据，释放内存空间。
同样，这个阈值可以在数据字典表找到，是40%。也就是说，当Server 进程扫描LRU超过40% 还没能找到足够的Free Buffer就会停止搜索，通知DBWn执行写出。这时进程会处于 free busy wait 等待。
select kvittag, kvitval, kvitdsc from x$kvit where kvittag ='kcbfsp'
idle> /
KVITTAG KVITVAL
---------------------------------------------------------------- ----------
KVITDSC
----------------------------------------------------------------
kcbfsp 40
Max percentage of LRU list foreground can scan for free
同时，因为增量检查点的引入，DBWn也会主动扫描LRU list，将发现的Dirty Buffer移至Checkpoint queue。这个扫描比例也受一个内部约束，在oracle 9iR2中，是25%
4. 找到足够的Buffer之后，SErver进程就可以将Buffer从数据文件读入Buffer Cache中。
5. 如果读取的Block不满足读一致性需求，则server进程需要通过当前Block版本和回滚段构造前镜像返回给用户。
从Oracle 8i开始，LRU list 和Dirty list 又分别增加了辅助list （auxiliary list）用于提高管理效率。引入辅助list之后，当数据库初始化的时候，Buffer 首先存放在LRU 辅助List上（auxiliary RPL_LST），当被使用后移动到LRU主list上(MAIN RPL_LST)，这样当用户搜索Free Buffer时候，就可以从LRU-AUX list 开始，而DBWn死哦所Dirty Buffer时，则可以从LRU-MAIN list 开始，从而提高搜索效率和数据性能。
可以通过如下命令转储Buffer CAche的内容，从而清晰的看到其数据结构。
alter session set events 'immediate trace name buffers level 4';
转储仅限于在测试环境中，生成的跟踪文件巨大，为了获得完整的跟踪文件，建议设置初始参数 max_dump_file_size 为unlimited
6.
当用户进程需要读数据到Buffer Cache时，或Cache Buffer根据LRU算法进行管理时，就不可避免的要扫描LRU list。 Buffer Cache 是共享内存，可以为众多并发进程访问，所以在搜索的过程中，必须获得latch，锁定内存结构，防止并发访问内存中的数据。
这个多顶LRU的latch 就是经常见到的Cache buffer LRU chain
select addr,latch#,name, gets,misses,immediate_gets, immediate_misses from v$latch where name = 'cache buffers lru chain';
ADDR LATCH# NAME GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES
-------- ---------- ------------------------------ ---------- ---------- -------------- ----------------
2000944C 117 cache buffers lru chain 23345 0 986 0
Cache Buffer Lru Chain Latch 存在多个子latch, 其数量受隐含参数 _db_block_lru_latches控制。
NAME VALUE
------------------------------ ------------------------------
DESCRIB
------------------------------------------------------------
_db_block_lru_latches 8
number of lru latches
从v$latch_children 视图看当前各子latch使用情况：
select addr,latch#,name, gets,misses,immediate_gets, immediate_misses from v$latch_children where name = 'cache buffers lru chain'
idle> /
ADDR LATCH# NAME GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES
-------- ---------- ------------------------------ ---------- ---------- -------------- ----------------
5FA02B3C 117 cache buffers lru chain 14 0 1 0
5FA02EC0 117 cache buffers lru chain 14 0 1 0
5FA03244 117 cache buffers lru chain 27350 0 981 0
5FA035C8 117 cache buffers lru chain 14 0 1 0
5FA0394C 117 cache buffers lru chain 14 0 1 0
5FA03CD0 117 cache buffers lru chain 14 0 1 0
5FA04054 117 cache buffers lru chain 14 0 1 0
5FA043D8 117 cache buffers lru chain 14 0 1 0
8 rows selected.
如果该latch 竞争激烈，通常有如下方法可以采用：
1. 适当增大Buffer Cache，这样可以减少数据到buffer Cache 的机会，减少扫描LRU List 的竞争。
2. 可以适当增加LRU latch的数量，修改_db_block_lru_latches 参数可是实现。但是该参数通常是足够的。
3. 通过多种缓冲池技术，可以减少不希望的数据老化和全表扫描等操作对default 池的冲击，从而可以减少竞争，
7. Cache Buffer Chain 闩锁竞争与解决
在LRU和dirty list 这两个内存结构外，Buffer Cache 的管理还存在另外两个重要的数据结构： Hash Bucket和Cache Buffer Chain
_spin_count
由于Buffer 根据Buffer Header进行散列，从而最终决定存入哪一个hash Bucket, 那么Hash Bucket 的数量在一定程度上，决定了每个Bucket中的Buffer数量，也就间接影响了搜索性能。
Hash Bucket 受 _db_block_hash_buckets 影响。 在Oracle 7 和Oracle 8 中，缺省值为 db_block_buffers/4 下的一个素数。在Oracle 8i中，该参数缺省为db_block_buffers * 2
对于每个Bucket，只存在一个chain,当用户试图搜索Cache Buffer Chain 时，必须首先获得 Cache Buffer Chain Latch。
在Oracle 8i 之前，对于每一个Bucket，Oracle 使用一个独立的hash latch 来维护。 其缺省Bucket 数量为 next_prime(db_block_buffers / 4)
从Oracle 8i 开始， _db_block_hash_buckets 增加到 db_block_buffers, 而 _db_block_hash_latches的数量也发生了变化 。
1. 当 Cache Buffer 少于 2052 Buffers：
_db_block_hash_latches = power(2, trunc( log( 2, db_block_buffers - 4) - 1 ) )
2. 当Cache Buffers 多于 131075 buffers:
_db_block_hash_latches = power ( 2, trunc ( log( 2, db_block_buffers - 4 ) - 6))
3. 当Cache Buffers 位于 2052 和131075 之间时：
_db_block_hash_latches = 1024
从oracle 8i 开始， _db_block_hash_buckets 的数量较以前增加了8倍，而 _db_block_hash_latches 的数量增加有限。 这意味着每个Latch 需要管理多个Bucket，但是由于Bucket 数量的多倍增加，每个Bucket 上的Block数量得以减少，从而使得少量Latch 管理更多Bucket成为可能。
8。 X$bh
select * from (select addr, ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc) where rownum < 11
idle> /
ADDR TS# FILE# DBARFIL DBABLK TCH
-------- ---------- ---------- ---------- ---------- ----------
6081016C 0 1 1 1658 8003
6081016C 0 1 1 1657 7889
6081016C 0 1 1 1674 7787
6081016C 0 1 1 1666 719
6081016C 0 1 1 796 642
6081016C 0 1 1 794 640
6081016C 0 1 1 795 637
6081016C 0 1 1 651 633
6081016C 0 1 1 650 633
6081016C 0 1 1 92 632
10 rows selected.
再结合dba_extends,可以查询到这些热点Buffers来自哪些对象。
select e.owner, e.segment_name,e.segment_type from dba_extents e, ( select * from (select addr, ts#, file#,dbarfil, dbablk, tch from x$bh order by tch desc ) where rownum < 11 ) b where e.relative_fno = b.dbarfil and e.block_id <= b.dbablk and e.block_id + e.blocks > b.dbablk
idle> /
OWNER SEGMENT_NAME
------------------------------ ---------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
SYS SYSAUTH$
TABLE
SYS SYSAUTH$
TABLE
SYS JOB$
TABLE
SYS JOB$
TABLE
SYS C_USER#
CLUSTER
SYS I_SYSAUTH1
INDEX
SYS I_SYSAUTH1
INDEX
SYS I_SYSAUTH1
INDEX
SYS I_JOB_JOB
INDEX
SYS I_JOB_NEXT
INDEX
10 rows selected.
除了查询X$BH以外，也可以从Buffer Cache 的转储信息中，看到Buffer Header 的具体内容。
在Oracle 10g 之前，数据库的等待事件中，所有的Latch等待被归入latch free 等待中，在statspack 的report 中，如果top 5等待事件中看到latch free 这一等待事件处于较高的位置，需要介入研究和解决。
案例：
由于Latch Free 是一个汇总等待事件，需要从v$latch视图中获得具体的latch 竞争主要由哪些latch 引起的。
Cache Buffer Chains 正是主要latch 的竞争。
查看v$session_wait
如果需要具体的热点对象，可以从v$latch_children中查询具体的子Latch信息。
select * from
2 (select addr, child#, gets, misses, sleeps, immediate_gets igets, immediate_misses imiss, spin_gets sgets from v$latch_children where name = 'cache buffer chains' order by sleeps desc) where rownum < 11;
在X$BH中还有一个字段HLADDR，即Hash Chain Latch Addrss, 这个字段可以和v$latch_children 进行关联，这样就可以把具体的Latch 竞争和数据块关联起来，再结合dba_extents 视图，就可以找到具体的热点竞争对象。找到具体的热点竞争对象之后，可以结合v$sqlarea或者v$sqltext，找到频繁操作这些对象的SQL，然后对其进行优化。
select b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch,b.gets,b.misses,b.sleeps
2 from (select *
3 from (select addr,ts#,file#,dbarfil, dbablk,tch,hladdr from x$bh order by tch desc )
4 where rownum < 11 ) a,
5 (select addr,gets,misses,sleeps from v$latch_children where name = 'cache buffers chains') b
6* where a.hladdr = b.addr
idle> /
ADDR TS# DBARFIL DBABLK TCH GETS MISSES SLEEPS
-------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
5F9B7204 0 1 92 20 4022 0 0
5F98D5D8 0 1 794 20 1711 3 0
5F986140 0 1 1658 182 1529 0 0
5F981088 1 2 137 20 2046 0 0
5F966208 0 1 1674 175 1230 0 0
5F91FC74 0 1 795 20 1558 3 0
5F9120B8 0 1 282 23 3806 0 0
5F8E21E4 1 2 73 21 1382 0 0
5F8D4AA4 0 1 1657 179 1634 0 0
5F8C22AC 1 2 89 21 2117 0 0
10 rows selected.
利用这个SQL，可以找到热点Buffer对应的对象信息：
select distinct e.owner, e.segment_name, e.segment_type from dba_extents e,
(select *
from (select addr,ts#,file#,dbarfil, dbablk,tch from x$bh order by tch desc ) where rownum < 11 ) b
where e.relative_fno = b.dbarfil
and e.block_id <= b.dbablk
and e.block_id + e.blocks > b.dbablk
/
结合V$sqlarea 或者 v$sqltext 就可以找到操作这些对象的相关SQL：
select /*+ rule */ hash_value, sql_text from v$sqltext
where (hash_value ,address) in (
select a.hash_value, a.address from v$sqltext a,
(select distinct a.owner, a.segment_name, a.segment_type from dba_extents a,
(select dbarfil,dbablk from (select dbarfil, dbablk from x$bh order by tch desc)
where rownum < 11 ) b
where a.relative_fno = b.dbarfil
and a.block_id < b.dbablk
and a.block_id + a.blocks > b.dbablk ) b
where a.sql_text like '%' || b.segment_name || '%'
and b.segment_type = 'TABLE' )
ORDER BY hash_value, address, piece
/
找到SQL之后，剩下的问题，可以通过优化SQL减少数据访问，避免或者优化某些容易引起争用的操作 （如 Connect By）来减少热点。
9。 Shared Pool 基本原理
Shared Pool 是Oracle SGA 中最复杂也是最重要的一部分，Oracle通过Shared Pool 来实现SQL 共享、减少代码硬解析等，从而提高数据库性能。在某些版本中，如果设置不当，Shared Pool 可能会极大的影响数据的性能。
在Oracle 7 之前，Shared Pool 并不存在，每个Oracle 连接都有一个独立的Server进程与之相关联，Server进程负责解析，优化所有的SQl和PL/SQL代码。典型的，在OLTP环境中很多代码具有相同或者类似的结构，反复的独立解析浪费了大量的时间以及资源，Oracle 从PL/SQL开始尝试把这部分可共享进行独立的存储和管理，于是Shared Pool作为一个独立的SGA组建被引入，并且其作用和功能被逐渐的完善和发展起来。
Shared Pool 最初被引入的目的，也就是它的本质功能就是 实现共享 。 如果你的代码是完全异构的（假设你的代码从不绑定变量，从不反复执行），那么你发现你的Shared Pool 完全成了一个负担，它在徒劳无功的进行无谓的努力：保存代码，执行计划等期待重用，并且客户端要不停的获取latch，试题寻找共享代码，却始终一无所获。
大多数性能低下的系统都存在这样的通病：代码极少共享，缺乏或从不实行变量绑定。优化这些系统的根本办法是优化代码，使代码充分共享，减少无谓的反复硬/软解析
Shared Pool 大小通过参数 shared_pool_size 设置。一直以来，这是很有争议的。很多人建议可以把Shared pool 设置的稍大，易充分CAche 代码和减少ora-04031 错误的出现；另一方面，一些人不建议把Shared Pool 设置的过大，因为其可能带来管理上的额外负担。从而会影响数据库的性能。
转储Shared Pool 共享内存的内容：
alter session set events 'immediate trace name heapdump level 2';
Shared Pool 通过Free Lists 管理 Free 内存块 （Chunk），free 的内存块 （Chunk）按照不同size被划分到不同的Bucket进行管理。
初始时，数据库启动后，Shared Pool 多数是连续内存块。但是当空间分配使用以后，内存块开始被分割，碎片开始出现，Bucket 列表开始变长。
Oracle 请求Shared Pool 空间时，首先进入相应的Bucket进行查找。如果找不到，则转向下一个非空的Bucket，获取第一个Chunk，剩余的部分会进入相应的Bucket，进一步增加碎片。
最终的结果是，由于不停的分割，每个Bucket上的内存块会越来越多，越来越小。通常Bucket 0 的问题会最为显著。
通常，如果每个Bucket上的Chunk多于2000个，就被认为是Shared Pool 碎片过多。而Shared Pool 的碎片过多，是Shared Pool 产生性能问题的主要原因。
碎片过多会导致搜索Free List的时间过长，而Free List 的管理和搜索都需要获得和持有Latch。
Latch 是 Oracle 数据库内部提供的一种串行锁，通过串行机制保护共享内存不被并发更新/修改所损坏。Latch 的持有通常都很短暂（通常微妙级），但是对于一个繁忙的的数据库，这个串行机制往往成为极大的性能瓶颈。
如果Free Lists 链表过长，搜索这个链表的时间就会变长，从而导致Shared Pool Latch 被长时间持有，在一个繁忙的系统中，这会引起严重的Shared Pool Latch 竞争。在Oracle 9i之前，这个重要的Shared Pool Latch只有一个，所以长时间持有将会导致严重的性能问题。
而大多数情况下，用户都是请求相对小的内存块（Chunk），这个搜索Bucket 0往往消耗了大量的时间以及资源。Latch的争用成了一个严重的问题。
从Oracle 8.1.7 开始， Oracle 对Shared Pool的管理进行了改进。初始Oracle 分配了255个Bucket。
在Oracle 9i开始，Oracle 进一步改写了Shared Pool 管理的算法。最为显著的变化就是，对于数量众多的Chunk，Oracle 增加了更多的Bucket来管理。所以在Oracle 9i之前，如果盲目的增大shared_pool_size 或者设置过大的shared_pool_size 往往会适得其反。这也就是也也许曾经听过的“过大的Shared Pool会带来管理上的负担”。
在Oracle 9i 中，为了增大对于大共享池的支持，Shared Pool Latch 从原来的一个增加到现在的7个。
如果用户的系统有4个或者4个以上的CPU，并且shared_pool_size 大于250MB，那么Oracle可以把Shared Pool分割为多个子缓冲池进行管理，每个subpoll都拥有独立的结构、LRU和shared Pool Latch。
select addr, name, gets, misses,spin_gets from v$latch_children where name = 'shared pool';
ADDR NAME GETS MISSES SPIN_GETS
-------- -------------------------------------------------- ---------- ---------- ----------
200960D4 shared pool 177117 162 159
20096138 shared pool 12 0 0
2009619C shared pool 12 0 0
20096200 shared pool 12 0 0
20096264 shared pool 12 0 0
200962C8 shared pool 12 0 0
2009632C shared pool 12 0 0
7 rows selected.
子缓冲的数量由一个新引入的参数设置 _KGHDSIDX_count。
10. X$KSMSP
Shared Pool 的空间分配和使用情况，可以通过一个视图来观察， 即 X$KSMSP, Kernel Storage Memory Management SGA Heap. 其中每一行都代表着Shared Pool中的一个chunk.
desc x$ksmsp
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
KSMCHIDX NUMBER
KSMCHDUR NUMBER
KSMCHCOM VARCHAR2(16)
KSMCHPTR RAW(4)
KSMCHSIZ NUMBER
KSMCHCLS VARCHAR2(8)
KSMCHTYP NUMBER
KSMCHPAR
主要字段：
1. ksmchcom: 注释字段。每个内存块被分配之后，注释会添加到该字段。
2. ksmchsiz： size 字段
3. ksmchcls： 代表类型， 主要有4类：
1.free
Free chunks: 不包含任何对象的Chunk，可以不受限制的被自由分配。
2. recreate
Recreatable Chunks： 包含可以被临时移除内存的对象，在需要的时候，这个对象可以被重新创建。例如，许多存储共享SQL代码的内存都是可以重建的。
3. freeable
Freeable Chunks: 包含session 周期或调用的对象，随后可以被释放。这部分内存有时候可以全部或者部分提前释放，由于某些对象是中间过程产生的，这些对象不能被移除内存（因为不可重建）
4. perm
Permanent Memory Chunks： 包含永久对象，通常不能独立释放。
可以通过查询视图X$KSMSP来考察 Shared Pool 中存在的内存片数量。
如果数据库中存在大量的硬解析，不停的请求分配Free 的shared Pool内存，除了必须的Shared Pool Latch等竞争外，还不可避免的会导致Shared Pool中产生更多的内存碎片（当然在内存回收时，可能看到Chunk数量减少的情况）
创建一张临时表用以保存之前的x$ksmsp的状态：
create global temporary table e$ksmsp on commit preserve rows as
select a.ksmchcom,
sum(a.chunk) chunk,
sum(a.recr) recr,
sum(a.freeabl) freeabl,
sum(a.sum ) sum
from (select ksmchcom, count(ksmchcom) chunk,
decode(ksmchcls, 'recr',sum(ksmchsiz),null) recr,
decode (ksmchcls,'freeabl', sum(ksmchsiz), NULL) freeabl,
sum (ksmchsiz) SUM
from x$ksmsp group by ksmchcom, ksmchcls ) a
where 1=0
group by a.ksmchcom
/
保存当前Shared Pool状态：
Insert into E$ksmsp
select a.ksmchcom,
sum(a.chunk) chunk,
sum(a.recr) recr,
sum(a.freeabl) freeabl,
sum(a.sum) sum
from (select ksmchcom, count(ksmchcom) chunk ,
decode (ksmchcls, 'recr', sum(ksmchsiz), NULL ) recr,
decode (ksmchcls, 'freeabl',sum(ksmchsiz), NULL ) freeabl,
sum (ksmchsiz) sum
from x$ksmsp
group by ksmchcom, ksmchcls ) a
group by a.ksmchcom
/
比较前后 Shared Pool 内存分配变化：
select a.ksmchcom, a.chunk, a.sum, b.chunk, b.sum, (a.chunk - b.chunk) c_diff, (a.sum - b.sum) s_diff
from
( select a.ksmchcom,
sum(a.chunk ) chunk,
sum(a.recr) recr,
sum(a.freeabl) freeabl,
sum(a.sum) sum
from (select ksmchcom, count(ksmchcom) CHUNK,
decode(ksmchcls, 'recr', sum(ksmchsiz), NULL) recr,
decode (ksmchcls,'freeabl', sum(ksmchsiz), NULL) freeabl,
sum (ksmchsiz) sum
from x$ksmsp
group by ksmchcom, ksmchcls ) a
group by a.ksmchcom ) a, e$ksmsp b
where a.ksmchcom = b.ksmchcom and (a.chunk - b.chunk ) > 0
/
KSMCHCOM CHUNK SUM CHUNK SUM C_DIFF S_DIFF
---------------- ---------- ---------- ---------- ---------- ---------- ----------
free memory 313 12648252 308 12758040 5 -109788
Heap0: KGL 918 992180 917 991108 1 1072
KGL handles 3784 1815964 3776 1812096 8 3868
KGLS heap 2239 3114676 2238 3113604 1 1072
obj stat memo 410 124640 409 124336 1 304
KQR SO 548 337392 547 336852 1 540
PCursor 1193 1278896 1189 1274608 4 4288
CCursor 1938 2087040 1919 2066672 19 20368
library cache 1904 182824 1896 182056 8 768
sql area 1708 6997068 1690 6923340 18 73728
KQR PO 3997 2027052 3990 2023272 7 3780
11 rows selected.
11. 诊断和解决 ora-04031
Shared Pool 的根本问题只有一个：碎片过多带来的性能影响。
oerr ora 04031
04031, 00000, "unable to allocate %s bytes of shared memory ("%s","%s","%s","%s")"
// *Cause: More shared memory is needed than was allocated in the shared
// pool.
// *Action: If the shared pool is out of memory, either use the
// dbms_shared_pool package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// INIT.ORA parameters "shared_pool_reserved_size" and
// "shared_pool_size".
// If the large pool is out of memory, increase the INIT.ORA
// parameter "large_pool_size".
当尝试在共享池分配大块的连续内存失败，很多时候是由于碎片过多，而并非真正内存不足。Oracle 首先清除共享池总当前没有使用的对象，使空闲内存合并，如果仍没有足够大的单块内存可以满足需求，就会产生ORA-04031.
12. 绑定变量和 cursor sharing
如果shared Pool size 设置的足够大， 又可以排除Bug 的因素，那么大多数的Ora-04031 错误都是由共享池中的大量SQL代码等导致了内存碎片而引起的。可能的主要原因有：
1.SQL没有足够的共享
2. 大量的不必要的解析调用。
3. 没有使用绑定变量
实际上，应用的编写和调整始终是最重要的内容，Shared Pool的调整根本上要从应用入手。使用绑定变量可以充分的降低Shared pool 和library Cache 的Latch 竞争。
如果用户的应用没有很好的使用绑定变量，那么Oracle 从8.1.6开始提供的一个新的初始化参数用以在server 端进行强制变量绑定，这个参数就是cursor_sharing。
最初这个参数有两个可选设置 exact 和 force。
从Oracle 9i 开始， Oracle 引入了绑定变量的Peeking机制，SQL在第一次执行时，首先在session的PGA中使用具体值生成精确的执行计划，以期提高执行计划的准确性。然而Peeking 的方式只是在第一次硬解析的时候生效，所以仍然可能存在问题，导致后续的SQL错误执行。
同时，在Oracle 9i中，cursor_sharing 参数有了第三选项：similar。该参数指定Oracle在存在柱状图信息时，对于不同的变量值重新解析，从而可以利用柱状图更为精确的制定SQL执行计划。即当存在柱状图信息时，similar的表和和exact相同，当不存在柱状图时，similar 的表现和force相同。
13. 使用 flush shared pool
如果不能修改应用，或者不能强制绑定变量， 那么 oracle 提供了一种应急处理方法： 强制刷新共享池：
alter system flush shared_pool;
刷新共享池可以帮助合并碎片（small Chunks），强制老化SQL，释放共享池，但通常不推荐，因为：
1. Flush Shared Pool 会导致当前未使用的cursor被清除出共享池。如果这些SQL随后需要执行，那么数据库将经历大量的硬解析，系统将会经历严重的CPU争用，数据库将会产生激烈的latch竞争。
2. 如果应用没有绑定变量，大量的类似的SQL 不停的执行，那么Flush Shared Pool 可能只带来短暂的改善，数据库很快回到原来的状态。
3. 如果Shared Pool 很大，并且系统非常繁忙，刷新Shared Pool可能会导致系统挂起，对于类似系统尽量在空闲时进行。
从Oracle 9i 开始，oracle 的共享池算法发生了改变，Flush Shared Pool 的方法已经不再推荐使用了。
14 。Shared_pool_Reserved_size参数的设置以及作用。
该参数指定了保留的空想池空间，用于满足大的连续的共享池空间请求。
当共享池出现过多的碎片，请求大块空间会导致orace 大范围的查找并释放共享池内存来满足请求，由此可能带来严重的性能下降。设置设当的 shared_pool_reserved_size，结合 shared_pool_reserved_min_alloc 参数可以避免由此导致的性能下降。
这个参数的理想值应该大到足以满足任何对 reserved list 的内存请求，而无需数据库从共享池中刷新对象。这个参数的缺省值是 shared_pool_size 的 5%，通常这个参数的建议值为 shared_poo_size 的 10% ～ 20%， 最大不得超过 shared_pool_size 的 50%
。
shared_pool_reserved_min_alloc 这个参数控制保留内存的使用和分配。如果在共享内存池列表总请求一个足够尺寸的大块内存，但没有找到合适的空间，内存就从保留列表 中分配一块比这个参数值大的空间。
在oracle 9i 中，该参数的缺省值是4400，这是一个隐含参数。
@getp
Enter value for par: shared_pool_reserved_min_alloc
old 14: x.ksppinm like '%_&par%'
new 14: x.ksppinm like '%_shared_pool_reserved_min_alloc%'
NAME
--------------------------------------------------------------------------------
VALUE
----------------------------------------------------------------------------------------------------
ISDEFAULT ISMOD ISADJ
--------- ---------- -----
_shared_pool_reserved_min_alloc
4400
TRUE FALSE FALSE
这个参数的默认值对于大多数系统来说都足够了，如果系统经常出现ORA-04031的错误都是请求大于4400bytes的内存块，那么就需要增加 shared_pool_reserved_size 参数设置了。
如果主要的引发LRU合并、老化并出现ORA-04031 错误的内存请求在4100～4400 bytes之间，那么降低_shared_pool_reserved_min_alloc 同时适时增大 shared_pool_reserved_size 参数通常会有所帮助。设置 _shared_pool_reserved_min_alloc = 4100 可以增加 Shared Pool成功满足请求的概率。
查询 v$shared_pool_reserved 视图可以判断共享池问题的引发原因;
select free_space, avg_free_size, used_space, avg_used_size, request_failures, last_failure_size from v$shared_pool_reserved;
FREE_SPACE AVG_FREE_SIZE USED_SPACE AVG_USED_SIZE REQUEST_FAILURES LAST_FAILURE_SIZE
---------- ------------- ---------- ------------- ---------------- -----------------
3831984 212888 0 0 0 0
如果request_failures > 0 且 last_failure_size > shared_pool_reserved_min_alloc， 那么ORA-04031 错误可能是因为共享池保留空间缺少连续空间所致。要解决这个问题，可以考虑增大 _shared_pool_reserved_min_alloc 来降低缓冲池保留空间的对象数目，并增大 shared_pool_reserved_size 和 shared_pool_size 来加大共享池保留空间的可用内存。
如果 request_failures > 0 且 last_failure_size < shared_pool_reserved_min_alloc， 或者 request_failures = 0 且 last_failure_size < shared_pool_reserved_min_alloc，那么是由于在库高速缓冲缺少连续的空间而导致了ORA-04031 错误。对于这一类情况，应该考虑降低 shared_pool_reserved_min_alloc， 以放入更多的对象到共享池保留空间并加大shared_pool_size
次外，某些特定的SQL，较大 的指针或者较大的Package 都可能导致 ORA-04031 错误。在很多ERP软件中，这样的情况非常常见。在这种情况下，可以考虑把这个大的对象pin到共享池中，减少其动态请求，分配所带来的负担。
使用 dbms_shared_pool.keep 系统包可以把这些对象pin到内存中。 sys.standard、sys.dbms_standard 等都是常见的候选对象。
要使用dbms_shared_pool 系统包，首先需要运行 dbmspool.sql 脚本，该脚本会自动调用 prvpool.plb 脚本创建所需对象。
引发 ORA-04031 错误的因素还有很多，通过设置相关的参数 如： session_cached_cursors、 cursor_space_for_time 等也可以解决一些性能问题，并带来针对性的性能改善。
15. 模拟 ORA-04031
Oracle 9iR2 开始引入 段级统计信息 （Segment Statistics）收集的新特性，其中一个新引入的视图就是 v$segstat， 查寻该视图会引发 Shared Pool 内存泄漏。
16。 Library Cache pin 和 Library Cache Lock 分析
Oracle 使用两种数据结构来进行Shared Pool 的并发访问控制： lock 和 pin。 lock 比 pin 具有更高的级别。
lock 在handle 上获得，在pin 一个对象之前，首先获得该handle的锁定。锁定主要有3种模式： NUll，Share， Exclusive。在读取访问对象时，通常需要或缺NUll （空）模式以及 Share （共享）模式的锁定。在修改对象时，需要获得Exclusive（排他）锁定。
在锁定了Library Cache 对象之后，一个进程在访问之前，必须pin该对象。同样，pin 有3种模式：NUll， Shared， Exclusive。只读模式时获得共享 pin，修改模式时获得排他pin。
通常访问、执行过程和Package的时候，获得的都是共享pin，如果排他pin被持有，那么数据库此时就要产生等待。
在很多Statspack 的report 中，可能看到：
Library Cache lock 和 Library Cache pin
Library Cache pin 是用来管理Library Cache 的并发访问的，pin 一个object会引起相应的 heap 被载入内存中（如果此前没有被加载），pins 可以在三种模式下获得： Null，Share 和 Exclusive，可以认为 pin 是一种特殊的 锁。
当Library Cache Pin 等待事件出现时，通常说明该 pin 被其他用户以非兼容模式持有。
Library Cache pin 的等待时间是 3秒钟，其中 1秒 用于PMON后台进程，即在取得pin之前，最多等待3秒，否则就超时。
Library Cache Pin 的参数如下，有用的主要是 P1 和 P2：
P1 -- KGL handle address
P2 -- Pin address
P3 -- Encoded Mode & Namespace
Library Cache pin 通常发生在编译或者重新编译PL/SQL、view、Types 等Objects时，编译通常都是显性的，如安装应用程序、升级、安装补丁等，later、grant、 和 revoke 等操作也会使得Object变得无效，可以通过 Object 的 LAST_DDL_TIME 观察这些变化。
当Object 变得无效时，oracle 会在第一次访问此Object的时候试图重新编译它，如果此时其他session 已经把此 Object pin 到Library Cache 中，就会出现问题，特别是当有大量的活动的 session 并且存在复杂的 dependence 的时候。在某种情况下，重新编译 Object 可能会花几个小时，从而阻塞其他试图访问此 Object 的进程。
下面通过一个例子来模拟这个等待：
create or replace procedure pining
is
begin
NULL;
end;
/
create or replace procedure calling
is
begin
pining;
dbms_lock.sleep(3000);
end;
/
select sid, seq#,event,p1, p1raw, p2, p2raw, p3, p3raw, state from v$session_wait where event like 'library%';
SID SEQ# EVENT P1
---------- ---------- ---------------------------------------------------------------- ----------
P1RAW P2 P2RAW P3 P3RAW STATE
-------- ---------- -------- ---------- -------- -------------------
159 875 library cache pin 1530767612
5B3DA8FC 1576696368 5DFA7A30 301 0000012D WAITING
等待了3秒，并未发现SEQ#发生变化啊？？？？？？？
在这个输出总，P1 是 library Cache handle Address，Pn 字段是 十进制表示，PnRAW 是十六进制表示。
查询X$KGLOB 可以得到对象的具体信息：
X$KGLOB 代表 Kernel Generic Library Cache Object
col KGLNAOWN for a10
col KGLNAOBJ for a20
select ADDR, KGLHDADR, KGLHDPAR,KGLNAOWN,KGLNAOBJ, KGLNAHSH,KGLHDOBJ from x$kglob where kglhdadr ='5B3DA8FC';
ADDR KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNAHSH KGLHDOBJ
-------- -------- -------- ---------- -------------------- ---------- --------
40EE7AD8 5B3DA8FC 5B3DA8FC SYS PINING 2030642293 5C86B9E0
KGLNAHSH 代表对象的Hash value，由此可知，在pining 对象上正经历 Library Cache pin 的等待，然后引入另一个视图： X$KGLPN， 为 Kernel Genric Library Cache manager object PiNs
select a.sid, a.username, a.program, b.addr, b.kglpnadr, b.kglpnuse, b.kglpnses, b.kglpnhdl, b.kglpnlck, b.kglpnmod, b.kglpnreq from v$session a, x$kglpn b where a.saddr = b.kglpnuse and b.kglpnhdl = '5B3DA8FC' and b.kglpnmod > 0
sys@ORA10G> /
SID USERNAME PROGRAM ADDR
---------- ------------------------------ ------------------------------------------------ --------
KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGLPNMOD KGLPNREQ
-------- -------- -------- -------- -------- ---------- ----------
144 SYS sqlplus@arch (TNS V1-V3) 40F09DB0
5DFB5804 60582F88 60582F88 5B3DA8FC 5DAEE70C 2 0
通过联合 v$session，获得当前持有该handle 的用户信息。在本测试中，sid 为 144 的用户正在持有该handle。
select * from v$session_wait where sid = 144
sys@ORA10G> /
SID SEQ# EVENT
---------- ---------- ----------------------------------------------------------------
P1TEXT P1 P1RAW
---------------------------------------------------------------- ---------- --------
P2TEXT P2 P2RAW
---------------------------------------------------------------- ---------- --------
P3TEXT P3 P3RAW WAIT_CLASS_ID
---------------------------------------------------------------- ---------- -------- -------------
WAIT_CLASS# WAIT_CLASS WAIT_TIME
----------- ---------------------------------------------------------------- ----------
SECONDS_IN_WAIT STATE
--------------- -------------------
144 65 PL/SQL lock timer
duration 300000 000493E0
0 00
0 00 2723168908
6 Idle 0
1246 WAITING
由此可知，这个用户正在等待一次PL/SQL Lock Timer 计时。
得到了SID，就可以通过v$session.SQL_HASH_VALUE、v$session.SQL_ADDRESS 等字段关联 v$sqltext、 v$sqlarea 等视图获得当前session正在执行的操作：
select sql_text from v$sqlarea where v$sqlarea.hash_value = '3918317640';
SQL_TEXT
----------------------------------------------------------------------------------------------------
BEGIN calling; END;
接下来就应该去检查 calling 在做什么了。
简化一下：
1. Library Cache 等待对象：
select addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj from x$kglob where kglhdadr in ( select p1raw from v$session_wait where event like 'library%')
sys@ORA10G> /
ADDR KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNAHSH KGLHDOBJ
-------- -------- -------- ---------- -------------------- ---------- --------
6088F0DC 5B3DA8FC 5B3DA8FC SYS PINING 2030642293 5C86B9E0
2. 获取持有等待对象的 session 信息：
select a.sid, a.username, a.program, b.addr, b.kglpnadr, b.kglpnuse, b.kglpnses,b.kglpnhdl, b.kglpnlck, b.kglpnmod, b.kglpnreq
from v$session a, x$kglpn b
where a.saddr = b.kglpnuse and
b.kglpnmod <> 0
and b.kglpnhdl in ( select p1raw from v$session_wait where event like 'library%');
SID USERNAME PROGRAM ADDR
---------- ------------------------------ ------------------------------------------------ --------
KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGLPNMOD KGLPNREQ
-------- -------- -------- -------- -------- ---------- ----------
144 SYS sqlplus@arch (TNS V1-V3) 40F02D28
5DFB5804 60582F88 60582F88 5B3DA8FC 5DAEE70C 2 0
3. 获得持有对象用户执行的代码：
select sql_text
from v$sqlarea aa
where (aa.address, aa.hash_value ) in (
select sql_address, sql_hash_value
from v$session
where sid in (
select sid
from v$session a, x$kglpn b
where a.saddr = b.kglpnuse and
b.kglpnmod <> 0 and
b.kglpnhdl in (select p1raw
from v$session_wait where event like 'library%')));
SQL_TEXT
----------------------------------------------------------------------------------------------------
BEGIN calling; END;
在revoke之前和之后，可以转储一下 Shared Pool的内容，以进行观察和比较。
在grant 之前，
-- 在Object 上存在共享 pin
-- 在handle 上存在NUll 模式锁定，此模式允许其他用户继续以 NULL/shared 模式锁定该对象。
在 grant 之后，
-- 由于calling 执行未完成，在 Object 仍保持共享pin
-- 由于grant 会导致重新编译该对象，所以在 handle 上的排它锁已经被持有
-- 进一步的需要获得 Object 上的 exclusive Pin， 由于Shared Pin 被Calling 持有，所以Library Cache pin 等待出现。
实际上，recomplie 过程包含以下步骤，同时看一下lock 和pin 是如何交替发挥作用的
1. 存储过程的 library Cache Object 以排他模式被锁定，这个锁定是在handle 上获得的。Exclusive 锁定可以防止其他用户执行同样的操作，同时防止其他用户创建新的引用此过程的对象。
2. 以 Shared 模式 pin 该对象，以执行安全和错误检查。
3. 共享 pin 被释放，重新以排他模式 pin 该对象，执行重编译。
4. 使所有依赖该过程的对象失效。
5. 释放 Exclusive Lock 和 Exclusive Pin
17. Library Cache Lock 等待事件
如果此时再发出一条 grant 或 compile 命令，那么 Library Cache Lock 等待事件将会出现。
Session 3：
select * from v$session_wait where event like 'library%'
sys@ORA10G> /
SID SEQ# EVENT
---------- ---------- ----------------------------------------------------------------
P1TEXT P1 P1RAW
---------------------------------------------------------------- ---------- --------
P2TEXT P2 P2RAW
---------------------------------------------------------------- ---------- --------
P3TEXT P3 P3RAW WAIT_CLASS_ID
---------------------------------------------------------------- ---------- -------- -------------
WAIT_CLASS# WAIT_CLASS WAIT_TIME
----------- ---------------------------------------------------------------- ----------
SECONDS_IN_WAIT STATE
--------------- -------------------
145 1121 library cache lock
handle address 1530767612 5B3DA8FC
lock address 1576868480 5DFD1A80
100*mode+namespace 301 0000012D 3875070507
4 Concurrency 0
85 WAITING
159 958 library cache pin
handle address 1530767612 5B3DA8FC
pin address 1576874120 5DFD3088
100*mode+namespace 301 0000012D 3875070507
4 Concurrency 0
127 WAITING
由于handle 上的lock 已经被 session 2 以Exclusive模式持有，所以Session 3产生了等待。
可以看到，在生产数据库中权限的授予、对象的重新编译，都可能导致 Library Cache Pin 等待的出现。所以应该尽量避免在高峰期进行以上操作。
另外，测试本身就说明，如果package 或 过程 中存在复杂的、交互依赖的关系就极易导致Library Cache Pin的出现。在开发应用中，应当注意。
18。 version_count 过高造成的 Latch 竞争。
Shared Pool Latch 用于共享池内存空间的分配和回收。如果SQL没有充分共享，反复解析过程将是十分昂贵的。
Library Cache Latches 用于保护Cache 杂内存中的 SQL 以及 对象定义等，当需要向Library Cache 中增加新的SQL时，Library Cache latch 必须被获得。在解析SQl 过程中， Oracle 搜索 Library Cache 查询匹配的SQL，如果没有可共享的SQL代码， Oracle 将分析 SQL，获得 Library Cache Latch 向 Library Cache 中插入新的代码。
Library Cache latch 的数量受一个隐含 参数 _kgl_latch_count 的控制，其缺省值 大于或者等于 CPU_COUNTA 的素数，最大值不能超过 66.
简化一下SQL 执行过程，以说明 Latch 在 SQL 解析过程中所起的作用：
1. 首先需要获得 Library Cache Latch，根据 SQL 的 HASH_VALUE 值 在Library Cache 中寻找是否存在可共享代码。如果找的则为 软解析， Server 进程获得该 SQL 执行计划，转向 第 （4）步；如果找不到共享代码则执行硬解析。
2. 释放 Library Cache latch，获取 Shared Pool Latch,查找并锁定自由空间。
3. 释放 Shared Pool Latch，重新获得 Library Cache Latch，将 SQL 及执行计划插入 Library Cache 中。
4. 释放Library Cache Latch，保持NULL 模式的 Library Cache pin/Lock
5. 开始执行
可以看到，如果系统中存在过度的硬解析，系统的性能必然受到反复解析、latch 争用的折磨。
select name, value from v$sysstat where name like 'parse%'
sys@ORA10G> /
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 206
parse time elapsed 351439
parse count (total) 34089
parse count (hard) 1715
parse count (failures) 15
通过 [parse count (total) - parse count ( hard) ] / parse count ( total ) 得出的软解析率经常被用作衡量数据库性能的一个重要标准。
select sql_text, version_count, invalidations, parse_calls, optimizer_mode, parsing_user_id, parsing_schema_id, address, hash_value from v$sqlarea where version_count > 100;
应该注意 version_count 过高的SQL 语句，version_count 过高通常会导致Library Cache Latch的长时间持有， 从而影响性能。
最简单的，比如 scott 和 tiger 两个用户 同时执行：
select * from emp;
如果scott 和 tiger 各拥有一张 emp 表，那么这条 SQl 将存在两个子指针，而显然两者代码不能共享。所以 Oracle 虽然支持不同用户拥有同名对象，但还是应该尽量避免。
继续诊断，最直接的， dump 内存来看：
Oracle Bug 1210242
On certain SQL statement cursors are not shared when TIMED_STATISTICS is enabled.
碰巧这个数据库的TIMED_STATISTICS 设置为 TRUE，修改 false 之后，观察 v$sql，发现有效指针很快下降到两个。
对于这个问题，另一个可选的方法是设置一个隐含参数： _sqlexec_progression_cost = 0
该参数的具体含义为： SQL execution progression monitoring cost threadhold， 即SQL 执行进度监控成本脚本。
这个参数根据 COST 来决定监控 SQL。 执行进度监控会引入额外的函数调用和 Row Sources 这可能导致 SQL 的执行计划或成本发生改变，从而产生不同的子指针。
_sqlexec_progression_cost 的缺省值为1000，成本大于1000 的SQL 都会被监控，如果设置为0 ， 则那么SQL 的执行进度将不会被跟踪。
执行进度的监控信息被记录到 v$session_longops 视图中。如果 TIMED_STATISTICS 设置为 false， 则信心不会被记录。
所以，TIMED_STATISTICS 和 _sqlexec_progression_cost 是解决问题的两个途径。
select distinct(sql_text) from v$sql where optimizer_cost > 1000;
no rows selected
select sql_text,version_count from v$sqlarea where version_count > 10;
no rows selected
19. 在9i中遇到的一个问题，同样是 Library Cache Latch 和 Shared Pool Latch 竞争，类似的，在 v$sqlarea 发现大量的高 version_count 的 SQL。
对于version_count 过高的问题，可以查询 v$sql_shared_cursor 视图，这个视图会给出 SQL不能共享的具体原因。如果是正常因素导致的，相应的字段会被标记为“Y”；如果是异常情况，查询结果可能都是 “N”，这就表明Oracle 认为这种行为是正常的，在当前的系统设置下，这些SQL 不应该被共享，那么可以判断是某个参数引起的。
和 Cursor Sharing 关系最大的参数是：cursor_sharing。在这个案例中，cursor_sharing 被设置为similar，正正是这个导致了大量的子指针不能共享。
搜索 metalink，获得相关说明，当 cursor_sharing 的参数为 similar，并且数据库存在相关柱状图（Histogram）信息时，对于每一条新执行的SQL， Oracle 都通过硬解析以获得更为精确的执行计划，这导致了version_count 过高，这是 cursor_sharing = similar 的正常行为，并发bug。
了解之后，可以将cursor_sharing 设置为 exeact 或者 force以避免问题，或者可以删除柱状图信息来防止不必要的硬解析。实际上，如果数据库不存在失衡分布，也不必搜集柱状图信息。
20. 如果一个DB里面的几个存储过程总是跑不完，同样的存储过程在其他6个地方都正常。数据库里没有锁，数据库和server上的空间足够。正常情况下，几分钟就能运行完，运行了几个小时了还没完，为什么？
检查 v$session_wait, 看都有什么。如果系统慢，通常存在等待，v$session_wait是应该优先检查的视图。
[@more@]在Buffer Cache 中，Oracle 通过几个链表进行内存管理，其中最为人熟知的就是LRU list and Dirty list （也通常被成为write list，从Oracle 8i开始，因为算法变化，也被称为checkpoint queue），各种list存放的是指向具体的Buffer的指针。
数据库初始化时，所有的Buffer都被Hash到LRU list上，当需要从数据文件读数据的时，首先在LRU list 寻找Free 的Buffer，然后读取数据到Buffer Cache 中。当数据被修改后，状态变为dirty，就可以被移至dirty list（checkpoint queue）。Dirty list 上的都是候选的可以被DBWR写出到数据文件的Buffer。一个Buffer要么在LRU list 上，要么在dirty list上。
1. 当一个server 进程需要读数据到Buffer Cache中时候，首先需要判断该数据在Buffer中是否存在。如果存在且可用，则获取该数据。根据LRU算法在LRU list上移动该Block。如果Buffer中不存在该数据，则需要从数据文件中读取。
2.在读取数据之前，SErver进程需要扫描LRU list寻找free 的Buffer，扫描过程中server进程会发现的已有的被修改过的Buffer 移至Checkpoint Queue 上。 这些Dirty buffer 随后可以被写出到数据文件。
3. 如果checkpoint queue 超过了阈值，Server进程就会通知DBWn去写出脏数据，这也是触发DBWn写的一个条件。这个阈值是25%。也就是说，当检查点队列超过25%满就会触发DBWn的写操作。
select kvittag,kvitval,kvitdsc from x$kvit where kvittag = 'kcbldq';
KVITTAG KVITVAL
---------------------------------------------------------------- ----------
KVITDSC
----------------------------------------------------------------
kcbldq 25
large dirty queue if kcbclw reaches this
如果SErver进程扫描LRU超过一定的阈值仍然不能找到足够的Free Buffer，将停止寻找，转而通知DBWn去削除脏数据，释放内存空间。
同样，这个阈值可以在数据字典表找到，是40%。也就是说，当Server 进程扫描LRU超过40% 还没能找到足够的Free Buffer就会停止搜索，通知DBWn执行写出。这时进程会处于 free busy wait 等待。
select kvittag, kvitval, kvitdsc from x$kvit where kvittag ='kcbfsp'
idle> /
KVITTAG KVITVAL
---------------------------------------------------------------- ----------
KVITDSC
----------------------------------------------------------------
kcbfsp 40
Max percentage of LRU list foreground can scan for free
同时，因为增量检查点的引入，DBWn也会主动扫描LRU list，将发现的Dirty Buffer移至Checkpoint queue。这个扫描比例也受一个内部约束，在oracle 9iR2中，是25%
4. 找到足够的Buffer之后，SErver进程就可以将Buffer从数据文件读入Buffer Cache中。
5. 如果读取的Block不满足读一致性需求，则server进程需要通过当前Block版本和回滚段构造前镜像返回给用户。
从Oracle 8i开始，LRU list 和Dirty list 又分别增加了辅助list （auxiliary list）用于提高管理效率。引入辅助list之后，当数据库初始化的时候，Buffer 首先存放在LRU 辅助List上（auxiliary RPL_LST），当被使用后移动到LRU主list上(MAIN RPL_LST)，这样当用户搜索Free Buffer时候，就可以从LRU-AUX list 开始，而DBWn死哦所Dirty Buffer时，则可以从LRU-MAIN list 开始，从而提高搜索效率和数据性能。
可以通过如下命令转储Buffer CAche的内容，从而清晰的看到其数据结构。
alter session set events 'immediate trace name buffers level 4';
转储仅限于在测试环境中，生成的跟踪文件巨大，为了获得完整的跟踪文件，建议设置初始参数 max_dump_file_size 为unlimited
6.
当用户进程需要读数据到Buffer Cache时，或Cache Buffer根据LRU算法进行管理时，就不可避免的要扫描LRU list。 Buffer Cache 是共享内存，可以为众多并发进程访问，所以在搜索的过程中，必须获得latch，锁定内存结构，防止并发访问内存中的数据。
这个多顶LRU的latch 就是经常见到的Cache buffer LRU chain
select addr,latch#,name, gets,misses,immediate_gets, immediate_misses from v$latch where name = 'cache buffers lru chain';
ADDR LATCH# NAME GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES
-------- ---------- ------------------------------ ---------- ---------- -------------- ----------------
2000944C 117 cache buffers lru chain 23345 0 986 0
Cache Buffer Lru Chain Latch 存在多个子latch, 其数量受隐含参数 _db_block_lru_latches控制。
NAME VALUE
------------------------------ ------------------------------
DESCRIB
------------------------------------------------------------
_db_block_lru_latches 8
number of lru latches
从v$latch_children 视图看当前各子latch使用情况：
select addr,latch#,name, gets,misses,immediate_gets, immediate_misses from v$latch_children where name = 'cache buffers lru chain'
idle> /
ADDR LATCH# NAME GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES
-------- ---------- ------------------------------ ---------- ---------- -------------- ----------------
5FA02B3C 117 cache buffers lru chain 14 0 1 0
5FA02EC0 117 cache buffers lru chain 14 0 1 0
5FA03244 117 cache buffers lru chain 27350 0 981 0
5FA035C8 117 cache buffers lru chain 14 0 1 0
5FA0394C 117 cache buffers lru chain 14 0 1 0
5FA03CD0 117 cache buffers lru chain 14 0 1 0
5FA04054 117 cache buffers lru chain 14 0 1 0
5FA043D8 117 cache buffers lru chain 14 0 1 0
8 rows selected.
如果该latch 竞争激烈，通常有如下方法可以采用：
1. 适当增大Buffer Cache，这样可以减少数据到buffer Cache 的机会，减少扫描LRU List 的竞争。
2. 可以适当增加LRU latch的数量，修改_db_block_lru_latches 参数可是实现。但是该参数通常是足够的。
3. 通过多种缓冲池技术，可以减少不希望的数据老化和全表扫描等操作对default 池的冲击，从而可以减少竞争，
7. Cache Buffer Chain 闩锁竞争与解决
在LRU和dirty list 这两个内存结构外，Buffer Cache 的管理还存在另外两个重要的数据结构： Hash Bucket和Cache Buffer Chain
_spin_count
由于Buffer 根据Buffer Header进行散列，从而最终决定存入哪一个hash Bucket, 那么Hash Bucket 的数量在一定程度上，决定了每个Bucket中的Buffer数量，也就间接影响了搜索性能。
Hash Bucket 受 _db_block_hash_buckets 影响。 在Oracle 7 和Oracle 8 中，缺省值为 db_block_buffers/4 下的一个素数。在Oracle 8i中，该参数缺省为db_block_buffers * 2
对于每个Bucket，只存在一个chain,当用户试图搜索Cache Buffer Chain 时，必须首先获得 Cache Buffer Chain Latch。
在Oracle 8i 之前，对于每一个Bucket，Oracle 使用一个独立的hash latch 来维护。 其缺省Bucket 数量为 next_prime(db_block_buffers / 4)
从Oracle 8i 开始， _db_block_hash_buckets 增加到 db_block_buffers, 而 _db_block_hash_latches的数量也发生了变化 。
1. 当 Cache Buffer 少于 2052 Buffers：
_db_block_hash_latches = power(2, trunc( log( 2, db_block_buffers - 4) - 1 ) )
2. 当Cache Buffers 多于 131075 buffers:
_db_block_hash_latches = power ( 2, trunc ( log( 2, db_block_buffers - 4 ) - 6))
3. 当Cache Buffers 位于 2052 和131075 之间时：
_db_block_hash_latches = 1024
从oracle 8i 开始， _db_block_hash_buckets 的数量较以前增加了8倍，而 _db_block_hash_latches 的数量增加有限。 这意味着每个Latch 需要管理多个Bucket，但是由于Bucket 数量的多倍增加，每个Bucket 上的Block数量得以减少，从而使得少量Latch 管理更多Bucket成为可能。
8。 X$bh
select * from (select addr, ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc) where rownum < 11
idle> /
ADDR TS# FILE# DBARFIL DBABLK TCH
-------- ---------- ---------- ---------- ---------- ----------
6081016C 0 1 1 1658 8003
6081016C 0 1 1 1657 7889
6081016C 0 1 1 1674 7787
6081016C 0 1 1 1666 719
6081016C 0 1 1 796 642
6081016C 0 1 1 794 640
6081016C 0 1 1 795 637
6081016C 0 1 1 651 633
6081016C 0 1 1 650 633
6081016C 0 1 1 92 632
10 rows selected.
再结合dba_extends,可以查询到这些热点Buffers来自哪些对象。
select e.owner, e.segment_name,e.segment_type from dba_extents e, ( select * from (select addr, ts#, file#,dbarfil, dbablk, tch from x$bh order by tch desc ) where rownum < 11 ) b where e.relative_fno = b.dbarfil and e.block_id <= b.dbablk and e.block_id + e.blocks > b.dbablk
idle> /
OWNER SEGMENT_NAME
------------------------------ ---------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
SYS SYSAUTH$
TABLE
SYS SYSAUTH$
TABLE
SYS JOB$
TABLE
SYS JOB$
TABLE
SYS C_USER#
CLUSTER
SYS I_SYSAUTH1
INDEX
SYS I_SYSAUTH1
INDEX
SYS I_SYSAUTH1
INDEX
SYS I_JOB_JOB
INDEX
SYS I_JOB_NEXT
INDEX
10 rows selected.
除了查询X$BH以外，也可以从Buffer Cache 的转储信息中，看到Buffer Header 的具体内容。
在Oracle 10g 之前，数据库的等待事件中，所有的Latch等待被归入latch free 等待中，在statspack 的report 中，如果top 5等待事件中看到latch free 这一等待事件处于较高的位置，需要介入研究和解决。
案例：
由于Latch Free 是一个汇总等待事件，需要从v$latch视图中获得具体的latch 竞争主要由哪些latch 引起的。
Cache Buffer Chains 正是主要latch 的竞争。
查看v$session_wait
如果需要具体的热点对象，可以从v$latch_children中查询具体的子Latch信息。
select * from
2 (select addr, child#, gets, misses, sleeps, immediate_gets igets, immediate_misses imiss, spin_gets sgets from v$latch_children where name = 'cache buffer chains' order by sleeps desc) where rownum < 11;
在X$BH中还有一个字段HLADDR，即Hash Chain Latch Addrss, 这个字段可以和v$latch_children 进行关联，这样就可以把具体的Latch 竞争和数据块关联起来，再结合dba_extents 视图，就可以找到具体的热点竞争对象。找到具体的热点竞争对象之后，可以结合v$sqlarea或者v$sqltext，找到频繁操作这些对象的SQL，然后对其进行优化。
select b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch,b.gets,b.misses,b.sleeps
2 from (select *
3 from (select addr,ts#,file#,dbarfil, dbablk,tch,hladdr from x$bh order by tch desc )
4 where rownum < 11 ) a,
5 (select addr,gets,misses,sleeps from v$latch_children where name = 'cache buffers chains') b
6* where a.hladdr = b.addr
idle> /
ADDR TS# DBARFIL DBABLK TCH GETS MISSES SLEEPS
-------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
5F9B7204 0 1 92 20 4022 0 0
5F98D5D8 0 1 794 20 1711 3 0
5F986140 0 1 1658 182 1529 0 0
5F981088 1 2 137 20 2046 0 0
5F966208 0 1 1674 175 1230 0 0
5F91FC74 0 1 795 20 1558 3 0
5F9120B8 0 1 282 23 3806 0 0
5F8E21E4 1 2 73 21 1382 0 0
5F8D4AA4 0 1 1657 179 1634 0 0
5F8C22AC 1 2 89 21 2117 0 0
10 rows selected.
利用这个SQL，可以找到热点Buffer对应的对象信息：
select distinct e.owner, e.segment_name, e.segment_type from dba_extents e,
(select *
from (select addr,ts#,file#,dbarfil, dbablk,tch from x$bh order by tch desc ) where rownum < 11 ) b
where e.relative_fno = b.dbarfil
and e.block_id <= b.dbablk
and e.block_id + e.blocks > b.dbablk
/
结合V$sqlarea 或者 v$sqltext 就可以找到操作这些对象的相关SQL：
select /*+ rule */ hash_value, sql_text from v$sqltext
where (hash_value ,address) in (
select a.hash_value, a.address from v$sqltext a,
(select distinct a.owner, a.segment_name, a.segment_type from dba_extents a,
(select dbarfil,dbablk from (select dbarfil, dbablk from x$bh order by tch desc)
where rownum < 11 ) b
where a.relative_fno = b.dbarfil
and a.block_id < b.dbablk
and a.block_id + a.blocks > b.dbablk ) b
where a.sql_text like '%' || b.segment_name || '%'
and b.segment_type = 'TABLE' )
ORDER BY hash_value, address, piece
/
找到SQL之后，剩下的问题，可以通过优化SQL减少数据访问，避免或者优化某些容易引起争用的操作 （如 Connect By）来减少热点。
9。 Shared Pool 基本原理
Shared Pool 是Oracle SGA 中最复杂也是最重要的一部分，Oracle通过Shared Pool 来实现SQL 共享、减少代码硬解析等，从而提高数据库性能。在某些版本中，如果设置不当，Shared Pool 可能会极大的影响数据的性能。
在Oracle 7 之前，Shared Pool 并不存在，每个Oracle 连接都有一个独立的Server进程与之相关联，Server进程负责解析，优化所有的SQl和PL/SQL代码。典型的，在OLTP环境中很多代码具有相同或者类似的结构，反复的独立解析浪费了大量的时间以及资源，Oracle 从PL/SQL开始尝试把这部分可共享进行独立的存储和管理，于是Shared Pool作为一个独立的SGA组建被引入，并且其作用和功能被逐渐的完善和发展起来。
Shared Pool 最初被引入的目的，也就是它的本质功能就是 实现共享 。 如果你的代码是完全异构的（假设你的代码从不绑定变量，从不反复执行），那么你发现你的Shared Pool 完全成了一个负担，它在徒劳无功的进行无谓的努力：保存代码，执行计划等期待重用，并且客户端要不停的获取latch，试题寻找共享代码，却始终一无所获。
大多数性能低下的系统都存在这样的通病：代码极少共享，缺乏或从不实行变量绑定。优化这些系统的根本办法是优化代码，使代码充分共享，减少无谓的反复硬/软解析
Shared Pool 大小通过参数 shared_pool_size 设置。一直以来，这是很有争议的。很多人建议可以把Shared pool 设置的稍大，易充分CAche 代码和减少ora-04031 错误的出现；另一方面，一些人不建议把Shared Pool 设置的过大，因为其可能带来管理上的额外负担。从而会影响数据库的性能。
转储Shared Pool 共享内存的内容：
alter session set events 'immediate trace name heapdump level 2';
Shared Pool 通过Free Lists 管理 Free 内存块 （Chunk），free 的内存块 （Chunk）按照不同size被划分到不同的Bucket进行管理。
初始时，数据库启动后，Shared Pool 多数是连续内存块。但是当空间分配使用以后，内存块开始被分割，碎片开始出现，Bucket 列表开始变长。
Oracle 请求Shared Pool 空间时，首先进入相应的Bucket进行查找。如果找不到，则转向下一个非空的Bucket，获取第一个Chunk，剩余的部分会进入相应的Bucket，进一步增加碎片。
最终的结果是，由于不停的分割，每个Bucket上的内存块会越来越多，越来越小。通常Bucket 0 的问题会最为显著。
通常，如果每个Bucket上的Chunk多于2000个，就被认为是Shared Pool 碎片过多。而Shared Pool 的碎片过多，是Shared Pool 产生性能问题的主要原因。
碎片过多会导致搜索Free List的时间过长，而Free List 的管理和搜索都需要获得和持有Latch。
Latch 是 Oracle 数据库内部提供的一种串行锁，通过串行机制保护共享内存不被并发更新/修改所损坏。Latch 的持有通常都很短暂（通常微妙级），但是对于一个繁忙的的数据库，这个串行机制往往成为极大的性能瓶颈。
如果Free Lists 链表过长，搜索这个链表的时间就会变长，从而导致Shared Pool Latch 被长时间持有，在一个繁忙的系统中，这会引起严重的Shared Pool Latch 竞争。在Oracle 9i之前，这个重要的Shared Pool Latch只有一个，所以长时间持有将会导致严重的性能问题。
而大多数情况下，用户都是请求相对小的内存块（Chunk），这个搜索Bucket 0往往消耗了大量的时间以及资源。Latch的争用成了一个严重的问题。
从Oracle 8.1.7 开始， Oracle 对Shared Pool的管理进行了改进。初始Oracle 分配了255个Bucket。
在Oracle 9i开始，Oracle 进一步改写了Shared Pool 管理的算法。最为显著的变化就是，对于数量众多的Chunk，Oracle 增加了更多的Bucket来管理。所以在Oracle 9i之前，如果盲目的增大shared_pool_size 或者设置过大的shared_pool_size 往往会适得其反。这也就是也也许曾经听过的“过大的Shared Pool会带来管理上的负担”。
在Oracle 9i 中，为了增大对于大共享池的支持，Shared Pool Latch 从原来的一个增加到现在的7个。
如果用户的系统有4个或者4个以上的CPU，并且shared_pool_size 大于250MB，那么Oracle可以把Shared Pool分割为多个子缓冲池进行管理，每个subpoll都拥有独立的结构、LRU和shared Pool Latch。
select addr, name, gets, misses,spin_gets from v$latch_children where name = 'shared pool';
ADDR NAME GETS MISSES SPIN_GETS
-------- -------------------------------------------------- ---------- ---------- ----------
200960D4 shared pool 177117 162 159
20096138 shared pool 12 0 0
2009619C shared pool 12 0 0
20096200 shared pool 12 0 0
20096264 shared pool 12 0 0
200962C8 shared pool 12 0 0
2009632C shared pool 12 0 0
7 rows selected.
子缓冲的数量由一个新引入的参数设置 _KGHDSIDX_count。
数据库初始化时，所有的Buffer都被Hash到LRU list上，当需要从数据文件读数据的时，首先在LRU list 寻找Free 的Buffer，然后读取数据到Buffer Cache 中。当数据被修改后，状态变为dirty，就可以被移至dirty list（checkpoint queue）。Dirty list 上的都是候选的可以被DBWR写出到数据文件的Buffer。一个Buffer要么在LRU list 上，要么在dirty list上。
1. 当一个server 进程需要读数据到Buffer Cache中时候，首先需要判断该数据在Buffer中是否存在。如果存在且可用，则获取该数据。根据LRU算法在LRU list上移动该Block。如果Buffer中不存在该数据，则需要从数据文件中读取。
2.在读取数据之前，SErver进程需要扫描LRU list寻找free 的Buffer，扫描过程中server进程会发现的已有的被修改过的Buffer 移至Checkpoint Queue 上。 这些Dirty buffer 随后可以被写出到数据文件。
3. 如果checkpoint queue 超过了阈值，Server进程就会通知DBWn去写出脏数据，这也是触发DBWn写的一个条件。这个阈值是25%。也就是说，当检查点队列超过25%满就会触发DBWn的写操作。
select kvittag,kvitval,kvitdsc from x$kvit where kvittag = 'kcbldq';
KVITTAG KVITVAL
---------------------------------------------------------------- ----------
KVITDSC
----------------------------------------------------------------
kcbldq 25
large dirty queue if kcbclw reaches this
如果SErver进程扫描LRU超过一定的阈值仍然不能找到足够的Free Buffer，将停止寻找，转而通知DBWn去削除脏数据，释放内存空间。
同样，这个阈值可以在数据字典表找到，是40%。也就是说，当Server 进程扫描LRU超过40% 还没能找到足够的Free Buffer就会停止搜索，通知DBWn执行写出。这时进程会处于 free busy wait 等待。
select kvittag, kvitval, kvitdsc from x$kvit where kvittag ='kcbfsp'
idle> /
KVITTAG KVITVAL
---------------------------------------------------------------- ----------
KVITDSC
----------------------------------------------------------------
kcbfsp 40
Max percentage of LRU list foreground can scan for free
同时，因为增量检查点的引入，DBWn也会主动扫描LRU list，将发现的Dirty Buffer移至Checkpoint queue。这个扫描比例也受一个内部约束，在oracle 9iR2中，是25%
4. 找到足够的Buffer之后，SErver进程就可以将Buffer从数据文件读入Buffer Cache中。
5. 如果读取的Block不满足读一致性需求，则server进程需要通过当前Block版本和回滚段构造前镜像返回给用户。
从Oracle 8i开始，LRU list 和Dirty list 又分别增加了辅助list （auxiliary list）用于提高管理效率。引入辅助list之后，当数据库初始化的时候，Buffer 首先存放在LRU 辅助List上（auxiliary RPL_LST），当被使用后移动到LRU主list上(MAIN RPL_LST)，这样当用户搜索Free Buffer时候，就可以从LRU-AUX list 开始，而DBWn死哦所Dirty Buffer时，则可以从LRU-MAIN list 开始，从而提高搜索效率和数据性能。
可以通过如下命令转储Buffer CAche的内容，从而清晰的看到其数据结构。
alter session set events 'immediate trace name buffers level 4';
转储仅限于在测试环境中，生成的跟踪文件巨大，为了获得完整的跟踪文件，建议设置初始参数 max_dump_file_size 为unlimited
6.
当用户进程需要读数据到Buffer Cache时，或Cache Buffer根据LRU算法进行管理时，就不可避免的要扫描LRU list。 Buffer Cache 是共享内存，可以为众多并发进程访问，所以在搜索的过程中，必须获得latch，锁定内存结构，防止并发访问内存中的数据。
这个多顶LRU的latch 就是经常见到的Cache buffer LRU chain
select addr,latch#,name, gets,misses,immediate_gets, immediate_misses from v$latch where name = 'cache buffers lru chain';
ADDR LATCH# NAME GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES
-------- ---------- ------------------------------ ---------- ---------- -------------- ----------------
2000944C 117 cache buffers lru chain 23345 0 986 0
Cache Buffer Lru Chain Latch 存在多个子latch, 其数量受隐含参数 _db_block_lru_latches控制。
NAME VALUE
------------------------------ ------------------------------
DESCRIB
------------------------------------------------------------
_db_block_lru_latches 8
number of lru latches
从v$latch_children 视图看当前各子latch使用情况：
select addr,latch#,name, gets,misses,immediate_gets, immediate_misses from v$latch_children where name = 'cache buffers lru chain'
idle> /
ADDR LATCH# NAME GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES
-------- ---------- ------------------------------ ---------- ---------- -------------- ----------------
5FA02B3C 117 cache buffers lru chain 14 0 1 0
5FA02EC0 117 cache buffers lru chain 14 0 1 0
5FA03244 117 cache buffers lru chain 27350 0 981 0
5FA035C8 117 cache buffers lru chain 14 0 1 0
5FA0394C 117 cache buffers lru chain 14 0 1 0
5FA03CD0 117 cache buffers lru chain 14 0 1 0
5FA04054 117 cache buffers lru chain 14 0 1 0
5FA043D8 117 cache buffers lru chain 14 0 1 0
8 rows selected.
如果该latch 竞争激烈，通常有如下方法可以采用：
1. 适当增大Buffer Cache，这样可以减少数据到buffer Cache 的机会，减少扫描LRU List 的竞争。
2. 可以适当增加LRU latch的数量，修改_db_block_lru_latches 参数可是实现。但是该参数通常是足够的。
3. 通过多种缓冲池技术，可以减少不希望的数据老化和全表扫描等操作对default 池的冲击，从而可以减少竞争，
7. Cache Buffer Chain 闩锁竞争与解决
在LRU和dirty list 这两个内存结构外，Buffer Cache 的管理还存在另外两个重要的数据结构： Hash Bucket和Cache Buffer Chain
_spin_count
由于Buffer 根据Buffer Header进行散列，从而最终决定存入哪一个hash Bucket, 那么Hash Bucket 的数量在一定程度上，决定了每个Bucket中的Buffer数量，也就间接影响了搜索性能。
Hash Bucket 受 _db_block_hash_buckets 影响。 在Oracle 7 和Oracle 8 中，缺省值为 db_block_buffers/4 下的一个素数。在Oracle 8i中，该参数缺省为db_block_buffers * 2
对于每个Bucket，只存在一个chain,当用户试图搜索Cache Buffer Chain 时，必须首先获得 Cache Buffer Chain Latch。
在Oracle 8i 之前，对于每一个Bucket，Oracle 使用一个独立的hash latch 来维护。 其缺省Bucket 数量为 next_prime(db_block_buffers / 4)
从Oracle 8i 开始， _db_block_hash_buckets 增加到 db_block_buffers, 而 _db_block_hash_latches的数量也发生了变化 。
1. 当 Cache Buffer 少于 2052 Buffers：
_db_block_hash_latches = power(2, trunc( log( 2, db_block_buffers - 4) - 1 ) )
2. 当Cache Buffers 多于 131075 buffers:
_db_block_hash_latches = power ( 2, trunc ( log( 2, db_block_buffers - 4 ) - 6))
3. 当Cache Buffers 位于 2052 和131075 之间时：
_db_block_hash_latches = 1024
从oracle 8i 开始， _db_block_hash_buckets 的数量较以前增加了8倍，而 _db_block_hash_latches 的数量增加有限。 这意味着每个Latch 需要管理多个Bucket，但是由于Bucket 数量的多倍增加，每个Bucket 上的Block数量得以减少，从而使得少量Latch 管理更多Bucket成为可能。
8。 X$bh
select * from (select addr, ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc) where rownum < 11
idle> /
ADDR TS# FILE# DBARFIL DBABLK TCH
-------- ---------- ---------- ---------- ---------- ----------
6081016C 0 1 1 1658 8003
6081016C 0 1 1 1657 7889
6081016C 0 1 1 1674 7787
6081016C 0 1 1 1666 719
6081016C 0 1 1 796 642
6081016C 0 1 1 794 640
6081016C 0 1 1 795 637
6081016C 0 1 1 651 633
6081016C 0 1 1 650 633
6081016C 0 1 1 92 632
10 rows selected.
再结合dba_extends,可以查询到这些热点Buffers来自哪些对象。
select e.owner, e.segment_name,e.segment_type from dba_extents e, ( select * from (select addr, ts#, file#,dbarfil, dbablk, tch from x$bh order by tch desc ) where rownum < 11 ) b where e.relative_fno = b.dbarfil and e.block_id <= b.dbablk and e.block_id + e.blocks > b.dbablk
idle> /
OWNER SEGMENT_NAME
------------------------------ ---------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
SYS SYSAUTH$
TABLE
SYS SYSAUTH$
TABLE
SYS JOB$
TABLE
SYS JOB$
TABLE
SYS C_USER#
CLUSTER
SYS I_SYSAUTH1
INDEX
SYS I_SYSAUTH1
INDEX
SYS I_SYSAUTH1
INDEX
SYS I_JOB_JOB
INDEX
SYS I_JOB_NEXT
INDEX
10 rows selected.
除了查询X$BH以外，也可以从Buffer Cache 的转储信息中，看到Buffer Header 的具体内容。
在Oracle 10g 之前，数据库的等待事件中，所有的Latch等待被归入latch free 等待中，在statspack 的report 中，如果top 5等待事件中看到latch free 这一等待事件处于较高的位置，需要介入研究和解决。
案例：
由于Latch Free 是一个汇总等待事件，需要从v$latch视图中获得具体的latch 竞争主要由哪些latch 引起的。
Cache Buffer Chains 正是主要latch 的竞争。
查看v$session_wait
如果需要具体的热点对象，可以从v$latch_children中查询具体的子Latch信息。
select * from
2 (select addr, child#, gets, misses, sleeps, immediate_gets igets, immediate_misses imiss, spin_gets sgets from v$latch_children where name = 'cache buffer chains' order by sleeps desc) where rownum < 11;
在X$BH中还有一个字段HLADDR，即Hash Chain Latch Addrss, 这个字段可以和v$latch_children 进行关联，这样就可以把具体的Latch 竞争和数据块关联起来，再结合dba_extents 视图，就可以找到具体的热点竞争对象。找到具体的热点竞争对象之后，可以结合v$sqlarea或者v$sqltext，找到频繁操作这些对象的SQL，然后对其进行优化。
select b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch,b.gets,b.misses,b.sleeps
2 from (select *
3 from (select addr,ts#,file#,dbarfil, dbablk,tch,hladdr from x$bh order by tch desc )
4 where rownum < 11 ) a,
5 (select addr,gets,misses,sleeps from v$latch_children where name = 'cache buffers chains') b
6* where a.hladdr = b.addr
idle> /
ADDR TS# DBARFIL DBABLK TCH GETS MISSES SLEEPS
-------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
5F9B7204 0 1 92 20 4022 0 0
5F98D5D8 0 1 794 20 1711 3 0
5F986140 0 1 1658 182 1529 0 0
5F981088 1 2 137 20 2046 0 0
5F966208 0 1 1674 175 1230 0 0
5F91FC74 0 1 795 20 1558 3 0
5F9120B8 0 1 282 23 3806 0 0
5F8E21E4 1 2 73 21 1382 0 0
5F8D4AA4 0 1 1657 179 1634 0 0
5F8C22AC 1 2 89 21 2117 0 0
10 rows selected.
利用这个SQL，可以找到热点Buffer对应的对象信息：
select distinct e.owner, e.segment_name, e.segment_type from dba_extents e,
(select *
from (select addr,ts#,file#,dbarfil, dbablk,tch from x$bh order by tch desc ) where rownum < 11 ) b
where e.relative_fno = b.dbarfil
and e.block_id <= b.dbablk
and e.block_id + e.blocks > b.dbablk
/
结合V$sqlarea 或者 v$sqltext 就可以找到操作这些对象的相关SQL：
select /*+ rule */ hash_value, sql_text from v$sqltext
where (hash_value ,address) in (
select a.hash_value, a.address from v$sqltext a,
(select distinct a.owner, a.segment_name, a.segment_type from dba_extents a,
(select dbarfil,dbablk from (select dbarfil, dbablk from x$bh order by tch desc)
where rownum < 11 ) b
where a.relative_fno = b.dbarfil
and a.block_id < b.dbablk
and a.block_id + a.blocks > b.dbablk ) b
where a.sql_text like '%' || b.segment_name || '%'
and b.segment_type = 'TABLE' )
ORDER BY hash_value, address, piece
/
找到SQL之后，剩下的问题，可以通过优化SQL减少数据访问，避免或者优化某些容易引起争用的操作 （如 Connect By）来减少热点。
9。 Shared Pool 基本原理
Shared Pool 是Oracle SGA 中最复杂也是最重要的一部分，Oracle通过Shared Pool 来实现SQL 共享、减少代码硬解析等，从而提高数据库性能。在某些版本中，如果设置不当，Shared Pool 可能会极大的影响数据的性能。
在Oracle 7 之前，Shared Pool 并不存在，每个Oracle 连接都有一个独立的Server进程与之相关联，Server进程负责解析，优化所有的SQl和PL/SQL代码。典型的，在OLTP环境中很多代码具有相同或者类似的结构，反复的独立解析浪费了大量的时间以及资源，Oracle 从PL/SQL开始尝试把这部分可共享进行独立的存储和管理，于是Shared Pool作为一个独立的SGA组建被引入，并且其作用和功能被逐渐的完善和发展起来。
Shared Pool 最初被引入的目的，也就是它的本质功能就是 实现共享 。 如果你的代码是完全异构的（假设你的代码从不绑定变量，从不反复执行），那么你发现你的Shared Pool 完全成了一个负担，它在徒劳无功的进行无谓的努力：保存代码，执行计划等期待重用，并且客户端要不停的获取latch，试题寻找共享代码，却始终一无所获。
大多数性能低下的系统都存在这样的通病：代码极少共享，缺乏或从不实行变量绑定。优化这些系统的根本办法是优化代码，使代码充分共享，减少无谓的反复硬/软解析
Shared Pool 大小通过参数 shared_pool_size 设置。一直以来，这是很有争议的。很多人建议可以把Shared pool 设置的稍大，易充分CAche 代码和减少ora-04031 错误的出现；另一方面，一些人不建议把Shared Pool 设置的过大，因为其可能带来管理上的额外负担。从而会影响数据库的性能。
转储Shared Pool 共享内存的内容：
alter session set events 'immediate trace name heapdump level 2';
Shared Pool 通过Free Lists 管理 Free 内存块 （Chunk），free 的内存块 （Chunk）按照不同size被划分到不同的Bucket进行管理。
初始时，数据库启动后，Shared Pool 多数是连续内存块。但是当空间分配使用以后，内存块开始被分割，碎片开始出现，Bucket 列表开始变长。
Oracle 请求Shared Pool 空间时，首先进入相应的Bucket进行查找。如果找不到，则转向下一个非空的Bucket，获取第一个Chunk，剩余的部分会进入相应的Bucket，进一步增加碎片。
最终的结果是，由于不停的分割，每个Bucket上的内存块会越来越多，越来越小。通常Bucket 0 的问题会最为显著。
通常，如果每个Bucket上的Chunk多于2000个，就被认为是Shared Pool 碎片过多。而Shared Pool 的碎片过多，是Shared Pool 产生性能问题的主要原因。
碎片过多会导致搜索Free List的时间过长，而Free List 的管理和搜索都需要获得和持有Latch。
Latch 是 Oracle 数据库内部提供的一种串行锁，通过串行机制保护共享内存不被并发更新/修改所损坏。Latch 的持有通常都很短暂（通常微妙级），但是对于一个繁忙的的数据库，这个串行机制往往成为极大的性能瓶颈。
如果Free Lists 链表过长，搜索这个链表的时间就会变长，从而导致Shared Pool Latch 被长时间持有，在一个繁忙的系统中，这会引起严重的Shared Pool Latch 竞争。在Oracle 9i之前，这个重要的Shared Pool Latch只有一个，所以长时间持有将会导致严重的性能问题。
而大多数情况下，用户都是请求相对小的内存块（Chunk），这个搜索Bucket 0往往消耗了大量的时间以及资源。Latch的争用成了一个严重的问题。
从Oracle 8.1.7 开始， Oracle 对Shared Pool的管理进行了改进。初始Oracle 分配了255个Bucket。
在Oracle 9i开始，Oracle 进一步改写了Shared Pool 管理的算法。最为显著的变化就是，对于数量众多的Chunk，Oracle 增加了更多的Bucket来管理。所以在Oracle 9i之前，如果盲目的增大shared_pool_size 或者设置过大的shared_pool_size 往往会适得其反。这也就是也也许曾经听过的“过大的Shared Pool会带来管理上的负担”。
在Oracle 9i 中，为了增大对于大共享池的支持，Shared Pool Latch 从原来的一个增加到现在的7个。
如果用户的系统有4个或者4个以上的CPU，并且shared_pool_size 大于250MB，那么Oracle可以把Shared Pool分割为多个子缓冲池进行管理，每个subpoll都拥有独立的结构、LRU和shared Pool Latch。
select addr, name, gets, misses,spin_gets from v$latch_children where name = 'shared pool';
ADDR NAME GETS MISSES SPIN_GETS
-------- -------------------------------------------------- ---------- ---------- ----------
200960D4 shared pool 177117 162 159
20096138 shared pool 12 0 0
2009619C shared pool 12 0 0
20096200 shared pool 12 0 0
20096264 shared pool 12 0 0
200962C8 shared pool 12 0 0
2009632C shared pool 12 0 0
7 rows selected.
子缓冲的数量由一个新引入的参数设置 _KGHDSIDX_count。
10. X$KSMSP
Shared Pool 的空间分配和使用情况，可以通过一个视图来观察， 即 X$KSMSP, Kernel Storage Memory Management SGA Heap. 其中每一行都代表着Shared Pool中的一个chunk.
desc x$ksmsp
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
KSMCHIDX NUMBER
KSMCHDUR NUMBER
KSMCHCOM VARCHAR2(16)
KSMCHPTR RAW(4)
KSMCHSIZ NUMBER
KSMCHCLS VARCHAR2(8)
KSMCHTYP NUMBER
KSMCHPAR
主要字段：
1. ksmchcom: 注释字段。每个内存块被分配之后，注释会添加到该字段。
2. ksmchsiz： size 字段
3. ksmchcls： 代表类型， 主要有4类：
1.free
Free chunks: 不包含任何对象的Chunk，可以不受限制的被自由分配。
2. recreate
Recreatable Chunks： 包含可以被临时移除内存的对象，在需要的时候，这个对象可以被重新创建。例如，许多存储共享SQL代码的内存都是可以重建的。
3. freeable
Freeable Chunks: 包含session 周期或调用的对象，随后可以被释放。这部分内存有时候可以全部或者部分提前释放，由于某些对象是中间过程产生的，这些对象不能被移除内存（因为不可重建）
4. perm
Permanent Memory Chunks： 包含永久对象，通常不能独立释放。
可以通过查询视图X$KSMSP来考察 Shared Pool 中存在的内存片数量。
如果数据库中存在大量的硬解析，不停的请求分配Free 的shared Pool内存，除了必须的Shared Pool Latch等竞争外，还不可避免的会导致Shared Pool中产生更多的内存碎片（当然在内存回收时，可能看到Chunk数量减少的情况）
创建一张临时表用以保存之前的x$ksmsp的状态：
create global temporary table e$ksmsp on commit preserve rows as
select a.ksmchcom,
sum(a.chunk) chunk,
sum(a.recr) recr,
sum(a.freeabl) freeabl,
sum(a.sum ) sum
from (select ksmchcom, count(ksmchcom) chunk,
decode(ksmchcls, 'recr',sum(ksmchsiz),null) recr,
decode (ksmchcls,'freeabl', sum(ksmchsiz), NULL) freeabl,
sum (ksmchsiz) SUM
from x$ksmsp group by ksmchcom, ksmchcls ) a
where 1=0
group by a.ksmchcom
/
保存当前Shared Pool状态：
Insert into E$ksmsp
select a.ksmchcom,
sum(a.chunk) chunk,
sum(a.recr) recr,
sum(a.freeabl) freeabl,
sum(a.sum) sum
from (select ksmchcom, count(ksmchcom) chunk ,
decode (ksmchcls, 'recr', sum(ksmchsiz), NULL ) recr,
decode (ksmchcls, 'freeabl',sum(ksmchsiz), NULL ) freeabl,
sum (ksmchsiz) sum
from x$ksmsp
group by ksmchcom, ksmchcls ) a
group by a.ksmchcom
/
比较前后 Shared Pool 内存分配变化：
select a.ksmchcom, a.chunk, a.sum, b.chunk, b.sum, (a.chunk - b.chunk) c_diff, (a.sum - b.sum) s_diff
from
( select a.ksmchcom,
sum(a.chunk ) chunk,
sum(a.recr) recr,
sum(a.freeabl) freeabl,
sum(a.sum) sum
from (select ksmchcom, count(ksmchcom) CHUNK,
decode(ksmchcls, 'recr', sum(ksmchsiz), NULL) recr,
decode (ksmchcls,'freeabl', sum(ksmchsiz), NULL) freeabl,
sum (ksmchsiz) sum
from x$ksmsp
group by ksmchcom, ksmchcls ) a
group by a.ksmchcom ) a, e$ksmsp b
where a.ksmchcom = b.ksmchcom and (a.chunk - b.chunk ) > 0
/
KSMCHCOM CHUNK SUM CHUNK SUM C_DIFF S_DIFF
---------------- ---------- ---------- ---------- ---------- ---------- ----------
free memory 313 12648252 308 12758040 5 -109788
Heap0: KGL 918 992180 917 991108 1 1072
KGL handles 3784 1815964 3776 1812096 8 3868
KGLS heap 2239 3114676 2238 3113604 1 1072
obj stat memo 410 124640 409 124336 1 304
KQR SO 548 337392 547 336852 1 540
PCursor 1193 1278896 1189 1274608 4 4288
CCursor 1938 2087040 1919 2066672 19 20368
library cache 1904 182824 1896 182056 8 768
sql area 1708 6997068 1690 6923340 18 73728
KQR PO 3997 2027052 3990 2023272 7 3780
11 rows selected.
11. 诊断和解决 ora-04031
Shared Pool 的根本问题只有一个：碎片过多带来的性能影响。
oerr ora 04031
04031, 00000, "unable to allocate %s bytes of shared memory ("%s","%s","%s","%s")"
// *Cause: More shared memory is needed than was allocated in the shared
// pool.
// *Action: If the shared pool is out of memory, either use the
// dbms_shared_pool package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// INIT.ORA parameters "shared_pool_reserved_size" and
// "shared_pool_size".
// If the large pool is out of memory, increase the INIT.ORA
// parameter "large_pool_size".
当尝试在共享池分配大块的连续内存失败，很多时候是由于碎片过多，而并非真正内存不足。Oracle 首先清除共享池总当前没有使用的对象，使空闲内存合并，如果仍没有足够大的单块内存可以满足需求，就会产生ORA-04031.
12. 绑定变量和 cursor sharing
如果shared Pool size 设置的足够大， 又可以排除Bug 的因素，那么大多数的Ora-04031 错误都是由共享池中的大量SQL代码等导致了内存碎片而引起的。可能的主要原因有：
1.SQL没有足够的共享
2. 大量的不必要的解析调用。
3. 没有使用绑定变量
实际上，应用的编写和调整始终是最重要的内容，Shared Pool的调整根本上要从应用入手。使用绑定变量可以充分的降低Shared pool 和library Cache 的Latch 竞争。
如果用户的应用没有很好的使用绑定变量，那么Oracle 从8.1.6开始提供的一个新的初始化参数用以在server 端进行强制变量绑定，这个参数就是cursor_sharing。
最初这个参数有两个可选设置 exact 和 force。
从Oracle 9i 开始， Oracle 引入了绑定变量的Peeking机制，SQL在第一次执行时，首先在session的PGA中使用具体值生成精确的执行计划，以期提高执行计划的准确性。然而Peeking 的方式只是在第一次硬解析的时候生效，所以仍然可能存在问题，导致后续的SQL错误执行。
同时，在Oracle 9i中，cursor_sharing 参数有了第三选项：similar。该参数指定Oracle在存在柱状图信息时，对于不同的变量值重新解析，从而可以利用柱状图更为精确的制定SQL执行计划。即当存在柱状图信息时，similar的表和和exact相同，当不存在柱状图时，similar 的表现和force相同。
13. 使用 flush shared pool
如果不能修改应用，或者不能强制绑定变量， 那么 oracle 提供了一种应急处理方法： 强制刷新共享池：
alter system flush shared_pool;
刷新共享池可以帮助合并碎片（small Chunks），强制老化SQL，释放共享池，但通常不推荐，因为：
1. Flush Shared Pool 会导致当前未使用的cursor被清除出共享池。如果这些SQL随后需要执行，那么数据库将经历大量的硬解析，系统将会经历严重的CPU争用，数据库将会产生激烈的latch竞争。
2. 如果应用没有绑定变量，大量的类似的SQL 不停的执行，那么Flush Shared Pool 可能只带来短暂的改善，数据库很快回到原来的状态。
3. 如果Shared Pool 很大，并且系统非常繁忙，刷新Shared Pool可能会导致系统挂起，对于类似系统尽量在空闲时进行。
从Oracle 9i 开始，oracle 的共享池算法发生了改变，Flush Shared Pool 的方法已经不再推荐使用了。
14 。Shared_pool_Reserved_size参数的设置以及作用。
该参数指定了保留的空想池空间，用于满足大的连续的共享池空间请求。
当共享池出现过多的碎片，请求大块空间会导致orace 大范围的查找并释放共享池内存来满足请求，由此可能带来严重的性能下降。设置设当的 shared_pool_reserved_size，结合 shared_pool_reserved_min_alloc 参数可以避免由此导致的性能下降。
这个参数的理想值应该大到足以满足任何对 reserved list 的内存请求，而无需数据库从共享池中刷新对象。这个参数的缺省值是 shared_pool_size 的 5%，通常这个参数的建议值为 shared_poo_size 的 10% ～ 20%， 最大不得超过 shared_pool_size 的 50%
。
shared_pool_reserved_min_alloc 这个参数控制保留内存的使用和分配。如果在共享内存池列表总请求一个足够尺寸的大块内存，但没有找到合适的空间，内存就从保留列表 中分配一块比这个参数值大的空间。
在oracle 9i 中，该参数的缺省值是4400，这是一个隐含参数。
@getp
Enter value for par: shared_pool_reserved_min_alloc
old 14: x.ksppinm like '%_&par%'
new 14: x.ksppinm like '%_shared_pool_reserved_min_alloc%'
NAME
--------------------------------------------------------------------------------
VALUE
----------------------------------------------------------------------------------------------------
ISDEFAULT ISMOD ISADJ
--------- ---------- -----
_shared_pool_reserved_min_alloc
4400
TRUE FALSE FALSE
这个参数的默认值对于大多数系统来说都足够了，如果系统经常出现ORA-04031的错误都是请求大于4400bytes的内存块，那么就需要增加 shared_pool_reserved_size 参数设置了。
如果主要的引发LRU合并、老化并出现ORA-04031 错误的内存请求在4100～4400 bytes之间，那么降低_shared_pool_reserved_min_alloc 同时适时增大 shared_pool_reserved_size 参数通常会有所帮助。设置 _shared_pool_reserved_min_alloc = 4100 可以增加 Shared Pool成功满足请求的概率。
查询 v$shared_pool_reserved 视图可以判断共享池问题的引发原因;
select free_space, avg_free_size, used_space, avg_used_size, request_failures, last_failure_size from v$shared_pool_reserved;
FREE_SPACE AVG_FREE_SIZE USED_SPACE AVG_USED_SIZE REQUEST_FAILURES LAST_FAILURE_SIZE
---------- ------------- ---------- ------------- ---------------- -----------------
3831984 212888 0 0 0 0
如果request_failures > 0 且 last_failure_size > shared_pool_reserved_min_alloc， 那么ORA-04031 错误可能是因为共享池保留空间缺少连续空间所致。要解决这个问题，可以考虑增大 _shared_pool_reserved_min_alloc 来降低缓冲池保留空间的对象数目，并增大 shared_pool_reserved_size 和 shared_pool_size 来加大共享池保留空间的可用内存。
如果 request_failures > 0 且 last_failure_size < shared_pool_reserved_min_alloc， 或者 request_failures = 0 且 last_failure_size < shared_pool_reserved_min_alloc，那么是由于在库高速缓冲缺少连续的空间而导致了ORA-04031 错误。对于这一类情况，应该考虑降低 shared_pool_reserved_min_alloc， 以放入更多的对象到共享池保留空间并加大shared_pool_size
次外，某些特定的SQL，较大 的指针或者较大的Package 都可能导致 ORA-04031 错误。在很多ERP软件中，这样的情况非常常见。在这种情况下，可以考虑把这个大的对象pin到共享池中，减少其动态请求，分配所带来的负担。
使用 dbms_shared_pool.keep 系统包可以把这些对象pin到内存中。 sys.standard、sys.dbms_standard 等都是常见的候选对象。
要使用dbms_shared_pool 系统包，首先需要运行 dbmspool.sql 脚本，该脚本会自动调用 prvpool.plb 脚本创建所需对象。
引发 ORA-04031 错误的因素还有很多，通过设置相关的参数 如： session_cached_cursors、 cursor_space_for_time 等也可以解决一些性能问题，并带来针对性的性能改善。
15. 模拟 ORA-04031
Oracle 9iR2 开始引入 段级统计信息 （Segment Statistics）收集的新特性，其中一个新引入的视图就是 v$segstat， 查寻该视图会引发 Shared Pool 内存泄漏。
16。 Library Cache pin 和 Library Cache Lock 分析
Oracle 使用两种数据结构来进行Shared Pool 的并发访问控制： lock 和 pin。 lock 比 pin 具有更高的级别。
lock 在handle 上获得，在pin 一个对象之前，首先获得该handle的锁定。锁定主要有3种模式： NUll，Share， Exclusive。在读取访问对象时，通常需要或缺NUll （空）模式以及 Share （共享）模式的锁定。在修改对象时，需要获得Exclusive（排他）锁定。
在锁定了Library Cache 对象之后，一个进程在访问之前，必须pin该对象。同样，pin 有3种模式：NUll， Shared， Exclusive。只读模式时获得共享 pin，修改模式时获得排他pin。
通常访问、执行过程和Package的时候，获得的都是共享pin，如果排他pin被持有，那么数据库此时就要产生等待。
在很多Statspack 的report 中，可能看到：
Library Cache lock 和 Library Cache pin
Library Cache pin 是用来管理Library Cache 的并发访问的，pin 一个object会引起相应的 heap 被载入内存中（如果此前没有被加载），pins 可以在三种模式下获得： Null，Share 和 Exclusive，可以认为 pin 是一种特殊的 锁。
当Library Cache Pin 等待事件出现时，通常说明该 pin 被其他用户以非兼容模式持有。
Library Cache pin 的等待时间是 3秒钟，其中 1秒 用于PMON后台进程，即在取得pin之前，最多等待3秒，否则就超时。
Library Cache Pin 的参数如下，有用的主要是 P1 和 P2：
P1 -- KGL handle address
P2 -- Pin address
P3 -- Encoded Mode & Namespace
Library Cache pin 通常发生在编译或者重新编译PL/SQL、view、Types 等Objects时，编译通常都是显性的，如安装应用程序、升级、安装补丁等，later、grant、 和 revoke 等操作也会使得Object变得无效，可以通过 Object 的 LAST_DDL_TIME 观察这些变化。
当Object 变得无效时，oracle 会在第一次访问此Object的时候试图重新编译它，如果此时其他session 已经把此 Object pin 到Library Cache 中，就会出现问题，特别是当有大量的活动的 session 并且存在复杂的 dependence 的时候。在某种情况下，重新编译 Object 可能会花几个小时，从而阻塞其他试图访问此 Object 的进程。
下面通过一个例子来模拟这个等待：
create or replace procedure pining
is
begin
NULL;
end;
/
create or replace procedure calling
is
begin
pining;
dbms_lock.sleep(3000);
end;
/
select sid, seq#,event,p1, p1raw, p2, p2raw, p3, p3raw, state from v$session_wait where event like 'library%';
SID SEQ# EVENT P1
---------- ---------- ---------------------------------------------------------------- ----------
P1RAW P2 P2RAW P3 P3RAW STATE
-------- ---------- -------- ---------- -------- -------------------
159 875 library cache pin 1530767612
5B3DA8FC 1576696368 5DFA7A30 301 0000012D WAITING
等待了3秒，并未发现SEQ#发生变化啊？？？？？？？
在这个输出总，P1 是 library Cache handle Address，Pn 字段是 十进制表示，PnRAW 是十六进制表示。
查询X$KGLOB 可以得到对象的具体信息：
X$KGLOB 代表 Kernel Generic Library Cache Object
col KGLNAOWN for a10
col KGLNAOBJ for a20
select ADDR, KGLHDADR, KGLHDPAR,KGLNAOWN,KGLNAOBJ, KGLNAHSH,KGLHDOBJ from x$kglob where kglhdadr ='5B3DA8FC';
ADDR KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNAHSH KGLHDOBJ
-------- -------- -------- ---------- -------------------- ---------- --------
40EE7AD8 5B3DA8FC 5B3DA8FC SYS PINING 2030642293 5C86B9E0
KGLNAHSH 代表对象的Hash value，由此可知，在pining 对象上正经历 Library Cache pin 的等待，然后引入另一个视图： X$KGLPN， 为 Kernel Genric Library Cache manager object PiNs
select a.sid, a.username, a.program, b.addr, b.kglpnadr, b.kglpnuse, b.kglpnses, b.kglpnhdl, b.kglpnlck, b.kglpnmod, b.kglpnreq from v$session a, x$kglpn b where a.saddr = b.kglpnuse and b.kglpnhdl = '5B3DA8FC' and b.kglpnmod > 0
sys@ORA10G> /
SID USERNAME PROGRAM ADDR
---------- ------------------------------ ------------------------------------------------ --------
KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGLPNMOD KGLPNREQ
-------- -------- -------- -------- -------- ---------- ----------
144 SYS sqlplus@arch (TNS V1-V3) 40F09DB0
5DFB5804 60582F88 60582F88 5B3DA8FC 5DAEE70C 2 0
通过联合 v$session，获得当前持有该handle 的用户信息。在本测试中，sid 为 144 的用户正在持有该handle。
select * from v$session_wait where sid = 144
sys@ORA10G> /
SID SEQ# EVENT
---------- ---------- ----------------------------------------------------------------
P1TEXT P1 P1RAW
---------------------------------------------------------------- ---------- --------
P2TEXT P2 P2RAW
---------------------------------------------------------------- ---------- --------
P3TEXT P3 P3RAW WAIT_CLASS_ID
---------------------------------------------------------------- ---------- -------- -------------
WAIT_CLASS# WAIT_CLASS WAIT_TIME
----------- ---------------------------------------------------------------- ----------
SECONDS_IN_WAIT STATE
--------------- -------------------
144 65 PL/SQL lock timer
duration 300000 000493E0
0 00
0 00 2723168908
6 Idle 0
1246 WAITING
由此可知，这个用户正在等待一次PL/SQL Lock Timer 计时。
得到了SID，就可以通过v$session.SQL_HASH_VALUE、v$session.SQL_ADDRESS 等字段关联 v$sqltext、 v$sqlarea 等视图获得当前session正在执行的操作：
select sql_text from v$sqlarea where v$sqlarea.hash_value = '3918317640';
SQL_TEXT
----------------------------------------------------------------------------------------------------
BEGIN calling; END;
接下来就应该去检查 calling 在做什么了。
简化一下：
1. Library Cache 等待对象：
select addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj from x$kglob where kglhdadr in ( select p1raw from v$session_wait where event like 'library%')
sys@ORA10G> /
ADDR KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNAHSH KGLHDOBJ
-------- -------- -------- ---------- -------------------- ---------- --------
6088F0DC 5B3DA8FC 5B3DA8FC SYS PINING 2030642293 5C86B9E0
2. 获取持有等待对象的 session 信息：
select a.sid, a.username, a.program, b.addr, b.kglpnadr, b.kglpnuse, b.kglpnses,b.kglpnhdl, b.kglpnlck, b.kglpnmod, b.kglpnreq
from v$session a, x$kglpn b
where a.saddr = b.kglpnuse and
b.kglpnmod <> 0
and b.kglpnhdl in ( select p1raw from v$session_wait where event like 'library%');
SID USERNAME PROGRAM ADDR
---------- ------------------------------ ------------------------------------------------ --------
KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGLPNMOD KGLPNREQ
-------- -------- -------- -------- -------- ---------- ----------
144 SYS sqlplus@arch (TNS V1-V3) 40F02D28
5DFB5804 60582F88 60582F88 5B3DA8FC 5DAEE70C 2 0
3. 获得持有对象用户执行的代码：
select sql_text
from v$sqlarea aa
where (aa.address, aa.hash_value ) in (
select sql_address, sql_hash_value
from v$session
where sid in (
select sid
from v$session a, x$kglpn b
where a.saddr = b.kglpnuse and
b.kglpnmod <> 0 and
b.kglpnhdl in (select p1raw
from v$session_wait where event like 'library%')));
SQL_TEXT
----------------------------------------------------------------------------------------------------
BEGIN calling; END;
在revoke之前和之后，可以转储一下 Shared Pool的内容，以进行观察和比较。
在grant 之前，
-- 在Object 上存在共享 pin
-- 在handle 上存在NUll 模式锁定，此模式允许其他用户继续以 NULL/shared 模式锁定该对象。
在 grant 之后，
-- 由于calling 执行未完成，在 Object 仍保持共享pin
-- 由于grant 会导致重新编译该对象，所以在 handle 上的排它锁已经被持有
-- 进一步的需要获得 Object 上的 exclusive Pin， 由于Shared Pin 被Calling 持有，所以Library Cache pin 等待出现。
实际上，recomplie 过程包含以下步骤，同时看一下lock 和pin 是如何交替发挥作用的
1. 存储过程的 library Cache Object 以排他模式被锁定，这个锁定是在handle 上获得的。Exclusive 锁定可以防止其他用户执行同样的操作，同时防止其他用户创建新的引用此过程的对象。
2. 以 Shared 模式 pin 该对象，以执行安全和错误检查。
3. 共享 pin 被释放，重新以排他模式 pin 该对象，执行重编译。
4. 使所有依赖该过程的对象失效。
5. 释放 Exclusive Lock 和 Exclusive Pin
17. Library Cache Lock 等待事件
如果此时再发出一条 grant 或 compile 命令，那么 Library Cache Lock 等待事件将会出现。
Session 3：
select * from v$session_wait where event like 'library%'
sys@ORA10G> /
SID SEQ# EVENT
---------- ---------- ----------------------------------------------------------------
P1TEXT P1 P1RAW
---------------------------------------------------------------- ---------- --------
P2TEXT P2 P2RAW
---------------------------------------------------------------- ---------- --------
P3TEXT P3 P3RAW WAIT_CLASS_ID
---------------------------------------------------------------- ---------- -------- -------------
WAIT_CLASS# WAIT_CLASS WAIT_TIME
----------- ---------------------------------------------------------------- ----------
SECONDS_IN_WAIT STATE
--------------- -------------------
145 1121 library cache lock
handle address 1530767612 5B3DA8FC
lock address 1576868480 5DFD1A80
100*mode+namespace 301 0000012D 3875070507
4 Concurrency 0
85 WAITING
159 958 library cache pin
handle address 1530767612 5B3DA8FC
pin address 1576874120 5DFD3088
100*mode+namespace 301 0000012D 3875070507
4 Concurrency 0
127 WAITING
由于handle 上的lock 已经被 session 2 以Exclusive模式持有，所以Session 3产生了等待。
可以看到，在生产数据库中权限的授予、对象的重新编译，都可能导致 Library Cache Pin 等待的出现。所以应该尽量避免在高峰期进行以上操作。
另外，测试本身就说明，如果package 或 过程 中存在复杂的、交互依赖的关系就极易导致Library Cache Pin的出现。在开发应用中，应当注意。
18。 version_count 过高造成的 Latch 竞争。
Shared Pool Latch 用于共享池内存空间的分配和回收。如果SQL没有充分共享，反复解析过程将是十分昂贵的。
Library Cache Latches 用于保护Cache 杂内存中的 SQL 以及 对象定义等，当需要向Library Cache 中增加新的SQL时，Library Cache latch 必须被获得。在解析SQl 过程中， Oracle 搜索 Library Cache 查询匹配的SQL，如果没有可共享的SQL代码， Oracle 将分析 SQL，获得 Library Cache Latch 向 Library Cache 中插入新的代码。
Library Cache latch 的数量受一个隐含 参数 _kgl_latch_count 的控制，其缺省值 大于或者等于 CPU_COUNTA 的素数，最大值不能超过 66.
简化一下SQL 执行过程，以说明 Latch 在 SQL 解析过程中所起的作用：
1. 首先需要获得 Library Cache Latch，根据 SQL 的 HASH_VALUE 值 在Library Cache 中寻找是否存在可共享代码。如果找的则为 软解析， Server 进程获得该 SQL 执行计划，转向 第 （4）步；如果找不到共享代码则执行硬解析。
2. 释放 Library Cache latch，获取 Shared Pool Latch,查找并锁定自由空间。
3. 释放 Shared Pool Latch，重新获得 Library Cache Latch，将 SQL 及执行计划插入 Library Cache 中。
4. 释放Library Cache Latch，保持NULL 模式的 Library Cache pin/Lock
5. 开始执行
可以看到，如果系统中存在过度的硬解析，系统的性能必然受到反复解析、latch 争用的折磨。
select name, value from v$sysstat where name like 'parse%'
sys@ORA10G> /
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 206
parse time elapsed 351439
parse count (total) 34089
parse count (hard) 1715
parse count (failures) 15
通过 [parse count (total) - parse count ( hard) ] / parse count ( total ) 得出的软解析率经常被用作衡量数据库性能的一个重要标准。
select sql_text, version_count, invalidations, parse_calls, optimizer_mode, parsing_user_id, parsing_schema_id, address, hash_value from v$sqlarea where version_count > 100;
应该注意 version_count 过高的SQL 语句，version_count 过高通常会导致Library Cache Latch的长时间持有， 从而影响性能。
最简单的，比如 scott 和 tiger 两个用户 同时执行：
select * from emp;
如果scott 和 tiger 各拥有一张 emp 表，那么这条 SQl 将存在两个子指针，而显然两者代码不能共享。所以 Oracle 虽然支持不同用户拥有同名对象，但还是应该尽量避免。
继续诊断，最直接的， dump 内存来看：
Oracle Bug 1210242
On certain SQL statement cursors are not shared when TIMED_STATISTICS is enabled.
碰巧这个数据库的TIMED_STATISTICS 设置为 TRUE，修改 false 之后，观察 v$sql，发现有效指针很快下降到两个。
对于这个问题，另一个可选的方法是设置一个隐含参数： _sqlexec_progression_cost = 0
该参数的具体含义为： SQL execution progression monitoring cost threadhold， 即SQL 执行进度监控成本脚本。
这个参数根据 COST 来决定监控 SQL。 执行进度监控会引入额外的函数调用和 Row Sources 这可能导致 SQL 的执行计划或成本发生改变，从而产生不同的子指针。
_sqlexec_progression_cost 的缺省值为1000，成本大于1000 的SQL 都会被监控，如果设置为0 ， 则那么SQL 的执行进度将不会被跟踪。
执行进度的监控信息被记录到 v$session_longops 视图中。如果 TIMED_STATISTICS 设置为 false， 则信心不会被记录。
所以，TIMED_STATISTICS 和 _sqlexec_progression_cost 是解决问题的两个途径。
select distinct(sql_text) from v$sql where optimizer_cost > 1000;
no rows selected
select sql_text,version_count from v$sqlarea where version_count > 10;
no rows selected
19. 在9i中遇到的一个问题，同样是 Library Cache Latch 和 Shared Pool Latch 竞争，类似的，在 v$sqlarea 发现大量的高 version_count 的 SQL。
对于version_count 过高的问题，可以查询 v$sql_shared_cursor 视图，这个视图会给出 SQL不能共享的具体原因。如果是正常因素导致的，相应的字段会被标记为“Y”；如果是异常情况，查询结果可能都是 “N”，这就表明Oracle 认为这种行为是正常的，在当前的系统设置下，这些SQL 不应该被共享，那么可以判断是某个参数引起的。
和 Cursor Sharing 关系最大的参数是：cursor_sharing。在这个案例中，cursor_sharing 被设置为similar，正正是这个导致了大量的子指针不能共享。
搜索 metalink，获得相关说明，当 cursor_sharing 的参数为 similar，并且数据库存在相关柱状图（Histogram）信息时，对于每一条新执行的SQL， Oracle 都通过硬解析以获得更为精确的执行计划，这导致了version_count 过高，这是 cursor_sharing = similar 的正常行为，并发bug。
了解之后，可以将cursor_sharing 设置为 exeact 或者 force以避免问题，或者可以删除柱状图信息来防止不必要的硬解析。实际上，如果数据库不存在失衡分布，也不必搜集柱状图信息。
20. 如果一个DB里面的几个存储过程总是跑不完，同样的存储过程在其他6个地方都正常。数据库里没有锁，数据库和server上的空间足够。正常情况下，几分钟就能运行完，运行了几个小时了还没完，为什么？
检查 v$session_wait, 看都有什么。如果系统慢，通常存在等待，v$session_wait是应该优先检查的视图。
[@more@]在Buffer Cache 中，Oracle 通过几个链表进行内存管理，其中最为人熟知的就是LRU list and Dirty list （也通常被成为write list，从Oracle 8i开始，因为算法变化，也被称为checkpoint queue），各种list存放的是指向具体的Buffer的指针。
数据库初始化时，所有的Buffer都被Hash到LRU list上，当需要从数据文件读数据的时，首先在LRU list 寻找Free 的Buffer，然后读取数据到Buffer Cache 中。当数据被修改后，状态变为dirty，就可以被移至dirty list（checkpoint queue）。Dirty list 上的都是候选的可以被DBWR写出到数据文件的Buffer。一个Buffer要么在LRU list 上，要么在dirty list上。
1. 当一个server 进程需要读数据到Buffer Cache中时候，首先需要判断该数据在Buffer中是否存在。如果存在且可用，则获取该数据。根据LRU算法在LRU list上移动该Block。如果Buffer中不存在该数据，则需要从数据文件中读取。
2.在读取数据之前，SErver进程需要扫描LRU list寻找free 的Buffer，扫描过程中server进程会发现的已有的被修改过的Buffer 移至Checkpoint Queue 上。 这些Dirty buffer 随后可以被写出到数据文件。
3. 如果checkpoint queue 超过了阈值，Server进程就会通知DBWn去写出脏数据，这也是触发DBWn写的一个条件。这个阈值是25%。也就是说，当检查点队列超过25%满就会触发DBWn的写操作。
select kvittag,kvitval,kvitdsc from x$kvit where kvittag = 'kcbldq';
KVITTAG KVITVAL
---------------------------------------------------------------- ----------
KVITDSC
----------------------------------------------------------------
kcbldq 25
large dirty queue if kcbclw reaches this
如果SErver进程扫描LRU超过一定的阈值仍然不能找到足够的Free Buffer，将停止寻找，转而通知DBWn去削除脏数据，释放内存空间。
同样，这个阈值可以在数据字典表找到，是40%。也就是说，当Server 进程扫描LRU超过40% 还没能找到足够的Free Buffer就会停止搜索，通知DBWn执行写出。这时进程会处于 free busy wait 等待。
select kvittag, kvitval, kvitdsc from x$kvit where kvittag ='kcbfsp'
idle> /
KVITTAG KVITVAL
---------------------------------------------------------------- ----------
KVITDSC
----------------------------------------------------------------
kcbfsp 40
Max percentage of LRU list foreground can scan for free
同时，因为增量检查点的引入，DBWn也会主动扫描LRU list，将发现的Dirty Buffer移至Checkpoint queue。这个扫描比例也受一个内部约束，在oracle 9iR2中，是25%
4. 找到足够的Buffer之后，SErver进程就可以将Buffer从数据文件读入Buffer Cache中。
5. 如果读取的Block不满足读一致性需求，则server进程需要通过当前Block版本和回滚段构造前镜像返回给用户。
从Oracle 8i开始，LRU list 和Dirty list 又分别增加了辅助list （auxiliary list）用于提高管理效率。引入辅助list之后，当数据库初始化的时候，Buffer 首先存放在LRU 辅助List上（auxiliary RPL_LST），当被使用后移动到LRU主list上(MAIN RPL_LST)，这样当用户搜索Free Buffer时候，就可以从LRU-AUX list 开始，而DBWn死哦所Dirty Buffer时，则可以从LRU-MAIN list 开始，从而提高搜索效率和数据性能。
可以通过如下命令转储Buffer CAche的内容，从而清晰的看到其数据结构。
alter session set events 'immediate trace name buffers level 4';
转储仅限于在测试环境中，生成的跟踪文件巨大，为了获得完整的跟踪文件，建议设置初始参数 max_dump_file_size 为unlimited
6.
当用户进程需要读数据到Buffer Cache时，或Cache Buffer根据LRU算法进行管理时，就不可避免的要扫描LRU list。 Buffer Cache 是共享内存，可以为众多并发进程访问，所以在搜索的过程中，必须获得latch，锁定内存结构，防止并发访问内存中的数据。
这个多顶LRU的latch 就是经常见到的Cache buffer LRU chain
select addr,latch#,name, gets,misses,immediate_gets, immediate_misses from v$latch where name = 'cache buffers lru chain';
ADDR LATCH# NAME GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES
-------- ---------- ------------------------------ ---------- ---------- -------------- ----------------
2000944C 117 cache buffers lru chain 23345 0 986 0
Cache Buffer Lru Chain Latch 存在多个子latch, 其数量受隐含参数 _db_block_lru_latches控制。
NAME VALUE
------------------------------ ------------------------------
DESCRIB
------------------------------------------------------------
_db_block_lru_latches 8
number of lru latches
从v$latch_children 视图看当前各子latch使用情况：
select addr,latch#,name, gets,misses,immediate_gets, immediate_misses from v$latch_children where name = 'cache buffers lru chain'
idle> /
ADDR LATCH# NAME GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES
-------- ---------- ------------------------------ ---------- ---------- -------------- ----------------
5FA02B3C 117 cache buffers lru chain 14 0 1 0
5FA02EC0 117 cache buffers lru chain 14 0 1 0
5FA03244 117 cache buffers lru chain 27350 0 981 0
5FA035C8 117 cache buffers lru chain 14 0 1 0
5FA0394C 117 cache buffers lru chain 14 0 1 0
5FA03CD0 117 cache buffers lru chain 14 0 1 0
5FA04054 117 cache buffers lru chain 14 0 1 0
5FA043D8 117 cache buffers lru chain 14 0 1 0
8 rows selected.
如果该latch 竞争激烈，通常有如下方法可以采用：
1. 适当增大Buffer Cache，这样可以减少数据到buffer Cache 的机会，减少扫描LRU List 的竞争。
2. 可以适当增加LRU latch的数量，修改_db_block_lru_latches 参数可是实现。但是该参数通常是足够的。
3. 通过多种缓冲池技术，可以减少不希望的数据老化和全表扫描等操作对default 池的冲击，从而可以减少竞争，
7. Cache Buffer Chain 闩锁竞争与解决
在LRU和dirty list 这两个内存结构外，Buffer Cache 的管理还存在另外两个重要的数据结构： Hash Bucket和Cache Buffer Chain
_spin_count
由于Buffer 根据Buffer Header进行散列，从而最终决定存入哪一个hash Bucket, 那么Hash Bucket 的数量在一定程度上，决定了每个Bucket中的Buffer数量，也就间接影响了搜索性能。
Hash Bucket 受 _db_block_hash_buckets 影响。 在Oracle 7 和Oracle 8 中，缺省值为 db_block_buffers/4 下的一个素数。在Oracle 8i中，该参数缺省为db_block_buffers * 2
对于每个Bucket，只存在一个chain,当用户试图搜索Cache Buffer Chain 时，必须首先获得 Cache Buffer Chain Latch。
在Oracle 8i 之前，对于每一个Bucket，Oracle 使用一个独立的hash latch 来维护。 其缺省Bucket 数量为 next_prime(db_block_buffers / 4)
从Oracle 8i 开始， _db_block_hash_buckets 增加到 db_block_buffers, 而 _db_block_hash_latches的数量也发生了变化 。
1. 当 Cache Buffer 少于 2052 Buffers：
_db_block_hash_latches = power(2, trunc( log( 2, db_block_buffers - 4) - 1 ) )
2. 当Cache Buffers 多于 131075 buffers:
_db_block_hash_latches = power ( 2, trunc ( log( 2, db_block_buffers - 4 ) - 6))
3. 当Cache Buffers 位于 2052 和131075 之间时：
_db_block_hash_latches = 1024
从oracle 8i 开始， _db_block_hash_buckets 的数量较以前增加了8倍，而 _db_block_hash_latches 的数量增加有限。 这意味着每个Latch 需要管理多个Bucket，但是由于Bucket 数量的多倍增加，每个Bucket 上的Block数量得以减少，从而使得少量Latch 管理更多Bucket成为可能。
8。 X$bh
select * from (select addr, ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc) where rownum < 11
idle> /
ADDR TS# FILE# DBARFIL DBABLK TCH
-------- ---------- ---------- ---------- ---------- ----------
6081016C 0 1 1 1658 8003
6081016C 0 1 1 1657 7889
6081016C 0 1 1 1674 7787
6081016C 0 1 1 1666 719
6081016C 0 1 1 796 642
6081016C 0 1 1 794 640
6081016C 0 1 1 795 637
6081016C 0 1 1 651 633
6081016C 0 1 1 650 633
6081016C 0 1 1 92 632
10 rows selected.
再结合dba_extends,可以查询到这些热点Buffers来自哪些对象。
select e.owner, e.segment_name,e.segment_type from dba_extents e, ( select * from (select addr, ts#, file#,dbarfil, dbablk, tch from x$bh order by tch desc ) where rownum < 11 ) b where e.relative_fno = b.dbarfil and e.block_id <= b.dbablk and e.block_id + e.blocks > b.dbablk
idle> /
OWNER SEGMENT_NAME
------------------------------ ---------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
SYS SYSAUTH$
TABLE
SYS SYSAUTH$
TABLE
SYS JOB$
TABLE
SYS JOB$
TABLE
SYS C_USER#
CLUSTER
SYS I_SYSAUTH1
INDEX
SYS I_SYSAUTH1
INDEX
SYS I_SYSAUTH1
INDEX
SYS I_JOB_JOB
INDEX
SYS I_JOB_NEXT
INDEX
10 rows selected.
除了查询X$BH以外，也可以从Buffer Cache 的转储信息中，看到Buffer Header 的具体内容。
在Oracle 10g 之前，数据库的等待事件中，所有的Latch等待被归入latch free 等待中，在statspack 的report 中，如果top 5等待事件中看到latch free 这一等待事件处于较高的位置，需要介入研究和解决。
案例：
由于Latch Free 是一个汇总等待事件，需要从v$latch视图中获得具体的latch 竞争主要由哪些latch 引起的。
Cache Buffer Chains 正是主要latch 的竞争。
查看v$session_wait
如果需要具体的热点对象，可以从v$latch_children中查询具体的子Latch信息。
select * from
2 (select addr, child#, gets, misses, sleeps, immediate_gets igets, immediate_misses imiss, spin_gets sgets from v$latch_children where name = 'cache buffer chains' order by sleeps desc) where rownum < 11;
在X$BH中还有一个字段HLADDR，即Hash Chain Latch Addrss, 这个字段可以和v$latch_children 进行关联，这样就可以把具体的Latch 竞争和数据块关联起来，再结合dba_extents 视图，就可以找到具体的热点竞争对象。找到具体的热点竞争对象之后，可以结合v$sqlarea或者v$sqltext，找到频繁操作这些对象的SQL，然后对其进行优化。
select b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch,b.gets,b.misses,b.sleeps
2 from (select *
3 from (select addr,ts#,file#,dbarfil, dbablk,tch,hladdr from x$bh order by tch desc )
4 where rownum < 11 ) a,
5 (select addr,gets,misses,sleeps from v$latch_children where name = 'cache buffers chains') b
6* where a.hladdr = b.addr
idle> /
ADDR TS# DBARFIL DBABLK TCH GETS MISSES SLEEPS
-------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
5F9B7204 0 1 92 20 4022 0 0
5F98D5D8 0 1 794 20 1711 3 0
5F986140 0 1 1658 182 1529 0 0
5F981088 1 2 137 20 2046 0 0
5F966208 0 1 1674 175 1230 0 0
5F91FC74 0 1 795 20 1558 3 0
5F9120B8 0 1 282 23 3806 0 0
5F8E21E4 1 2 73 21 1382 0 0
5F8D4AA4 0 1 1657 179 1634 0 0
5F8C22AC 1 2 89 21 2117 0 0
10 rows selected.
利用这个SQL，可以找到热点Buffer对应的对象信息：
select distinct e.owner, e.segment_name, e.segment_type from dba_extents e,
(select *
from (select addr,ts#,file#,dbarfil, dbablk,tch from x$bh order by tch desc ) where rownum < 11 ) b
where e.relative_fno = b.dbarfil
and e.block_id <= b.dbablk
and e.block_id + e.blocks > b.dbablk
/
结合V$sqlarea 或者 v$sqltext 就可以找到操作这些对象的相关SQL：
select /*+ rule */ hash_value, sql_text from v$sqltext
where (hash_value ,address) in (
select a.hash_value, a.address from v$sqltext a,
(select distinct a.owner, a.segment_name, a.segment_type from dba_extents a,
(select dbarfil,dbablk from (select dbarfil, dbablk from x$bh order by tch desc)
where rownum < 11 ) b
where a.relative_fno = b.dbarfil
and a.block_id < b.dbablk
and a.block_id + a.blocks > b.dbablk ) b
where a.sql_text like '%' || b.segment_name || '%'
and b.segment_type = 'TABLE' )
ORDER BY hash_value, address, piece
/
找到SQL之后，剩下的问题，可以通过优化SQL减少数据访问，避免或者优化某些容易引起争用的操作 （如 Connect By）来减少热点。
9。 Shared Pool 基本原理
Shared Pool 是Oracle SGA 中最复杂也是最重要的一部分，Oracle通过Shared Pool 来实现SQL 共享、减少代码硬解析等，从而提高数据库性能。在某些版本中，如果设置不当，Shared Pool 可能会极大的影响数据的性能。
在Oracle 7 之前，Shared Pool 并不存在，每个Oracle 连接都有一个独立的Server进程与之相关联，Server进程负责解析，优化所有的SQl和PL/SQL代码。典型的，在OLTP环境中很多代码具有相同或者类似的结构，反复的独立解析浪费了大量的时间以及资源，Oracle 从PL/SQL开始尝试把这部分可共享进行独立的存储和管理，于是Shared Pool作为一个独立的SGA组建被引入，并且其作用和功能被逐渐的完善和发展起来。
Shared Pool 最初被引入的目的，也就是它的本质功能就是 实现共享 。 如果你的代码是完全异构的（假设你的代码从不绑定变量，从不反复执行），那么你发现你的Shared Pool 完全成了一个负担，它在徒劳无功的进行无谓的努力：保存代码，执行计划等期待重用，并且客户端要不停的获取latch，试题寻找共享代码，却始终一无所获。
大多数性能低下的系统都存在这样的通病：代码极少共享，缺乏或从不实行变量绑定。优化这些系统的根本办法是优化代码，使代码充分共享，减少无谓的反复硬/软解析
Shared Pool 大小通过参数 shared_pool_size 设置。一直以来，这是很有争议的。很多人建议可以把Shared pool 设置的稍大，易充分CAche 代码和减少ora-04031 错误的出现；另一方面，一些人不建议把Shared Pool 设置的过大，因为其可能带来管理上的额外负担。从而会影响数据库的性能。
转储Shared Pool 共享内存的内容：
alter session set events 'immediate trace name heapdump level 2';
Shared Pool 通过Free Lists 管理 Free 内存块 （Chunk），free 的内存块 （Chunk）按照不同size被划分到不同的Bucket进行管理。
初始时，数据库启动后，Shared Pool 多数是连续内存块。但是当空间分配使用以后，内存块开始被分割，碎片开始出现，Bucket 列表开始变长。
Oracle 请求Shared Pool 空间时，首先进入相应的Bucket进行查找。如果找不到，则转向下一个非空的Bucket，获取第一个Chunk，剩余的部分会进入相应的Bucket，进一步增加碎片。
最终的结果是，由于不停的分割，每个Bucket上的内存块会越来越多，越来越小。通常Bucket 0 的问题会最为显著。
通常，如果每个Bucket上的Chunk多于2000个，就被认为是Shared Pool 碎片过多。而Shared Pool 的碎片过多，是Shared Pool 产生性能问题的主要原因。
碎片过多会导致搜索Free List的时间过长，而Free List 的管理和搜索都需要获得和持有Latch。
Latch 是 Oracle 数据库内部提供的一种串行锁，通过串行机制保护共享内存不被并发更新/修改所损坏。Latch 的持有通常都很短暂（通常微妙级），但是对于一个繁忙的的数据库，这个串行机制往往成为极大的性能瓶颈。
如果Free Lists 链表过长，搜索这个链表的时间就会变长，从而导致Shared Pool Latch 被长时间持有，在一个繁忙的系统中，这会引起严重的Shared Pool Latch 竞争。在Oracle 9i之前，这个重要的Shared Pool Latch只有一个，所以长时间持有将会导致严重的性能问题。
而大多数情况下，用户都是请求相对小的内存块（Chunk），这个搜索Bucket 0往往消耗了大量的时间以及资源。Latch的争用成了一个严重的问题。
从Oracle 8.1.7 开始， Oracle 对Shared Pool的管理进行了改进。初始Oracle 分配了255个Bucket。
在Oracle 9i开始，Oracle 进一步改写了Shared Pool 管理的算法。最为显著的变化就是，对于数量众多的Chunk，Oracle 增加了更多的Bucket来管理。所以在Oracle 9i之前，如果盲目的增大shared_pool_size 或者设置过大的shared_pool_size 往往会适得其反。这也就是也也许曾经听过的“过大的Shared Pool会带来管理上的负担”。
在Oracle 9i 中，为了增大对于大共享池的支持，Shared Pool Latch 从原来的一个增加到现在的7个。
如果用户的系统有4个或者4个以上的CPU，并且shared_pool_size 大于250MB，那么Oracle可以把Shared Pool分割为多个子缓冲池进行管理，每个subpoll都拥有独立的结构、LRU和shared Pool Latch。
select addr, name, gets, misses,spin_gets from v$latch_children where name = 'shared pool';
ADDR NAME GETS MISSES SPIN_GETS
-------- -------------------------------------------------- ---------- ---------- ----------
200960D4 shared pool 177117 162 159
20096138 shared pool 12 0 0
2009619C shared pool 12 0 0
20096200 shared pool 12 0 0
20096264 shared pool 12 0 0
200962C8 shared pool 12 0 0
2009632C shared pool 12 0 0
7 rows selected.
子缓冲的数量由一个新引入的参数设置 _KGHDSIDX_count。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21342557/viewspace-1031543/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/21342557/viewspace-1031543/
扫一扫
612
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
