Re: Ora 4031

From: Paul Drake <bdbafh_at_gmail.com>
Date: Mon, 2 May 2005 19:26:24 -0400
Message-ID: <910046b405050216263390f7fd@mail.gmail.com>

On 5/2/05, Burton, Laura <BurtonL_at_frmaint.com> wrote:
> I have just created a database for 9.2.0.1 on Windows 2003. This was an
> 8.1.6 database that I imported the database from. Today we started
> getting these errors on only one application, which does perform a lot
> of pl/sql. We did not get those errors on the 8.1.6 database version. =
=3D20

>=20

> I have applied the latest patch (9.2.0.6) and have modified different
> pool settings, but we are still getting the error.

>=20

> Does anyone know anything about this type error. Metalink has some
> people doing the same thing, and it works for one, but not the other. I
> am going to try pinning the one procedure to see if this will help.

>=20

> Any suggestions or insight would be greatly appreciated.

>=20

> Thank you,
> Laura

Hi Laura.

Did you happen to re-run catpatch after creating the database using dbca? Did you run catpatch after applying the 9.2.0.6 patchset? Check v$sgastat for free memory in the shared pool. What you might have is fragmentation of the shared pool: There may be bytes available, but no contiguous chunks big enough. Have you tried increasing the value of the shared_pool_size (what was it, is it now)?
Do you have larger objects pinned, using the package dbms_shared_pool? After flushing the shared pool,
(SQL> alter system flush shared_pool;)
 how long was it before the ORA-4031 errors resumed? What is the value for the parameter shared_pool_reserved_size? What is the value for the parameter cursor_sharing? How many CPUs are in the box?

There are several notes in Metalink regarding this class of errors. As 9.2 has multiple child latches for the shared pool, you might be in more trouble than in 8.1.6 as the chunks are divided over multiple lists/chains.

Memory usage always increases as one upgrades. This was true for 8.1.7 to 9.2, and especially true for 9.2 to 10.1.

hth.

Paul

--=20
#/etc/init.d/init.cssd stop
# f=3Dma, divide by 1, convert to moles.

--
http://www.freelists.org/webpage/oracle-l

Received on Mon May 02 2005 - 19:30:45 CDT