Re: RE: Parse Vs Execute

By using DBMS_SQL you can open a cursor and re-execute as many times as needed.

You can't do that with execute immediate.

On Wed, 2003-11-26 at 12:04, ryan_oracle_at_cox.net wrote:
> i remember in tom kytes new book there is a 'softer parse' he was referring to using dbms_sql instead of execute immediate. Im not referring to using dbms_sql when you have to loop and use the same cursor repeatedly so you eliminate all parsing.
>
> he didnt go into great detail on this just gave benchmarks. do you know anymore?
> >
> > From: "Cary Millsap" <cary.millsap_at_hotsos.com>
> > Date: 2003/11/26 Wed PM 02:39:39 EST
> > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > Subject: RE: Parse Vs Execute
> >
> > Don't do this:
> >
> > Loop
> > Parse
> > Execute
> > Fetch
> > End loop
> >
> > Do this:
> >
> > Parse
> > Loop
> > Execute
> > Fetch
> > End loop
> >
> > If you parse inside your loop, then all that using bind variables will
> > gain you is a reduced "hard parse" count. If you parse outside the loop
> > (in which case, you MUST use bind variables), then you reduce your
> > number of parse calls. A "soft parse" is a little cheaper than a "hard
> > parse." NO PARSE is a lot cheaper than a "soft parse."
> >
> >
> > Cary Millsap
> > Hotsos Enterprises, Ltd.
> > http://www.hotsos.com
> >
> > Upcoming events:
> > - Performance Diagnosis 101: 12/16 Detroit, 1/27 Atlanta
> > - SQL Optimization 101: 12/8 Dallas, 2/16 Dallas
> > - Hotsos Symposium 2004: March 7-10 Dallas
> > - Visit www.hotsos.com for schedule details...
> >
> >
> > -----Original Message-----
> > jaysingh1_at_optonline.net
> > Sent: Wednesday, November 26, 2003 12:14 PM
> > To: Multiple recipients of list ORACLE-L
> >
> > Hi List,
> >
> > Almost fro all SQLs I am getting Prase count is same as Execute count.
> > How to reduce parse count?
> >
> > 1) We are using bind variable
> > 2) session_cached_cursors set to 100
> >
> >
> > call count cpu elapsed disk query current
> > rows
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > Parse 11 0.01 0.02 0 0 0
> > 0
> > Execute 11 0.00 0.00 0 0 0
> > 0
> > Fetch 22 0.01 0.00 0 33 44
> > 110
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > total 44 0.02 0.02 0 33 44
> > 110
> >
> > Any somebody give more hint on this?
> >
> > Thanks
> > Jay
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author:
> > INET: jaysingh1_at_optonline.net
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Cary Millsap
> > INET: cary.millsap_at_hotsos.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: <ryan_oracle_at_cox.net
> INET: ryan_oracle_at_cox.net
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).