Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: shared pool problems

Re: shared pool problems

From: Mark D Powell <markp7832_at_my-deja.com>
Date: 2000/04/14
Message-ID: <8d7fet$jo7$1@nnrp1.deja.com>#1/1

In article <8d5bs7$9mq$1_at_nnrp1.deja.com>,   giardine_at_my-deja.com wrote:
> 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
>

What is your version of Oracle?
What is your shared pool size? How much did you reserve? How much free memory is there in the pool? And how is it being used?

Here are queries to answer the last question in case you do not have them:

set echo off
rem
select to_number(p.value) "Total|Pool",

        s.bytes            "Free|Bytes",
        round(( s.bytes / p.value ) * 100,1)  "Free"
from    v$parameter p,
        v$sgastat   s
where   p.name      = 'shared_pool_size'
and     s.name      = 'free memory'

/

rem
rem filename: memory_sum.sql
rem
rem SQL*Plus script to display shared memory usage by areas. rem
rem 10/21/1996 s3537 m d powell added type rem
set pagesize 55
column namespace format a20
column type format a12
column sharable_mem format 999999
column locks format 999
rem
select namespace, type, sum(sharable_mem) "memory" from v$db_object_cache
group by namespace, type
/

The value of the dictionary cache hit ratio and library hit ratio are also of interest as low values would indicate you really need more pool and are not hitting a bug those that existed in the early days of 7.2 that resulted in the 04031 error. Large packages can result in this error due to shared pool fragmentation. Pinning large packages and Oracle pl/sql heavy use packages like dbms_application_info, dmbs_lock, dbms_alert, dmbs_sys_sql, etc... can help.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US