This chapter SQL Processing And Sql Optimizer demonstrates about sql processing and optimization in oracle. Sql processing describe how the Database forms SQL articulations. Explicitly, this sector explains the path in which the database forms DDL articulations to make objects, DML to change the information, and inquiries to recover information. SQL converts into anatomize, enhancement, row origin creation, and finishing of a SQL articulation. Calculating upon the articulations, the database might discard few of  these steps.

The below figure describes the SQL Processing And Sql Optimizer process.

Here in the below example, showing an example of sql processing.

The primary phase of SQL Processing And Sql Optimizer deals with parsing. This step includes isolating the bits of a SQL articulation into a information design that different schedules can be handled. The parses an announcement taught by the operation, that implies that just the operation is parsed no more the database, and decrease the quantity of parses. At certain point when an operation issues a SQL proclamation, the function compose a parse call to the database to set up the announcement for the implements. The parse call exposed or makes a cursor, and can hold for the session-particular private SQL zone that holds a parsed SQL explanation and other processing data. The cursor and exclusive SQL range are placed system world wide zone. At the time of parse call, the database performs the accompanying checks.

Amid this parse, the database achieve a common pool check to figure out that it can skip assets-intensive steps of statement preparing. To this end, the database utilizes a hashing calculation to produce a hash value for each SQL statement. The announcement hash value is the SQL ID appeared in V$SQL.SQL_ID. This hash quality is deterministic inside a variant of Oracle Database, so the similar statement in a solitary case or in various cases has the same SQL ID. At that point when a client presents a SQL statement, the database looks the mutual SQL area to check whether a current parsed statements has the same hash value. The hash estimation of a SQL statement is different from the accompanying qualities.

• Memory address for the statement
• Hash estimation of an execution arrangement for the statement.

Database utilizes the SQL ID to achieve an entered read in a hookup table. Along these lines, the database acquires conceivable recollection locations for the announcement.

Hash estimation of an execution arrangement

A SQL explanation contain various arrangement based on mutual pool. commonly, every execution has a alternate hash esteems. In the event that the similar SQL ID has various arrangements hash values, then the database realizes that numerous arrangements exist for each of this SQL ID.

The announcement are its significance. Hence, a semantic check figure out if an announcement is important.For instance, whether the item and section exists in the statement. A systematic and appropriate explanation can come up short in the semantic check.

By viewing the below example, semantic check can be easily understands. [sql]sql> select * from nonexistent_table; ERROR 1146 (42S02): Table 'employee.nonexistent_table' doesn't exist[/sql] In the above example there is no table exist by the name non existent and must be throughly check before executing.

Database need to investigate every SQL explanation for syntactical legitimacy. An announcement will discard a guidelines for all around shaped SQL language structure comes up short the check. For instance, the consecutive articulations decline by the virtue of the password FROM is incorrectly spelled as FORM.

By viewing the below example, syntax check can be easily understands. [sql]sql> select * form employee; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to the right syntax to use near 'form employee' at line 1[/sql] In the above example, the select proclamation should be compose accurately without any errors.

Amid, the optimization step,  Database fundamentally achieve a hard parse at any rate once for each one of the kind  DML statement and performs the optimization during this parse. The database never optimizes DDL unless it incorporates a DML part, for example a subquery that requires optimization. And SQL optimization contains sql row source and execution. The row origin generator is program that gets the ideal execution arrangement from the analyser and produces an re-iterative execution that is usable for the rest of the database. The iterative arrangement is a binate program that, when executed by the SQL engine, creates the outcome set. The execution arrangement takes the type of a consolidation of steps. Every step gives a row set and the following step either utilizes the lines as a part of this set, or the finishing stage gives the lines to the operation issuing the SQL proclamation. A row origin is a line and collection of all returned stage in the operation arrangement alongside a control design that can re-iteratively handle the lines. The row origin can be a view, table or consequence of a join or an execution. Row origin generates  the following instructions.

• A requesting of  tables attribute by the announcement.
• An approach strategy for every table described in the statement.
• Information operations, for example, channel, sort, or collection.
• A join technique for tables influenced by join operations in the announcement.

SQL Execution

During SQL Processing And Sql Optimizer implement, each row origin in the tree delivered by the row origin allocator. And this is the compulsory step in DML processing. SQL SQL Processing And Sql Optimizer execution is also called as Parse tree.When all said is done, the request of the progressions in execution is opposite to the request in the arrangement, so the arrangement should be read from the bottom to top.And after reading the SQL statement the execution will be closed in the statement.

• SQL parsing - Parsing includes combining the SQL statements into informations structures,so that different schedules can be handled.
• SQL optimization - SQL optimizations are the commands used in performing the DML commands parses.
• Row source generation - Is a software that receives unique execution plans.
• SQL execution - The statements executed in the SQL commands,also called as parse trees.