Re: High hard parse and version counts for dbms_lob functions

"El Toro" <medawsonjr_at_yahoo.com> wrote in message news:2e1cd2b.0305011046.fac6d55_at_posting.google.com...
> OS: Solaris 8
> Oracle: 8.1.5.0
>
> While running a tkprof off of a 10046 level 12 tracefile, I noticed that
> each and every parse of all the dbms_lob functions (e.g., getchunksize(),
> read(), write(), etc.) are hard parses.
>
> On the getchunksize() calls, it gets hard parsed for every execute. For
the
> read() calls, exactly 50% of the total executions resulted in hard parses.
> Lastly, for all the write() calls, 90% of the total executions resulted in
> hard parses.
>
> Every call makes use of bind variables. The version counts for these
state-
> ments are each into the single digit thousands.
>
> While I'm aware of the 8.1.5 bug with timed_statistics=true, I notice in
the
> same tkprof output that all other SQL that makes use of bind vars (e.g.,
> select, insert, update, delete) parses once and executes many.
>
> Is there anything that I'm missing here? Should this be so for the
dbms_lob
> package functions? Is this to be expected if LOBS are used such that they
are
> stored out-of-line from the table and in a separate LOB segment?
>
> Thanks in advance for your help.

You might want to check whether pinning the dbms_lob package (exec dbms_shared_pool.keep('SYS.DBMS_LOB') )

-- 
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address
changes this situation.
At the least the dbms_lob package now doesn't need to be revalidated on
every use.

Regards