Sunday, 24 January 2016

SQL Parsing in Oracle

Oracle keeps SQL statements,packages,information on the Objects and many other things in a memory area named as Shared pool. The purpose of the shred area is to maximize the sharing and reuse the information. This information created in memory for session.It is not useful for any another session. Oracle remove the older data to make available the space for another session.

 Whenever a SQL statement is executed. Oracle follows a process to evaluate the statement in terms of syntax,validity of objects being referred and of course,privileges to the user. Apart from oracle also checks for identical statements that may have been fired, with the intention of reducing the processing over heads. All this takes place in second, Even less, without the user knowing what is happening to the statement that was fired. This process know an Parsing.
Parsing Process:
Syntactical check: Verify the statement for syntax.
Semantic check: checks the validity of objects,privileges to the user. This is Data dictionary check
Types of Parsing:
1) Hard parsing: its follow the all parsing process like Syntactical check ,Semantic check and gathering the stats for best execution plan.
We can say first time query execution its follow all the oracle process to evaluate query called as Hard Parsing.
2) Soft parsing: User executed the statement second time oracle should not follow the Oracle process to evaluate the statement. Whenever user execute the query its directly provide the data without any Syntactical and Semantic check.
Soft parse will considerably improve the system performance where as frequent Hard parsing will affect the system. Reducing hard parsing will improve the resource utilization and optimize the SQL code.