Thursday, January 26, 2012

Find Current Running SQL Statements In Oracle

List Curent Running Queries From Oracle Database Serv er

 
Here was a query I used to check the queries which were submitted by the module “ASBAgent.exe” (the datastage ASB Agent, if you use DataStage). You can remove the module clause and the username clause to see all running queries .

 

 
SELECT first_load_time,
  module,
  sql_text,
  username,
  disk_reads_per_exec,
  buffer_gets,
  rows_processed,
  hit_ratio
  FROM
(SELECT module,
  sql_text ,
  u.username ,
  round((s.disk_reads/decode(s.executions,0,1, s.executions)),2) disk_reads_per_exec,
  s.buffer_gets ,
   100 - round(100 * s.disk_reads/greatest(s.buffer_gets,1),2) hit_ratio,
  s.first_load_time
FROM sys.v_$sql s, sys.all_users u
WHERE s.parsing_user_id=u.user_id
  and UPPER(u.username) ='SCOTT'
  and module = 'ASBAgent.exe'
) s
order by first_load_time desc;

 

Related Notes:

  •   

No comments: