Re: Dynamic SQL Performance Question

Hi.

 It's true if you are using hard-coded values instead of making a bind.  When bind is used - Oracle is able to reuse the cursor without  reparsing it.

 HTH. Michael.

In article <38F5D313.E2004A53_at_primenet.com>,   Tony Johnson <tonyj_at_primenet.com> wrote:
> in our system using trace and tkprof we were able to see that 80-90%
 of
> the elapsed/cpu time for executing dynamic sql is taken up in the
 parsing
> phses. if the query is executed a few times a day its no big deal - if
> executed
> 10 times a second it would be prudent to use a stored procedure. IMO.
>
> billmil_at_my-deja.com wrote:
>
> > What are the performance benefits of using stored procedures versus
> > dynamic sql?
> >
> > Developers here initially put SQL directly into our Java Server
 Pages
> > (JSP) and use JDBC statements to access the database. Example:
> >
> > String SQL = "select HANDLE from USERS where USER_ID='" + userID
> > + "'";
> > myResultSet = stmt.executeQuery(SQL);
> >
> > For performance reasons, I'm looking to replace this dynamic sql
 with
> > stored procedures. As we're in development now, it's hard to tell
 how
> > the cost of this dynamic SQL (i.e. small number of records, limited
> > number of users, etc.).
> >
> > I understand the main cost comes from a) increased parsing and b)
> > inefficient use of the library cache.
> >
> > How big a benefit can one expect when moving from dynamic sql to
 stored
> > procedures?
> >
> > bill milbratz
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
> --
>
> Tony Johnson
> The Straford Group
> PO Box 2875
> Chandler, AZ 85244
>
> Email : tonyj_at_primenet.com
> Phone : (480)497-9685
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Apr 13 2000 - 00:00:00 CDT