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 the maximum to get high throughput, however, at the same time we need to make sure that a single process doesn’t eat 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 the number of processes waiting for the run time shown by vmstat command. It could be a point of concern if this starts happening frequently and stays for a long duration. In this post, we would discuss how to identify and fix the cause of high CPU usage.
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 the Operating system level might be consuming high CPU. You can use ‘top’ command to find the same.
Here is step by step guide:
– 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 the top command would be sorted by CPU usage. You can get the Process ID of the top CPU-consuming processes. The username 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.
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 a 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 keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a 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 enjoy more advanced topics from our partner resource.
