Oracle Execute to Parse 执行解析比分析

蔚落 2022-08-09 14:54 124阅读 0赞

> Execute to Parse%是AWR报告中Instance Efficiency Percentages部分中重要的一个性能指标，反应了数据库SQL解析和执行的比率。这个比率值同时也涉及到了与cursor相关的参数以及硬解析，软解析，软软解析等。本文是围绕这个比率进行展开及描述。

# 一、什么是Execute to Parse% #

--下面是来自AWR报告的相关信息
    Instance Efficiency Percentages (Target 100%)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                Buffer Nowait %:   99.60       Redo NoWait %:  100.00
                Buffer  Hit   %:   99.99    In-memory Sort %:  100.00
                Library Hit   %:   99.96        Soft Parse %:   99.98
             Execute to Parse %:   -8.46         Latch Hit %:   98.39
    Parse CPU to Parse Elapsd %:   90.79     % Non-Parse CPU:   97.35
    
    Statistic                              Total     per Second     per Trans
    -------------------------------- ----------- -------------- -------------
    index crx upgrade (prefetch)               0            0.0           0.0
    opened cursors cumulative          2,296,221           91.0         780.5
    parse count (describe)                     3            0.0           0.0
    parse count (failures)                     5            0.0           0.0
    parse count (hard)                       512            0.0           0.2
    parse count (total)                2,272,639           90.1         772.5
    parse time cpu                        16,934            0.7           5.8
    parse time elapsed                    18,651            0.7           6.3

> Tom大师关于Execute to Parse的描述：  
> the only way to influence that number is to either change
> 
> a) the number of times 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 of parse calls is near the number of execute calls, then this ratio drifts towards zero (as yours is). As the number of execute calls increases (while holding parse calls constant), this number drifts towards 100%. That means you have parsed 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 bad thing and make it simply very very bad). cursor sharing similar CANNOT change the number of times parse is invoked however.
> 
> There is precisely, exactly and only ONE person that can do that. That is the application 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%, you need 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 developers must 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 automagically caches statements for us, it is the most efficient method to interface with the database.
> 
> Alternatively, they can program it, or they can see if the API they are using can do it magically for them (search for jdbc statement caching on google for example if you are using jdbc)
> 
> But it will have to be done in the application, there is nothing we can do outside of the application to influence how often it parses.
> 
> [https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11\_QUESTION\_ID:1594740500346667363][https_asktom.oracle.com_pls_asktom_f_p_100_11_0_P11_QUESTION_ID_1594740500346667363]

## 二、Execute to Parse相关参数描述 ##

> Google了一些关于这个问题的描述，大部分描述涉及到了以下2个参数  
> OPEN\_CURSORS： specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors.
> 
> It is important to set the value of OPEN\_CURSORS high enough to prevent your application from running out of open cursors. The number will vary from one application to another. Assuming that a session does not open the number of cursors specified by OPEN\_CURSORS, there is no added overhead to setting this value higher than actually needed.
> 
> SESSION\_CACHED\_CURSORS: specifies the number of session cursors to cache. Repeated parse calls of the same SQL (including recursive SQL) or PL/SQL statement cause the session cursor for that statement to be moved into the session cursor cache. Subsequent parse calls will find the cursor in the cache and do not need to reopen the cursor. Oracle uses a least recently used algorithm to remove entries in the session cursor cache to make room for new entries when needed.
> 
> If SESSION\_CACHED\_CURSORS is not set, it defaults to 0 and no cursors will be cached for your session. (Your cursors will still be cached in the shared pool, but your session will have to find them there.) If it is set, then when a parse request is issued, Oracle checks the library cache to see whether more than 3 parse requests have been issued for that statement. If so, Oracle moves the session cursor associated with that statement into the session cursor cache. Subsequent parse requests for that statement by the same session are then filled from the session cursor cache, thus avoiding even a soft parse. (Technically, a parse can’t be completely avoided; a “softer” soft parse is done that’s faster and requires less CPU.)
> 
> In the session cursor cache, Oracle manages the cached cursors using a LRU list. Once more than SESSION\_CACHED\_CURSORS closed cursors are cached, Oracle starts dropping cached cursors off the LRU end of the list whenever it needs to make room to cache a new cursor.
> 
> 当我们执行一条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更高的性能，也就是说连open cursor的动作都给省了。

# 三、分析及调整 #

