Why are the SQL's hard parsed over and over?

Hi,

System 8.0.5.2.1 std edition on WinNt 4.0 I am trying to optimize an application that consumes 100% CPU on the server when run. I have identified those SQL's with the higest number of executions and I discovered that they all have as many parse calls as executions ! Could you help me find out why these SQL's are parsed over and over?

1)
This SQL was executed and parsed 350000 times in the sample, each time returning one row. Buffer/total buffer get ratio 0.00015, so it hit the cache almost every time. I suspect though that it used a lot of CPU time for parsing.

SELECT S.*, SE.ENHED_TYPE, T.OPLOESNING
FROM SERIE S, SERIE_ENHED SE, TIDSOPLOES T WHERE S.SERIE_RECNUM=:RECNUM
AND SE.SERIE_ENHED_RECNUM=S.SERIE_ENHED_RECNUM AND T.TIDSOPLOES_RECNUM=S.TIDSOPLOES_RECNUM Serie, Serie_enhed og Tidsoploes are simple tables. :Recnum i s a bind variable. The joins are on keys and foreign keys have indexes.

2)
This SQL was executed and parsed 350000 times, processing a total of 31M rows hitting the cache in 98,5%. Suspect high CPU usage because of hard parses.

SELECT S.*, S.VALUE*:OMREGN OMREGNVALUE
FROM SAMPLES S
WHERE S.SERIE_RECNUM=:SERIE
AND S.SAMPLETIME_FROM_CET BETWEEN :FRADATO AND :TILDATO-1/86400 ORDER BY SAMPLETIME_FROM_CET Samples is a simple table, :Serie is a bind variable. :Omregn is a bind variable, but is used in the in Select s.Value * : Omregn. Does that trigger the hard parses?

3)
Executed 160M times, parsed 160M times

Select action
From summertime
where :when >= summertime_start
and :when < summertime_end

Summertime is a simple table, :when is a bind var

TIA
Peter Laursen Received on Sat Sep 22 2001 - 05:55:23 CDT