Home » Open Source » Programming Interfaces » out of memory issue (oracle10g)
out of memory issue [message #685656] Tue, 01 March 2022 06:59 Go to next message
arun888
Messages: 78
Registered: June 2015
Location: INDIA
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 Go to previous messageGo to next message
arun888
Messages: 78
Registered: June 2015
Location: INDIA
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;
}

Re: out of memory issue [message #685667 is a reply to message #685666] Tue, 01 March 2022 21:29 Go to previous messageGo to next message
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,
Re: out of memory issue [message #685668 is a reply to message #685667] Tue, 01 March 2022 22:06 Go to previous messageGo to next message
arun888
Messages: 78
Registered: June 2015
Location: INDIA
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 Go to previous messageGo to next message
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,
Re: out of memory issue [message #685670 is a reply to message #685669] Wed, 02 March 2022 01:19 Go to previous messageGo to next message
arun888
Messages: 78
Registered: June 2015
Location: INDIA
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.
Re: out of memory issue [message #685673 is a reply to message #685670] Wed, 02 March 2022 16:12 Go to previous messageGo to next message
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,
Re: out of memory issue [message #685674 is a reply to message #685673] Wed, 02 March 2022 20:25 Go to previous messageGo to next message
arun888
Messages: 78
Registered: June 2015
Location: INDIA
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];
        }
		
}
Re: out of memory issue [message #685685 is a reply to message #685669] Mon, 07 March 2022 01:25 Go to previous messageGo to next message
arun888
Messages: 78
Registered: June 2015
Location: INDIA
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];
Re: out of memory issue [message #685686 is a reply to message #685669] Mon, 07 March 2022 01:56 Go to previous messageGo to next message
arun888
Messages: 78
Registered: June 2015
Location: INDIA
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 ?
Re: out of memory issue [message #685687 is a reply to message #685686] Mon, 07 March 2022 01:57 Go to previous messageGo to next message
arun888
Messages: 78
Registered: June 2015
Location: INDIA
Member
Currently, i am getting outofmemory issue in the lookup table due to size. can we replace any alternate method to replace lookup.
Re: out of memory issue [message #685691 is a reply to message #685673] Mon, 07 March 2022 05:40 Go to previous message
arun888
Messages: 78
Registered: June 2015
Location: INDIA
Member
rdbms to restrict the amount of data that goes into that hash

how i can restrict this ? Any idea.

[Updated on: Mon, 07 March 2022 05:40]

Report message to a moderator

Previous Topic: passing values manually
Next Topic: error in query
Goto Forum:
  


Current Time: Sun Jun 26 02:49:24 CDT 2022