--Simulation of high CPU utilization:
Sql> Select COUNT (*) from T_lte_card_info;
COUNT (*)----------3516598
Sql> select * from T_lte_card_info;
[email protected] ~]$ Top
Top-11:10:20 up 19:31, 3 users, load average:0.17, 0.23, 0.14tasks:187 Total, 3 running, 184 sleeping, 0 stopped , 0 zombiecpu (s): 15.7%us, 8.2%sy, 0.0%ni, 74.3%id, 1.1%wa, 0.0%hi, 0.7%si, 0.0%stmem:3925464k total, 3774424k Used, 151040k free, 64040k buffersswap:8388600k total, 366612k used, 8021988k free, 1963848k cached
PID USER  PR ni virt res shr S%cpu%MEM time+ comman D and nbsp , &NB Sp 7085 Oracle 20 0 45480 10m 73 56.7 0.3 0:13.21 sqlplus &N Bsp , &NB Sp 6525 Root &NB Sp 20 0 98.4m 4460 3164 R 31.2 0.1 2:50.75 sshd &N BSp , &NB Sp , &NB Sp 22 root 20 0 0 0 0 S & nbsp;4.3 0.0 0:30.05 EVENTS/3 &N Bsp , &NB Sp 2954 Oracle 20 0 1687m 125m 120m S 2.7 3.3 0:04.18 Oracle &N Bsp /   , &NB Sp 19 root 20 0 0 0 0 S 1. 0 0.0 0:06.06 events/0 &N Bsp , &NB Sp 21 root &nbs P 20 0 0 0 0 S 0.3 0.0 0:03.63 events/2 &NBSP ; , &NB Sp 2349 MySQL 0 1121m 247m 696 S 0 .3 6.5 1:01.55 mysqld &NB Sp , &NB Sp 2952 Oracle 20 0 1686m 46m 42m S 0.3 1.2 0:18.78 Oracle , &N Bsp , &NB Sp 2958 Oracle 20 0 1688m 53m 47m S 0.3 1.4 &nbs P 0:18. Oracle
--Find out what's going on backstage [[email protected] ~]$ Ps-ef | grep 7085oracle 7085 6764 11:08 pts/0 00:00:55 sqlplus Oracle 7089 7085 0 11:08? 00:00:00 ORACLEORCL (description= (Local=yes) (address= (PROTOCOL=BEQ))) Oracle 7101 6950 0 11:11 pts/1 00:00:00 grep 7085[email protected] ~]$
--Find the specific SQL statement, the original is in the execution of large table query [[email protected] ~]$ sqlplus "/as sysdba"
Sql> sql> SELECT B.sql_text 2 from V$session A, V$sqlarea B 3 WHERE a.sql_hash_value = B.hash_value 4 and A.sid = (SELECT a.sid 5 from V$session A, v$process P 6 WHERE p.a DDR = a.paddr and p.spid = 7089) 7;
Sql_text--------------------------------------------------------------------------------SELECT * from T_lte_card_ INFO
Sql> SELECT a.pid, 2 a.spid, 3 B.sid, 4 b.serial#, 5 C.sql_text 6 from V$proces s A, v$session B, v$sqlarea c 7 WHERE a.addr = b.paddr and b.sql_address = c.address and b.process= ' 7085 '8;
PID SPID SID serial#Sql_text--------------------------------------------------------------------------------7089 144 3293SELECT * from T_lte_card_info
sql> alter system kill session ' 144,3293 ';System altered.
--CPU Utilization Decreased[Email protected] ~]$ toptop-11:17:26 up 19:38, 3 users, load average:0.29, 0.31, 0.20tasks:187 Total, 1 running, 186 sleeping, 0 stopped, 0 zombiecpu (s): 0.4%us, 0.2%sy, 0.0%ni, 98.7%id, 0.8%wa, 0.0%hi, 0.0%si, 0.0%stmem: 3925464k Total, 3746036k used, 179428k free, 58188k buffersswap:8388600k total, 367056k used, 8021544k free, 1 941180k Cached
PID USER  PR ni virt res shr S%cpu%MEM time+ comman D and nbsp , &NB Sp 1 root 20 0 19364 &N bsp;612 436 S 0.0 0.0 0:01.69 init , &N Bsp , &NB Sp 2 root 20 0 0 0 0 S 0.0   ; 0.0 0:00.00 kthreadd , &NB Sp 3 root &NBS P RT 0 0 0 0 S 0.0 0.0 0:00.01 migration/0 & nbsp , &NB Sp , &NB Sp 4 root 20 0 0 0   ; 0 S 0.0 0.0 0:00.12 ksoftirqd/0 &NB SP , &NB Sp 5 root   ;  RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0 &N Bsp , &NB Sp , &NB Sp 6 root  RT 0 0 0 0 S 0.0 0.0 0:00.12 watchdog/0 & nbsp  &NB Sp 7 root  RT 0 0 0 0 S 0.0 0.0 0 : 00.03 migration/1 &NBSP ; , &NB Sp 8 root  RT 0 & nbsp 0 0 0 S 0.0 0.0 0:00.00 migration/1 , &N Bsp , &NB Sp 9 root 20 0 0 0 0 S 0. 0 0.0 0:00.45 ksoftirqd/1 , &N Bsp 10 root   ; rt 0 0 0 0 S 0.0 0.0 0:00.11 watchdog/1 &NB Sp , &NB Sp , &NB Sp 11 root  RT 0 0 0 &nbs P &nBsp;0 S 0.0 0.0 0:00.01 migration/2
From for notes (Wiz)
Grab TOP SQL to solve high CPU utilization issues
