Pre-compile sql
- From: LeRoy Kemnitz <lkemnitz@xxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 29 Apr 2008 15:19:32 -0500
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?
--
//www.freelists.org/webpage/oracle-l
Other related posts:
