Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> calculating cost of soft parses
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
Received on Wed Apr 30 2003 - 14:45:07 CDT
![]() |
![]() |