Parsing SQL Statements in Oracle
How Oracle Database processes SQL statements ?
For performance tuning basic knowledge a developer or tuning expert needs to know the basic execution of the select statement works.
What happens when a select statement get issues in the DB (select * from emp)
This explains the way in which the database processes DDL statements to create objects, DML to modify data, and queries to retrieve data.
First stage of SQL processing is PARSING
When an application issues a SQL statement, the application makes a parse call to the database to prepare the statement for execution.
The parse call opens or creates a cursor, which is a handle for the session-specific private SQL area that holds a parsed SQL statement and other processing information. The cursor and private SQL area are in the PGA.
PGA : Program Global Area (PGA) is a memory buffer that contains data and control information for a server process.
Every session will have its own PGA
Cursors: The Cursor is a handle (name or a pointer) for the memory associated with a specific statement.
A cursor is basically an Area allocated by Oracle for executing the SQL statements.
Steps in Parsing
A statement that breaks a rule for well-formed SQL syntax fails the check.
syntactic checking verifies that keywords,
· object names
and so on are placed correctly in your SQL statement.
For example, the following embedded SQL statements contain syntax errors: For example, the following statement fails because the keyword FROM is misspelled as FORM:
SQL> SELECT * FORM employees;
SELECT * FORM employees
ERROR at line 1: ORA-00923: FROM keyword not found where expected
Semantic CheckMeaning. Thus, a semantic check determines whether a statement is meaningful , Do the tables and columns referenced in the SQL statement actually exist in the database? Does the user executing the statement have access to the objects and are the proper privileges in place? Are there ambiguities in the statement?SQL> select * from table_doesnt_exist;select * from table_doesnt_exist*ERROR at line 1:ORA-00942: table or view does not existShared Pool Check(Technical Aspects )The database performs a shared pool check to determine whether it can skip resource-intensive steps of statement processing. To this end, the database uses a hashing algorithm to generate a hash value for every SQL statement. The statement hash value is the SQL ID shown in V$SQL.SQL_ID.Hard parseIf Oracle Database cannot reuse existing code, then it must build a new executable version of the application code. This operation is known as a hard parse, or a library cache miss.The database always perform a hard parse of DDL.During the hard parse, the database accesses the library cache and data dictionary cache numerous times to check the data dictionary. When the database accesses these areas, it uses a serialization device called a latch on required objects so that their definition does not change. Latch contention increases statement execution time and decreases concurrency.Latch :A latch is a low-level internal lock used by Oracle to protect memory structures (for example if you are issuing a statement at time table structure should not be modified like drop column).A low-level serialization control mechanism used to protect shared data structures in the SGA from simultaneous access.library cache :. The Library Cache is a piece of memory within the SGA that Oracle uses in order to store SQL StatementsAn area of memory in the shared pool. This cache includes the shared SQL areas, private SQL areas (in a shared server configuration), PL/SQLprocedures and packages, and control structures such as locks and library cache handles.Soft parseA soft parse is any parse that is not a hard parse. If the submitted statement is the same as a reusable SQL statement in the shared pool, then Oracle Database reuses the existing code. This reuse of code is also called a library cache hit.Soft parses can vary in the amount of work they perform. For example, configuring the session shared SQL area can sometimes reduce the amount of latching in the soft parses, making them "softer."In general, a soft parse is preferable to a hard parse because the database skips the optimization and row source generation steps, proceeding straight to execution.The Oracle database now needs to check in the Shared Pool to determine if the current SQL statement being parsed has already been processed by any other sessions.If the current statement has already been processed, the parse operation can skip the next two functions in the process: Optimization and Row Source Generation. If the parse phase does, in fact, skip these two functions, it is called a soft parse. A soft parse will save a considerable amount of CPU cycles when running your query. On the other hand, if the current SQL statement has never been parsed by another session, the parse phase must execute ALL of the parsing steps. This type of parse is called a hard parse. It is especially important that developers write and design queries that take advantage of soft parses so that parsing phase can skip the optimization and row source generation functions, which are very CPU intensive and a point of contention (serialization). If a high percentage of your queries are being hard-parsed, your system will function slowly, and in some cases, not at all.Oracle uses a piece of memory called the Shared Pool to enable sharing of SQL statements. The Shared Pool is a piece of memory in the System Global Area (SGA) and is maintained by the Oracle database. After Oracle completes the first two functions of the parse phase (Syntax and Semantic Checks), it looks in the Shared Pool to see if that same exact query has already been processed by another session. Since Oracle has already performed the semantic check, it has already determined:Exactly what table(s) are involvedThat the user has access privileges to the tables.Oracle will now look at all of the queries in the Shared Pool that have already been parsed, optimized, and generated to see if the hard-parse portion of the current SQL statement has already been done.Oracle will always keep an unparsed representation of the SQL code in the Shared Pool, and that the database will perform a hashing algorithm to quickly located the SQL code. OK, so why doesn't Oracle make this step (checking the Shared Pool for a matching statement) the first step in its parsing phase, before making any other checks. Even when soft parsing, Oracle needs to parse the statement before it goes looking in the Shared Pool,One of the big reason'sselect * from emp; Assume that this query was first submitted by user "SCOTT" and that the "emp" table in the FROM clause is a table owned by SCOTT. You then submit the same exact query (as a user other than SCOTT) to Oracle. The database has no idea what "emp" is a reference to. Is it a synonym to another table? Is it a view in your schema that references another table? For this reason, Oracle needs to perform a Semantic Check on the SQL statement to ensure that the code you are submitting is going to reference the same exact objects you are requesting in your query
Query optimization is the process of choosing the most efficient means of executing a SQL statement. The database optimizes queries based on statistics collected about the actual data being accessed. The optimizer uses the number of rows, the size of the data set, and other factors to generate possible execution plans, assigning a numeric cost to each plan. The database uses the plan with the lowest cost.
The database must perform a hard parse at least once for every unique DML statement and performs optimization during this parse. DDL is never optimized unless it includes a DML component such as a subquery that requires optimization.
SQL Row Source GenerationThe row source generator is software that receives the optimal execution plan from the optimizer and produces an iterative plan, called the query plan, that is usable by the rest of the database. The iterative plan is a binary program that, when executed by the SQL virtual machine, produces theresult set.A row source is an iterative control structure. It processes a set of rows, one row at a time, in an iterated manner. A row source produces a row set.The row source can be a table, view, or result of a join or grouping operation. Each row source produced by the row source generator is executed by the SQL execution engine.SQL Statement ProcessingSQL Statements are processed differently depending on whether the statement is a query, data manipulation language (DML) to update, insert, or delete a row, or data definition language (DDL) to write information to the data dictionary.Processing a query:· Parse:o Search for identical statement in the Shared SQL Area.o Check syntax, object names, and privileges.o Lock objects used during parse.o Create and store execution plan.· Bind: Obtains values for variables.· Execute: Process statement.· Fetch: Return rows to user process.Processing a DML statement:· Parse: Same as the parse phase used for processing a query.· Bind: Same as the bind phase used for processing a query.· Execute:o If the data and undo blocks are not already in the Database Buffer Cache, the server process reads them from the datafiles into the Database Buffer Cache.o The server process places locks on the rows that are to be modified. The undo block is used to store the before image of the data, so that the DML statements can be rolled back if necessary.o The data blocks record the new values of the data.o The server process records the before image to the undo block and updates the data block. Both of these changes are made in the Database Buffer Cache. Any changed blocks in the Database Buffer Cache are marked as dirty buffers. That is, buffers that are not the same as the corresponding blocks on the disk.o The processing of a DELETE or INSERT command uses similar steps. The before image for a DELETE contains the column values in the deleted row, and the before image of an INSERT contains the row location information.Processing a DDL statement:· The execution of DDL (Data Definition Language) statements differs from the execution of DML (Data Manipulation Language) statements and queries, because the success of a DDL statement requires write access to the data dictionary.· For these statements, parsing actually includes parsing, data dictionary lookup, and execution. Transaction management, session management, and system management SQL statements are processed using the parse and execute stages. To re-execute them, simply perform another execute.Referencehttp://docs.oracle.com/cd/E11882_01/server.112/e16508/sqllangu.htm#CHDFCAGA