Saturday, October 31, 2020
dbametrix
More
    Home Oracle DBA Tips How to find out High CPU culprit

    How to find out High CPU culprit

    Article shows how to find high CPU usage in database server and resolve this issue.

    High CPU usage-spike-load on Database Server

    One of the issues that could make the DBA life uncomfortable is high CPU usage. Occasionally, you might see High CPU usage or CPU spikes on the Database Server. Ideally, the Operating systems are designed to use CPU to maximum to get high throughput, however at the same time we need to make sure that a single process doesn’t eats up the whole CPU, causing the other processes to starve.

    ‘Load average’ field in the top command would tell you the average number of processes ready to run in the last 1, 5 and 15 minutes, which could be viewed in combination with number of processes waiting for run time shown by vmstat command. It could be a point of concern if this starts happening frequently and stays for long duration. In this post, we would discuss how to identify and fix the cause of high CPU usage.

    - Advertisement -
    dbametrix

    The first thing is to find out the process that is causing high CPU usage. It’s not necessary that it would be a DB process. There is a possibility that some other process at Operating system level might be consuming high CPU. You can use ‘top’ command to find the same.

    – Run the top command at OS prompt
    – Sort the output by CPU usage (Shift + p in linux)
    – To see individual CPU usage, press 1

    Now the output of top command would be sorted by CPU usage. You can get the Process ID of the top CPU consuming processes. The user name would tell you whether it’s a Database process or some other.

    You can easily get the details about that session e.g. username, program, module, sql etc by providing the OS Process ID to the following Script.

    - Advertisement -
    dbametrix

    set head on
    set lin 700
    col module format a20

    SELECT s.sid, p.spid "OS Pid", s.module, s.process, s.schemaname "Schema", s.username "Username",
    s.osuser "OS User", s.program "Program", a.sql_id, substr(a.sql_text,1,550) "SQL Text"
    FROM v$session s, v$sqlarea a, v$process p
    WHERE s.sql_hash_value = a.hash_value (+)
    AND s.sql_address = a.address (+)
    AND s.paddr = p.addr
    and s.sid = (select s.sid from v$session s, v$process p where s.paddr = p.addr and p.spid = &p);

    You have now identified the SQL which is consuming high CPU and the corresponding program or module running it. To fix the issue, you need to tune the SQL.

    When you want to make strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals , Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert. This expert DBA Team club blog always provides you latest technology news and database news to be keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. These all Oracle DBA tips are available in single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would be enjoy more advance topics from our partner resource.

     

    - Advertisement -
    dbametrix
    - Advertisment -
    dbametrix

    Most Popular

    Identifying important information in Big Data

    The way in which Big Data technologies have evolved in the real word enterprise goes on to show that even technologists and scientists who might have disparaged the word (Big Data) previously

    Five Reasons Why Database Outsourcing

    The blog post explains why database outsourcing and remote dba support becomes most popular during present time of Covid-19 Pandemic.

    Hardware of Database Server

    The blog post explains how to choose best hardware of your database server for getting high performance and security.

    oracle 19c new features – Automatic Indexing

    A major Oracle 19c autonomous database new Feature automatic indexing using DBMS_AUTO_INDEX some details and how to article.

    Recent Comments