How to query the CPU-consuming oracle Processes

How to deal with the high cpu usage of oracle, this article will introduce the high CPU usage of the oracle process, You Need To Know More friends can refer
1: first, use the TOP command to upload the SPID that occupies the CPU.
PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
3575 oracle 1 12 0 0 K 0 K run 748.6 H 24.98% oracle
3571 oracle 1 22 0 0 K 0 K sleep 706.2 H 22.84% oracle

2. log on to the database using the DBA account and use the following SQL statement for query:
SELECT * from v $ process where spid = 3575; query SQL-related information
3: Use the following SQL query based on the information found above:
SELECT sid, program from v $ session s where exists (SELECT 1 from v $ process where spid = 3575 and addr = S. PADDR );
You can find that the specific client query is constantly occupying ORACLE resources!
Finally, process this query!

4. Check SQLITPUB Based on SIDCopy codeThe Code is as follows: select SQL _textITPUB
From v $ sqltext
Where a. 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 locks:Copy codeThe Code is as follows: 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 statements 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:Copy codeThe Code is as follows: 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 PIDCopy codeThe Code is as follows: 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 number:Copy codeThe Code is as follows: 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:Copy codeThe Code is as follows: 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;