Re: Pre-compile sql
- From: "Jeremy Schneider" <jeremy.schneider@xxxxxxxxxxxxxx>
- To: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
- Date: Wed, 30 Apr 2008 14:40:54 -0400
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?
> > >
> > > --
> > > //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
Other related posts:
