Oracle Real-Time query of SQL statements that consume the most CPU resources

 

1. First, use the top command to view the spid numbers with more resources

 

2. query the current time-consuming session ID, user name, sqlid, and so on:
Select Sid, serial #, machine, username, program, SQL _hash_value, SQL _id,
To_char (logon_time, 'yyyy/MM/DD hh24: MI: ss') as login_time from V $ session
Where paddr in (select ADDR from V $ process where spid in ('20160301', '20160301 '));

3. If the SQL _id or hash_value in the previous step is not empty, use v $ sqlarea to find the SQL statement in use.
Select SQL _text
From v $ sqltext_with_newlines
Where hash_value = & hash_value
Order by piece;

Select * from V $ SQL where SQL _id =''

 

 

 

--- Provided by csdn users ----

 

Select SQL _text
From v $ sqltext
Where (A. hash_value, A. Address) in
(Select decode (SQL _hash_value, 0, prev_hash_value, SQL _hash_value ),
Decode (SQL _hash_value, 0, prev_ SQL _addr, SQL _address)
From v $ session B
Where B. paddr = (select ADDR from V $ Process C where c. spid = '& pid '))
Order by piece ASC

 

 

 

------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
Online Resources: http://tianlesoftware.download.csdn.net
Video: http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Dba1 group: 62697716 (full); dba2 group: 62697977