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
