Wednesday, March 21, 2012

Current Running Queries Oracle

Several different ways to get this data. The first one is a query to get the current running quries.


select sesion.sid,
sess.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
from v$sqlarea sqlarea, v$session sess
where sess.sql_hash_value = sqlarea.hash_value
and sess.sql_address = sqlarea.address
and sess.username is not null

The next one will display the SQL of the PIDs using the Most CPU. You get the PID number by running the top command


select proc.spid, sess.username, s.sql_text
from v$process proc, v$session sess, v$sqlarea s
where proc.addr = sess.paddr
and sess.sql_hash_value = s.hash_value
and proc.spid in (22725,553)

0 comments:

Post a Comment