How to find the top Oracle cpu processesDetermining which Oracle statements are taking out the most CPU is a very common need. Till recently Oracle hadn't offered any practical tool for this purposes and this handy script can give the answer.The main script here available is called oramagSent.sh, because in the original form it is also published by the Oracle Online Magazine in its script collection; it helps identify the Oracle CPU consumption. The Oracle script oramagSent.sh accepts a username, password, a connection string and runs a simple script getPID.sql, which generates a list of the spid of the active oracle processes in a temporary file PID.lst. The pid of the top UNIX processes as shown by "top" is checked against this list; any match is passed to a script ./oracleMaxCPU.sql, which displays the statement being carried out in the database, that is responsible for the top CPU usage. The main script oramagSent.sh could be run in a loop, provided it connects to Oracle internally without asking for connection parameters:
while true
do
./oramagSent.sh
sleep 180
done
or something similar. It will provide a monitoring tool for the CPU usage caused by the various oracle processes. If more than one instance is running in the server, the script can be changed accordingly or more scripts can be run at the same time.Finding the top cpu Oracle processes if often the first step in the performance tuning exercise. Using this script you identify the UNIX Oracle top CPU processes; from the UNIX pid the Oracle spid is determined, and therefore the statement using much CPU. With explain plan or one of the many available tools (SQL Expert etc.) one can try improving the execution. |
|
| [Home] [Web Design] [HTML tutorials] [Javascript] [PSP] [About us] [Links] [Anonymous email] [Best hosting] [Daily Oracle Life] [IT jobs in Switzerland] [Web Submission] [Web traffic] | |