利用操作系统的top工具，找出消耗系统性能较多的sql
1.使用top工具，找出消耗CPU 较多的进程
|
[oracle@cuug ~]$ top
top - 10:48:27 up 23:15, 4 users, load average: 1.09, 0.43, 0.15 Tasks: 161 total, 3 running, 158 sleeping, 0 stopped, 0 zombie Cpu(s): 74.3%us, 23.7%sy, 0.0%ni, 0.0%id, 2.0%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 900096k total, 894948k used, 5148k free, 6436k buffers Swap: 2097144k total, 24692k used, 2072452k free, 666588k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 10306 oracle 25 0 326m 171m 168m R 93.4 19.5 1:51.53 oracle 3258 oracle 16 0 338m 32m 29m S 3.3 3.7 1:31.02 oracle 3256 oracle 15 0 347m 191m 175m S 1.3 21.8 0:04.65 oracle
可以看到用户ORACLE的进程 为 PID=10306 占用cpu 93.4 |
2.根据刚才查到的pid 查找数据库中该进程的相关信息
|
SYS @ prod >select pid,spid,addr from v$process where spid=10306;
PID SPID ADDR ---------- ------------ -------- 15 10306 2E61B254 |
3.根据查到的pid 查找v$session的相关会话信息
|
SYS @ prod >select sid,saddr,paddr from v$session where paddr='2E61B254';
SID SADDR PADDR ---------- -------- -------- 146 2E6FC134 2E61DA40 147 2E6FD3E8 2E61C370 150 2E700C04 2E61B254 |
4.根据查到的sid ,查找该会话的 sql_hash_value
|
SYS @ prod >select sid,SQL_HASH_VALUE from v$session where sid=150;
SID SQL_HASH_VALUE ---------- -------------- 150 3514920902 |
5.根据查到的sql_hash_value,查找对应的sql语句
|
SYS @ prod >select sql_text from v$sqltext where hash_value='3514920902';
SQL_TEXT ---------------------------------------------------------------- declare begin for i in 1..10000000 loop insert into test values( i); end loop; end; |
哈哈，找到了。原来 是自己写的一条死循环语句
汇总后的sql语句
|
SYS @ prod >select sess.sid,sql.sql_text from v$sqltext sql,v$session sess,v$process procss where procss.addr=sess.saddr procss.spid=&pid and sess.sql_hash_value=sql1.hash_value;;
SID SQL_TEXT ---------------------------------------------------------------- 148 declare begin for i in 1..10000000 loop insert into test values( i); end loop; end; |
