Re: Pre-compile sql

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@xxxxxxxxxxxxxx>
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@xxxxxxxxx> 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@xxxxxxxxxxxxxx> 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@xxxxxxxx> 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


Other related posts: