Main menu
You are here
Find Informix LOCKS Session
Di, 09/07/2010 - 10:24 — brngor
Ermitteln einer LOCKS Session mit anschl. kill
· Auszug aus der Log Datei oder mit onstat -m
· Verursacher vom LOCKS ist die Spalte mit der Nummer (54)
· Welche Session verursacht die LOCKS
· Die Session (50) mancht die Probleme, sql Statement anzeigen.
· Welche rblsnum wird geändert, welches die meisten tblsnum hat und welches type „IX“ und rowid „0“hat
· Welche Table wird durch sql Statement verändert.
-- Auszug aus der Log Datei oder mit onstat -m
ZAV02V01@informix[goran]: onstat -m
14:17:13 dynamically allocated 100000 locks 14:17:16 Lock table overflow - user id -1, session id xxxxx
-- Verursacher vom LOCKS ist die Spalte mit der Nummer (54)
ZAV02V01@informix[goran]: onstat -u
IBM Informix Dynamic Server Version 10.00.FC4 -- On-Line -- Up 00:10:59 -- 55216 Kbytes Userthreads address flags sessid user tty wait tout locks nreads nwrites 7000000202f6028 ---P--D 1 informix - 0 0 0 42 34 7000000202f6760 ---P--F 0 informix - 0 0 0 0 708 7000000202f6e98 ---P--- 15 informix - 0 0 0 0 2 7000000202f75d0 ---P--B 16 informix - 0 0 0 0 0 7000000202f7d08 Y-BP--- 50 informix 2 700000021107b28 0 54 0 0 7000000202f8b78 ---P--D 19 informix - 0 0 0 0 0 7000000202f92b0 ---P--- 22 informix - 0 0 0 0 0 7000000202f99e8 Y--P--D 40 informix - 7000000100f29d8 0 0 0 0 7000000202fa120 ---P--- 24 informix - 0 0 0 0 0 7000000202fa858 Y--P--- 25 informix - 70000005025a448 0 3 0 0 7000000202faf90 ---P--- 27 informix - 0 0 0 0 0 7000000202fb6c8 ---P--- 28 informix - 0 0 1 0 0 7000000202fbe00 ---P--- 29 informix - 0 0 1 0 0 7000000202fc538 ---P--- 30 informix - 0 0 0 0 0 7000000202fcc70 Y-BP--- 62 informix 3 7000000211ee128 0 3 0 0 7000000202fdae0 ---P--- 33 informix - 0 0 0 2957 0 7000000202fe218 ---P--- 34 informix - 0 0 1 5 0 17 active, 128 total, 19 maximum concurrent
-- Welche Session verursacht die LOCKS
ZAV02V01@informix[goran]: onstat -g ses
IBM Informix Dynamic Server Version 10.00.FC4 -- On-Line -- Up 00:11:13 -- 55216 Kbytes session #RSAM total used dynamic id user tty pid hostname threads memory memory explain 63 informix - 0 - 0 12288 11656 off 62 informix 3 454768 ZAV02V01 1 94208 75944 off 50 informix 2 503968 ZAV02V01 1 81920 68320 off 14 informix - 0 - 0 12288 11656 off 13 informix - 0 - 0 16384 13240 off 12 informix - 0 - 0 12288 11656 off 11 informix - 0 - 0 12288 11656 off 10 informix - 0 - 0 16384 13240 off 9 informix - 0 - 0 12288 11656 off 8 informix - 0 - 0 12288 11656 off 6 informix - 0 - 0 16384 13240 off 5 informix - 0 - 0 16384 13240 off 4 informix - 0 - 0 12288 11656 off 3 informix - 0 - 0 12288 11656 off 2 informix - 0 - 0 12288 11656 off
-- Die Session (50) mancht die Probleme, sql Statement anzeigen.
ZAV02V01@informix[goran]: onstat -g ses 50
IBM Informix Dynamic Server Version 10.00.FC4 -- On-Line -- Up 00:18:23 -- 55216 Kbytes session #RSAM total used dynamic id user tty pid hostname threads memory memory explain 50 informix 2 503968 ZAV02V01 1 81920 68320 off tid name rstcb flags curstk status 68 sqlexec 7000000202f7d08 Y-BP--- 4640 cond wait(netnorm) Memory pools count 2 name class addr totalsize freesize #allocfrag #freefrag 50 V 700000021064040 77824 12760 98 15 50*O0 V 70000002106d040 4096 840 1 1 name free used name free used overhead 0 6512 scb 0 144 opentable 0 3432 filetable 0 720 ru 0 632 log 0 14144 temprec 0 10104 keys 0 1040 gentcb 0 1600 ostcb 0 3456 sqscb 0 18912 sql 0 72 rdahead 0 184 hashfiletab 0 552 osenv 0 2960 sqtcb 0 3480 fragman 0 376 sqscb info scb sqscb optofc pdqpriority sqlstats optcompind directives 700000020e116c0 7000000501d8028 0 0 0 2 1 Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 50 - stores_demo CR Not Wait 0 0 9.03 Off Last parsed SQL statement : update state set sname = "xxx" where 1=1
ZAV02V01@informix[goran]: onstat -k
IBM Informix Dynamic Server Version 10.00.FC4 -- On-Line -- Up 00:18:34 -- 55216 Kbytes Locks address wtlist owner lklist type tblsnum rowid key#/bsiz 700000010190e90 0 7000000202fa858 700000010190bc0 S 100002 206 0 700000010191160 0 7000000202fbe00 0 S 100002 205 0 7000000101913b8 0 7000000202f7d08 700000010190cb0 HDR+IX 100098 0 0 700000010191430 0 7000000202f7d08 700000010193488 HDR+X 100098 132 0 700000010191598 0 7000000202f7d08 700000010192498 HDR+X 100098 117 0 700000010191778 0 7000000202f7d08 7000000101918e0 HDR+X 100098 103 0 700000010191868 0 7000000202f7d08 7000000101913b8 HDR+X 100098 101 0 7000000101918e0 0 7000000202f7d08 700000010191868 HDR+X 100098 102 0 700000010191958 0 7000000202f7d08 700000010191778 HDR+X 100098 104 0 700000010192b28 0 7000000202f7d08 700000010192ab0 HDR+X 100098 122 0 700000010192c18 0 7000000202f7d08 700000010192b28 HDR+X 100098 123 0 700000010192c90 0 7000000202f7d08 700000010192c18 HDR+X 100098 124 0 700000010192d08 0 7000000202f7d08 700000010192c90 HDR+X 100098 125 0 7000000101935f0 0 7000000202f7d08 700000010193578 HDR+X 100098 134 0 7000000101937d0 0 7000000202fcc70 0 S 100002 207 0 7000000101938c0 0 7000000202fcc70 7000000101937d0 HDR+IX 10008a 0 0 700000010193938 0 7000000202fcc70 7000000101938c0 HDR+X 10008a 10a 0 65 active, 9000 total, 16384 hash buckets, 0 lock table overflows
-In dbacces
SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit Run the current SQL statements. ----------------------- stores_demo@goran ------ Press CTRL-W for Help --------
select stn.*, hex(ti_partnum)
from sysmaster:systabinfo sti, sysmaster:systabnames stn
where sti.ti_partnum = stn.partnum
and hex(ti_partnum)
like "%100098%";
-- Welche Table wird durch sql Statement verändert.
SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit Run the current SQL statements. ----------------------- stores_demo@goran ------ Press CTRL-W for Help -------- partnum 1048728 dbsname stores_demo owner informix tabname state collate en_US.819 (expression) 0x00100098