1

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

Improve this question

1 Answer 1

Reset to default
2

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 WHERE clause 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 the WHERE clause 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.

Improve this answer
8
  • Thanks for your reply.So does it mean that, "when an application issues a SQL statement" said in Oracle documentation means "when an application issues a NEW SQL statement"?
    – Jason Yang
    Apr 3, 2021 at 14:09
  • @JasonYang I don't know enough about the exact inner workings under the hood of Oracle, but I would I assume the sentence is correct even without adding the word "new" to it because a parse call is probably still made but ignored for non-new queries whose execution plans are already cached.
    – J.D.
    Apr 3, 2021 at 14:21
  • That is what I also feel confused. In the Oracle document, it saids, "During the parse call, the database performs the following checks: Syntax Check, Semantic Check and Shared Pool Check. " Then in the scenario you assume, the non-new queries won't do shared pool check in the parse call , because if the shared pool check is done, then it will enter the parse operation process, even though it may be go the soft-parse way. But seems the documentation doesn't clarify this... docs.oracle.com/database/121/TGSQL/tgsql_sqlproc.htm#TGSQL178
    – Jason Yang
    Apr 3, 2021 at 15:00
  • 1
    And further down t says "If a check determines that a statement in the shared pool has the same hash value". I don't find where they spell it out, but I would assume that hashing the statement is one of the very first things done, so that it can compare hash of the newly submitted query to what has already been fully processed, and determine how much other processing is required. And that would also explain why something as simple as an additional white space would trigger a full hard parse of what is otherwise an identical statement.
    – EdStevens
    Apr 3, 2021 at 15:11
  • @JasonYang I will say their documentation is confusing, but perhaps that's why they put a dashed border around the parsing process. It sounds like regardless at least one parsing operation occurs which checks the shared pool so it can determine if it can re-use an existing execution plan and then qualify it as a soft parse otherwise it needs to re-generate a new execution plan and would be considered a hard parse. "When a user submits a SQL statement, the database searches the shared SQL area to see if an existing parsed statement has the same hash value"...
    – J.D.
    Apr 3, 2021 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.