Simulate and diagnose high CPU usage of Oracle in Linux

Source: Internet
Author: User
Tags high cpu usage

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]

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.