shared pool problems

From: <giardine_at_my-deja.com>
Date: 2000/04/13
Message-ID: <8d5bs7$9mq$1@nnrp1.deja.com>#1/1

Hi. I am new to this list. I am having problems with Oracle and the shared pool. Everything was working fine then suddenly problems appeared without any changes being made. These are databases under development and get only light use. I have tried increasing the shared pool size, increasing the reserved share pool size, and clearing the shared pool all without success. It seems to be tied to querying a table with a primary key and having a large number of bind parameters. I receive the error

DBD::Oracle::st execute failed: ORA-04031: unable to allocate 52 bytes of shared memory ("shared pool","SELECT id, bar FROM foo WHER...","sql area","optdef: apanlg") (DBD ERROR: OCIStmtExecute) at ./testpool line 50.

I flushed the shared pool and then ran this test script

#!/usr/bin/perl -w

use strict;
use DBI;

my $dbh = DBI->connect("dbi:Oracle:", "scott", "tiger")

        or die "$DBI::err: $DBI::errstr\n";
$dbh->{RaiseError} = 1;

#$dbh->trace(3);

my($sth, $sql, $i);
eval {

   $sth = $dbh->prepare("DROP TABLE foo");    $sth->execute;
};
#either dropped or didn't exist;

$sth = $dbh->prepare(qq{

CREATE TABLE foo (
ID NUMBER PRIMARY KEY,
bar VARCHAR(200)
)
});
$sth->execute;

#fill table;
$sth = $dbh->prepare("INSERT INTO foo VALUES(?, ?)");
foreach $i (1 .. 1000) {

   $sth->execute($i, "Are we there yet?"); }

$sql = "SELECT id, bar FROM foo ";
$sql .= "WHERE id IN (?";
$sql .= ",?" x 250;
$sql .= ") OR id IN (?";
$sql .= ",?" x 250;
$sql .= ") OR id IN (?";
$sql .= ",?" x 250;
$sql .= ") OR id IN (?";
$sql .= ",?" x 250;
$sql .= ") ORDER BY id";

my @bind_values = ( 0..1003 );

$sth = $dbh->prepare($sql);
$sth->execute(@bind_values);

my @row;
while (@row = $sth->fetchrow_array) {

    foreach (@row) {

        print if defined;
        print "\t";

    }
 print "\n";
}

$dbh->disconnect;

Any suggestions or help would be greatly appreciated. Belinda

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Apr 13 2000 - 00:00:00 CDT