Oracle Execute to Parse for parsing Ratio Analysis

Oracle Execute to Parse for parsing Ratio Analysis

Execute to Parse % is an important performance indicator in the Instance Efficiency Percentages section in the AWR report, reflecting the ratio of Database SQL parsing to execution. This ratio value also involves cursor-related parameters and hard parsing, soft parsing, soft parsing, and so on. This document describes the ratio.

1. What is Execute to Parse %

-- The following is information about Instance Efficiency Percentages (Target 100%) from the AWR report) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~ 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 average -------------------- ----------- 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

Master Tom's description of 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 %:
The number of times a statement is executed and analyzed. The formula is: Execute to Parse = 100 * (1-Parses/Executions ). If the system Parses> Executions, this ratio may be less than 0. This value <0 usually indicates that there is a problem with the shared pool setting or sentence efficiency, resulting in repeated parsing. The reparse may be serious, or may be related to snapshot. It usually indicates that the database performance is faulty.

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

2. Execute to Parse parameter description

Google has some descriptions about this problem. Most of the descriptions involve the following two parameters:
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.

When we execute an SQL statement, we will generate a library cache object in the shared pool. cursor is a library cache object for SQL statements. in addition, we will have a copy of cursor in pga and a statement handle on the client. These are called cursor, in v $ open_cursor, we can see the currently opened cursor and cached cursor in pga.

Session_cached_cursor:
This parameter limits the length of the session cursor cache list in pga. The session cursor cache list is a bidirectional lru linked list. When a session intends to close a cursor, if the parse count of the cursor exceeds three times, the cursor will be added to the mru end of the session cursor cache list. when a session intends to parse an SQL statement, it will first search for the session cursor cache list in pga. If it is found, the cursor will be removed from the list, then add the cursor to the MRU end when it is disabled. session_cached_cursor provides fast soft analysis and higher performance than soft parse. That is to say, the open cursor action is saved.

Iii. Analysis and Adjustment

View the current system session configuration SQL> Select 'session _ cached_cursors 'Parameter, 2 Lpad (Value, 5) Value, 3 Decode (Value, 0, 'n'/', to_Char (100 * Used/Value, '000000') | '%') 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 Sel Ect 'open _ cursors ', 11 Lpad (Value, 5), 12 To_Char (100 * Used/Value, '123 ') | '%' 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% -- the current session_cached_cursors usage is 98%. You should consider adding this parameter value open_cursors 300 20% -- currently open_cursors is only 20%, it indicates that the current usage is sufficient-you can also follow the steps below to view the usage of cursor SQL> SELECT MAX (. VALUE) AS HIGHEST_OPEN_CUR, P. value as MAX_OPEN_CUR 2 from v $ sesstat a, V $ statname B, V $ PARAMETER P 3 WHERE. STATISTIC # = B. STATISTIC #4 and B. NAME = 'opened cursors current' 5 and p. NAME = 'open _ cursors '6G Roup by p. VALUE; HIGHEST_OPEN_CUR MAX_OPEN_CUR -------------- limit 300 19 -- view the statistical value of cursor, instance-level 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 coun T 399411460 cursor authentications 56w. SQL execution involves several steps: open, parse, bind, execute, crawl, and close. Hard parsing: SQL statements in the library cache without cache soft parsing: SQL statements in the library cache find the execution plan soft parsing: Search for the session cursor cache list in the pga to find the corresponding SQL, this operation is available for both soft resolution and soft resolution. To improve the ratio between resolution and execution, you need to increase the number of times that no resolution is required. If no resolution is required, the system will not resolve the SQL statement, bind different variables to the SQL statement, and then execute the statement. The premise for doing so is: 1. The Session cannot be disconnected; 2. the SQL statements that have been parsed by the Session should not be closed; if these two conditions are met, no resolution can be implemented. Based on the above analysis and session_cached_cursors Usage Analysis, add the session_cached_cursors parameter to 300 alter system set session_cached_cursors = 300 scope = spfile. After modification, restart the database to take effect. SQL> @ cursor_usage -- execute the query and you can see that the adjusted session_cached_cursors usage is sufficient. PARAMETER VALUE USAGE -------------------------------- ----- session_cached_cursors 300 12% 300

Iv. SQL _id az33m61ym46y4

After adjustment, the Instance Efficiency Percentages (Target 100%) still exists when Execute to Parse is negative) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~ 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 further analysis 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.exe cutions_total, 4 st. parse_calls_total, 5 ROUND (100 * (1-(st. parse_calls_total/st.exe cutions_total), 2) 6 execute_to_parse, 7 st.exe cutions_delta, 8 st. parse_calls_delta, 9 ROUND (100 * (1-(st. parse_calls_delta/st.exe cutions_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 ('2017-10-22 09:30:00 ', 'yyyy-MM- DD HH24: MI: ss') 15 AND s. end_interval_time <= 16 TO_DATE ('1970-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.exe cutions_total! = 0 20 AND st.exe cutions_delta! = 0 21 order by delta_ratio; SQL _ID EXECUTIONS_TOTAL PARSE_CALLS_TOTAL limit EXECUTIONS_DELTA PARSE_CALLS_DELTA DELTA_RATIO ------------- -------------- limit ---------------- limit --------------- limit 91316 91390 -. 08 12530 12542 -. 1az33m61ym46y4 78786 78848 -. 08 12504 12517 -. 1az33m61ym46y4 41137 41166 -. 07 12388 12399 -. 09az33m61ym46y4 66282 66330 -. 07 12550 12561 -. 09az33m61ym46y4 28749 28767 -. 06 12589 12599 -. 08az33m61ym46y4 53732 53769 -. 07 12595 12603 -. 06

-From the preceding query, we can see that SQL _id az33m61ym46y4 is the only description of this SQL statement on Metalink. However, we have not seen many suggestions. In the same case in Oracle
11.1.1.7 also exists, but this SQL statement does not currently exist in THE awr report 11.2.1.0. THE following is THE specific description Bug on metalink: 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 versions 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 *

V. Summary

A. Execute to Parse % is the measurement from execution to resolution. In the best case, it is performed multiple times at a resolution. The best is soft resolution;
B. The parameters involved are mainly OPEN_CURSORS and session_cached_cursors. The former defines the number of opened cursors for a single session, and the latter defines the cache length of the cursor.
C. Normally, the usage of the above two parameters should be deviated from 80% as much as possible to ensure adequate performance and resources. Note that the increase of these two parameters should consider whether the pga and sga need to be further adjusted.