Re: Pre-compile sql

The data is updated online so it needs to be refreshed quickly. I will look into trying to tune the refresh time of the materialized view. 



Blanchard William wrote:

 Have you considered a materialized view?



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of LeRoy Kemnitz
Sent: Tuesday, April 29, 2008 3:20 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Pre-compile sql

All -

I have a java app running on Tomcat - Linux and going against my
10.2.0.2 DB 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


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





--
LeRoy Kemnitz
UW System Administration
Database Administrator
780 Regent Street, #246
Madison, WI 53714
Phone: (608) 265 -5775
Fax: (608) 265 - 2090

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

Other related posts: