Re: Help with handling CLOBs in Oracle and ORA-1460 error

Bobak, Mark wrote:
> Hi Martin,
>
> Well, actually, a lot has happened since I wrote my original mail to this list.
>
>   
I realise this as I've been in contact with John.

> 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.
I've got that and applied it.

>   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.
>
>   
ok, so just to be certain - before the patch you found v$temporary_lobs 
view was showing increasing CACHE_LOBS and since the patch is applied 
this has gone away?

If so that is not my case as CACHE_LOBS increases forever.

How are you retrieving your lobs? I am using reference cursors returned 
from a procedure then reading them via the lob locator.

> As to the definition of V$TEMPORARY_LOBS, I know what you mean.  The documentation is pretty useless on this view.
>
>   
so it is - I've found very little that says enough for me to be 
confident in it.
> 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
>
>
>   
Doesn't seem to make a difference to me. I'm not at work now but I will 
try and isolate my example tomorrow.

Thanks for coming back on this.

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