Re: RE: Parse Vs Execute

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).