[20190319]shared pool latch与library cache latch的简单探究.txt

--//昨天看Oracle DBA手记3：数据库性能优化与内部原理解析.pdf 电子书,看了eygle的关于latch之类的测试.
--//自己也重复测试看看.

--//首先说明一下11g已经不存在这个shared pool latch,改为mutexes.所以测试在10g下进行:
--//注意不要在生产系统做这样的测试!!

1.环境:
SCOTT@test> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

--//把一些常用命令先执行多次(3次以上避免执行时分析时挂起),比如 @ &r/wait10g,也可以事先多开几个sys登录会话.

$ cat wait10g.sql
select p1raw,p2raw,p3raw,p1,p2,p3,sid,serial#,seq#,event,state,wait_time,seconds_in_wait from v$session where wait_class<>'Idle'
and sid not in (select sid from v$mystat where rownum=1)
order by event ;

SELECT addr
        ,latch#
        ,child#
        ,level#
        ,name
        ,gets
        ,sleeps
        ,immediate_gets
        ,immediate_misses
        ,spin_gets
    FROM V$LATCH_CHILDREN
   WHERE name LIKE 'library cache';

ADDR             LATCH# CHILD# LEVEL# NAME          GETS SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES SPIN_GETS
---------------- ------ ------ ------ ------------- ---- ------ -------------- ---------------- ---------
000000007A753430    215     29      5 library cache 1256      0              0                0         1
000000007A7534D0    215     28      5 library cache  741      0              0                0         0
000000007A753570    215     27      5 library cache 1022      0              0                0         0
000000007A753610    215     26      5 libr