I was out to meeting till little bit ago. I judged the compile time by using PowerBuilder. Upon executing the sql, I timed the amount of time it took to display the columns in the view. I then timed the amount of time before seeing data. I am working on redoing the java to use prepared statements. I have the cursor pinned in the shard pool already. +++++++++++++++++++++++++++++++++++++++++++++++ LeRoy Kemnitz UW System Administration Database Administrator (608) 265-5775 ----- Original Message ----- From: "Jeremy Schneider" <jeremy.schneider@xxxxxxxxxxxxxx> Date: Wednesday, April 30, 2008 1:40 pm Subject: Re: Pre-compile sql > 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 > -- //www.freelists.org/webpage/oracle-l