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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of LeRoy Kemnitz
> Sent: Tuesday, April 29, 2008 3:20 PM
> To: oracle-l_at_freelists.org
> 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