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

Bobak, Mark wrote:
> 
> 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

Below is a test I've written to check DBD::Oracle is leaking no more
than 1 temp lob (as it does not clean up until the next execute). It
(the test code) works for me with DBD::Oracle 1.24a and the following
patch provided by John:

 int
   dbd_rebind_ph_lob(SV *sth, imp_sth_t *imp_sth, phs_t *phs)
   {
    dTHX;
    D_imp_dbh_from_sth ;
    sword status;
    ub4 lobEmpty = 0;
++  if (phs->desc_h && phs->desc_t == OCI_DTYPE_LOB)
++  ora_free_templob(sth, imp_sth, (OCILobLocator*)phs->desc_h);

    if (!phs->desc_h) {

Also, in my tests it does not seem to break anything.

However, it is not as yet stopping the leaking of temporary lobs in my
application (which uses prepare_cached). I will investigate further.

John, it may be worth including this test in DBD::Oracle.

martin@bragi:~/svn/dbd-oracle/trunk$ perl -I blib/lib -I blib/arch
t/91_lob_leak.pl
1..7
ok 1 - found sid 84
ok 2 - found 0 cached lobs
ok 3 - created test function
ok 4 - found 0 cached lobs
ok 5 - Not leaking temporary lobs on prepare
ok 6 - found 1 cached lobs
ok 7 - Not leaking temporary lobs on prepare_cached

#!perl -w

##
----------------------------------------------------------------------------
## 91_lob_leak.pl
## By Martin Evans, Easysoft Limited
##
----------------------------------------------------------------------------
## Test we are not leaking temporary lobs
##
----------------------------------------------------------------------------

use Test::More;

use DBI;
use Config;
use DBD::Oracle qw(:ora_types);
use strict;
use warnings;
use Data::Dumper;

unshift @INC ,'t';
require 'nchar_test_lib.pl';

$| = 1;

my $dsn = oracle_test_dsn();
my $dbuser = $ENV{ORACLE_USERID} || 'scott/tiger';
my $dbh = DBI->connect($dsn, $dbuser, '');

if ($dbh) {
    plan tests => 7;
} else {
    plan skip_all => "Unable to connect to Oracle ($DBI::errstr)\nTests
skipped.\n";
}

# get SID and cached lobs
# if sid not passed in we run 2 tests, get the sid and the cached lobs
# if sid passed in we run 1 test which is to get the cached lobs
sub get_cached_lobs
{
    my ($dbh, $sid) = @_;
    my $cached_lobs;

    if (!defined($sid)) {
      SKIP: {
            eval {
                ($sid) = $dbh->selectrow_array(
                    q/select sid from v$session where audsid =
SYS_CONTEXT('userenv', 'sessionid')/);
            };
            skip 'unable to find sid', 2 if ($@ || !defined($sid));

            pass("found sid $sid");
        };
    }
    if (defined($sid)) {
      SKIP: {
            eval {
                $cached_lobs = $dbh->selectrow_array(
                    q/select CACHE_LOBS from V$TEMPORARY_LOBS where sid
= ?/, undef, $sid);
            };
            skip 'unable to find cached lobs', 1
                if ($@ || !defined($cached_lobs));
            pass("found $cached_lobs cached lobs");
        };
    }
    return ($sid, $cached_lobs);
}

sub setup_test
{
    my ($h) = @_;
    my ($sth, $ev);

    my $fn = 'p_DBD_Oracle_drop_me';

    my $createproc = << "EOT";
CREATE OR REPLACE FUNCTION $fn(pc IN CLOB) RETURN NUMBER AS
BEGIN
    NULL;
    RETURN 0;
END;
EOT

    eval {$h->do($createproc);};
    BAIL_OUT("Failed to create test function - $@") if $@;
    pass("created test function");

    return $fn;
}

sub call_func
{
    my ($dbh, $function, $how) = @_;

    eval {
        my $sth;
        my $sql = qq/BEGIN ? := $function(?); END;/;
        if ($how eq 'prepare') {
            $sth = $dbh->prepare($sql) or die($dbh->errstr);
        } elsif ($how eq 'prepare_cached') {
            $sth = $dbh->prepare_cached($sql) or die($dbh->errstr);
        } else {
            BAIL_OUT("Unknown prepare type $how");
        }
        $sth->{RaiseError} = 1;

        BAIL_OUT("Cannot prepare a call to $function") if !$sth;

        my ($return, $clob);
        $clob = 'x' x 1000;
        $sth->bind_param_inout(1, \$return, 10);
        $sth->bind_param(2, $clob, {ora_type => ORA_CLOB});
        $sth->execute;
    };
    BAIL_OUT("Cannot call $function successfully") if $@;
}


my ($sid, $cached_lobs);
my ($function);
SKIP: {
    ($sid, $cached_lobs) = get_cached_lobs($dbh); # 1 2
    skip 'Cannot find sid/cached lobs', 5 if !defined($cached_lobs);

    $function = setup_test($dbh); # 3
    my $new_cached_lobs;

    foreach my $type (qw(prepare prepare_cached)) {
        for my $count(1..100) {
            call_func($dbh, $function, $type);
        };
        ($sid, $new_cached_lobs) = get_cached_lobs($dbh, $sid);

        # we expect to leak 1 temporary lob as the last statement is
        # cached and the temp lob is not thrown away until you next
        # execute
        if ($new_cached_lobs > ($cached_lobs + 1)) {
            diag("Looks like we might be leaking temporary lobs from
$type");
            fail("old cached lobs: $cached_lobs " .
                     "new cached lobs: $new_cached_lobs");
        } else {
            pass("Not leaking temporary lobs on $type");
        }
        $cached_lobs = $new_cached_lobs;
    }

};

END {
    if ($dbh) {
        local $dbh->{PrintError} = 0;
        local $dbh->{RaiseError} = 1;
        eval {$dbh->do(qq/drop function $function/);};
        if ($@) {
            warn("function p_DBD_Oracle_drop_me possibly not dropped" .
                     "- check - $@\n") if $dbh->err ne '4043';
        } else {
            diag("function p_DBD_Oracle_drop_me dropped");
        }
    }
};

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com