RE: how to get parse and execution number for a sql

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Thu, 26 Feb 2004 11:51:44 -0500
Message-ID: <564DE4477544D411AD2C00508BDF0B6A1CE0AA19@USAHM018.amer.corp.eds.com>

Paul, if you look at the (9.2) glossary Oracle definitely implies that it always checks the syntax before it looks in the shared pool for the SQL:

parse call
A call to Oracle to prepare a SQL statement for execution. This includes syntactically checking the SQL statement, optimizing it, and building (or locating) an executable form of that statement.

I realize the manuals are not always very precise, but in the last week I have just read a post by Tom Kyte on the parse order where he explained the difference between checking syntax and semantics and the definition follows what he said.    

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Paul Baumgartel Sent: Thursday, February 26, 2004 11:23 AM To: oracle-l_at_freelists.org
Subject: Re: how to get parse and execution number for a sql

This doesn't sound right to me, in fact it sounds backwards.

If an incoming SQL statement is found in the library cache, then that is evidence that it is syntactically and semantically correct. The soft parsing is required to resolve names, etc. within the context of the issuing session's privilege domain; this may lead to the creation of a new child cursor.

Paul Baumgartel

• Tanel_Poder <tanel.poder.003_at_mail.ee> wrote:
  > Yes, parse_calls shows any parse calls. A parse is always a parse.
  > You can't
  > avoid parsing when you issue a SQL statement to be executed. Syntax
  > and
  > semantics check is always done. Only after that if Oracle finds out
  > that
  > required statement is already parsed against the correct objects,
  > correct
  > bind variable types and with correct session parameters, then it can
  > skip
  > the rest of parsing and use the already parsed statement.
  >
  > You can use session statistics parse count (total) and parse count
  > (hard) to
  > find out whether a parse was soft or hard...
  

  ---

Do you Yahoo!?
Get better spam protection with Yahoo! Mail. http://antispam.yahoo.com/tools

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Feb 26 2004 - 11:23:56 CST