Another quick utility for identifying resource-intensive processes is the top command.
Use this tool to quickly identify which processes are the highest consumers of resources on the server.
By default, top will repeatedly refresh every three seconds with information regarding the most CPU-intensive processes.
$ top
And you can run it batch mode and send the output to a file for later analysis:
$ top -b > tophat.out
Be careful to stop the batch job so a very large output file is not created causing another issue.
The top-consuming sessions are listed in the first column. This doesn’t necessarily show a bottleneck, but you can see the processes that are using the resources on the server.
Mapping an Operating System Process to a SQL Statement
When identifying OS processes, it is useful to view which processes are consuming the greatest amount of CPU. If the resource hog is a database process, it is also useful to map the OS process to a database job or query.
To determine the ID of the processes consuming the most CPU resources, use a command such as ps, like so:
$ ps -ef -o pcpu,pid,user,tty,args | sort -n -k 1 -r | head
Here is a snippet of the output:
14.6 24875 oracle ? 0.8 21613 oracle ? 0.1 21679 oracle ?
oracledb23c (DESCRIPTION=(LOCAL=YES)(ADDRESS=… ora_vktm_db23c ora_mmon_db23c
You can see that session 24875 is the top consumer of CPU resources and associated with the db23c database.
With that information, log in to the database, and use the SQL statement to determine what type of program is associated with OS process 24875:
SQL> select
Once you have identified a top-consuming process associated with a database, you can query the data dictionary views, based on the SPID, to identify what the database process is executing.
Leave a Reply