根据系统spid 定位sql

最新推荐文章于 2021-08-12 13:57:53 发布

cmbpr28628

最新推荐文章于 2021-08-12 13:57:53 发布

阅读量239

点赞数

文章标签：数据库操作系统

首先我们看操作系统的spid占用资源从高到低排序。

点击(此处)折叠或打开

top - 16:29:07 up 1:25, 5 users, load average: 0.52, 0.27, 0.51
Tasks: 116 total, 3 running, 113 sleeping, 0 stopped, 0 zombie
Cpu(s): 82.9%us, 6.0%sy, 0.0%ni, 0.0%id, 10.0%wa, 0.3%hi, 0.7%si, 0.0%st
Mem: 2059580k total, 2049244k used, 10336k free, 45644k buffers
Swap: 4192956k total, 120k used, 4192836k free, 1697872k cached

  PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1791 oracle 24 0 728m 144m 135m R 85.6 7.2 0:11.58 oracle
3001 oracle 16 0 732m 38m 34m S 3.7 1.9 0:00.49 oracle
  141 root 10 -5 0 0 0 S 0.3 0.0 0:00.52 kswapd0
  326 root 10 -5 0 0 0 S 0.3 0.0 0:00.53 kjournald
    1 root 15 0 10348 688 576 S 0.0 0.0 0:00.51 init
    2 root RT -5 0 0 0 S 0.0 0.0 0:00.00 migration/0
    3 root 34 19 0 0 0 S 0.0 0.0 0:00.00 ksoftirqd/0
    4 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/0
    5 root 10 -5 0 0 0 S 0.0 0.0 0:00.19 events/0
    6 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 khelper
   23 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kthread
   27 root 10 -5 0 0 0 S 0.0 0.0 0:00.26 kblockd/0
   28 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 kacpid
   66 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 cqueue/0
   69 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 khubd

点击(此处)折叠或打开

16:19:05 SQL> select sql_text,HASH_VALUE
  from v$sql
where (address, hash_value) in
       (select sql_address, sql_hash_value
          from v$session s
         where s.paddr =
               (select addr from v$process p where p.spid = '&pid'));
Enter value for pid: 1791
old 7:        (select addr from v$process p where p.spid = '&pid'))
new 7:        (select addr from v$process p where p.spid = '1791'))

SQL_TEXT HASH_VALUE
------------------------------------------------------------------------------------------------------------------------ UPDATE EMP SET SAL = :B1 +1 278858787
Elapsed: 00:00:00.65

这是我执行的测试脚本，这样我们就可以根据spid找到在数据库占用的资源大的sql。

点击(此处)折叠或打开

16:27:49 SQL> begin
16:27:57 2 for i in 1..100000 loop
16:28:17 3 update emp set sal = i+1;
16:28:34 4 commit;
16:28:41 5 end loop;
16:28:46 6 end;
16:28:50 7 /

------------------------------------------------------------------------
------------------------------------------------------------------------
1. 根据os进程号定位客户机器：

点击(此处)折叠或打开

select sid, serial#, username, machine, osuser, process
from v$session s
where s.paddr = (select addr from v$process p where p.spid = '&pid')

2. 根据os进程号定位SQL语句：

点击(此处)折叠或打开

select sql_text,HASH_VALUE
  from v$sql
where (address, hash_value) in
       (select sql_address, sql_hash_value
          from v$session s
         where s.paddr =
               (select addr from v$process p where p.spid = '&pid'))