Re: Pre-compile sql

From: <lkemnitz_at_uwsa.edu>
Date: Wed, 30 Apr 2008 14:37:45 -0500
Message-ID: <23b0625a78.25a7823b06@uwsa.edu>

I was out to meeting till little bit ago.

I judged the compile time by using PowerBuilder. Upon executing the sql, I timed the amount of time it took to display the columns in the view. I then timed the amount of time before seeing data.

I am working on redoing the java to use prepared statements.

I have the cursor pinned in the shard pool already.

+++++++++++++++++++++++++++++++++++++++++++++++

LeRoy Kemnitz
UW System Administration
Database Administrator
(608) 265-5775

• Original Message ----- From: "Jeremy Schneider" <jeremy.schneider_at_ardentperf.com> Date: Wednesday, April 30, 2008 1:40 pm Subject: Re: Pre-compile sql

> 25s to parse (and 4s to exec) just seems a bit extreme... Could
> hinting or
> perhaps a stored outline/plan improve parsing time? (Maybe
> ORDERED?) Seems
> like usually the max permutations would get hit before 25 seconds?
>
>
> On 4/30/08, Niall Litchfield <niall.litchfield_at_gmail.com> wrote:
> >
> > select ... from t1,t2,t3,t4...t42 where t1.id=t2.id and t2.id =
> > t3.id ... can take a while.
> >
> > On 30/04/2008, Jeremy Schneider
> <jeremy.schneider_at_ardentperf.com> wrote:
> > > 25 seconds to hard parse and 4 seconds to execute? just
> curious, how
> > did
> > > you get these figures?
> > >
> > > -Jeremy
> > >
> > >
> > > On 4/29/08, LeRoy Kemnitz <lkemnitz_at_uwsa.edu> wrote:
> > > >
> > > > All -
> > > >
> > > > I have a java app running on Tomcat - Linux and going
> against my
> > > 10.2.0.2DB on Unix. This DB is dedicated to this app so I can
> configure> the
> > > init
> > > > parms and settings to maximize the performance for this app.
> We have
> > one
> > > > view that is re-used a lot and is slower than required. I
> want to get
> > 5
> > > sec
> > > > or less on all data retrievals from this view.
> > > >
> > > > Currently, we are executing a select from the view when
> Tomcat starts
> > to
> > > > get the data/plan into oracle memory. The hard parse takes
> about> 25secs.
> > > > The data retrieval takes about 4 seconds. So the first
> time in, it
> > is
> > > > about 30 secs to get a response back from the db.
> > > >
> > > > So I am researching how to help this situation. We have been
> > > > experimenting with the cursor_sharing parm. We have found that
> > setting it
> > > > to 'similar' has the best result for us. I am looking into the
> > > > dbms_describe to see if it will do this for me. I will then
> start to
> > play
> > > > with the keep in the shared pool. I would set up a trigger
> to fire
> > this
> > > on
> > > > startup/logon.
> > > > Has anyone done this before and if so, got any ideas of
> where to go
> > next?
> > > >
> > > > --
> > > > http://www.freelists.org/webpage/oracle-l
> > > >
> > > >
> > > >
> > >
> > >
> > > --
> > > Jeremy Schneider
> > > Chicago, IL
> > > http://www.ardentperf.com/category/technical
> > >
> >
> >
> > --
> > Niall Litchfield
> > Oracle DBA
> > http://www.orawin.info
> >
>
>
>
> --
> Jeremy Schneider
> Chicago, IL
> http://www.ardentperf.com/category/technical
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 30 2008 - 14:37:45 CDT