Mat Smith
asked on
ORA-01034: ORACLE not available ORA-27102: out of memory Linux-x86_64 Error: 12: Cannot allocate memory
Hi,
I have a java app, which acts as an oracle client/bridge between a mobile app ( data visualisation) and oracle database. The java app is capable to multithread if more than one request is made at a time.
No issues here, all works fine. The aim was to refrain from plugin mobile devices to the db.
Apart from the mobile app side, it's possible to run queries against the db on a scheduler i.e. every 5 mins and keep results in a buffer.
Anyway, In a production environment, my app was connected to a client database for a couple of weeks and suddenly the below error stopped the oracle form working causing a massive issue to the customer of mine.
ORA-01034: ORACLE not available ORA-27102: out of memory Linux-x86_64 Error: 12: Cannot allocate memory
Obviously, suspicion is that my product caused the Oracle problem. Apparently, oracle ran out of "accessible memory" ( whatever that means - it wasn't techy guy who talked to me)
...so my question to you is if running queries against DB can cause any memory issues?
As far as I know, a bad sql ( there wasn't any in this case) can cause long-running queries/sessions, which can consume a lot of CPU and indeed can slow oracle down...
what's your view on this, please ?
I have a java app, which acts as an oracle client/bridge between a mobile app ( data visualisation) and oracle database. The java app is capable to multithread if more than one request is made at a time.
No issues here, all works fine. The aim was to refrain from plugin mobile devices to the db.
Apart from the mobile app side, it's possible to run queries against the db on a scheduler i.e. every 5 mins and keep results in a buffer.
Anyway, In a production environment, my app was connected to a client database for a couple of weeks and suddenly the below error stopped the oracle form working causing a massive issue to the customer of mine.
ORA-01034: ORACLE not available ORA-27102: out of memory Linux-x86_64 Error: 12: Cannot allocate memory
Obviously, suspicion is that my product caused the Oracle problem. Apparently, oracle ran out of "accessible memory" ( whatever that means - it wasn't techy guy who talked to me)
...so my question to you is if running queries against DB can cause any memory issues?
As far as I know, a bad sql ( there wasn't any in this case) can cause long-running queries/sessions, which can consume a lot of CPU and indeed can slow oracle down...
what's your view on this, please ?
ASKER
Thanks slightwn.
I must say I was counting on you ;)
The db isn't mine, so I can't check the SGA etc.
However just had look at v$session - are inactive sessions being counted here /can consume memory?
I must say I was counting on you ;)
The db isn't mine, so I can't check the SGA etc.
However just had look at v$session - are inactive sessions being counted here /can consume memory?
Inactive sessions are sessions that are connected and just not currently running a query. They are consuming resources.
I have seen this in the past being the multithreaded application not freeing connections and just taking over everything. Make sure your application is freeing the resources it doesn't need and not relying on any system garbage collection.
I have seen this in the past being the multithreaded application not freeing connections and just taking over everything. Make sure your application is freeing the resources it doesn't need and not relying on any system garbage collection.
ASKER
Thanks Johnsone,
It should always open connection, run a query , pass data over and then close the connection. Is my understanding correct ?
It should always open connection, run a query , pass data over and then close the connection. Is my understanding correct ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Java application have generic defect - often the "garbage collector' doesn't work and eats the RAM.
You have to measure the RAM occupied by Java applications and try to reduce it.
You have to measure the RAM occupied by Java applications and try to reduce it.
ASKER
Hi Schertner,
I forgot to say that, my java apps runs on a completely different server than Oracle DB. I don't think the GC can have an impact on the oracle performance, can it ?
I forgot to say that, my java apps runs on a completely different server than Oracle DB. I don't think the GC can have an impact on the oracle performance, can it ?
ASKER
Johnsone,
I'll run the sessions checks you suggested. The oracle version (all the numbers) are Oracle Database 11g Release 11.2.0.4.0
I'll run the sessions checks you suggested. The oracle version (all the numbers) are Oracle Database 11g Release 11.2.0.4.0
>> pass data over and then close the connection. Is my understanding correct ?
To add that at times just closing the connection may not be enough. I'm not sure what framework you are using, if any. You need to ensure that the close also disposes of the object properly. My background is more in .Net than Java. In .Net you could explicitly dispose of the object. There were times that just a close didn't alert the database the connection was dead so the database kept it open.
Anyway, my bet is the Linux Kernel not being properly set is what is causing the error but I lose bets from time to time...
To add that at times just closing the connection may not be enough. I'm not sure what framework you are using, if any. You need to ensure that the close also disposes of the object properly. My background is more in .Net than Java. In .Net you could explicitly dispose of the object. There were times that just a close didn't alert the database the connection was dead so the database kept it open.
Anyway, my bet is the Linux Kernel not being properly set is what is causing the error but I lose bets from time to time...
If so, please investigate the swap space assigned to your Linux installation.
Use vmstat command also
free -m
cat /proc/meminfo
If you suspect dead sessions then use the service of the Listener that detects and kills dead sessions:
In sqlnet.ora add these lines:
SQLNET.EXPIRE_TIME=10
USE_NS_PROBES_FOR_DCD=YES
Restart the listener:
lsnrctl stop
lsnrctl start
Use vmstat command also
free -m
cat /proc/meminfo
If you suspect dead sessions then use the service of the Listener that detects and kills dead sessions:
In sqlnet.ora add these lines:
SQLNET.EXPIRE_TIME=10
USE_NS_PROBES_FOR_DCD=YES
Restart the listener:
lsnrctl stop
lsnrctl start
I'm curious what you found causing the issue.
ASKER
Hi Slightwv,
The problem was indeed with the java app. In a specific case, it wasn't closing a connection. Adding connection.close(); solved the issue.
The oracle problem was caused by too many open connections - the limit of 500 was reached, thus ORA-27102: out of memory popped out.
The problem was indeed with the java app. In a specific case, it wasn't closing a connection. Adding connection.close(); solved the issue.
The oracle problem was caused by too many open connections - the limit of 500 was reached, thus ORA-27102: out of memory popped out.
Also make sure the app is properly closing/disposing of the connections. I would monitor v$session to make sure sessions are ended when the app is done with them.
I would look at the memory configuration of the kernel itself. There is a pretty good discussion about that here:
https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:454420165038