OraFAQ Forum: Programming Interfaces

Home » Open Source » Programming Interfaces » out of memory issue (oracle10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

out of memory issue [message #685656]

Tue, 01 March 2022 06:59

arun888
Messages: 100
Registered: June 2015
Location: INDIA

Senior Member

I have tried the below query and it is consuming more time and Out of memory issues.
Out of memory!
can you please let me know to avoid the out of memory issues.

  my $dbh = DBI->connect ('dbi:Oracle:usbmfs', 'US', 'states', {AutoCommit => 0, RaiseError => 1});
  my $sth = $dbh->prepare (qq{ select cpu_id, system, gen, vendor, item, week_first_moved, week_last_moved from us_item_tbl });
        $sth->execute;
        $sth->bind_columns (\(my( $cpu, $sys, $gen, $vend, $item, $wad, $wlm)));
        while ($sth->fetch) {
            my $ean = sprintf "%02s%05s%05s", $sys, $vend, $item;
            $cpu = sprintf "%014s", $cpu;
            $lookup{$nae}{$cpu} = [$wad, $wlm];
        }
    $dbh->disconnect;
    $$self{'SANE'} = \%lookup;
}

[Updated on: Tue, 01 March 2022 07:02]

Report message to a moderator

out of memory issue [message #685666 is a reply to message #685656]

Tue, 01 March 2022 21:22

arun888
Messages: 100
Registered: June 2015
Location: INDIA

Senior Member

I have tried to use fetchall_arrayref function for fetching all the records from the oracle and currently getting out of memory issue. any idea how to clear the cache to get rid of the out of the memory issue.


sub sane {
    my $self = shift;
    my %lookup;
    my $dbh = DBI->connect ('dbi:Oracle:usbmfs', 'US', 'states', {AutoCommit => 0, RaiseError => 1});
    my $sth = $dbh->prepare (qq{ select cpu_id, system, gen, vendor, item,
                                        week_first_moved, week_last_moved
                                     from us_item_tbl });

        $sth->execute ();
        $sth->bind_columns (\(my( $cpu, $sys, $gen, $vend, $item, $wad, $wlm)));
         my $rows = $sth->fetchall_arrayref({});;
         for my $row (@$rows) {
            my $nae = sprintf "%02s%05s%05s", $sys, $vend, $item;
            print "$nae\n";
            $upc = sprintf "%014s", $cpu;
            $lookup{$ean}{$cpu} = [$wad, $wlm];
        }
#    }
    $dbh->disconnect;
    $$self{'SANE'} = \%lookup;
}

Report message to a moderator

Re: out of memory issue [message #685667 is a reply to message #685666]

Tue, 01 March 2022 21:29

dwatkins
Messages: 6
Registered: June 2011
Location: Australia

Junior Member

Hi Arun,
perhaps you could clarify what is running out of memory?

is it oracle, because you have no where clause limiting the size of your resultset
or the perl process when you construct that giant hash of hashes?

cheers,

Report message to a moderator

Re: out of memory issue [message #685668 is a reply to message #685667]

Tue, 01 March 2022 22:06

arun888
Messages: 100
Registered: June 2015
Location: INDIA

Senior Member

I am getting error from oracle. Please find the message below.

Error:

Out of memory!
Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Oracle::db handle ukbmfp at /opt/acc_perl/lib/site_perl/5.14.2/PA-RISC2.0/DBI.pm line 2061.

2061 line code.

      if (not defined $max_rows) {
            push @rows, { %row } while ($sth->fetch); # full speed ahead!
        }
        else {
            push @rows, { %row } while ($max_rows-- and $sth->fetch);
        }

        return \@rows;
    }
        my $rows = $sth->fetchall_arrayref($slice, my $MaxRows = $attr->{MaxRows});
        $sth->finish if defined $MaxRows;
        if (my $max = $attr->{MaxRows}) {
        by including a 'C<MaxRows>' attribute in \%attr. In which case finish()
        'C<MaxRows>' attribute in \%attr.

[Updated on: Tue, 01 March 2022 22:18]

Report message to a moderator

Re: out of memory issue [message #685669 is a reply to message #685668]

Tue, 01 March 2022 22:41

dwatkins
Messages: 6
Registered: June 2011
Location: Australia

Junior Member

Hi Arun,
That doesn't look like an Oracle out-of-memory error. (No ORA-0xxxxx code)

Try with a where clause to restrict your result set size, (and temporarily comment out this line -> $lookup{$ean}{$cpu} = [$wad, $wlm] Wink

It's been years, but I recall you could do something like '...or die "Problem $DBI::errstr";' to pry out a more useful error message.

cheers,

Report message to a moderator

Re: out of memory issue [message #685670 is a reply to message #685669]

Wed, 02 March 2022 01:19

arun888
Messages: 100
Registered: June 2015
Location: INDIA

Senior Member

I have disabled the below line and could see the job completed successfully.

$lookup{$nae}{$cpu} = [$wad, $wlm];

Any other alternative way for the above line.

Report message to a moderator

Re: out of memory issue [message #685673 is a reply to message #685670]

Wed, 02 March 2022 16:12

dwatkins
Messages: 6
Registered: June 2011
Location: Australia

Junior Member

Hi Arun,
Well, you've found where the out-of-memory error occurs.
At this point, you can either use your

rdbms to restrict the amount of data that goes into that hash.
sysadmin to increase the amount of RAM assigned to your VM.
manager to pay for additional physical RAM

other options are, well, less useful.
You could investigate tie-ing the hash (saves it to disk on the machine running perl)
(https://perldoc.perl.org/perltie#Tying-Hashes)
If you are trying to cache data in an appserver, this can be a way of doing that.

cheers,

Report message to a moderator

Re: out of memory issue [message #685674 is a reply to message #685673]

Wed, 02 March 2022 20:25

arun888
Messages: 100
Registered: June 2015
Location: INDIA

Senior Member

Do you have any other easy way to store the records into the array values.

Query to fetch all records from the table.
select cpu_id, system, generation, vendor, item, week_first_moved, week_last_moved from us_item_tbl

Rows :5812750


Fetching all records through below code and getting out of memory:

sub sane {
    my $self = shift;
    my %lookup;
    my $dbh = DBI->connect ('dbi:Oracle:usbmfs', 'US', 'states', {AutoCommit => 0, RaiseError => 1});
    my $sth = $dbh->prepare (qq{ select cpu_id, system, generation, vendor, item, week_first_moved, week_last_moved from us_item_tbl});
        $sth->execute();
	my $rows = @{$dbh->selectall_arrayref('select upc_id, system, generation, vendor, item, week_first_moved, eek_last_moved from uk_item_tbl')};
	foreach my $row (@$rows) {
	my($cpu, sys, $gen, $vend, $item, $wad, $wlm) =@$rows;
            my $ean = sprintf "%02s%05s%05s", $sys, $vend, $item;
            $cpu = sprintf "%014s", $cpu;
            $lookup{$nae}{$cpu} = [$wad, $wlm];
        }
		
}

Report message to a moderator

Re: out of memory issue [message #685685 is a reply to message #685669]

Mon, 07 March 2022 01:25

arun888
Messages: 100
Registered: June 2015
Location: INDIA

Senior Member

Yes. I have disabled the below line and code went through. Can you let me know what i can use to save the values instead of lookup.

$lookup{$nae}{$cpu} = [$wad, $wlm];

Report message to a moderator

Re: out of memory issue [message #685686 is a reply to message #685669]

Mon, 07 March 2022 01:56

arun888
Messages: 100
Registered: June 2015
Location: INDIA

Senior Member

I have commented out the line $lookup{$nae}{$cpu} = [$wad, $wlm]; and stopped getting the out of memory issue.

Do you have any alternate method to replace lookup ?

Report message to a moderator