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 的情况。该值 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 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需要进一步调整
：