Re: Pre-compile sql
- From: LeRoy Kemnitz <lkemnitz@xxxxxxxx>
- To: William.Blanchard@xxxxxxxxxx
- Date: Tue, 29 Apr 2008 16:05:32 -0500
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 firethis 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