根據系統spid 定位sql

哎呀我的天吶發表於2015-01-12
SQL
首先我們看作業系統的spid佔用資源從高到低排序。

點選(此處)摺疊或開啟

  1. top - 16:29:07 up 1:25, 5 users, load average: 0.52, 0.27, 0.51
  2. Tasks: 116 total, 3 running, 113 sleeping, 0 stopped, 0 zombie
  3. 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
  4. Mem: 2059580k total, 2049244k used, 10336k free, 45644k buffers
  5. Swap: 4192956k total, 120k used, 4192836k free, 1697872k cached


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

點選(此處)摺疊或開啟

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

  12. SQL_TEXT                       HASH_VALUE
  13. ------------------------------------------------------------------------------------------------------------------------
  14. UPDATE EMP SET SAL = :B1 +1    278858787

  16. Elapsed: 00:00:00.65

這是我執行的測試指令碼，這樣我們就可以根據spid找到在資料庫佔用的資源大的sql。

點選(此處)摺疊或開啟

  1. 16:27:49 SQL> begin
  2. 16:27:57 2 for i in 1..100000 loop
  3. 16:28:17 3 update emp set sal = i+1;
  4. 16:28:34 4 commit;
  5. 16:28:41 5 end loop;
  6. 16:28:46 6 end;
  7. 16:28:50 7 /
------------------------------------------------------------------------
------------------------------------------------------------------------
1. 根據os程式號定位客戶機器：

點選(此處)摺疊或開啟


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

2. 根據os程式號定位SQL語句：

點選(此處)摺疊或開啟

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




                

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29990276/viewspace-1399277/，如需轉載，請註明出處，否則將追究法律責任。

相關文章