Re: Latch process in Shared pool

I am not sure if Sybrand has the order 100% correct or not but you can find information on the SQL statement parse process in the Concepts manual:

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14220/sqlplsql.htm

The information in under the topic heading of PARSING leads me to believe the syntax check always takes place and that Oracle does not generate a hash until after the statement is determine to be valid SQL.

(Using 10gR2 doc) The Performance and Tuning manual contains the following blurg >>
Good Cursor Usage and Management

Maintaining user connections is equally important to minimizing the parsing activity on the system. Parsing is the process of interpreting a SQL statement and creating an execution plan for it. This process has many phases, including syntax checking, security checking, execution plan generation, and loading shared structures into the shared pool. There are two types of parse operations:

Hard Parsing: A SQL statement is submitted for the first time, and no match is found in the shared pool. Hard parses are the most resource-intensive and unscalable, because they perform all the operations involved in a parse.

Soft Parsing: A SQL statement is submitted for the first time, and a match is found in the shared pool. The match can be the result of previous execution by another user. The SQL statement is shared, which is good for performance. However, soft parses are not ideal, because they still require syntax and security checking, which consume system resources.

Since parsing should be minimized as much as possible, application developers should design their applications to parse SQL statements once and execute them many times.
<<

HTH -- Mark D Powell -- Received on Thu Mar 02 2006 - 14:57:00 CST