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

1. Use the top command to see the SPID numbers with the most productive resources first

2. Query the current time-consuming session ID, user name, Sqlid, etc.: 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 (' 5648612 ', ' 256523 '));

3. If the previous step sql_id or Hash_value is not empty, V$sqlarea can be used to isolate the SQL select Sql_text from V$sqltext_with_newlines where Hash_value is currently in use = &hash_value order by piece;
SELECT * from V$sql where sql_id= '

---the----provided by CSDN netizens

SELECTSql_textFromV$sqltext a WHERE(A.hash_value, a.address)In (SELECTDECODE (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