How to locate SQL statements that occupy too much cpu of an oracle Database

We can see that the cpu usage of oracle processes such as 6331,6517 is too high,

2) view related process information:

[oracle@oracle-one ~]$ ps -ef | grep 6331oracle    6331     1 23 20:24 ?        00:02:05 ora_vktm_RHYSoracle    6555  6385  0 20:32 pts/2    00:00:00 grep 6331[oracle@oracle-one ~]$ ps -ef | grep 6517oracle    6557  6385  0 20:33 pts/2    00:00:00 grep 6517[oracle@oracle-one ~]$ 

3) view the session information:

SQL> select sid,serial#,username,machine,osuser,process from v$session s  2     where s.paddr=(select addr from v$process p where p.spid='&pid');Enter value for pid: 6517old   2:    where s.paddr=(select addr from v$process p where p.spid='&pid')new   2:    where s.paddr=(select addr from v$process p where p.spid='6517')       SID    SERIAL# USERNAME                       MACHINE                                                          OSUSER                         PROCESS---------- ---------- ------------------------------ ---------------------------------------------------------------- ------------------------------ ------------------------         1         21 RHYS                           oracle-one                                                       oracle                         6513

The session information is sid: 1 serial #: 21. The database user is RHYS, the client is oracle-one, and the operating system user is oracle process number: 6513.

4) view the SQL statement that the session is running:

SQL> select sql_text from v$sqltext  2   where (address,hash_value) in (  select sql_address,sql_hash_value from v$session s   3    4        where s.paddr=  5       (select addr from v$process p where p.spid='&pid'));Enter value for pid: 6517old   5:      (select addr from v$process p where p.spid='&pid'))new   5:      (select addr from v$process p where p.spid='6517'))

We can see that the current user is deleting the table. This time, the user finds the process number through the spid of v $ process, and then finds the addr address of v $ session, then find the SQL _address and SQL _hash_value of v $ sqltext, and use these two fields to locate the unique SQL _text. This time, through v $ process, v $ session, combine the three views of v $ SQL _text to find the corresponding SQL statement;

5) if the session is invalid, run the following command to kill the session:

Alter sytem kill session '1, 21 ';

Now that the SQL statement is found, we can notify the application personnel to check whether relevant data operations are ongoing.

In addition, we can use the dbms_system package to perform more detailed tracking on this session.