18.8 Parse Ratios

---

Parsing involves syntax checking and building of an execution query plan for SQL code. Every time SQL code is parsed its cursor and query plan are stored in the shared pool. The stored shared cursors can be re-used by matching SQL code statements; the match must be an exact text string match. Parsing is expensive so always pay attention to parse-related ratios. The less re-parsing in the shared pool the better.

The most obvious solution is increasing the size of the shared pool. However, increasing the shared pool to a ridiculous amount can cause its own problems. One of the most common causes of heavy re-parsing is lack of bind variables in SQL code. Setting the CURSOR_SHARING configuration parameter to SIMILAR or FORCE can help alleviate this problem without recoding SQL code.

Also increasing the SESSION_CACHED_CURSORS parameter can help reduce re-parsing by caching more session-specific cursors per session.

SELECT 'Soft Parses ' "Ratio" , ROUND( ((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse     count (total)') - (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse     count (hard)')) / (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'execute     count') * 100, 2)||'%' "Percentage" FROM DUAL UNION SELECT 'Hard Parses ' "Ratio" , ROUND( (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count     (hard)') / (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'execute     count') * 100, 2)||'%' "Percentage" FROM DUAL UNION SELECT 'Parse Failures ' "Ratio" , ROUND( (SELECT SUM(value) FROM V$SYSSTAT  WHERE name = 'parse count (failures)') / (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse      count (total)') * 100, 2)||'%' "Percentage"     FROM DUAL;     Ratio            Percentage --------------   ---------- Hard Parses       .04% Parse Failures   0% Soft Parses     44.98%

---