AWR报告中Execute to parse %的浅析

王亮

Execute to parse %，AWR报告中，该指标主要指的是sql的执行次数与解析次数的比率。

关于Execute toParse的描述：

the only way toinfluence that number is to either change

a) the number oftimes you parse. b) the number of times you execute.

The formula used:

Execute to Parse%: dscr , round(100*(1-:prse/:exe),2) pctval

Execute to Parse %：

一个语句执行和分析了多少次的度量。计算公式为：Execute to Parse =100 * (1 - Parses/Executions)。如果系统Parses > Executions，就可能出现该比率小于 0 的情况。该值<0 通常说明 shared pool 设置或者语 句效率存在问题，造成反复解析，reparse 可能较严重,或者是可能同snapshot 有关，通常说明数据库性能存在一定问题。

If the number ofparse calls is near the number of execute calls, then this ratio drifts towardszero (as yours is). As the number of execute calls increases (while holdingparse calls constant), this number drifts towards 100%. That means you haveparsed a statement ONCE and executed it MANY TIMES (that is good, that is best)

cursor sharing =similar MIGHT change a hard parse into a soft parse (take a very very very badthing and make it simply very very bad). cursor sharing similar CANNOT changethe number of times parse is invoked however.

There isprecisely, exactly and only ONE person that can do that. That is theapplication developer.

When they say“parse this”, we parse it - it matters not what the value of cursor sharing is(if you have a hard parse problem, if your soft parse percent is below 99%, youneed to have the coders FIX that, you have (in addition to performance, memory,scalability issues) a HUGE security risk if you are not using binds).

The developersmust cache open cursors they know will be used over and over. The easiest way(to me) to accomplish this is to move all SQL into plsql, plsql automagicallycaches statements for us, it is the most efficient method to interface with thedatabase.

Alternatively,they can program it, or they can see if the API they are using can do itmagically for them (search for jdbc statement caching on google for example ifyou are using jdbc)

But it will haveto be done in the application, there is nothing we can do outside of theapplication to influence how often it parses.

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1594740500346667363

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

session_cached_cursor：

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

可以把 Executeto Parse %和Soft Parse %这两个参数放在一起看。

  1.如果两个参数同时很低时，说明硬解析次数多，建议使用绑定变量。

  2.如果Soft Parse%高，而Execute to Parse %比低时(<40%)，说明执行解析比率低，可以通过静态sql、动态绑定、调整session_cached_cursor参数、调整open_cursor等方法来减少软解析。

   session_cached_cursor ：表示一个会话中可以缓存多少个cursor。让后续相同的sql语句不在打开游标，从而避免软解析来提高性能（软解析与硬解析同样消耗资源）。

   session cursor cache ：用来存储关闭了的cursor。当一个cursor关闭之后，oracle会检查这个cursor的request次数是否超过3次，如果超过了三次，就会放入session cursor cache，这样在下次parse的时候，就可以从session cursor cache中找到这个statement, session cursor cache的管理也是使用LRU。session_cached_cursors这个参数是控制session cursorcache的大小的。session_cached_cursors定义了session cursor cache中存储的cursor的个数。这个值越大，则会消耗的内存越多。

测试session_cached_cursors参数的值设置是否合理（两种方法查看）：

1.查看session_cached_cursors的使用率

Select'session_cached_cursors' Parameter,

    Lpad(Value, 5) Value,

    Decode(Value, 0, ' n/a', To_Char(100 * Used/ Value, '990') || '%') Usage

