In our test server database, we are facing performance issue. We have detected that database locks are impacting our work. Though locks are released, they are held on regular basis on same tables.
Our test environment includes 2 databases. One database is allocated 2 gb of SGA and other database is allocated 5 gb of RAM. Our physical RAM is 16 GB. Previously we configured our sga with AMM and disabled it. Now manual SGA configuration is in effect with sga_max_size=8g, sga_target=7G. Also in order to make AMM function in database, we have modified kernel parameters to oracle recommended values and increased /dev/shm to 25gb.
From last 1 week we are facing out of memory issues from java application connecting to database. We have increased sga. but no use. Other error is "high cpu load is observerd in test server. When there are no users logged on at all, during off peak hours also, i can see that load is getting increased to 2 to 5 and even up to 15.
I tried all possible solutions. But not able to fix issue. None of the queries are consuming high cpu. I can see that jboss and java applications are consuming most of the memory. For some time if we stop java application completely, load is coming down to zero. but when the java application starts real problem is starting. users are not to able to login to database. database is hanging. This is ever ending process.
Please assist. Request all experts to provide all possible solutions.