Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Why Oracle always reparses anonymous blocks?
In article <ef61c6ff.0112250406.63448594_at_posting.google.com>,
corvax-msk_at_yandex.ru says...
>
>Hi, all!
>When I opened v$sqlarea I saw that all anonumous PL/SQL blocks were
>been always reparsed. If I exec the same block again, Oracle reparses
>it again...
>I call from anonymous blocks packages' procedures. And each reparsing
>takes a redundant CPU resourse, I think. So, it's unreasonable.
>
>How can I avoid redundant reparsing of identical anonymous PL/SQL
>blocks?
>
>Thanks in advance.
It is upto the programmer to ensure a block is parsed only ONCE per program, not once per execution.
I see this lots in java, the developers open a callable statement (that PARSES it, it'll be a soft parse, not as bad as a hard but still overhead and inhibits scalability), bind to it (if we are lucky), execute it and then close it.
They should:
open it once per session.
bind it / execute it many times over and over.
close it or just disconnect, that'll close it.
then you will see the parse count go waaaayyy down.
-- Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle CorpReceived on Tue Dec 25 2001 - 12:26:27 CST