Hi Martin,
Well, actually, a lot has happened since I wrote my original mail to this list.
First, I was able to come up with a simple, reproducible test case, and I
provided that to John Scoles. From that, he has provided me with a simple, (2
lines of C code) patch to oci8.c. We have built a custom DBD::Oracle based on
it, and it does seem to solve the LOB leaking problem. The count in
V$TEMPORARY_LOBS stays zero for CACHE_LOBS after this fix.
As to the definition of V$TEMPORARY_LOBS, I know what you mean. The
documentation is pretty useless on this view.
As to the actual definition, that can be seen from:
SQL> select view_definition from v$fixed_view_definition where view_name
='V$TEMPORARY_LOBS';
VIEW_DEFINITION
------------------------------------------------------------------------------------------------------------------------------------------------------
select SID, CACHE_LOBS, NOCACHE_LOBS, ABSTRACT_LOBS from
GV$TEMPORARY_LOBS where inst_id = USERENV('Instance')
SQL> select view_definition from v$fixed_view_definition where view_name
='GV$TEMPORARY_LOBS';
VIEW_DEFINITION
------------------------------------------------------------------------------------------------------------------------------------------------------
select kdlt.inst_id, kdlt.kdltsno, sum(kdlt.kdltctmp),
sum(kdlt.kdltnctmp), abs.count from X$KDLT
kdlt, X$AB
STRACT_LOB abs group by kdlt.inst_id, kdlt.kdltsno,
abs.count order by kdltsno
But, it's not particularly useful, since X$ABSTRACT_LOB and X$KDLT don't really
get you anywhere....
Anyhow, I'm hopeful that John Scoles patch will solve the problem. The initial
testing I've done with his patch, seems to indicate that it's working. It
probably needs a lot more testing, though.
-Mark
-----Original Message-----
From: Martin Evans [mailto:[email protected]]
Sent: Wednesday, April 07, 2010 12:38 PM
To: Bobak, Mark
Cc: [email protected]
Subject: Re: Help with handling CLOBs in Oracle and ORA-1460 error
Martin J. Evans wrote:
> Bobak, Mark wrote:
>> Hi all,
>>
>> I'm new to the list, and I'm a DBA, not really a Perl programmer, but I'm
>> running into this issue that I hope someone can help with.
>>
>> I think I'm getting dangerously close to getting this to work. But, I'm
>> just not quite getting there.
>>
>> We have an application coded in Perl w/ DBI * DBD::Oracle. Versions:
>> Database: Oracle 11.2.0.1.0, Perl 5.10.1, DBI 1.609, DBD::Oracle 1.23.
>>
>> We've had a long standing problem of leaking temporary LOBs when handling
>> lobs. (i.e., count of lobs reported in V$TEMPORARY_LOBS continues to
>> increase till the session disconnects from the database.) I recently
>> discovered that if you use ORA_CLOB and bind by reference (bind_param_inout)
>> it solves the leak problem. But my problem now is, prepare works, bind
>> works, but execute hits ORA-1460 "unimplemented or unreasonable conversion
>> requested".
>
> Were you looking at CACHE_LOBS?
>
> As I said in my previous reply we have a leak with DBD::Oracle which
> I've never been able to track down and we use a lot of lobs.
>
> My daemon process currently has over 400 CACHE_LOBS (after 10 minutes of
> running) and it is steadily increasing.
>
> Do you know what CACHE_LOBS are and are you sure this is a sign of
> leaking temporary lobs?
BTW, forgot to mention that I used the following SQL to locate the SQL
that created the temporary lobs. It returns far less rows that you'd
expect from CACHE_LOBS (above):
select b.SQL_TEXT, a.USERNAME, a.SQL_ID, a.CONTENTS, a.SEGTYPE,
a.SEGFILE#, a.SEGBLK#, a.EXTENTS, a.BLOCKS from V$TEMPSEG_USAGE a
join v$sql b on a.sql_id = b.sql_id
I've not found a good definition of v$temporay_lobs yet.
>> So, my questions are:
>> If I'm using temporary lobs that are created/managed in Perl, I should use:
>> $sth->bind_param_inout(':pi_cit_comprep', \$paramHash->{cit_comprep},
>> {ora_type => ORA_CLOB});
>>
>> to do the bind? I think this is correct, as the LOB leaking goes away, but,
>> doing it this way, I hit ORA-1460 on CLOBs larger than 32k.
>>
>> Previously, I was binding with:
>> $sth->bind_param(':pi_cit_comprep', $paramHash->{cit_comprep}, {ora_type =>
>> ORA_CLOB});
>>
>> And this never hit the ORA-1460 error, but would leak temporary LOBs.
>>
>> So, how do I *both* avoid leaking temporary LOBs *and* not encounter
>> ORA-1460 on LOBs larger than 32k? Is this a bug? In Oracle? Perl? DBI?
>> DBD::Oracle?
>>
>> Any thoughts or suggestions would be greatly appreciated!
>>
>> Thanks,
>>
>> -Mark
>>
>>
>
> Martin
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
