Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have a J2EE Spring/Hibernate webapp using Oracle 11g for persistence, occasionally in production the Oracle CPU spikes to 100% usage, restarting Tomcat drops the Oracle CPU. I am unable to replicate this in test, even using an export from production. Is there anyway to have Oracle show me the SQL statement that's causing the spike? Or is there another approach I can take that would help me figure out where the problem is?

Additional information: Webapp and Oracle on different boxes (both windows). Using JDBC over SSL

Thanks.

share|improve this question

3 Answers 3

up vote 2 down vote accepted

When production reaches 100% CPU, you (or your DBA) can run "top" command, grab the process id that contributes most to the CPU (assuming you have just a few of these) and then use the following query to catch the session and what's it doing at the time:

select p.SPID UnixProcess ,s.SID,s.serial#,s.USERNAME,s.COMMAND,s.MACHINE,s.SQL_ADDRESS,s.SQL_HASH_VALUE ,s.program, status, cpu_time,fetches,disk_reads,buffer_gets,rows_processed,executions,child_latch,event, sql_text,COMMAND_TYPE from gv$session s left outer join gv$process p on p.ADDR = s.PADDR and s.inst_id=p.inst_id left outer join gv$sqlarea sa on sa.ADDRESS = s.SQL_ADDRESS and s.inst_id=sa.inst_id where p.spid=

share|improve this answer

You can enable performance tracing on the database and monitor the execution of your queries with some built in utilities.

I personally haven't done this on Oracle, but they do have online documentation to point you in the right direction.

http://download.oracle.com/docs/cd/B19306%5F01/server.102/b14211/sqltrace.htm#sthref2019

share|improve this answer
    
I'll try this. Thanks –  BZ. Nov 6 '09 at 20:58

You should really check that on your servlets lifecycle, you are closing correctly your DB connections and that the objects you use on your servlets, are being correctly garbage collected. sometimes there can be some very tricky memory leaks on servlets, even more if you are using some kind of DI framework.

One I had a memory leak on a servlet that was being garbage collected, but when doing some RMI to another app, the proxy objects were not being collected, so that was eating up all my free memory.

Check out this article on tips for doing performance testing and memory checking on servlets.

http://www.ibm.com/developerworks/rational/library/2820.html

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.