SQL Parsing Flow Diagram

SQL Parsing Flow Diagram

This article show the parsing mechanism as a flow diagram. Its main purpose is to show the difference between a 'soft' and a 'hard' parse. It is intended to give a feel of how parsing operates to make explanation of parsing activity easier.

SQL Parsing Flow Diagram:

  Statement Submitted
         |
         V
         |
 Is it in an open cursor?------------YES----V   <-- ------------------yes----v="" -------------="" ----------yes----v="" 3="" a="" already="" and="" area="" been="" cache="" cases="" client="" compared="" cursor="" has="" hashed="" held="" hold_cursor="Y" in="" is="" know="" no="" open="" parsed="" re-parsing="" server="" session="" session_cached_cursors="Value" side="" so="" sql="" statement="" that="" the="" these="" unnecessary.="" v="" value="" we="" with="">_______________________<_________ -------="" ------="" area="" in="" is="" it="" sql="" v="" yes="">       ---------
                      |       ('Soft' Parse)    |     |         |
                      NO                        |-->--| EXECUTE |
                      |                         |     |         |
                 PARSE STATEMENT -------------->       ---------
          ('Hard' Parse / library cache miss)  

NOTES
=====

1. A cursor is an address on the client that points to the memory location of a SQL statement on the server. Multiple-client cursors may point at the same address on the server.

2. Remember that 'Client' and 'Server' sides may reside on the same machine - in which case Client/Server is a logical distinction.

3. If a cursor is open, then the statement will be in the sql_area, so no parsing is necessary. This is why locks may remain when a client is terminated abnormally (such as a PC Client being turned off without closing open cursors).

4. SESSION_CACHED_CURSORS is the initialisation parameter that specifies how many cursors to hold open for a particular session.
The open cursor request will still be sent to the server but it will not be executed once a matching cursor is found in the session cursor cache.

5. HOLD_CURSOR is an precompiler parameter that specifies that an individual cursor should be held open. See:

Document 2055810.6 Precompiler HOLD_CURSOR and RELEASE_CURSOR Options

Oracle® Database Programmer's Guide to the Oracle Precompilers
11g Release 2 (11.2)
Part Number E10830-01
6 Running the Oracle Precompilers
HOLD_CURSOR
http://docs.oracle.com/cd/E11882_01/appdev.112/e10830/ch_six.htm#sthref867

Page 11-3 of the Programmer's guide to the Oracle Precompilers.

6. Both the soft and hard parse register as a parse in tkprof. Hashing the current statement updates the parse count.

7. Soft parse avoids many of the steps taken during the parse phase for a particular statement. Initial syntactic and semantic checks are made and then the statement is hashed and compared with hashed statements in the SQL area. If a match is found, then existing information is used and relatively expensive steps (such as query optimization etc.) are avoided.

8. The 10053 event is only invoked during a hard parse.

9. Adaptive cursor sharing is a variation that occurs within the soft parse step to allow multiple plans to be stored for the same SQL representing the middle ground between cursor sharing and optimization. For more details see:

Document 1115994.1 Introduction to Adaptive Cursor Sharing concepts in 11G and mutimedia demo [Video]
Document 836256.1 Adaptive Cursor Sharing in 11G
Document 740052.1 Adaptive Cursor Sharing: OverviewOracle® Database Performance Tuning Guide
11g Release 2 (11.2)
Part Number E16638-06
11.1.3.1 Adaptive Cursor Sharing
http://docs.oracle.com/cd/E29505_01/server.1111/e16638/optimops.htm#PFGRF95174

==============================================================


Note---> This informationmation taken from oracle metalink. all copy rights oracle only.