AWR Report and session_cached_cursor

　　近日陆续接到项目组反馈的ORA-00020错误反馈，并且监控数据库服务器还有CPU占用较高的情况，初步判定是新上线的一个项目连接数过多导致超出最大进程数的问题，ORACLE的processes参数默认是150，通过调整该参数至400后解决ORA-00020问题，但CPU占用较高(16核均在80%左右)的问题依然存在。当天生成AWR报告观察实例性能主要参数，如图：

　　观察发现Excute to Parse %和Parse CPU to Parse Elapsd %的比值与目标值差距较大。

　　Execute to Parse %说明执行sql和解析sql之间的比例，越大越好，计算公式是100*（1-parse/execute），其值越大，表示一次解析，越多次执行，当然如果出现parse比excute多的情况，该值也会出现负值。

　　Parse CPU to Parse Elapsd %说明在解析sql语句过程中，cpu用时占整个的解析时间比例，如果是100%，说明没有产生等待。计算公式为：100*（parse cpu/parse elapsed），从公式也可以分析，parse elapsed=parse cpu+各种wait，如果该值越接近100，那么等待是越少的。

　　先考虑Excute to Parse %，观察Soft Parse %为99.84%，说明软解析比例还是可以的，但Execute to Parse %值（76.28）表明，parse24次，excute了100次。如果parse了1次excute了100次，那么该参数值应为99%。推测是应该是shared pool中的sql执行计划被淘汰了，或者没有绑定变量（同一类语法的sql语句没有采用shared pool中的执行计划），导致parse次数增加。

　　再看Parse CPU to Parse Elapsd %的值为49.77%，也就是说存在未知等待。查看其他影响因素，未见异常，顺其自然想到sql语句执行时需要解析（软解析较硬解析消耗资源较少），但大量绑定变量查询的情况下还是要申请share pool中shared pool latch、library cache lock、library cache pin几个闩都要耗费时间，是否软软解析占比少了？

　　两个参数：open_cursors和session_cached_cursor.

　　open_cursors：设定每个session（会话）最多能同时打开多少个cursor（游标）。相关代码：

--检查当前程序实际打开cursor的最大值和open_cursor的设定值
SELECT MAX(A.VALUE) AS HIGHEST_OPEN_CUR, P.VALUE AS MAX_OPEN_CUR  
FROM V$SESSTAT A, V$STATNAME B, V$PARAMETER P  
WHERE A.STATISTIC# = B.STATISTIC#  
AND B.NAME = 'opened cursors current'  
AND P.NAME = 'open_cursors'  
GROUP BY P.VALUE;  
--HIGHEST_OPEN_CUR和MAX_OPEN_CUR如果过于接近，则容易引发“ORA-01000: 超出打开游标的最大数”错误，如果MAX_OPEN_CUR值过大，则无端消耗系统资源。

--确定游标溢出的会话
SELECT A.VALUE, S.USERNAME, S.SID, S.SERIAL# 
FROM V$SESSTAT A, V$STATNAME B, V$SESSION S 
WHERE A.STATISTIC# = B.STATISTIC# 
AND S.SID = A.SID 
AND B.NAME = 'opened cursors curent';

　　session_cached_cursor：设定每个session（会话）最多可以缓存多少个关闭掉的cursor。相关代码：

SQL> SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%cursor%';
NAME                      VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative            354078579
opened cursors current              408
pinned cursors current              270
session cursor cache hits            139214487
session cursor cache count              1261605
cursor authentications                  20226
 
6 rows selected.
 
SQL> SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%parse%';
NAME                      VALUE
---------------------------------------------------------------- ----------
parse time cpu                  1503693
parse time elapsed                1693356
parse count (total)                2258291
parse count (hard)                  77460
parse count (failures)                  39866
parse count (describe)              512
 
6 rows selected.
--session cursor cache hits就是系统在高速缓存区中找到相应cursors的次数，parse count(total)就是总的解析次数，二者比值越高，性能越好。
 
