Re: Pre-compile sql

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Wed, 30 Apr 2008 11:37:40 +0100
Message-ID: <7765c8970804300337q75a99acfwb77fe5d08895df57@mail.gmail.com>


Typically if you are considering setting CURSOR_SHARING then it's likely that you are using string substitution - i.e the statement object rather than prepared statements in your java app - and so aren't using bind variables. Rewriting the java code is the best route for this one.

regards

Niall

On Tue, Apr 29, 2008 at 9:19 PM, LeRoy Kemnitz <lkemnitz_at_uwsa.edu> 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?
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Apr 30 2008 - 05:37:40 CDT

Original text of this message