From: Martin J. Evans [mailto:[email protected]]
Sent: Wednesday, April 07, 2010 2:49 PM
To: Bobak, Mark
Cc: [email protected]
Subject: 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.
Oh, ok, good. I wasn't aware of that.
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.
Ok....
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?
Yes, that's correct. Before the patch, if I used 'prepare_cached' instead of
'prepare', I would see CACHE_LOBS increasing in V$TEMPORARY_LOBS and after the
patch, it goes away.
If so that is not my case as CACHE_LOBS increases forever.
Hmm...so there's still more to the puzzle.
How are you retrieving your lobs? I am using reference cursors returned from a
procedure then reading them via the lob locator.
Well, I have a really simple, brain dead test case, that I provided to John,
which demonstrates the problem. I'll send it to you. (Not sure if this
mailing list will accept attachments, so I'll send it in a separate email
directly to you.) But, then I also have a more complex test program, based on
our actual codebase. Both of these demonstrate a leak before the patch, and
lack of a leak after the patch.
The simple test case I'm happy to provide, but, it doesn't really do *anything*
with the LOB. It's truly a braindead test case, but fit the bill in terms of
the smallest piece of code I could reproduce the problem with. The more
complex test case may take some time to distill down to something I can package
up.
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
Thanks Martin!
-Mark
-----Original Message-----
From: Martin Evans [mailto:[email protected]]
Sent: Wednesday, April 07, 2010 12:38 PM
To: Bobak, Mark
Cc: [email protected]<mailto:[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
