Execute to Parse%是AWR報告中Instance Efficiency Percentages部分中重要的一個性能指標，反應了數據庫SQL解析和執行的比率。這個比率值同時也涉及到了與cursor相關的參數以及硬解析，軟解析，軟軟解析等。本文是圍繞這個比率進行展開及描述。
--下面是來自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
二、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需要進一步調整
