Here is a simple sql query which will show which query is misbehaving and consuming the most CPU usage SELECT ss.username, ss.OSUSER, ss.TERMINAL, se.SID, VALUE / 100 cpu, sq.SQL_TEXT FROM v$session ss, v$sesstat se, v$statname sn, v$sql sq WHERE se.STATISTIC# = sn.STATISTIC# AND NAME LIKE '%CPU used by this session%' AND se.SID = ss.SID AND ss.STATUS = 'ACTIVE' AND ss.username IS NOT NULL AND ss.SQL_ID = sq.SQL_ID AND VALUE / 100 >= 1 ORDER BY VALUE DESC; And when you see the top CPU using process, monitor its activity through this query: SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME, SID, MACHINE, REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT, ltrim(to_char(FLOOR(SES.LAST_CALL_ET/3600), '09')) || ':' || ltrim(to_char(FLOOR(MOD(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
Just when I was thinking how boring my job as DBA was (doing the same routine work and nothing new to implement until you convince everyone and take approvals from them!!) I was made to feel that DBA work is not at all boring if you are at the receiving end 🙂 .
Checking the server utilization is one of the important task of an administrator, and a good administrator is one who knows how to automate his day to day task.
The Read performance of a Disk can be improved by increasing a parameter called “Read+Ahead” using ‘blockdev’ command.
This is for oracle database 10g, in 11g the table is prebuilt with another name SELECT METRIC_LABEL, T.COLUMN_LABEL, T.ALERT_STATE, T.MESSAGE, T.COLLECTION_TIMESTAMP FROM SYSMAN.MGMT$ALERT_CURRENT T