Re: Why Oracle always reparses anonymous blocks?

From: Sybrand Bakker <oradba_at_sybrandb.demon.nl>
Date: Tue, 25 Dec 2001 13:56:35 +0100
Message-ID: <jmtg2u8dadcuvpa3qd5fcm5h2dq0561j1k@4ax.com>

On 25 Dec 2001 04:06:19 -0800, corvax-msk_at_yandex.ru (Corvax) wrote:

>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.

An anonymous block is just like a statement in sql*plus. It is reparsed always, and you can't do anything about that. However you need to differentiate between a hard parse and a soft parse. If the block is still identical the parse will be a soft-parse, if the block changed it will escalate into a hard parse. So, I think it's a bit too early to start using the word 'unreasonable'. If you really think so, forward your concern to Oracle Technical Support.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue Dec 25 2001 - 06:56:35 CST