Query the SQL statements of oracle processes with high CPU usage

Query the SQL information of oracle processes that occupy the CPU 1: first, use the TOP command to pass the SPID number pid username thr pri nice size res state time cpu COMMAND3575 oracle 1 12 0 0 K 0 K run 748.6 H 24.98% oracle3571 oracle 1 22 0 0 0 K 0 K sleep 706.2 H 22.84% oracle www.2cto.com 2: log on to the database with a DBA account and use the following SQL statement to query: SELECT * from v $ process where spid = 3575; query SQL-related information 3: use the following SQL query based on the query information: SELECT sid, program FROM V $ SESSION S WHERE EXISTS (SELECT 1 FROM V $ PROCESS WHERE s Pid = 3575 and addr = S. PADDR); you can query the specific client that is constantly occupying ORACLE resources! Finally, process this query! 4. check SQLITPUBselect SQL _textITPUB from v $ sqltext where a according to the SID. hashvalue = (select SQL _hash_value from v $ session B where B. SID = '& sid') 0 order by piece ASC; Based on the lockwait field, you can query information about the currently waiting lock: select * from v $ lock where kaddr in (select lockwait from v $ session where sid = $ sid); (SQL _address, SQL _hash_value), (prev_ SQL _addr, prev_hash_value) based on the two sets of fields, you can query the details of the SQL statement being executed or last executed by the current session: select * from v $ sqltext where address = & SQL _address and hash_value = & SQL _hash_value; query SQL information by PID: select id, serial #, username, osuser, machine, program, process, to_char (logon_time, 'yyyy/mm/dd hh24: mi: ss ') logon from v $ session where paddr in (select addr from v $ process where spid in ('& pid'); query SQL statements by PID SELECT. username,. machine,. program,. sid,. serial #,. status, c. piece, c. SQL _text FROM v $ session a, v $ process B, v $ sqltext c WHERE B. spid = '& spid' AND B. addr =. paddr AND. SQL _address = c. address (+) order by c. piece; obtain the process sid: select id, serial #, username, osuser, machine, program, process, to_char (logon_time, 'yyyy/mm/dd hh24: mi: ss ') logon from v $ session where paddr in (select addr from v $ process where spid in (' & pid '); obtain the sqltext statement of the session: select SQL _text from v $ sqltext_with_newlines where hash_value in (select SQL _HASH_VALUE from v $ session where paddr in (select addr from v $ process where spid = '& pid') order by piece;