抓 TOP SQL 解决CPU利用率过高问题
浏览数：266 /
时间：2015年06月12日
--模拟CPU利用率过高的情况：
SQL> select count(*) from T_LTE_CARD_INFO ;
COUNT(*)
----------
3516598
SQL> select * from T_LTE_CARD_INFO ;
[oracle@txy ~]$ top
top - 11:10:20 up 19:31, 3 users, load average: 0.17, 0.23, 0.14
Tasks: 187 total, 3 running, 184 sleeping, 0 stopped, 0 zombie
Cpu(s): 15.7%us, 8.2%sy, 0.0%ni, 74.3%id, 1.1%wa, 0.0%hi, 0.7%si, 0.0%st
Mem: 3925464k total, 3774424k used, 151040k free, 64040k buffers
Swap: 8388600k total, 366612k used, 8021988k free, 1963848k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
7085 oracle 20 0 45480 10m 7388 R 56.7 0.3 0:13.21 sqlplus
6525 root 20 0 98.4m 4460 3164 R 31.2 0.1 2:50.75 sshd
22 root 20 0 0 0 0 S 4.3 0.0 0:30.05 events/3
2954 oracle 20 0 1687m 125m 120m S 2.7 3.3 0:04.18 oracle
19 root 20 0 0 0 0 S 1.0 0.0 0:06.06 events/0
21 root 20 0 0 0 0 S 0.3 0.0 0:03.63 events/2
2349 mysql 20 0 1121m 247m 696 S 0.3 6.5 1:01.55 mysqld
2952 oracle 20 0 1686m 46m 42m S 0.3 1.2 0:18.78 oracle
2958 oracle 20 0 1688m 53m 47m S 0.3 1.4 0:18.12 oracle
--查找后台在干什么
[oracle@txy ~]$ ps -ef | grep 7085
oracle 7085 6764 29 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
[oracle@txy ~]$
--找到具体的SQL语句，原来是在执行大表查询
[oracle@txy ~]$ 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.ADDR = 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$process 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
--------------------------------------------------------------------------------
16 7089 144 3293 select * from T_LTE_CARD_INFO
SQL> alter system kill session ‘144,3293‘;
System altered.
--CPU利用率下降
oracle@txy ~]$ top
top - 11:17:26 up 19:38, 3 users, load average: 0.29, 0.31, 0.20
Tasks: 187 total, 1 running, 186 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.4%us, 0.2%sy, 0.0%ni, 98.7%id, 0.8%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 3925464k total, 3746036k used, 179428k free, 58188k buffers
Swap: 8388600k total, 367056k used, 8021544k free, 1941180k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1 root 20 0 19364 612 436 S 0.0 0.0 0:01.69 init
2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd
3 root RT 0 0 0 0 S 0.0 0.0 0:00.01 migration/0
4 root 20 0 0 0 0 S 0.0 0.0 0:00.12 ksoftirqd/0
5 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0
6 root RT 0 0 0 0 S 0.0 0.0 0:00.12 watchdog/0
7 root RT 0 0 0 0 S 0.0 0.0 0:00.03 migration/1
8 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/1
9 root 20 0 0 0 0 S 0.0 0.0 0:00.45 ksoftirqd/1
10 root RT 0 0 0 0 S 0.0 0.0 0:00.11 watchdog/1
11 root RT 0 0 0 0 S 0.0 0.0 0:00.01 migration/2
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
