SQL Statement Parsing in Oracle By Vigyan Kaushik Jul 17, 2006  digg!     Print     Email to Friend Note: This article was written for educational purpose only. Please refer to the related vendor documentation for detail.

SQL Statement Parsing in Oracle Parsing of a SQL statement involves several steps. Blow is a flow diagram that will show you how this works. It also explains difference between soft and hard parse. Step 1: Statement is submitted. Step 2: Initial syntactic check is made. If there is an error statement returned to the client. Step 3: Checks if statement open cursor for the statement, if yes then statement is executed if not, step 4 is performed. Step 4: Checks if SESSION_CACHED_CURSORS initialization parameter is set and cursor is the Session Cursor cache. If yes then statement is executed if not, step 5 is performed. Step 5: Checks if HOLD_CURSOR is set to Y. HOLD_CURSOR is an precompiler parameter that specifies that an individual cursor should be held open. If cursor in Held Cursor Cache then statement is executed if not, step 6 is performed. Step 6: A cursor is opened. Statement is hashed and compared with the hashed value in the SQL area. If it found in SQL area then statement is executed and it is SOFT PARSE. If statement is not found then it statement is parsed and executed and it is called HARD PARSE