Parse To Execute Ratio

All Oracle SQL statements must be parsed at the first time that they execute. Parsing involves a syntax check, a semantic check (against the dictionary), the creation of a decision tree, and the generation of the lowest cost execution plan. Once the execution plan is created, it is stored in the library cache (part of the shared pool) to facilitate re-execution. There are two types of parses:

Hard parse

A new SQL statement must be parsed from scratch. If the database is parsing every statement that is executing, the parse to execute ratio will be close to 1% (high hard parses), often indicating non-reentrant SQL that does not use host variables (Bind Variables).

Soft parse

A reentrant SQL statement where the only unique feature are host variables. The best-case scenario is a parse to execute ratio of 100% which would indicate an application with fully reentrant SQL that parses SQL once and executes many times.




In a real database, some SQL statements will be fully reentrant (execute to parse = 100%), while others must be re-parsed for every execution (execute to parse = 1%). You can see this is the instance efficiency of any STATSPACK and AWR report.


High parses suggests that your system has many incoming unique SQL statements, or that your SQL is not reentrant (i.e. literal values in the WHERE clause, not using bind variables). A hard parse is expensive because each incoming SQL statement must be re-loaded into the shared pool; with the associated overhead involved in shared pool RAM allocation and memory management. Once loaded, the SQL must then be completely re-checked for syntax & semantics and an executable generated. Excessive hard parsing can occur when your shared_pool_size is too small (and reentrant SQL is paged out), or when you have non-reusable SQL statements without host variables.



If the execute to parse ratio is too low, it is possible that the application is not using shareable SQL, or the database has sub-optimal parameters that are reducing the effectiveness of cursor sharing. A problem like excessive parsing is likely to manifest itself as additional network traffic between the application server and clients. The additional parse activity may also show up as a marked increase in CPU consumption on the database server.

Here is a simple sql query which describe the parse call and execution of current sql queries :

Select x.sql_text , x.parse_calls , x.executions
,round( 100*(1-( x.parse_calls / x.executions )),2) execute_to_parse_ratio
FROM v$sql x
WHERE x.parse_calls >0
AND x.executions !=0
AND x.parsing_schema_name='EMP'
ORDER BY execute_to_parse_ratio ;

SELECT x.executions ,
  x.parse_calls ,
  ROUND( 100*(1-(x.parse_calls/x.executions)),2) execute_to_parse_ratio
  , x.sql_text
FROM
  (SELECT DBMS_LOB.SUBSTR (sq.sql_text,500,1) sql_text ,
    SUM(st.executions_delta) executions ,
    SUM(st.parse_calls_delta) parse_calls
  FROM DBA_HIST_SQLSTAT st ,
    DBA_HIST_SQLTEXT sq ,
    DBA_HIST_SNAPSHOT s
  WHERE s.snap_id           = st.snap_id
  AND s.begin_interval_time > sysdate-14
  AND s.end_interval_time   < sysdate
  AND st.sql_id             = sq.sql_id
  AND st.parsing_schema_name='EMP'
  GROUP BY DBMS_LOB.SUBSTR (sq.sql_text,500,1)
  ) x
WHERE x.executions != 0
AND ROUND( 100*(1-(x.parse_calls/x.executions)),2) < 10
ORDER BY execute_to_parse_ratio ;

Related Topics :

1. Bind Variable