Waits in database
Greetings
I have a degradation in a IDS 11.50.
With onstat -u I detect a lot of waits in a resource, but I cant identity wich
user causes this
The output is:
/informix/ids1150 [casiopea2] (informix): onstat -u | egrep 1440
2bdde5ba8 B--PX-- 944923 masiste - 1440e1440 0 0 303091 5189
2e0149e80 ---P--- 922478 masiste - 1440e1440 0 2 370541 13209
2e0152400 B--PX-- 923513 masiste WVMXAMUL 1440e1440 0 4 5531 1065
2e0153d08 B--PX-- 932984 masiste XMZ7129- 1440e1440 0 4 98173 964
2e0154db8 B--PX-- 918964 masiste MA14613- 1440e1440 0 4 46258 0
316c6d380 B--PX-- 1004916 masiste - 1440e1440 0 0 136069 11762
316c782b8 B--PX-- 923071 masiste MA00296P 1440e1440 0 3 26950 17661
316c91b90 ---P--- 1032894 masiste WVMXAMUL 1440e1440 0 2 161 0
316c97758 B--PX-- 1001928 masiste - 1440e1440 0 0 103790 9780
34ae94158 B--PX-- 1036188 masiste - 1440e1440 0 3 1134 0
34ae9c6d8 B--PX-- 1001968 masiste - 1440e1440 0 0 60741 19712
34aea5d08 B--PX-- 1038729 masiste - 1440e1440 0 3 1 0
34aeaf338 B--PX-- 1032877 masiste SEG048 1440e1440 0 4 70043 0
34aeb35f8 B---X-- 696956 masiste ASISTEVA 1440e1440 0 0 0 0
393c62f60 B--PX-- 1037700 masiste MA00757- 1440e1440 0 0 4374 0
393c82c58 B--PX-- 1009251 masiste ALEOROZC 1440e1440 0 0 351 0
393c915f8 B--PX-- 1034495 masiste WVMXAMUL 1440e1440 0 0 8995 1255
3e25ce6a8 B--PX-- 1038767 masiste POLIZARI 1440e1440 0 3 3 0
3e25d2110 B--PX-- 1029623 masiste - 1440e1440 0 0 331042 89529
4542b2cf0 B--PX-- 1004890 masiste - 1440e1440 0 0 102862 4912
49df37640 B--PX-- 916702 masiste - 1440e1440 0 4 769602 9768
49df38f48 B--PX-- 1005236 masiste XMBX096 1440e1440 0 0 2184 0
49df3b900 B--PX-- 1038225 masiste - 1440e1440 0 0 23 0
/informix/ids1150 [casiopea2] (informix):
How Can I dentify the user o resource
Thanks a lot
If the resource being waited for is a lock then onstat -k will show who is
holding that lock address:
onstat -k | fgrep 1440e1440
On the other hand if it is an internal latch then it will show in the
onstat -s report:
onstat -s | fgrep 1440e1440
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Thu, Jan 12, 2017 at 2:02 PM, HUGO PADILLA <hugo_padilla@terra.com.mx>
wrote:
> Greetings
>
> I have a degradation in a IDS 11.50.
>
> With onstat -u I detect a lot of waits in a resource, but I cant identity
> wich
> user causes this
>
> The output is:
> /informix/ids1150 [casiopea2] (informix): onstat -u | egrep 1440
> 2bdde5ba8 B--PX-- 944923 masiste - 1440e1440 0 0 303091 5189
> 2e0149e80 ---P--- 922478 masiste - 1440e1440 0 2 370541 13209
> 2e0152400 B--PX-- 923513 masiste WVMXAMUL 1440e1440 0 4 5531 1065
> 2e0153d08 B--PX-- 932984 masiste XMZ7129- 1440e1440 0 4 98173 964
> 2e0154db8 B--PX-- 918964 masiste MA14613- 1440e1440 0 4 46258 0
> 316c6d380 B--PX-- 1004916 masiste - 1440e1440 0 0 136069 11762
> 316c782b8 B--PX-- 923071 masiste MA00296P 1440e1440 0 3 26950 17661
> 316c91b90 ---P--- 1032894 masiste WVMXAMUL 1440e1440 0 2 161 0
> 316c97758 B--PX-- 1001928 masiste - 1440e1440 0 0 103790 9780
> 34ae94158 B--PX-- 1036188 masiste - 1440e1440 0 3 1134 0
> 34ae9c6d8 B--PX-- 1001968 masiste - 1440e1440 0 0 60741 19712
> 34aea5d08 B--PX-- 1038729 masiste - 1440e1440 0 3 1 0
> 34aeaf338 B--PX-- 1032877 masiste SEG048 1440e1440 0 4 70043 0
> 34aeb35f8 B---X-- 696956 masiste ASISTEVA 1440e1440 0 0 0 0
> 393c62f60 B--PX-- 1037700 masiste MA00757- 1440e1440 0 0 4374 0
> 393c82c58 B--PX-- 1009251 masiste ALEOROZC 1440e1440 0 0 351 0
> 393c915f8 B--PX-- 1034495 masiste WVMXAMUL 1440e1440 0 0 8995 1255
> 3e25ce6a8 B--PX-- 1038767 masiste POLIZARI 1440e1440 0 3 3 0
> 3e25d2110 B--PX-- 1029623 masiste - 1440e1440 0 0 331042 89529
> 4542b2cf0 B--PX-- 1004890 masiste - 1440e1440 0 0 102862 4912
> 49df37640 B--PX-- 916702 masiste - 1440e1440 0 4 769602 9768
> 49df38f48 B--PX-- 1005236 masiste XMBX096 1440e1440 0 0 2184 0
> 49df3b900 B--PX-- 1038225 masiste - 1440e1440 0 0 23 0
> /informix/ids1150 [casiopea2] (informix):
>
> How Can I dentify the user o resource
>
> Thanks a lot
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--94eb2c05cf72f380d50545ead880
Thanks
I triedseveral tomes with both, even with onstat -a | egrep 1440e1440 but
nothing, No aditional info about this wait
I also triyed from the database with the nex query, but idem, no blocking
sessions
select t.username waituser, t.sid waitsess, s.username hasuser,
s.sid hassess, l.type locktype, l.dbsname database,
l.tabname table, hex(l.rowidlk) rowid
from sysmaster:syslocks l, sysmaster:syssessions s, sysmaster:syssessions t
where s.sid = l.owner
and l.waiter = t.sid ;
These are buffer waits (B flag), and 1440e1440 consequently is a buffer's=20
address.
Possibly a hot index root node?
onstat -b | egrep "<that=5Faddress>|address"
-> use 'pagenum' information to find out which page this is=20
(chunk=5Fnum:chunk=5Foffsset)
-> find out which dbspace this chunk belongs to
-> use 'oncheck -pe <dbspace>' to identify which 'object'=20
(table/index) the page belongs to
-> use 'oncheck -pt <db>:<table>' to determine which precise=20
partition and logical page
Now you know which data it is that's so being waited on - from there you =
can find out possible reasons.
HTH,
Andreas
From: "HUGO PADILLA" <hugo=5Fpadilla@terra.com.mx>
To: ids@iiug.org
Date: 12.01.2017 20:02
Subject: Waits in database [38509]
Sent by: ids-bounces@iiug.org
Greetings=20
I have a degradation in a IDS 11.50.=20
With onstat -u I detect a lot of waits in a resource, but I cant identity=20
wich=20
user causes this=20
The output is:=20
/informix/ids1150 [casiopea2] (informix): onstat -u | egrep 1440=20
2bdde5ba8 B--PX-- 944923 masiste - 1440e1440 0 0 303091 5189=20
2e0149e80 ---P--- 922478 masiste - 1440e1440 0 2 370541 13209=20
2e0152400 B--PX-- 923513 masiste WVMXAMUL 1440e1440 0 4 5531 1065=20
2e0153d08 B--PX-- 932984 masiste XMZ7129- 1440e1440 0 4 98173 964=20
2e0154db8 B--PX-- 918964 masiste MA14613- 1440e1440 0 4 46258 0=20
316c6d380 B--PX-- 1004916 masiste - 1440e1440 0 0 136069 11762=20
316c782b8 B--PX-- 923071 masiste MA00296P 1440e1440 0 3 26950 17661=20
316c91b90 ---P--- 1032894 masiste WVMXAMUL 1440e1440 0 2 161 0=20
316c97758 B--PX-- 1001928 masiste - 1440e1440 0 0 103790 9780=20
34ae94158 B--PX-- 1036188 masiste - 1440e1440 0 3 1134 0=20
34ae9c6d8 B--PX-- 1001968 masiste - 1440e1440 0 0 60741 19712=20
34aea5d08 B--PX-- 1038729 masiste - 1440e1440 0 3 1 0=20
34aeaf338 B--PX-- 1032877 masiste SEG048 1440e1440 0 4 70043 0=20
34aeb35f8 B---X-- 696956 masiste ASISTEVA 1440e1440 0 0 0 0=20
393c62f60 B--PX-- 1037700 masiste MA00757- 1440e1440 0 0 4374 0=20
393c82c58 B--PX-- 1009251 masiste ALEOROZC 1440e1440 0 0 351 0=20
393c915f8 B--PX-- 1034495 masiste WVMXAMUL 1440e1440 0 0 8995 1255=20
3e25ce6a8 B--PX-- 1038767 masiste POLIZARI 1440e1440 0 3 3 0=20
3e25d2110 B--PX-- 1029623 masiste - 1440e1440 0 0 331042 89529=20
4542b2cf0 B--PX-- 1004890 masiste - 1440e1440 0 0 102862 4912=20
49df37640 B--PX-- 916702 masiste - 1440e1440 0 4 769602 9768=20
49df38f48 B--PX-- 1005236 masiste XMBX096 1440e1440 0 0 2184 0=20
49df3b900 B--PX-- 1038225 masiste - 1440e1440 0 0 23 0=20
/informix/ids1150 [casiopea2] (informix):=20
How Can I dentify the user o resource=20
Thanks a lot=20
***************************************************************************=
****=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20