Performance Tuning and Monitoring : CPU Scripts

Find CPU and memory usage of oracle DB server:

Below script is useful in getting CPU, memory and core, socket information of a database server
from SQL prompt.

set pagesize 299
set lines 299
select STAT_NAME,to_char(VALUE) as VALUE ,COMMENTS from v$osstat where
stat_name IN ('NUM_CPUS','NUM_CPU_CORES','NUM_CPU_SOCKETS')
union
select STAT_NAME,VALUE/1024/1024/1024 || ' GB' ,COMMENTS from v$osstat where
stat_name IN ('PHYSICAL_MEMORY_BYTES');

Find sessions that are consuming lot of CPU:

col program form a30 heading "Program"

col CPUMins form 99990 heading "CPU in Mins"

select rownum as rank, a.*

from (

SELECT v.sid, program, v.value / (100 * 60) CPUMins

FROM v$statname s , v$sesstat v, v$session sess

WHERE s.name = 'CPU used by this session'

and sess.sid = v.sid

and v.statistic#=s.statistic#

and v.value>0

ORDER BY v.value DESC) a

where rownum < 11;

Find CPU usage and wait event information in oracle database:

Below script will give information about the CPU usage and wait events class information of

every minute for last 2 hours. As this query uses gv$active_session_history, so make sure you

have TUNING license pack of oracle, before using this.

set lines 288

col sample_time for a14

col CONFIGURATION head "CONFIG" for 99.99

col ADMINISTRATIVE head "ADMIN" for 99.99

col OTHER for 99.99

SELECT TO_CHAR(SAMPLE_TIME, 'HH24:MI ') AS SAMPLE_TIME,

ROUND(OTHER / 60, 3) AS OTHER,

ROUND(CLUST / 60, 3) AS CLUST,

ROUND(QUEUEING / 60, 3) AS QUEUEING,

ROUND(NETWORK / 60, 3) AS NETWORK,

ROUND(ADMINISTRATIVE / 60, 3) AS ADMINISTRATIVE,

ROUND(CONFIGURATION / 60, 3) AS CONFIGURATION,

ROUND(COMMIT / 60, 3) AS COMMIT,

ROUND(APPLICATION / 60, 3) AS APPLICATION,

ROUND(CONCURRENCY / 60, 3) AS CONCURRENCY,

ROUND(SIO / 60, 3) AS SYSTEM_IO,

ROUND(UIO / 60, 3) AS USER_IO,

ROUND(SCHEDULER / 60, 3) AS SCHEDULER,

ROUND(CPU / 60, 3) AS CPU,

ROUND(BCPU / 60, 3) AS BACKGROUND_CPU

FROM (SELECT TRUNC(SAMPLE_TIME, 'MI') AS SAMPLE_TIME,

DECODE(SESSION_STATE,

'ON CPU',

DECODE(SESSION_TYPE, 'BACKGROUND', 'BCPU', 'ON CPU'),

WAIT_CLASS) AS WAIT_CLASS

FROM V$ACTIVE_SESSION_HISTORY

WHERE SAMPLE_TIME > SYSDATE - INTERVAL '2'

HOUR

AND SAMPLE_TIME <= TRUNC(SYSDATE, 'MI')) ASH PIVOT(COUNT(*)

FOR WAIT_CLASS IN('ON CPU' AS CPU,'BCPU' AS BCPU,

'Scheduler' AS SCHEDULER,

'User I/O' AS UIO,

'System I/O' AS SIO,

'Concurrency' AS CONCURRENCY,

'Application' AS APPLICATION,

'Commit' AS COMMIT,

'Configuration' AS CONFIGURATION,

'Administrative' AS ADMINISTRATIVE,

'Network' AS NETWORK,

'Queueing' AS QUEUEING,

'Cluster' AS CLUST,

'Other' AS OTHER));