Home / server / Questions / 82347
Accepted
BZ.
Asked: 2009-11-07 12:16:00 +0800 CST

How to determine SQL causing Oracle CPU Spikes

  • 772

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.

oracle

3 Answers

  1. 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_01/server.102/b14211/sqltrace.htm#sthref2019

    • 2
  2. Best Answer

    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=

    • 2

  3. 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

    • 1