Hi guys,
I'm in doubt about the tasks performed in the hard parse of a SQL statement execution. I have read about what Burleson says, at Oracle hard-parse vs. soft parse , so:
Oracle SQL is parsed before execution, and a hard parse includes these steps:
- Loading into shared pool - The SQL source code is loaded into RAM for parsing. (the "hard" parse step)
- Syntax parse - Oracle parses the syntax to check for misspelled SQL keywords.
- Semantic parse - Oracle verifies all table & column names from the dictionary and checks to see if you are authorized to see the data.
- Query Transformation - If enabled (query_rewrite=true), Oracle will transform complex SQL into simpler, equivalent forms and replace aggregations with materialized views, as appropriate.
- Optimization - Oracle then creates an execution plan, based on your schema statistics (or maybe with statistics from dynamic sampling in 10g).
- Create executable - Oracle builds an executable file with native file calls to service the SQL query.
So a hard parse includes ALL the steps above? Or just the first one marked with red.
That because Syntax parse, semantic parse are tasks which belongs to soft parse too.
I know that in the hard parse stage (please correct me if I'm wrong):
- the library cache is checked to find whether an existing statement has the same hash value
- locks are acquired on the required objects (so that their definitions does not change) - "Latch" contention
So: syntax and semantic checks (points 2 and 3 above) belongs to hard parse, or soft parse? Or both?
Thank you!