--判断'session_cached_cursors' 的使用情况。如果使用率为100%则增大这个参数值
SELECT 'session_cached_cursors' PARAMETER,
       LPAD(VALUE, 5) VALUE,
       DECODE(VALUE, 0, ' n/a', TO_CHAR(100 * USED / VALUE, '990') || '%') USAGE
  FROM (SELECT MAX(S.VALUE) USED
          FROM V$STATNAME N, V$SESSTAT S
         WHERE N.NAME = 'session cursor cache count'
           AND S.STATISTIC# = N.STATISTIC#),
       (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'session_cached_cursors')
UNION ALL
SELECT 'open_cursors',
       LPAD(VALUE, 5),
       TO_CHAR(100 * USED / VALUE, '990') || '%'
  FROM (SELECT MAX(SUM(S.VALUE)) USED
          FROM V$STATNAME N, V$SESSTAT S
         WHERE N.NAME IN
               ('opened cursors current', 'session cursor cache count')
           AND S.STATISTIC# = N.STATISTIC#
         GROUP BY S.SID),
       (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'open_cursors');

 

　　当执行一条sql语句的时候,将会在shared pool产生一个library cache object,cursor就是其中针对于sql语句的一种library cache object.另外我们会在pga有一个cursor的拷贝,同时在客户端会有一个statement handle,这些都被称为cursor,在v$open_cursor里面我们可以看到当前打开的cursor和pga内cached cursor。

　　session_cached_cursor这个参数限制了在pga内session cursor cache list的长度,session cursor cache list是一条双向的lru链表,当一个session打算关闭一个cursor时,如果这个cursor的parse count超过3次,那么这个cursor将会被加到session cursor cache list的MRU端.当一个session打算parse一个sql时,它会先去pga内搜索session cursor cache list,如果找到那么会把这个cursor脱离list,然后当关闭的时候再把这个cursor加到MRU端.session_cached_cursor提供了快速软分析的功能,提供了比soft parse更高的性能。

　　查看该参数的默认值，将其适当增加重启数据库，软软解析次数增加减少了等待，一天之后观察Excute to Parse %和Parse CPU to Parse Elapsd %均达到97%以上，CPU占用减少（降低至峰值在30%左右另外分布比较分散）。从结果看，增加了sql语句软软解析的次数，sql执行产生的资源申请、释放的wait减少了，在生产库上再次生成AWR report可以看到Excute to Parse %和Parse CPU to Parse Elapsd %的比值均接近于目标值100%，至此此问题解决完毕。（此办法治标不治本，根本上还是需要根据应用的业务来调整，例如为何sql语句频繁查询，能不能从业务上优化？应用的sql语句是否绑定变量？此例中应用程序中的sql语句均已绑定变量，应用的业务层面则未深究）

　　调整后AWR报告截图如下：

　　

　　一些AWR参数含义：

   1. Buffer Nowait 说明 在从内存取数据的时候，没有经历等待的比例，期望值是100%
   2. Buffer Hit 说明从内存取数据的时候，buffer的命中率的比例，期望值是100%，但100%并不代表性能就好。例如：1）buffer cache中存储的缓存数据过多，命中率是提升了，但占用其他内存资源；2）低选择的索引，正好选择出来的数据正好位于内存中，在内存大量数据中进行单行记录筛选较之在硬盘中加载单行数据反而消耗了更多的系统资源，此时命中率也为100%；3）同时开100个session更新一个小表，有60个session同时更新同一行，命中率也是100%，还是会产生锁等待；以上三种情况说明：不能单依靠某一个指标进行性能判断，还需要结合其他的指标来分析。
   3. Library Hit 说明sql在Shared Pool的命中率，期望值是100%
   4. Execute to Parse 说明解析sql和执行sql之间的比例，越高越好，说明一次解析，到处执行，如果parse多，execute少的话，还会出现负数，因为计算公式是100*（1-parse/execute）
   5. Parse CPU to Parse Elapsd 说明在解析sql语句过程中，cpu占整个的解析时间比例，，期望值是100%，说明没有产生等待，需要说明的是，即使有硬解析，只要cpu没有出现性能问题，也是可以容忍的，比较硬解析也有它的好处的
   6. Redo NoWait 说明在产生日志的时候，没有产生等待，期望值是100%
   7. Soft Parse 说明软解析的比例，期望值是100%，有一点要说明的是，不要单方面的追求软解析的高比例，而去绑定变量，要看性能的瓶颈在哪里
   8. Latch Hit 说明latch的命中率，期望值是100%，latch类似锁，是一种内存锁，但只会产生等待，不会产生阻塞，和lock还是有区别的，latch是在并发的情况下产生的
   9. Non-Parse CPU 说明非解析cpu的比例，越高越好，用100减去这个比例，可以看出解析sql所花费的cpu，100-99.30=0.7，说明花费在解析sql上的cpu很少

 

参照了下面两位仁兄的博客：

http://blog.sina.com.cn/s/blog_4bda1bf301010etx.html

http://www.cnblogs.com/rootq/archive/2009/08/27/1554747.html