From (SelectMax(s.Value) Used

      From V$statname n, V$sesstat s

     Where n.Name = 'session cursor cachecount'

      And s.Statistic# = n.Statistic#),

    (Select Value From V$parameter Where Name ='session_cached_cur

Union All

Select'open_cursors',

    Lpad(Value, 5),

    To_Char(100 * Used / Value, '990') || '%'

From (SelectMax(Sum(s.Value)) Used

      From V$statname n, V$sesstat s

     Where n.Name In

        ('opened cursors current', 'sessioncursor cache count')

      And s.Statistic# = n.Statistic#

     Group By s.Sid),

    (Select Value From V$parameter Where Name ='open_cursors');

2.查看sessioncursor cache hits 和parse count（total）的比率

   select name,value from v$sysstat where name like '%cursor%';

   select name,value from v$sysstat where name like '%parse%';

session cursorcache hits 和parse count（total） 就是总的parse次数中，在session cursor cache中找到的次数，所占比例越高，性能越好。如果比例比较低，并且有剩余内存的话，可以考虑加大该参数。但是参数session_cached_cursors并不是越大越好，太大会引起pga缓存碎片，消耗内存，

   例：alter system setsession_cached_cursors=100 scope=spfile;（修改后需要重启数据库才能生效）

  测试open_cursors的大小是否合理：

     SELECT MAX(A.VALUE) AS HIGHEST_OPEN_CUR,P.VALUE AS MAX_OPEN_CUR FROM V$SESSTAT A, V$STATNAME B, V$PARAMETER P WHEREA.STATISTIC# = B.STATISTIC#  AND B.NAME = 'opened cursors current' AND P.NAME = 'open_cursors'  GROUP BY P.VALUE;

分析及调整

查看当前系统session配置

SQL> Select'session_cached_cursors' Parameter,

  2     Lpad(Value, 5) Value,

  3     Decode(Value, 0, ' n/a', To_Char(100 * Used / Value, '990') || '%')Usage

  4  From(Select Max(s.Value) Used

  5       From V$statname n, V$sesstat s

  6      Where n.Name = 'session cursor cache count'

  7       And s.Statistic# = n.Statistic#),

  8     (Select Value From V$parameter Where Name = 'session_cached_cursors')

  9 Union All

10 Select 'open_cursors',

11     Lpad(Value, 5),

12     To_Char(100 * Used / Value, '990') || '%'

13  From(Select Max(Sum(s.Value)) Used

14       From V$statname n, V$sesstat s

15      Where n.Name In

16         ('opened cursors current', 'session cursor cache count')

17       And s.Statistic# = n.Statistic#

18       Group By s.Sid),

19     (Select Value From V$parameter Where Name = 'open_cursors');

PARAMETER              VALUE                USAGE

------------------------------------------ -----

session_cached_cursors    50                  98%   --当前session_cached_cursors的使用率为98%，应考虑增加该参数值

open_cursors             300                  20%   --当前open_cursors仅为20%，说明当前够用

也可以通过下面的脚步查看cursor的使用情况

SQL> SELECTMAX(A.VALUE) AS HIGHEST_OPEN_CUR, P.VALUE AS MAX_OPEN_CUR  

  2  FROM V$SESSTAT A, V$STATNAME B, V$PARAMETER P  

  3 WHERE A.STATISTIC# = B.STATISTIC#

  4   AND B.NAME = 'opened cursors current'

  5   AND P.NAME = 'open_cursors'  

  6 GROUP BY P.VALUE;

HIGHEST_OPEN_CURMAX_OPEN_CUR

-------------------------------------------------------------

300              19

--查看cursor相关统计值，实例级别

SQL> selectname,value from v$sysstat where name like '%cursor%';

NAME                                VALUE

---------------------------------------------

opened cursorscumulative            819271677

opened cursorscurrent                     350

pinned cursorscurrent                       6

session cursorcache hits            340959054

session cursorcache count           399411460

cursor authentications                   56465

SQL的执行包括几个步骤：打开、解析、绑定、执行、抓取、关闭。

硬解析：SQL语句在library cache无缓存

软解析：SQL语句在library cache找到了执行计划

软软解析：在pga内搜索session cursor cache list列表中找到对应的SQL，无论软解析、还是软软解析，都有解析这个操作。

要改善解析与执行的比率关系，就需要增加无解析的次数，无解析就是不再解析，为SQL绑定不同的变量，然后执行。

这样做的前提就是：1、Session不能断开；2、Session执行过解析过的SQL不要关闭；满足这两点就可以实现无解析。

根据上面的分析以及session_cached_cursors的使用率分析，将参数session_cached_cursors增加至300

alter system setsession_cached_cursors=300 scope=spfile;

修改后要重启数据库方能生效。

SQL>@cursor_usage  --执行查询可以看到调整后session_cached_cursorsusage完全充足

PARAMETER              VALUE                USAGE

------------------------------------------ -----

session_cached_cursors   300                  12%

open_cursors             300                  12%

小结

a、Execute toParse%是执行到解析的度量，最佳情况下，是一次解析多次执行，最佳的是使用软软解析；

b、涉及到的参数主要是OPEN_CURSORS与session_cached_cursors，前者定义单个session可以打开游标数，后者定义游标可缓存长度

c、通常情况下上述两个参数的使用率应尽可能偏离80%，以确保性能及资源充足，注意，这2个参数增大应考虑是否pga以及sga需要进一步调整