Issue with oracle memory

Question

User779033375 posted

Hi,

We are working on  Oracle database.

We observed the oracle is taking maximun process 99% allocation and max memory.

Usage of memory increases by every one hour.

Can u  suggest how can i reset memory and have good performance.

Please suggest.

Answer 1

User269602965 posted

Oracle 10g/11g

set Oracle to auto manage memory

set Oracle SGA memory size to 50% of available physical memory

reassess situation

log into Oracle database control, has many tools to help define and manage memory,

and find resource hogs.

 

 

Answer 2

User779033375 posted

Thaks for suggestion, we have implemeted. will get back once its working.

Oracle 10g/11g

set Oracle to auto manage memory

set Oracle SGA memory size to 50% of available physical memory

reassess situation

log into Oracle database control, has many tools to help define and manage memory,

and find resource hogs.

 

 

Answer 3

User779033375 posted

Hi,

We have done with suggested changes, This doesnt solve our issue.

After investigating came to know the issue.

We have a form that call a sql query and load data, when we execute this query then oracle process go  high to 99% and doesnt come down.

Even if we close the form, still oracle is in high process, until we restart the oracle service.

Can you please suggest, how can we resove this without restarting service.

Oracle 10g/11g

set Oracle to auto manage memory

set Oracle SGA memory size to 50% of available physical memory

reassess situation

log into Oracle database control, has many tools to help define and manage memory,

and find resource hogs.

 

 

Answer 4

User269602965 posted

show code used create the SQL query and call the database.

and

you are using ODP.NET oracle.dataaccess.dll

instead of the deprecated (no longer supported) system.data.oracleclient ?

Do you get the same performance problem running the SQL statement on the application server from SQLPLUS command line?