Latch Free、Library cache伪游标(pseudo cursor)之间的那些事

2016-01-15 11:33:43

作者：林印豪，新炬网络高级技术专家。

一、Latch Free很头疼

“57.8这套系统CPU又100%啦，新炬同事赶紧帮忙看一下。”远处传来了客户的声音。

我不慌不忙的打开终端，轻叹口气道：“唉，又是这套库，自从我来这边，已经查了2遍了，而且其他人也查过好几遍了，问题很难定位啊！“

前面我查了几次都觉得是SQL硬解析的问题，可是把这个事情反馈过去之后。他们给我们发了以前的statspack报告，发现硬解析过去是300次/秒，现在下降到了60次/秒,Latch free却增加了不少，着实摸不着头脑。”通过前期的排查基本上定位出了Latch Free的争用是由于library cache引起的。当系统出现大量的Library cache争用的时候，CPU就会达到100%。但是原因？最重要的原因一直未找到。我认为是硬解析导致的，也有同事认为是shared pool太小导致的。还有同事认为是几条逻辑读较高的语句导致的。众说纷纭，也没有一个准。基本上这些方法客户都尝试过了，仍然未能解决这个问题。

二、从Library cache入手走下去

前期的排查让我们确信的一点是CPU的消耗是在Library cache争用上面，那么Library cache里面情况又是如何的呢？为了一探究竟，我决定在出问题期间直接查询v$libarycache视图，找寻根源。

SQL> select NAMESPACE,GETS,GETHITS,GETHITRATIO,PINS,PINHITS,PINHITRATIO from v$librarycache;
NAMESPACE             GETS    GETHITS GETHITRATIO       PINS    PINHITS PINHITRATIO    RELOADS
--------------- ---------- ---------- ----------- ---------- ---------- ----------- ---------
SQL AREA        2681757300 2466988687   .91991497 2526130229 636861692 .252109604 145268976
TABLE/PROCEDURE 1425869375 1426215639 1.00024284 400135986 395962811 .989570608    4011372
BODY             149608221 149606649 .999989493 149608385 149605739 .999982314        747
TRIGGER          201588390 201584681 .999981601 201588426 201582160 .999968917       2513
INDEX               147927     144354 .975846194     147833     144231 .975634669          7
CLUSTER            1913453    1905342 .995761067    1116345    1100128 .985473129          1
OBJECT                   0          0           1          0          0           1          0
PIPE                     0          0           1          0          0           1          0
JAVA SOURCE           2995       1199   .40033389       2995       1199   .40033389          0
JAVA RESOURCE         2995       1202 .401335559       2995       1202 .401335559          0
JAVA DATA                0          0           1          0          0           1          0

从v$librarycache视图中我们发现了一个问题，在对SQL AREA这种对象在pin的时候发生了丢失，可以看到pin hint这项命中率只有区区的25%。而在其他上面都接近100%。那我们来具体的看一下library cache的结构。

Latch Free、Library cache伪游标(pseudo cursor)之间的那些1

在我们寻找hash bucket，定位cursor的handle，还有寻找具体的child cursor，或者是其依赖的对象，都需要将其pin住。然后做完操作在unpin。

发现pin的问题后，我根据文档的提示检查了v$latch_misses视图，通过这个视图我们可以看到在函数kglpnc:child和kglupc:child上面sleep_count和wtr_slp_count两个值是很高的。sleep_count字段记录进程在此位置hold对应latch而处于sleep状态的次数，而wtr_slp_count记录此内核位置request对应latch的次数。通过文档我们发现kglpnc:child和kglupc:child正好对应了pin和unpin的函数操作。

SQL> select * from v$latch_misses where PARENT_NAME = 'library cache' order by SLEEP_COUNT desc;

PARENT_NAME    WHERE                                SLEEP_COUNT WTR_SLP_COUNT
-------------- ------------------------------------ ----------- -------------
library cache kglpndl: child: before processing      176450042      56119602
library cache kglpnc: child                          175151792     205921071
library cache kglupc: child                          157330567     135873756
library cache kgllkdl: child: cleanup                103775154      93107365