查看当前系统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> SELECT MAX(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_CUR MAX_OPEN_CUR
    ---------------- ---------------------------------------------
    300              19
    
    --查看cursor相关统计值，实例级别
    SQL> select name,value from v$sysstat where name like '%cursor%';
    
    NAME                                VALUE
    ----------------------------------- ----------
    opened cursors cumulative            819271677
    opened cursors current                     350
    pinned cursors current                       6
    session cursor cache hits            340959054
    session cursor cache 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 set session_cached_cursors=300 scope=spfile;
    
    修改后要重启数据库方能生效。
    
    SQL> @cursor_usage  --执行查询可以看到调整后session_cached_cursors usage完全充足
    
    PARAMETER              VALUE                USAGE
    ---------------------- -------------------- -----
    session_cached_cursors   300                  12%
    open_cursors             300                  12%

# 四、sql\_id az33m61ym46y4 #

通过调整之后跟踪，Execute to Parse为负值的情形依旧存在
    Instance Efficiency Percentages (Target 100%)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                Buffer Nowait %:  100.00       Redo NoWait %:  100.00
                Buffer  Hit   %:   99.96    In-memory Sort %:  100.00
                Library Hit   %:   99.88        Soft Parse %:   99.93
             Execute to Parse %:   -5.17         Latch Hit %:   98.47
    Parse CPU to Parse Elapsd %:   90.85     % Non-Parse CPU:   98.40
    
    进一步分析
    SQL> set linesize 200;
    SQL> set pagesize 1000;
    SQL> col sql_text format a40;
    SQL> SELECT st.sql_id,
      2    -- sq.sql_text,
      3           st.executions_total,
      4           st.parse_calls_total,
      5           ROUND (100 * (1 - (st.parse_calls_total / st.executions_total)), 2)
      6              execute_to_parse,
      7           st.executions_delta,
      8           st.parse_calls_delta,
      9           ROUND (100 * (1 - (st.parse_calls_delta / st.executions_delta)), 2)
     10              delta_ratio
     11      FROM DBA_HIST_SQLSTAT st, DBA_HIST_SQLTEXT sq, DBA_HIST_SNAPSHOT s
     12     WHERE     s.snap_id = st.snap_id
     13           AND s.begin_interval_time >=
     14                  TO_DATE ('2015-10-22 09:30:00', 'YYYY-MM-DD HH24:MI:SS')
     15           AND s.end_interval_time <=
     16                  TO_DATE ('2015-10-22 17:00:00', 'YYYY-MM-DD HH24:MI:SS')
     17           AND st.sql_id = sq.sql_id
     18           AND st.parsing_schema_name in ('WX_USER','WX_XJW','XLKPORTALS','SCMONLINE')
     19           AND st.executions_total != 0
     20           AND st.executions_delta != 0
     21  ORDER BY delta_ratio;
    
    SQL_ID        EXECUTIONS_TOTAL PARSE_CALLS_TOTAL EXECUTE_TO_PARSE EXECUTIONS_DELTA PARSE_CALLS_DELTA DELTA_RATIO
    ------------- ---------------- ----------------- ---------------- ---------------- ----------------- -----------
    az33m61ym46y4            91316             91390             -.08            12530             12542         -.1
    az33m61ym46y4            78786             78848             -.08            12504             12517         -.1
    az33m61ym46y4            41137             41166             -.07            12388             12399        -.09
    az33m61ym46y4            66282             66330             -.07            12550             12561        -.09
    az33m61ym46y4            28749             28767             -.06            12589             12599        -.08
    az33m61ym46y4            53732             53769             -.07            12595             12603        -.06

> –从上面的查询可以看出sql\_id az33m61ym46y4是罪魁祸首 Metalink上有唯一一篇关于这个sql的描述，不过也没有看到太多的建议。 同样的情形在Oracle  
> 11.1.1.7上也存在，不过这条SQL在awr报告中没有当前11.2.1.0中突出 以下是metalink上的具体描述 Bug 12318969 : V2301072 MUST RECYCLE THE BATCH TPW WHEN DATABASE INSTANCE  
> IS RECYCLED Bug Attributes Type B - Defect Fixed in Product Version  
> Severity 2 - Severe Loss of Service Product Version 2.2 Status 92 -  
> Closed, Not a Bug Platform 226 - Linux x86-64  
> Created 02-Apr-2011 Platform Version NO DATA Updated 12-Oct-2011 Base  
> Bug N/A Database Version N/A Affects Platforms Generic Product  
> Source Oracle Knowledge, Patches and Bugs related to this bug  
> Related Products Line More Applications &  
> Technologies Family Industry Solutions Area Utilities Product 2245 -  
> Oracle Utilities Framework
> 
> Hdr: 12318969 N/A BATCH 2.2 BTJOBSUB PRODID-2245 PORTID-226 Abstract:  
> V2301072 MUST RECYCLE THE BATCH TPW WHEN DATABASE INSTANCE IS RECYCLED
> 
> **\* 04/01/11 03:56 pm \*** Short Description:  
> —————— instance is recycled Detailed Problem Statement:  
> ————————– When database node crashes or when database node is recycled , there will error they, however do not  
> re-connect to the database. way the online application reconnects  
> after a database recycle. Workaround  
> ———- groups. Impact on Business see attachments  
> \* 04/01/11 04:11 pm *\**  
> \* 04/05/11 03:39 pm *\**  
> \* 04/05/11 04:35 pm *\** (CHG: Sta->11 Asg->FJOCSON)  
> \* 04/06/11 08:56 am *\** (CHG: Sta->30 Asg->MZEEMAN)  
> \* 04/06/11 08:56 am *\**  
> \* 04/06/11 04:40 pm *\**  
> \* 04/08/11 12:34 pm *\**  
> \* 04/08/11 12:35 pm *\** (CHG: Sta->10 Asg->FJOCSON)  
> \* 04/08/11 12:35 pm *\**  
> \* 04/08/11 12:56 pm *\** (CHG: Sta->30 Asg->MZEEMAN)  
> \* 04/08/11 12:56 pm *\**  
> \* 04/15/11 05:38 pm *\**  
> \* 05/05/11 01:25 pm *\**  
> \* 05/05/11 03:29 pm *\** (CHG: Sta->10 Asg->ASHORTEN)  
> \* 05/05/11 03:29 pm *\**  
> \* 05/05/11 06:38 pm *\**  
> \* 05/05/11 06:38 pm *\** Updated the Batch Best Practices under “Threadpools and Database Recycling”  
> \* 05/06/11 02:42 pm *\** (CHG: Sta->30)  
> \* 05/06/11 02:42 pm *\**  
> \* 05/17/11 03:53 pm *\** (CHG: Sta->92)  
> \* 05/17/11 03:53 pm *\** (CHG: Sta->30)  
> \* 05/17/11 04:38 pm *\**  
> \* 05/24/11 03:55 pm *\**  
> \* 07/06/11 09:05 am *\**  
> \* 07/11/11 03:46 pm *\**  
> \* 07/11/11 03:49 pm *\** (CHG: Sta->11 Asg->ASHORTEN)  
> \* 07/11/11 03:49 pm *\**  
> \* 07/11/11 04:15 pm *\** (CHG: Sta->92 Asg->MZEEMAN)  
> \* 07/21/11 09:04 am *\** (CHG: Sta->10 Asg->ASHORTEN)  
> \* 07/21/11 09:04 am *\** When we changed the hibernate.c3p0.idle\_test\_period = 10 property per SPL recommendation,  
> a side affect of this change is that a SQL is executed way too  
> frequently.  
> \* 07/21/11 09:05 am *\**  
> \* 07/21/11 09:06 am *\** When PG&E changed the hibernate.c3p0.idle\_test\_period = 10 property per SPL recommendation,  
> a side affect of this change is that a SQL is executed way too  
> frequently. SQL\_ID az33m61ym46y4 SELECT NULL AS table\_cat, o.owner  
> AS table\_schem, o.object\_name AS table\_name, o.object\_type AS  
> table\_type, NULL AS remarks FROM all\_objects o WHERE o.owner LIKE  
> :1 ESCAPE ‘/’ AND o.object\_name LIKE :2 ESCAPE ‘/’ AND  
> o.object\_type IN (‘xxx’, ‘TABLE’) ORDER BY table\_type, table\_schem,  
> table\_name  
> —————— Query is run 150,000+ per hour–apparently forever, on nodes 3,4,5, which totals 500,000 executions per hour  
> \* 07/21/11 09:06 am *\**  
> \* 08/02/11 04:08 pm *\**  
> \* 08/02/11 04:32 pm *\** The SQL shown in the example is really not from the product. It is not an SQL I think exists in the product as  
> such (It is a database query and in fact CISADM should not really  
> have access to ALL\_TABLES. Please verify this is the ONLY SQL that  
> is excessive. The settings do not get the SQL to execute more than  
> they should. It only should affect reconnection checks.  
> \* 08/09/11 03:53 pm *\**  
> \* 08/09/11 03:54 pm *\**  
> \* 08/10/11 08:56 am *\**  
> \* 08/10/11 12:22 pm *\** (CHG: Sta->11 Asg->FJOCSON)  
> \* 08/10/11 02:32 pm *\**  
> \* 08/10/11 02:32 pm *\** (CHG: Sta->30 Asg->MZEEMAN)  
> \* 08/31/11 11:06 pm *\**  
> \* 08/31/11 11:14 pm *\**  
> \* 10/11/11 03:23 pm *\** (CHG: Sta->92)  
> \* 10/11/11 03:23 pm *\**

## 五、小结 ##

> a、Execute to Parse%是执行到解析的度量，最佳情况下，是一次解析多次执行，最佳的是使用软软解析；  
> b、涉及到的参数主要是OPEN\_CURSORS与session\_cached\_cursors，前者定义单个session可以打开游标数，后者定义游标可缓存长度  
> c、通常情况下上述两个参数的使用率应尽可能偏离80%，以确保性能及资源充足，注意，这2个参数增大应考虑是否pga以及sga需要进一步调整

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