如何查看及解决最耗 CPU 的 SQL 语句 1.1. 用 top 监控服务器负载
[root@node1 ~]# top top - 22:51:02 up 56 min, 1 user, load average: 0.00, 0.00, 0.00 Tasks: 96 total, 1 running, 95 sleeping, 0 stopped, 0 zombie Cpu(s): 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 1035096k total, 351488k used, 683608k free, 24140k buffers Swap: 2096472k total, 0k used, 2096472k free, 270360k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 1 root 15 0 2084 660 560 S 0.0 0.1 0:00.22 init 2 root RT -5 0 0 0 S 0.0 0.0 0:00.00 migration/0 |
如果发现 user 中的 CPU 过高,比如 oracle 中最高的进程 pid 为 1138782 ,占 CPU27% ,则执行下一步。
1.2. 查询数据库会话的 sid 、 serial#
进入数据库,根据 oracle 进程的 pid 查出对应数据库会话的 sid 、 serial# :
select s.sid,s.serial# from v$session s,v$process p where s.paddr=p.addr and p.spid='1138782'; |
查询出来的结果 sid 、 serial# 分别为 482 、 56767
1.3. 查询 SQL 语句
根据数据库会话的 sid 查出具体的 SQL 语句:
desc v$sqltext |
名称 是否为空 ? 类型
----------------------------------------- -------- --------------
ADDRESS RAW(4)
HASH_VALUE NUMBER
COMMAND_TYPE NUMBER
PIECE NUMBER
SQL_TEXT VARCHAR2(64)
select sql_text from v$sqltext a where a.hash_value=(select sql_hash_value from v$session b where b.sid='&sid') order by piece; |
输入 sid 的值 :
1.4. 处理 SQL 语句
如果 SQL 语句影响了数据库的运行,可以 kill 掉 SQL 语句的会话:
①在数据库中杀死 SQL 语句的会话:
alter system kill session '482,56767'; |
如果不能在数据库中杀死 SQL 语句,可在 LINUX 系统中强制杀死 Oracle 进程
②在 linux 系统中强制杀死 oracle 进程
#kill -9 1138782 |
1.5. SQL 语句优化
最后可以根据步骤( 3 )查询出来的 SQL 语句进行优化,以避免再次出现上述消耗 CPU 的情况。
1.4. 处理 SQL 语句
如果 SQL 语句影响了数据库的运行,可以 kill 掉 SQL 语句的会话:
①在数据库中杀死 SQL 语句的会话:
alter system kill session '482,56767'; |
如果不能在数据库中杀死 SQL 语句,可在 LINUX 系统中强制杀死 Oracle 进程
②在 linux 系统中强制杀死 oracle 进程
#kill -9 1138782 |
1.5. SQL 语句优化
最后可以根据步骤( 3 )查询出来的 SQL 语句进行优化,以避免再次出现上述消耗 CPU 的情况。
列出使用频率最高的5个查询:
select
sql_text,executions
from
(
select
sql_text,executions,
rank() over
(
order
by
executions
desc
) exec_rank
from
v$sql)
where
exec_rank <=5;
消耗磁盘读取最多的sql top5:
select
disk_reads,sql_text
from
(
select
sql_text,disk_reads,
dense_rank() over
(
order
by
disk_reads
desc
) disk_reads_rank
from
v$sql)
where
disk_reads_rank <=5;
找出需要大量缓冲读取(逻辑读)操作的查询:
select
buffer_gets,sql_text
from
(
select
sql_text,buffer_gets,
dense_rank() over
(
order
by
buffer_gets
desc
) buffer_gets_rank
from
v$sql)
where
buffer_gets_rank<=5;