Shared Pool Size (Hard to Explain)
Gerard H. Pille
John Landers (j...@csustan.edu) says...
!>
!>I am running a database that has one user doing inserts,deletes,updates
!>and selects. There are several triggers and procedures. Currently the
!>shared pool size is 9M and We get an ora-4031 saying to increase
!>our shared pool size. I can rationalize how one process can need
!>more that 9M. I think someithing else needs to be tuned. But haven't
!>stubled on it. Any ideas or places to search please email response.
!>If increaseing it is ok then an affirmative would be nice to here.
!>Oracle 7.2.24 for Os/2.
!>Thanks,
!>
!> John Landers
!> Computer Science Major
!> CSUS
!> j...@altair.csustan.edu
!>
I'm afraid you might have to enlarge it. Do the 9Mb include
db_block_buffers? The room you need depends on the size of Oracle's and
your procedures and SQL, not on the number of users (except if you were
using MTS, you wouldn't, would you, for one user?). So if this one user
is running very complex queries, fires long triggers, etc. etc., you may
run out of luck (and space). You can monitor the SGA and see what's in
it.
--
Kind reGards
\ / |
X |
/ \ s
Gerard
Jon Finke
>John Landers (j...@csustan.edu) says...
>!>
>!>I am running a database that has one user doing inserts,deletes,updates
>!>and selects. There are several triggers and procedures. Currently the
>!>shared pool size is 9M and We get an ora-4031 saying to increase
>!>our shared pool size. I can rationalize how one process can need
>!>more that 9M. I think someithing else needs to be tuned. But haven't
>!>stubled on it. Any ideas or places to search please email response.
>!>If increaseing it is ok then an affirmative would be nice to here.
>!>Oracle 7.2.24 for Os/2.
>!>Thanks,
>!>
>!> John Landers
>!> Computer Science Major
>!> CSUS
>!> j...@altair.csustan.edu
>!>
Crank up your Oracle support contract and call them. There was a bug
in the AIX version of Oracle (around 7.2.2.3) that resulted in this
exact error. Oracle was able to give us a work around in less than
48 hours, and patch that fixed the problem a few days after that.
We were finding applications were SOMETIMES getting that problem, and it
seemed to depend on what else was running at the time. Made tracking
down the problem a bit trickier at our end. We actually had applications
attempting a single insert fail, some of the time with this bug.
--
Jon Finke fin...@rpi.edu
Senior Network Systems Engineer http://www.rpi.edu/~finkej
Information Technology Services 518 276 8185 (voice) | 518 276 2809 (fax)
Rensselaer Polytechnic Institute 110 8th Street, Troy NY, 12180
Andrew Zitelli
>I am running a database that has one user doing inserts,deletes,updates
>our shared pool size. I can rationalize how one process can need
>stumbled on it. Any ideas or places to search please email response.
>If increasing shared pool is ok then an affirmative would be nice to
>hear. Oracle 7.2.2.4 for OS/2.
There is a known bug in 7.2.2.3 & 7.2.2.4 causing ORA-04031 errors along
with other symptoms. Increasing your shared pool and other standard
remedies for 4031 errors will only delay the onset of the errors.
Patches are available from Oracle, at least for some operating systems.
The errors are caused by multiple bugs in Oracle's coalescing of free
space. Some bugs are fixed in 7.2.3, the rest in 7.3. I patched
7.2.3 under HP/UX and the errors ceased without increasing shared pool.
Sanjay D.S.
startup sqldba
run following command at sqldba prompt:
1. alter system flush shared_pool;
(this command will flush all sql/pl-sql statements cached in shared_pool
of sga)
2. start your application.
3. at sqldba start monitoring using following:
4. set serveroutput on;
5. execute dbms_shared_pool.sizes(ip_size);
(Above command with display all objects cached in shared_pool with
size greater than or equal to ip_size(kilobytes).
6. If at any point a large object(cursor,sql_statement,procedure) has
to be cached or brought-in and if ORACLE doesnot find contiguous memory
blocks even after aging out other small objects from shared_pood, chances
are you'll get ORA-04031 message.
(Also check, if you application is "keeping" a particular database object
locked in shared_pool. This is possible using dbms_shared_pool.keep
procedure, if so, you may want to reconsider sizing your
shared_pool_size in sga).
more information about dbms_shared_pool package can be found in
/oracle/rdbms directory.
Hope it is helpful.
(If get real sound solution to the problem, let us know)
-- sanjay d.s.
Sameer Utrankar
>In article <Pine.SUN.3.91.960311...@reddog.csustan.edu>,
>John Landers (j...@csustan.edu) says...
>!>
>!>I am running a database that has one user doing inserts,deletes,updates
>!>and selects. There are several triggers and procedures. Currently the
>!>shared pool size is 9M and We get an ora-4031 saying to increase
>!>our shared pool size. I can rationalize how one process can need
>!>more that 9M. I think someithing else needs to be tuned. But haven't
>!>stubled on it. Any ideas or places to search please email response.
>!>If increaseing it is ok then an affirmative would be nice to here.
>!>Oracle 7.2.24 for Os/2.
>!>Thanks,
>!>
>!> John Landers
>!> Computer Science Major
>!> CSUS
>!> j...@altair.csustan.edu
>!>
>I'm afraid you might have to enlarge it. Do the 9Mb include
>db_block_buffers? The room you need depends on the size of Oracle's and
>your procedures and SQL, not on the number of users (except if you were
>using MTS, you wouldn't, would you, for one user?). So if this one user
>is running very complex queries, fires long triggers, etc. etc., you may
>run out of luck (and space). You can monitor the SGA and see what's in
>it.
>--
>Kind reGards
> \ / |
> X |
> / \ s
> Gerard
IOUW 1995 has a good paper on managing shared pool. Take a look.
----------
utra...@nyc.pipline.com (Sameer Utrankar)
All of us can't be heros. Someone has to sit by the curb and clap as they go by.
Craig Harper
Does anyone know where can I get this paper?
Thanks...
Craig Harper
IFTN
415.583.4528
Dale
CD ROM, or is it a hard-copy? Please e-mail me at
dale.nstn.ca - thanks babette dale underwood
In article <4ilggi$p...@news.nyc.pipeline.com>, utra...@nyc.pipeline.com
says...
Larry
pl/sql block that could not be reduced in size. We finally put
it in a package and pinned it in memory so that forever more it
owns what it needs. We've not experienced the 4031 error since,
but we have plenty of other problems. Larry