Stages of SQL Processing
- SQL Parsing
The first stage of SQL processing is SQL parsing. This stage involves separating the pieces of a SQL statement into a data structure that can be processed by other routines. During the parse call, the database performs the following checks:
Syntax check
Semantic check
Shared pool check
The shared pool is one of the SGA components. SGA has some components shared by all the sessions. The shared pool has some sub-memories, too. Library cache, data dictionary cache, result cache, and some other caches.
- SQL Optimization
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
- SQL Row Source Generation
The 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. - SQL Execution
During execution, the SQL engine executes each row source in the tree produced by the row source generator. This is the only mandatory step in DML processing. - During execution, if the data is not in memory, then the database reads the data from disk into memory. The database also takes out any locks and latches necessary to ensure data integrity and logs any changes made during the SQL execution. The final stage of processing a SQL statement is closing the cursor.
- SQL (oracle.com)
No comments:
Post a Comment