"music4" <music4_at_163.net> wrote in message
news:dk92nf$jfm_at_netnews.net.lucent.com...
> Greetings,
>
> I am running an Oracle 8.1.7 instance, and now I want to tune its
> performance. But I get two confusions:
>
> 1. From Oracle's report.txt statistic, I can see latch "library cache" has
> relative low hit ratio 85% while other latches' hit ratio are all above
99%.
> Then I am considering to enlarge SHARED_POOL_SIZE. But by "SELECT * FROM
> V$SGASTAT", I find "shared pool free memory" is 60M bytes, that means
shared
> pool is not full.
>
> 2. By "select sql_text, parse_calls, executions from v$sqlarea", I find
many
> of frequently-executed statements has close "parse_calls" and
"executions".
> For example, a statement's "parse_calls" is 436505031 and "executions" is
> 436511391. I writes all statement with bind mode, so the statements are
text
> identical. But why Oracle always reparse those statements? As mentioned in
> question 1, shared pool has free space, why Oracle don't put parsed
> statements in shared pool for reusing? How to solve this problem?
>
> Thnaks in advance,
> Evan
>
>
You are almost there. You can eliminate some of the parsing by reusing the
cursor and changing the values for the bind and rebinding and then
executing. Only close the cursor when you exit the application. You are
doing a soft parse (much better than the hard parse).
Jim
Received on Tue Nov 01 2005 - 21:51:36 CST
