Re: shared memory problem (ORA-04031)

In article <8ivbsb$86a$1_at_wrath.news.nacamar.de>,   "Walter Dorninger" <walter.dorninger_at_aon.at> wrote:
> Hi,
>
> I'm currently running a Oracle 8.1.5 on Solaris 2.6 ( E450 2GB ram)
 and if i
> run some certain statements I get the following error (not very large
> statements):
>
> ORA-04031: unable to allocate 131072 bytes of shared memory ("shared
 pool",
> "unknown object", "cursor work he", "QERHJ Hash Table")
>
> I have set the initora parameters listed below to the following
 values:
> ...
> shared_pool_size = 20000000
> sort_area_size = 3000000
> sort_area_retained_size = 500000
> ...
>
> Another issue is that the sort operations are extremly slow ...
 (perhaps
> this has to do something with this...)
>
> Is it possible that the error is related to the settings in
 my /etc/system
> file ? - The /etc/system file is listed below:
> set shmsys:shminfo_shmmax=4294967295
> set shmsys:shminfo_shmmin=1
> set shmsys:shminfo_shmmni=100
> set shmsys:shminfo_shmseg=10
> set semsys:seminfo_semmni=100
> set semsys:seminfo_semmsl=100
> set semsys:seminfo_semmns=200
> set semsys:seminfo_semopm=100
> set semsys:seminfo_semvmx=32767
>
> Thanks a lot
> Walter
>
>

To begin a shared pool of 20M is not very large. You probably just need a bigger pool size.

If your system is an OLTP then you should shrink your sort_area_size since you are wasting memory that could be better used elsewhere. Believe it or not a sort_area_size of 128K is ample for most OLTP's. You can use the alter session command to dynamically allocate a larger sort area size for batch jobs.

If you are using MTS then the sort area is part of the UGA which comes out of your shared pool, and you almost surely need a bigger pool in this case.

If your sorts are slow then you either have sorts larger than your sort area size taking place and you need to look at your temporary tablespace parameters. Make the initial = next with pctincrease of zero and make the initial 1 to 5M. You probably want the temporaray tablespace defined as temporary rather than permanent, and it should probably be stripped. Make sure all users other than sys are assigned to sort using the temporary tablespace.

Otherwise if the majority of your sorts should involve less than 3M and they are slow you probably have excessive paging and swapping at the OS level. Reducing your sort_area_size will free memory allowing the OS to assign it as necessary.

I hope this helps.

--
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.