Skip to main content
Database Administrators

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

Required fields*

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"? Commented 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. Commented 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 Commented 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. Commented 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"... Commented Apr 3, 2021 at 15:17