Re: statement caching in oracle

There are different kinds of parses, soft parsing and hard parsing. In v8 these come out as two different system statistics. In v7 there was just one parse statstic but you could tell from the get hit and pit hit ratios of utlbstat/estat whether you are getting hard parsing or soft parsing. Soft parsing is not that big of a deal and enormously less expensive than hard parsing. If you are closing a cursor in the client side, you are saying to release that memory and if you reopen the cursor you have to read the cursor into client memory from the shared pool again. This is a soft parse and is pretty cheap. If the cursor isn't in the shared pool you have to really parse it and put it in the shared pool - this is a hard parse and relatively expensive.

Let me know if you'd like more info

Hope that helps

Best
Kyle
In article <95ka41$9mi$1_at_nnrp1.deja.com>,   Keith D. Gregory <kgregory_at_tibco.com> wrote:
> In article <95cb92$44a$1_at_nnrp1.deja.com>,
> hailey_kyle_at_my-deja.com wrote:
>
> > Why do you think the statement is not kept on the server side ie the
> > shared pool? The statement should be there until it gets aged out.
 If
> > you want to ensure that it is not aged out you could use
> > dbms_shared_pool.keep.
>
> Session tracing shows that the statement is being reparsed each time
> it's called. By comparison, using Oracle's Pro/C
> with "release_cursor=no", session tracing shows a single parse for N
> executions. I didn't take a look at V$SQLAREA to see if there are
> separate rows for each execution.
>
> -kdg
>
> Sent via Deja.com
> http://www.deja.com/
>

Sent via Deja.com
http://www.deja.com/ Received on Mon Feb 05 2001 - 12:35:47 CST