在网上和Mos上胡乱搜索一通，还真找到了一个解决办法，可以将参数” cursor_space_for_time”改为true，能有效的将部分cursor给pin在内存中，这样就可以减少pin和unpin产生的次数，不过修改这个参数又是有风险的。长期把这些cursor pin在shared pool内存当中，内存一定要足够大才行，不然容易报ORA-04031的错误，毕竟这可是9i的数据库呀。另外一个风险就是你的应用有cursor泄漏，那么泄漏的cursor会浪费大量内存并在一段时间的运行之后对性能产生负面影响。

所以当我们提出修改这个参数的时候，客户也说了：”年关将至，我们最好保守一些，等过完年之后在大动干戈，现在是要想办法让他把这段时间撑过去。“

三、保守，促使需要寻找根本原因

既然要求我们保守一点，那就需要寻找问题的根源。这就需要找到引起Library cache争用的具体的对象。通过对系统的监控，我们发现了下列可疑的信息。

可以从图中看到。我们的Library cache争用主要集中在table_这一类的东西上面，那”table_”代表着什么呢？

通过一系列的文档和资料发现叫做伪游标(pseudo cursor)。首先我们在执行一条SQL语句的时候会在内存里面申请一块区域存放我们的游标，这个内存区域就是我们的Library cache。我们可以通过v$open_cursor查询我们打开的游标，同时我们也可以通过我们的v$sql、v$sqlarea查询执行过的游标，但是会有一个奇怪的现象，一部分v$open_cursor中打开的游标在v$sql或者v$sqlarea中是查不到的。那么这一类不存在的游标有一些就是我们的伪游标了。伪游标的具体表现形式就类似”table_4_9_d6c6_0_0_0“。

SQL> select NAME,TYPE from v$db_object_cache where name like '%table_%';
NAME TYPE
------------------------------------------------------------
table_4_9_d6c6_0_0_0 CURSOR

伪游标是怎么产生的呢？在Oracle访问数据字典表、Lob字段或者Nchar、Nvarchar2的时候，Oracle内部都会提供伪游标这个结构去直接访问数据库对象，这么做的好处是省去了写一个显示的Select语句的开销。

还有一些时候，我们的AWR报告会显示下面的一系列的内容。显示**SQL Text Not Avaliable **，这类的SQL有SQL ID，可是没有SQL Text，这类的SQL也可能是伪游标。

Latch Free、Library cache伪游标(pseudo cursor)之间的那些事3

前面介绍了伪游标是怎么产生的，那么如何查询伪游标是在那个对象产生的呢？我们可以通过v$open_cursor查询到打开的伪游标，可以看到它的展现形式是“table_4_9_d6c6_0_0_0”，那么这个中间有一个d6c6，我们需要将这个十六进制的字符串转换成十进制，转换成十进制后就是54982，这个值对应我们的object_id。然后我们就能把object_name找出来。

还有一个办法就是在文档1298471.1提供了一个脚本可以直接帮我们查到相关的对象。脚本内容如下：

create or replace view h$pseudo_cursor as
select Pseudo_cursor, sql_id,obj_id hex_obj_id
   ,obj# object_id, u.name owner, o.name object_name
from (select distinct
   KGLNAOBJ Pseudo_cursor,kglobt03 sql_id
   ,substr(KGLNAOBJ
   ,instr(KGLNAOBJ,'_',1,3)+1
   ,instr(KGLNAOBJ,'_',1,4)-instr(KGLNAOBJ,'_',1,3)-1) obj_id
   ,(case when
   replace(translate(substr(upper(KGLNAOBJ)
   ,instr(KGLNAOBJ,'_',1,3)+1
   ,instr(KGLNAOBJ,'_',1,4)
   -instr(KGLNAOBJ,'_',1,3)-1)
   ,'0123456789ABCDEF','................')
   ,'.') is null then 'Y' else 'N' end) is_safe_to_compare
   from x$kglob) k
   , obj$ o, user$ u
where obj#=decode(is_safe_to_compare,'Y',to_number(obj_id,'xxxxxxxxxx'),0)
   and o.owner#=u.user#;

找到了这些伪游标，我们又找到了其相关的对象，接下来就是找到和这些对象相关的SQL语句，最保守的做法就是把能想办法把一部分LOB类的SQL进行改造。或者是高峰期错开运行。

上一篇：对一次drop分区引发性能问题的思考
下一篇：软件全生命周期管理体系建设