The following is a simulation process in Linux
1. Simulate high CPU usage in a session, as shown below:
Declare
Num int: = 0;
Begin
Loop
Num: = num + 1;
End loop;
End;
/
2. Run the top command in the shell window to view CPU usage:
Pid user pri ni size rss share stat % CPU % MEM TIME CPU COMMAND
7913 Oracle 25 0 24364 23 M 22144 R 91.7 0: 44 0 oracle
3. view the Session information in the view provided by Oracle:
SQL> select addr, spid from v $ process where spid = '000000 ';
ADDR SPID
--------------------
2AA1A6EC 7913
SQL> select sid, SQL _id from v $ session where paddr = '2aa1a6ec ';
SID SQL _ID
-----------------------
157 948mkp962vqgy
SQL> select SQL _text from v $ SQL where SQL _id = '948mkp962vqgy ';
SQL _TEXT
--------------------------------------------------------------------------------
Declare num int: = 0; begin loop num: = num + 1; end loop; end;
4. From the above analysis, we can know that the executed SQL has an endless loop.
Kill corresponding service process kill-9 7913
[When encountering high CPU usage, you can analyze the SQL statements executed by the corresponding session according to the above steps for unblocking processing]