Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

oracle database 8.1.7.2 with HP-UX 11

Kaminiarz
Occasional Advisor

oracle database 8.1.7.2 with HP-UX 11

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.
11 REPLIES
steven Burgess_2
Honored Contributor

Re: oracle database 8.1.7.2 with HP-UX 11

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
take your time and think things through
Kaminiarz
Occasional Advisor

Re: oracle database 8.1.7.2 with HP-UX 11

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

Re: oracle database 8.1.7.2 with HP-UX 11

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
Honored Contributor

Re: oracle database 8.1.7.2 with HP-UX 11

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
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Kaminiarz
Occasional Advisor

Re: oracle database 8.1.7.2 with HP-UX 11

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
Honored Contributor

Re: oracle database 8.1.7.2 with HP-UX 11

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
Occasional Advisor

Re: oracle database 8.1.7.2 with HP-UX 11

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
Occasional Advisor

Re: oracle database 8.1.7.2 with HP-UX 11

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 ....
SVTGanesh
Dennis J Robinson
Frequent Advisor

Re: oracle database 8.1.7.2 with HP-UX 11

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
You know the drill
Wodisch
Honored Contributor

Re: oracle database 8.1.7.2 with HP-UX 11

Hello,

if you do not find any patches from Oracle or so, you could try to increase
- sort_area_size and
- sort_area_retained_size to something like 4MB, and 1MB, resp.,
- shared_pool_size to some 300MB or more,

HTH,
Wodisch
Kaminiarz
Occasional Advisor

Re: oracle database 8.1.7.2 with HP-UX 11

my SGA is 2,2 GB large.
1024 MB shared_pool_size,
50 MB shared_pool_reserved_size,
20 MB sort_area_size, i think, this is anouth!