This can be explained by considering 7 stages.
a) Parser: The parser converts the text of
the SQL statement into an internal representation called a query tree. Hence
the output of stage is query tree.
b) In the second stage query tree is
normalized. Normalization involves determining column and table names,
transforming query tree to conjugate normal form, and resolving data types. At
this point, we can determine if the statement may benefit from using the
statement cache. Outcome of this stage is that the query will get normalized
and get benefited from statement cache.
c) Pre-processor: This will transform the
query tree to some more SQL queries/statements,such as SQL statements with
sub queries,views ,to a more efficient query tree. Outcome at this stage is
efficient query tree.
d) Optimizer: After pre-processor stage SQL
optimizer will analyze the possible combination of operations to execute SQL
statement, and selects an efficient one based on the cost estimates (CPU time). Outcome at
this stage is efficient selection of SQL query plan/statement.
e) Code generator: This converts the query
plan generated by the optimizer into a format more suitable for query execution
engine.
f) Procedural execution engine: This setup
the execution environment for all query plans and calls the query execution
engine.
g) Query execution engine: Executes the
ordered steps specified in the query plan provided by the code generator.
Externally the above happens only an engine starts executing the
task/SQL query by getting it from runnable task queue. Then the tasks
gets into running state on the engine. If the task required any data or
input from memory or from disk it gets moved into sleep queue (for the
time I/O gets completed). Finally I/O is completed it again resume back to
running state and gets completed.
