Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ Oracle Execute to Parse perform analytical Ratio Analysis     - Hadoop 2.6.0 stand-alone / pseudo-distributed installation (Server)

- ORA-04031 error appears in the solution run utlrp.sql (Database)

- After restarting network services, DNS address failure (Linux)

- Writing Better Bash build script 8 (Programming)

- Running into the site-wide HTTPS (Server)

- To restore the last time applications running when Ubuntu user log in again (Linux)

- Java 8 perspective annotation types (Programming)

- Vi (Vim) keyboard map and its basic commands (Linux)

- Linux Learning --- disk partition / relational representation + mount (Linux)

- Learn to read the source code of vmstat (Linux)

- Mass data storage application of MongoDB database (Database)

- How to run in support UDp Docker container JBoss data virtualization GA (Server)

- CentOS 6.4 of cron scheduled task configuration (Linux)

- Linux operating system security settings initial understanding (Linux)

- Quagga use authentication on strengthening BGP session security (Linux)

- ImageMagick Tutorial: How to cut images in Linux command line (Linux)

- Ubuntu UFW firewall settings Introduction (Linux)

- C language binary tree (Programming)

- Flow control message transmission between RAC (Database)

- Amazon EC2 server using Rsync + Inotify real-time synchronization (Server)

  Oracle Execute to Parse perform analytical Ratio Analysis
  Add Date : 2017-08-31      
  Execute to Parse% is AWR report Instance Efficiency Percentages important part of a performance index, reflecting the ratio of the database SQL parsing and execution. This ratio value is also related to the relevant parameters with the cursor and hard to resolve, analytic soft, soft parse the like. This article is about the expansion and the ratios described.

First, what is Execute to Parse%
- Here is the relevant information from the AWR reports
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 Masters on Execute to Parse description:
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%:
A statement is executed and analyzed how many times the metric. The formula is: Execute to Parse = 100 * (1 - Parses / Executions). If the system Parses> Executions, it is possible the ratio is less than 0 appears. The value <0 usually indicates a shared pool settings or problems statement efficiency, resulting in repeated parsing, reparse may be more serious, or may be related to the same snapshot, usually indicates there are certain problems database performance.

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 CAN NOT 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.


Two, Execute to Parse parameters Description
Google some description on the subject, much of the description relates to 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 not 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 a sql statement, we will have a shared pool in a library cache object, cursor is one for the sql statement in a library cache object. In addition, we will have a copy of a cursor pga, while the client there will be a statement handle, these are referred to as cursor, v $ open_cursor in which we can see the currently open cursor and pga within cached cursor.

This parameter limits the length within the pga session cursor cache list, session cursor cache list is a two-way lru list, when a session intended to close a cursor, if the cursor is parse count more than three times, then the cursor will be was added to the MRU end of the session cursor cache list. when a session intended to parse a sql, it searches the session cursor cache list go inside pga, if you will find the cursor from the list, and then when the time then close the cursor was added to the MRU end .session_cached_cursor provides rapid analysis of soft features, provides higher performance than soft parse, that is even open cursor movement gave province.

Third, analysis and adjustment
View the current system configuration 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');

---------------------- -------------------- -----
session_cached_cursors 50 98% - session_cached_cursors current utilization rate of 98%, should consider increasing the value of this parameter
open_cursors 300 20% - Current open_cursors only 20%, indicating that the current enough

- You can also view usage following the footsteps of the cursor
  4 AND B.NAME = 'opened cursors current'
  5 AND P.NAME = 'open_cursors'

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

- View cursor associated statistical values, the instance level
SQL> select name, value from v $ sysstat where name like '% cursor%';

----------------------------------- ----------
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 implementation involves several steps: open, parse, bind, execute, fetch, close.

Hard analysis: SQL statement in the library cache unbuffered
Soft resolve: SQL statement in the library cache found the execution plan
Soft Analysis: search within pga session cursor cache list list to find the corresponding SQL, regardless soft parsing or soft parsing, have to resolve this operation.
To improve the analysis and implementation of a ratio relationship, you need to increase the number of non-resolution, no parsing is no longer resolve for SQL bind different variables, and then execute.
This premise is: 1, Session can not be disconnected; 2, Session executed parsed SQL do not close; to meet these two points can be achieved without resolution.

According to the analysis above and session_cached_cursors usage analysis will increase to 300 parameters session_cached_cursors

alter system set session_cached_cursors = 300 scope = spfile;

After modifications to restart the database to enter into force.

SQL> @cursor_usage - execute the query can be seen after adjustment session_cached_cursors usage entirely adequate

---------------------- -------------------- -----
session_cached_cursors 300 12%
open_cursors 300 12%
Four, sql_id az33m61ym46y4
By then adjust the tracking, Execute to Parse negative situation persists
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

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.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
 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;

------------- ---------------- ----------------- ---- ------------ ---------------- ----------------- ----- ------
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

- Can be seen from the above query sql_id az33m61ym46y4 is a unique description of the culprit this sql Metalink, but did not see too many recommendations. The same is true in Oracle on there, but not in this SQL awr report highlighted the current The following is a detailed description Bug metalink on 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:

* 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
* 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,
------ 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 executed to resolve a measure, under the best circumstances, is a parsing multiple times, the best is to use soft parsing;
b, the parameters involved are mainly OPEN_CURSORS with session_cached_cursors, former defines a single session can open cursors, which defines the length of the cursor cacheable
c, usage usually the two parameters should deviate as 80%, in order to ensure adequate performance and resources, it is noted that two parameters increases should consider whether further adjustments are needed pga and sga
- Linux environment has been running Tomcat how to deploy the new Tomcat (Server)
- Hadoop + Zookeeper NameNode achieve high availability (Server)
- Based on Google Cloud Storage Mirroring open Maven Central Repository (Server)
- A list of the basics of Python, Ganso, Dictionary (Programming)
- Construction CA certificate using OpenSSL command line (Server)
- Linux server remote user to obtain root privileges prevention (Linux)
- CentOS 6 compiling httpd-2.4.10 (Server)
- Related to optimize the use of Btrfs file system on SSD (Linux)
- Vim (Linux)
- PLSQL often lose resolution process automatically disconnect the connection (Database)
- Linux directory structure (Linux)
- Oracle 11g users to create, authorize and import dmp file (Database)
- Linux FTP setting file description (Server)
- Debian users to install FFmpeg 2.2.2 (Linux)
- Oracle 11g 10g induced into error (Database)
- It is time to upgrade your gulp 4.0 (Programming)
- Let Linux operating system more secure (Linux)
- 10 Codes of good practice PHP (Programming)
- SSH without password Definitive Guide (Linux)
- Linux Basic Course: Install the software from source code (Linux)
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.