oracle database 8.1.7.2 with HP-UX 11

[Kaminiarz]

i have a problem with an oracle 8.1.7.2-database with HP-UX 11. the error is ORA-04030--> Out of PGA.
With oracle 8.1.6.3, their no problems.
Somebody a idea?
I have 6 GB Memory, 4 CPU's.

[steven Burgess_2]

Hi

I have just asked our dba, they advised that
the issue is with shared memory in the operating system

Basically, oracle is asking for more memory

Have you checked the usual, glance, top etc

[Kaminiarz]

yes , i looked with glance. I think, oracle had a problem with pl/sql-Tables and rewrite this quickly.

[Kaminiarz]

i think, this is not a SGA-problem, their a enouth (3,5 GB an shared_pool_size and db_block_buffers). the problem, so i think, is the fragmentation of the memeory.

[Andreas D. Skjervold]

Hi

Its possible that this has to do with Oracle parameter SORT_AREA_SIZE in combination with HPUX parameter maxdsiz.

Try reducing SORT_AREA_SIZE or increasing maxdsiz.

Andreas

[Kaminiarz]

maxdsiz=1073741824
maxdsiz_64bit=1073741824

sort_area_size=64k (default)

i changed this from 64k to 1M and return.
Always the same error!

[T G Manikandan]

Hello,
You have only 1MB of maxdsiz.Just push the parameter to some more-- high value.

That's great Andreas is back!Nice seeing you back Andreas!Andreas is a great dba!

Andreas,
what about using a
alter system flush shared pool;

to clear it.
/*
FLUSH SHARED_POOL Clause
The FLUSH SHARED POOL clause lets you clear all data from the shared pool in the system global area (SGA). The shared pool stores

Cached data dictionary information and

Shared SQL and PL/SQL areas for SQL statements, stored procedures, function, packages, and triggers.

This statement does not clear shared SQL and PL/SQL areas for items that are currently being executed. You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.
*/

Thanks

[Kaminiarz]

Thanks, but i think, this is a ORACLE-problem with the analyze command. use in the future only
Execute dbms_stats.gather_schema_stats('SCHEMAOWNER', CASCADE=>TRUE);
Change SCHEMAOWNER to the owner of your schema.
the CBO in ORACLE is an old problem in this company!

[SVTGanesh]

Hi

There may be problem with your pl/sql or sql since you have sufficient memory and CPU.
I've have encountered same issues, the problem is if you use any function with in the olsql/sql and that function happen to call in the loop frequently, this problem occuring you may need to try deleting all statistics also change the funtion to procedure or ....

[Dennis J Robinson]

How big is shared_pool alone, and also is this oracle version 32 or 64 bit.

This makes a big difference. It is possible to have the large SGA like you say, but the shared pool is too small.

It could be large enough but heavily fragmented, if it always will become heavily fragmented, making shared_pool larger will ensure that you have higher probability of being able to acquire the segment size you need.

Later.

hpguru@optimalss.com