Execute to Parse is low in Statspack report Oracle

Execute to Parse is low in Statspack report Oracle

Execute to Parse % is showing negative value in statspack/awr report.
The execute to parse ratio is basically a measure between the number of times a sql is executed versus the number of times it is parsed.

Executes > Parses then its higher value.( in genreally term it is good)
Executes = Parses the its zero value.
Executes < Parses then its lower value (negative value)

Cause for Negative value
Negative value means that the number of parses is larger than the number of executions.
1. Shared pool size is too small and queries are aging out of the shared pool and need to be reparsed

Solution:
1. Use bind variables to utilize the same plan.
2. Sufficient Shared pool size.
3. If hard parsing is more then you can change CURSOR_SHARING value from EXACT to FORCE.

SQL> Show parameter cursor_sharing

NAME                TYPE      VALUE
------------------- --------- -----------
cursor_sharing      string    EXACT

SQL> ALTER SYSTEM SET CURSOR_SHARING=FORCE SCOPE=BOTH;

4. Increase the value of “session cached cursors” to hold the queries for reuse.

SQL> show parameter session_cached_cursors

NAME                     TYPE      VALUE
------------------------ --------- ---------
session_cached_cursors   integer   50

-- Parameter need restart of the database.
SQL> ALTER SYSTEM SET SESSION_CACHED_CURSORS=100 scope=spfile;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.