oracle sql解析流程

lyd2004888

SQL Parsing Flow Diagram (文档>转到底部http://blog.51cto.com/e/u/themes/default/images/spacer.gif修改时间:2013-7-25http://blog.51cto.com/e/u/themes/default/images/spacer.gif类型:REFERENCEhttp://blog.51cto.com/e/u/themes/default/images/spacer.gif状态:PUBLISHEDhttp://blog.51cto.com/e/u/themes/default/images/spacer.gifhttp://blog.51cto.com/e/u/themes/default/images/spacer.gif优先级:3Applies to:
Oracle Database - Enterprise Edition - Version 7.0.16.0 and later　　Information in this document applies to any platform.
Purpose
　　The document gives a basic overview of the steps involved in Parsing. This document was created in the Oracle 7 time frame, and>Scope
　　For users requiring a general overview of how parsing works.
Details
　　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          ---------
　　('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>
　　Oracle Database Programmer's Guide to the Oracle Precompilers

　　11g>　　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>　　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: Overview
　　Oracle Database Performance Tuning Guide

　　11g>　　Part Number E16638-06
　　11.1.3.1 Adaptive Cursor Sharing
　　http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#CIHIGAHB