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

What figures do you get from v$sysstat for:

    parses
    hard parses
    parse CPU

Reporting the counts from v$sql (which, I guess, is what you are doing below) doesn't tell you whether you are doing hard or soft parses. It is reasonably likely that most of your parsing will turn out to be 'soft' parsing.

What is the front-end sending this code in to the system - has it been (or can it be) written to parse once and re-execute. Better still, can it be written to parse and bind once, then re-execute ?

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

Peter Laursen wrote in message
<3bac6e0f$0$259$edfadb0f_at_dspool01.news.tele.dk>...


>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

>

>