| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: how to get parse and execution number for a sql
About concepts
from my infamous paper
1.1.1 Soft parse
Lets you specify the number of session cursors to cache.
After the first “soft parse”, subsequent “soft parse” calls will find the cursor in the cache and do not need to reopen the cursor. To get placed in the session cache the same statement has to be parsed 3 times within the same cursor. Oracle uses a least recently used algorithm to remove entries in the session cursor cache to make room for new entries when needed.
Session cached cursors is a great help in reducing latching that takes place due to excessive soft parsing (where a program parses, executes, closes a statement over and over)
The suggested value is a non zero value.
SESSION_CACHED_CURSORS = 300
1.1.1 Binding, Hard parse
Before every query, Oracle must parse the statement, this means, it have to
analyze and see the best execution plan.
Once a statement is parsed, the following statement exactly as this, don’t parse, they simply execute.
For example a query
SELECT COLUMN FROM TABLE WHERE COLUMN=’A’; Becomes in
SELECT COLUMN FROM TABLE WHERE COLUMN=:1; So any query using SELECT COLUMN FROM TABLE WHERE COLUMN=;
Will use that statement parsed.
There is a problem specially in non Developer/2000 applications, there is the problem because they don’t parse the query for example
In this case, the statement is parsed every distinct value you query. INCORRECT DECLARE nReturn NUMBER;
BEGIN EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ADM.TRANSAC_ME WHERE TRC_EMPRESAGESTION = ''DEF '' ' INTO nReturn
USING cEmpresa ;
DBMS_OUTPUT.PUT_LINE (nReturn);
END; In this case, the statement is parsed once, and executed several times. CORRECT DECLARE nReturn NUMBER;
cEmpresa VARCHAR2(3):= 'DEF';
BEGIN EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ADM.TRANSAC_ME WHERE TRC_EMPRESAGESTION = :cEmpresa'
INTO nReturn
USING cEmpresa ;
DBMS_OUTPUT.PUT_LINE (nReturn);
END; To fix this situation there is the database parameter, the correct value is EXACT, but to fix all this situations temporally can be set to SIMILAR OR FORCE. CURSOR_SHARING = EXACT
> Yes, parse always happens when you issue a sql statement.
>
> Tanel.
>
> ----- Original Message -----
> From: "Powell, Mark D" <mark.powell_at_eds.com>
> To: <oracle-l_at_freelists.org>
> Sent: Thursday, February 26, 2004 6:51 PM
> Subject: RE: how to get parse and execution number for a sql
>
>
> > 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
> > -----------------------------------------------------------------
> >
>
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>
-- 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 - 13:20:52 CST
 |
 |