1> vmstat 5 5
System configuration: lcpu=20 mem=32768MB ent=1.55
kthr memory page faults cpu
----- ----------- ------------------------ ------------ -----------------------
r b avm fre re pi po fr sr cy in sy cs us sy id wa pc ec
13 0 4565429 14948 0 0 0 1332 3000 0 147 36593 17377 83 4 12 0 4.96 320.0
14 0 4564860 10405 0 0 0 1310 1767 0 397 37416 19824 82 5 12 0 4.92 317.7
14 0 4564860 10498 0 0 0 2787 3114 0 231 37916 18829 83 5 12 0 4.96 319.8
14 0 4564574 16085 0 0 0 3378 25795 0 138 36146 17177 84 5 11 0 4.97 320.5
14 0 4561752 10716 0 0 0 649 8532 0 238 36615 18442 84 4 12 0 4.94 318.5
i) Check for r/lcpu > 1, it is okay. else some cpu usage issue.
ii) Check us+sys > idle
2> sar -u 5 10
System configuration: lcpu=20 ent=1.55 mode=Uncapped
08:00:30 %usr %sys %wio %idle physc %entc
08:00:35 87 3 1 9 4.95 319.5
08:00:40 88 3 1 9 4.97 320.6
08:00:45 88 3 1 9 4.95 319.1
08:00:50 87 3 1 9 4.95 319.5
08:00:55 87 2 1 9 4.97 320.7
08:01:00 87 3 0 10 4.94 318.5
08:01:05 87 3 1 10 4.97 320.9
08:01:10 88 2 1 9 4.98 321.2
08:01:15 87 3 1 9 4.98 321.3
08:01:20 87 3 0 10 4.96 319.8
Average 87 3 1 9 4.96 320.1
Check us+sys > idle
3> topas -P
Topas Monitor for host: chbsux0390 Interval: 2 Sat Oct 18 08:47:37 2014
DATA TEXT PAGE PGFAULTS
USER PID PPID PRI NI RES RES SPACE TIME CPU% I/O OTH COMMAND
oracle 52297956 1 120 20 4591 70170 4591 114182:46 8.2 0 0 oracle
oracle 37552374 1 120 20 8484 70170 8484 76136:06 6.8 0 0 oracle
oracle 35848374 1 120 20 9147 70170 9147 69766:57 6.6 0 0 oracle
oracle 44368092 1 120 20 4369 70170 4369 123343:11 6.6 0 0 oracle
oracle 30539906 1 120 20 6147 70170 6147 123031:18 6.6 0 0 oracle
oracle 60686468 1 120 20 4036 70170 4036 124:20 6.1 0 0 oracle
oracle 53281014 1 120 20 22823 70170 22823 0:44 6.0 0 0 oracle
oracle 48496640 1 120 20 12909 70170 12909 17:49 5.5 1 1 oracle
oracle 31850584 1 120 20 12479 70170 12479 238:18 5.4 5 4 oracle
oracle 27853310 63176802 127 22 7472 11963 7472 46:11 5.3 0 0 exp
oracle 22937798 1 120 20 3388 70170 3388 123:48 5.3 0 0 oracle
oracle 43515926 1 120 20 22787 70170 22787 0:44 5.3 0 0 oracle
oracle 65942 1 120 20 3386 70170 3386 123:50 5.2 24 446 oracle
oracle 6160726 1 120 20 3950 70170 3950 124:47 5.0 0 180 oracle
oracle 60031032 1 120 20 6040 70170 6040 90:58 4.8 18 21 oracle
oracle 50004036 63176802 95 22 220 6 220 59:16 1.8 0 0 compress
oracle 29819082 1 67 20 112 0 112 0:00 0.6 12 39 aioserve
oracle 33489320 1 68 20 112 0 112 0:04 0.4 8 15 aioserve
oracle 14614850 1 65 20 112 0 112 0:01 0.4 5 31 aioserve
oracle 12320844 1 65 20 16630 70170 16630 174:44 0.4 0 0 oracle
oracle 11731258 1 63 20 112 0 112 0:00 0.3 7 14 aioserve
oracle 48824456 1 66 20 112 0 112 0:00 0.3 5 13 aioserve
oracle 24772842 1 63 20 112 0 112 0:02 0.3 1 10 aioserve
oracle 27263444 1 68 22 112 0 112 0:02 0.3 3 16 aioserve
oracle 54591676 1 63 22 112 0 112 0:02 0.2 3 10 aioserve
oracle 12976340 27853310 66 22 5696 70170 5696 10:39 0.1 0 0 oracle
oracle 19333152 43384996 60 20 17761 3 17761 249:47 0.1 0 0 emagent
ggadm 16908346 22020196 60 20 6758 4645 6758 147:41 0.0 0 0 extract
Check for the highest usage PID and find out the Session ID and hash value from v$process and v$session. v$sqltextarea and v$sql tells us which sql is consuming most cpu.
4> ps -ef | grep pmon to check how many oracle databases are running on the server.
5> Oracle scripts:
select sess.username, stat.sid, name.name name, sum(stat.value)/100 valuesum_seconds from v$sesstat stat, v$statname name, v$session sess
where stat.sid = sess.sid and stat.statistic# = name.statistic# and name.name like '%CPU%' group by sess.username, stat.sid, name.name;
select s.username, s.sid, s.serial#, p.pid ppid, s.status, s.osuser, substr(s.program,1,20) client_program, s.process client_process,
substr(p.program,1,20) server_program, p.spid server_pid from v$session s, v$process p where s.username is not null
and p.addr(+) = s.paddr and userenv('SESSIONID') = s.audsid
order by username, sid
select s.username "Oracle User",s.osuser "OS User",i.consistent_gets "Consistent Gets",
i.physical_reads "Physical Reads",s.status "Status",s.sid "SID",s.serial# "Serial#",
s.machine "Machine",s.program "Program",to_char(logon_time, 'DD/MM/YYYY HH24:MI:SS') "Logon Time",
w.seconds_in_wait "Idle Time", P.SPID "PROC",
name "Stat CPU", value
from v$session s, v$sess_io i, v$session_wait w, V$PROCESS P, v$statname n, v$sesstat t
where s.sid = i.sid
and s.sid = w.sid (+)
and 'SQL*Net message from client' = w.event(+)
and s.osuser is not null
and s.username is not null
and s.paddr=p.addr
and n.statistic# = t.statistic#
and n.name like '%cpu%'
and t.SID = s.sid
order by 6 asc, 3 desc, 4 desc;
select
ss.username,
se.SID,
ss.status,
VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and
ss.status in ('ACTIVE','INACTIVE')
and
ss.username is not null
order by VALUE desc;
6> Kill the inactive sessions that are consuming space.
System configuration: lcpu=20 mem=32768MB ent=1.55
kthr memory page faults cpu
----- ----------- ------------------------ ------------ -----------------------
r b avm fre re pi po fr sr cy in sy cs us sy id wa pc ec
13 0 4565429 14948 0 0 0 1332 3000 0 147 36593 17377 83 4 12 0 4.96 320.0
14 0 4564860 10405 0 0 0 1310 1767 0 397 37416 19824 82 5 12 0 4.92 317.7
14 0 4564860 10498 0 0 0 2787 3114 0 231 37916 18829 83 5 12 0 4.96 319.8
14 0 4564574 16085 0 0 0 3378 25795 0 138 36146 17177 84 5 11 0 4.97 320.5
14 0 4561752 10716 0 0 0 649 8532 0 238 36615 18442 84 4 12 0 4.94 318.5
i) Check for r/lcpu > 1, it is okay. else some cpu usage issue.
ii) Check us+sys > idle
2> sar -u 5 10
System configuration: lcpu=20 ent=1.55 mode=Uncapped
08:00:30 %usr %sys %wio %idle physc %entc
08:00:35 87 3 1 9 4.95 319.5
08:00:40 88 3 1 9 4.97 320.6
08:00:45 88 3 1 9 4.95 319.1
08:00:50 87 3 1 9 4.95 319.5
08:00:55 87 2 1 9 4.97 320.7
08:01:00 87 3 0 10 4.94 318.5
08:01:05 87 3 1 10 4.97 320.9
08:01:10 88 2 1 9 4.98 321.2
08:01:15 87 3 1 9 4.98 321.3
08:01:20 87 3 0 10 4.96 319.8
Average 87 3 1 9 4.96 320.1
Check us+sys > idle
3> topas -P
Topas Monitor for host: chbsux0390 Interval: 2 Sat Oct 18 08:47:37 2014
DATA TEXT PAGE PGFAULTS
USER PID PPID PRI NI RES RES SPACE TIME CPU% I/O OTH COMMAND
oracle 52297956 1 120 20 4591 70170 4591 114182:46 8.2 0 0 oracle
oracle 37552374 1 120 20 8484 70170 8484 76136:06 6.8 0 0 oracle
oracle 35848374 1 120 20 9147 70170 9147 69766:57 6.6 0 0 oracle
oracle 44368092 1 120 20 4369 70170 4369 123343:11 6.6 0 0 oracle
oracle 30539906 1 120 20 6147 70170 6147 123031:18 6.6 0 0 oracle
oracle 60686468 1 120 20 4036 70170 4036 124:20 6.1 0 0 oracle
oracle 53281014 1 120 20 22823 70170 22823 0:44 6.0 0 0 oracle
oracle 48496640 1 120 20 12909 70170 12909 17:49 5.5 1 1 oracle
oracle 31850584 1 120 20 12479 70170 12479 238:18 5.4 5 4 oracle
oracle 27853310 63176802 127 22 7472 11963 7472 46:11 5.3 0 0 exp
oracle 22937798 1 120 20 3388 70170 3388 123:48 5.3 0 0 oracle
oracle 43515926 1 120 20 22787 70170 22787 0:44 5.3 0 0 oracle
oracle 65942 1 120 20 3386 70170 3386 123:50 5.2 24 446 oracle
oracle 6160726 1 120 20 3950 70170 3950 124:47 5.0 0 180 oracle
oracle 60031032 1 120 20 6040 70170 6040 90:58 4.8 18 21 oracle
oracle 50004036 63176802 95 22 220 6 220 59:16 1.8 0 0 compress
oracle 29819082 1 67 20 112 0 112 0:00 0.6 12 39 aioserve
oracle 33489320 1 68 20 112 0 112 0:04 0.4 8 15 aioserve
oracle 14614850 1 65 20 112 0 112 0:01 0.4 5 31 aioserve
oracle 12320844 1 65 20 16630 70170 16630 174:44 0.4 0 0 oracle
oracle 11731258 1 63 20 112 0 112 0:00 0.3 7 14 aioserve
oracle 48824456 1 66 20 112 0 112 0:00 0.3 5 13 aioserve
oracle 24772842 1 63 20 112 0 112 0:02 0.3 1 10 aioserve
oracle 27263444 1 68 22 112 0 112 0:02 0.3 3 16 aioserve
oracle 54591676 1 63 22 112 0 112 0:02 0.2 3 10 aioserve
oracle 12976340 27853310 66 22 5696 70170 5696 10:39 0.1 0 0 oracle
oracle 19333152 43384996 60 20 17761 3 17761 249:47 0.1 0 0 emagent
ggadm 16908346 22020196 60 20 6758 4645 6758 147:41 0.0 0 0 extract
Check for the highest usage PID and find out the Session ID and hash value from v$process and v$session. v$sqltextarea and v$sql tells us which sql is consuming most cpu.
4> ps -ef | grep pmon to check how many oracle databases are running on the server.
5> Oracle scripts:
select sess.username, stat.sid, name.name name, sum(stat.value)/100 valuesum_seconds from v$sesstat stat, v$statname name, v$session sess
where stat.sid = sess.sid and stat.statistic# = name.statistic# and name.name like '%CPU%' group by sess.username, stat.sid, name.name;
select s.username, s.sid, s.serial#, p.pid ppid, s.status, s.osuser, substr(s.program,1,20) client_program, s.process client_process,
substr(p.program,1,20) server_program, p.spid server_pid from v$session s, v$process p where s.username is not null
and p.addr(+) = s.paddr and userenv('SESSIONID') = s.audsid
order by username, sid
select s.username "Oracle User",s.osuser "OS User",i.consistent_gets "Consistent Gets",
i.physical_reads "Physical Reads",s.status "Status",s.sid "SID",s.serial# "Serial#",
s.machine "Machine",s.program "Program",to_char(logon_time, 'DD/MM/YYYY HH24:MI:SS') "Logon Time",
w.seconds_in_wait "Idle Time", P.SPID "PROC",
name "Stat CPU", value
from v$session s, v$sess_io i, v$session_wait w, V$PROCESS P, v$statname n, v$sesstat t
where s.sid = i.sid
and s.sid = w.sid (+)
and 'SQL*Net message from client' = w.event(+)
and s.osuser is not null
and s.username is not null
and s.paddr=p.addr
and n.statistic# = t.statistic#
and n.name like '%cpu%'
and t.SID = s.sid
order by 6 asc, 3 desc, 4 desc;
select
ss.username,
se.SID,
ss.status,
VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and
ss.status in ('ACTIVE','INACTIVE')
and
ss.username is not null
order by VALUE desc;
6> Kill the inactive sessions that are consuming space.
No comments:
Post a Comment