Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> calculating cost of soft parses

calculating cost of soft parses

From: NetComrade <andreyNSPAM_at_bookexchange.net>
Date: Wed, 30 Apr 2003 19:45:07 GMT
Message-ID: <3eb0264c.1331074754@nyc.news.speakeasy.net>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US