I am confused about when Oracle database won't do parsing?
In the AWR report, there is a metrics called "execute to parse", which means more SQL just execute without parsing when it increases.
But as the Oracle document describe:
"When an application issues a SQL statement, the application makes a parse call to the database to prepare the statement for execution. "
It seems that everytime a SQL statement is issued, parsing will be called.
So I wandering when Oracle won't do parsing and make the "execute to parse" become a larger number?
Or I just misunderstood?
What Oracle document said is:
SQL Parsing
The first stage of SQL processing is parsing.
The parsing stage involves separating the pieces of a SQL statement into a data structure that other routines can process. The database parses a statement when instructed by the application, which means that only the application, and not the database itself, can reduce the number of parses.
When an application issues a SQL statement, the application makes a parse call to the database to prepare the statement for execution.
https://docs.oracle.com/database/121/TGSQL/tgsql_sqlproc.htm#TGSQL178
So if "an application issues a SQL statement, the application makes a parse call", then how applications "can reduce the number of parses"?
Best Answer
Execute To Parse is "a ratio between the number of times a sql [statement] is executed versus the number of times it is parsed" as noted in this reference. In this context, parsed means hard parsed (which is the full parsing process that's inclusive of generating an execution plan).
The reasoning this metric exists is because whenever a new query is ran in Oracle, it needs to be parsed so that the Oracle SQL Engine can determine the best execution plan to serve the data for the query. After it has been parsed, and an execution plan is generated, that execution plan is typically cached and therefore the next time that same exact query is executed, it doesn't need to be parsed again, rather it will use the existing cached execution plan.
You can read more information on execution plans and Oracle's cache in the Oracle documentation. You can also find more information on the Execute To Parse metric here.
To answer your question "how applications "can reduce the number of parses"?": The application can do so in two somewhat obvious ways (which is why that statement in the Oracle documentation may be confusing). The first way is the application can reduce the number of parses by reducing the number of queries it executes in the database (e.g. if a query is converted to application code instead). Obviously this is a silly answer, but it's true. The second, more relevant way, is it can reduce the number of unique queries it executes in the database.
Because a cached execution plan is based on the exact query text that was sent to the database server, even the most minor change such as a comment or a different value in the
WHEREclause predicates could cause Oracle to parse and re-generate a new execution plan for a very similar query to one that was previously ran. If the application is building unique query strings for theWHEREclause predicates instead of passing them as actual parameters to the database server, then that query will be parsed every time. If instead, parameters were used, then every subsequent execution of that query would result in the cached execution plan to be used and that query wouldn't get re-parsed, therefore resulting in a positive number for the metric Execute To Parse ratio.