Re: calculating cost of soft parses

NetComrade wrote:
>
> We have an application that caches cursors, but given that it leaks
> memory it disconnects after serving 200 web pages. I want to convince
> app developers to get rid of memory leaks, but I want to give them
> certain #'s, such as what kind of db load improvement they're going to
> see if application wouldn't reconnect after every 200 pages (and
> therefore have tons of 'soft' parses). Is there any latch contention
> caused by soft parses? How can I calculate the overall CPU usage drop
> from getting rid of majority of soft parses?
>
> Thanks in advance!
>
> from oraperf:
>
> CPU Time Time Percentage Per Execute Per User Call Per Transaction
> Total 544715 100.00% 0.15 0.09 10.18
> Parse CPU Time 95246 17.49% 0.03 0.02 1.78
> Recursive CPU Time 95780 17.58% 0.03 0.02 1.79
> Other CPU Time 353689 64.93% 0.10 0.06 6.61
>
> parse - execute ratio
>
> 1390968 parses (4886 hard parses), 3562385 executions of SQL
> statements happened. Normally the number of parses should be low and
> executions should be high. Each cursor was parsed an average of 3.31
> times. A value greater than 1, means that the same cursor is parsed
> more than once. A value lower than 1 means that not all opened cursors
> have been parsed yet. Parsing the same cursor again and again will
> consume CPU and other resources. There is no need to parse the same
> cursor again for each execute. The re-parsing normally happens becomes
> some applications have an build in cursor cache which is configured
> too small. Making the cursor cache in the application larger will
> reduce the reparsing. During this interval 46960 sessions logged on
> and at the end of the timing interval 0 more sessions where active.
> Sessions are frequently logon and logoff. This will cause a lot of
> (soft) parses. The basic idea is to logon once and execute many SQL
> statements.
> .......
> We use Oracle 8.1.7.4 on Solaris 2.7 boxes
> remove NSPAM to email

One way would be to use PLSQL to compare:

for i in 1 .. 100000 loop
  (select statement)
end loop;

versus

for i in 1 .. 100000 loop
  execute immediate '(select statement)' end loop;

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"