Hello
- Yes , the flags showed no waiting for Lock or for another ressource
- PDQ is set to 10%
- 20 vp CPU with eff around 50%
- mpstat shows no cpu with 100%, but i will run it again at different times
onstat -g glo
Individual virtual processors:
vp pid class usercpu syscpu total Thread Eff
1 28705246 cpu 987.24 493.20 1480.44 2591.29 57%
2 18612350 adm 0.24 4.45 4.69 0.00 0%
3 23200252 lio 0.02 0.15 0.17 0.00 0%
4 18809718 pio 0.02 0.15 0.17 0.00 0%
5 26214942 aio 2.64 5.54 8.18 34.13 23%
6 15204880 msc 25.42 1.65 27.07 47.79 56%
7 23003978 fifo 0.01 0.15 0.16 0.00 0%
8 8061086 cpu 1189.33 447.61 1636.94 2806.56 58%
9 26214506 cpu 1040.67 426.16 1466.83 2466.69 59%
10 11142056 cpu 820.21 306.69 1126.90 1907.02 59%
11 23790126 cpu 779.60 304.78 1084.38 1897.82 57%
12 20906326 cpu 570.34 222.56 792.90 1313.01 60%
13 4194408 cpu 511.78 179.05 690.83 1179.56 58%
14 12452848 cpu 388.35 134.88 523.23 892.11 58%
15 12911226 cpu 321.67 109.72 431.39 728.19 59%
16 7733526 cpu 266.61 81.77 348.38 603.53 57%
17 21955288 cpu 292.04 93.47 385.51 684.57 56%
18 21103376 cpu 195.41 65.87 261.28 470.99 55%
19 25100644 cpu 176.31 49.49 225.80 400.20 56%
20 25953004 cpu 158.93 50.32 209.25 373.34 56%
21 18154166 cpu 127.78 37.42 165.20 294.70 56%
22 13697614 cpu 128.03 38.97 167.00 298.84 55%
23 17629916 cpu 161.79 42.07 203.86 368.74 55%
24 7275432 cpu 139.63 34.22 173.85 318.82 54%
25 26411564 cpu 145.30 30.43 175.73 317.29 55%
26 11010982 cpu 153.15 26.16 179.31 326.60 54%
27 4981138 aio 0.32 0.82 1.14 26.60 4%
28 21954616 aio 0.27 0.68 0.95 19.57 4%
30 5964572 aio 0.18 0.59 0.77 14.74 5%
31 18415800 aio 0.16 0.60 0.76 14.42 5%
32 15728724 aio 0.13 0.58 0.71 13.19 5%
29 23527894 aio 0.22 0.61 0.83 16.76 4%
33 27853136 aio 0.11 0.51 0.62 12.16 5%
34 13697912 aio 0.07 0.20 0.27 2.96 9%
35 9962312 aio 0.06 0.17 0.23 1.74 13%
36 18547636 soc 6.20 49.58 55.78 NA NA
37 16057114 soc 4.38 29.81 34.19 NA NA
38 14877574 soc 3.23 23.34 26.57 NA NA
39 13238476 soc 4.43 41.47 45.90 NA NA
40 9503652 soc 3.91 27.41 31.32 NA NA
41 23331616 soc 3.33 22.00 25.33 NA NA
42 25428522 soc 4.70 38.31 43.01 NA NA
43 19661556 soc 12.21 101.65 113.86 NA NA
44 8716580 soc 2.84 22.43 25.27 NA NA
45 6357774 soc 10.25 86.44 96.69 NA NA
46 4456980 soc 2.59 20.95 23.54 NA NA
47 19202700 soc 6.28 53.88 60.16 NA NA
48 15729184 soc 2.76 28.07 30.83 NA NA
49 5833450 soc 4.72 45.50 50.22 NA NA
50 10224564 soc 10.88 113.06 123.94 NA NA
51 5374686 soc 2.95 26.40 29.35 NA NA
52 17039618 soc 11.93 126.50 138.43 NA NA
53 13370034 soc 30.60 357.99 388.59 NA NA
tot 8712.23 4406.48 13118.71
------------------------------
John Smith
------------------------------
Original Message:
Sent: Wed December 08, 2021 10:05 AM
From: Art Kagel
Subject: flags (onstat -g ses)
John:
There can be several reasons for a session remaining in the "ready queue" (aka ready state) for several seconds. Here are the most common that I can think of off the top of my head:
- It is waiting for some resource like IO, a condition, a latch, a database lock (I believe you checked for this one)
- It is blocked on PDQ resources (only 100% of PDQ resources can be allocated, so if a session sets PDQPRIORITY to a level that exceeds the PDQ resources that are currently unused it will remain "ready" until another PDQ session releases resources). Run onstat -g mgm to see the MGM/PDQ resource utilization.
- There are more sessions and threads ready to process than the current CPU VPs can process in a timely manner. This can be because the cores are running at 100% utilization (ie 0% idle) or because there are not enough CPU VPs to take advantage of all of the CPU cycles available. Check "mpstat -P ALL 5 5 " on Linux and most UNIX's to see the utilization percent of each core and hardware thread (IB the option is different on AIX). Not sure how to do that on Windows (in case you are on Win).
Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
Original Message:
Sent: Wed December 08, 2021 09:47 AM
From: John Smith
Subject: flags (onstat -g ses)
Hello
According to times below (3:11:21 pm and 3:11:25 pm) with onstat -u and onstat -g ath to corresponding status for the flags ---P--- the state is ready ! but why it takes so long in this status "ready" abouy 5 seconds ? (captured every 3 seconds)
151111.txt:700000b50af0a38 ---PR-- 9207234 edi2 - 0 0 1 0 0
151114.txt:700000b50af0a38 ---PR-- 9207234 edi2 - 0 0 1 1 0
151118.txt:700000b50af0a38 ---PR-- 9207234 edi2 - 0 0 1 1 0
151121.txt:700000b50af0a38 ---P--- 9207234 edi2 - 0 0 1 1 0
151125.txt:700000b50af0a38 ---P--- 9207234 edi2 - 0 0 1 1 0
151128.txt:700000b50af0a38 ---PR-- 9207234 edi2 - 0 0 1 1 0
151111.txt: 10717990 700000c16f378b8 700000b50af0a38 1 ready 9cpu sqlexec
151114.txt: 10717990 700000c16f378b8 700000b50af0a38 1 ready 20cpu sqlexec
151118.txt: 10717990 700000c16f378b8 700000b50af0a38 1 running 15cpu sqlexec
151121.txt: 10717990 700000c16f378b8 700000b50af0a38 1 ready 14cpu sqlexec
151125.txt: 10717990 700000c16f378b8 700000b50af0a38 1 ready 20cpu sqlexec
151128.txt: 10717990 700000c16f378b8 700000b50af0a38 1 running 16cpu sqlexec
------------------------------
John Smith
Original Message:
Sent: Mon December 06, 2021 07:13 AM
From: Art Kagel
Subject: flags (onstat -g ses)
Hmm. So, it thinks that it is "running", ie doing something. Does the onstat -u line(s) for that session show any changes in the IO columns?
Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
Original Message:
Sent: Mon December 06, 2021 02:27 AM
From: John Smith
Subject: flags (onstat -g ses)
Hello
it"s always a sqlexec thread, status is running
tid name rstcb flags curstk status
6915001 sqlexec 700000bacbb2580 ---PR-- 7728 running-
-- here starts "---P---" for few seconds
tid name rstcb flags curstk status
6915302 sqlexec 700000c2ff1b150 ---P--- 10848 running-
tid name rstcb flags curstk status
6915001 sqlexec 700000bacbb2580 ---P--- 9760 running-
-- here "---P---" finishs
tid name rstcb flags curstk status
6915302 sqlexec 700000c2ff1b150 ---PR-- 7728 running-
tid name rstcb flags curstk status
6915302 sqlexec 700000c2ff1b150 ---PR-- 9760 running-
------------------------------
John Smith
Original Message:
Sent: Fri December 03, 2021 02:13 PM
From: Art Kagel
Subject: flags (onstat -g ses)
Run an onstat -g ath and look at the line for the thread (tid in the onstat -g ses <sid> report). You should see something like this:
1520 4a8b10d0 46bfb388 1 cond wait netnorm 8cpu sqlexec
This is an "sqlexec" thread, the main processing thread for a session. The status is "cond wait netnorm" that means that the thread is waiting for a network connected client to tell it what to do. It has finished whatever it was doing. Either it is between SQL statements waiting for the client to execute another, or it is in the middle of returning a large data set and waiting for the client to FETCH again. I don't remember the details of what the output looks like for a shared memory connection.
If the session is no longer there, which happens, for example, when a Windows app is close using the big "X" and doesn't trap that and close the connection to the database, the session will go away once the OS destroys the network connection when it times out. To get faster timeouts (they can take up to an hour on some OS's) set the keepalive setting in your SQLHOSTS file. Shared memory connections never timeout, but they also don't tend to hang when the client exits or crashes.
Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
Original Message:
Sent: Fri December 03, 2021 12:31 PM
From: Andreas Legner
Subject: flags (onstat -g ses)
That's the same set of flags as in 'onstat -u' I'd say.
For a session having multiple threads (PDQ), the 'P' thread would be the primary (sqlexec) one.
------------------------------
Andreas Legner
Original Message:
Sent: Fri December 03, 2021 10:32 AM
From: John Smith
Subject: flags (onstat -g ses)
Hello
i have some sessions with status "running" and flags "---P---" , not doing or waiting for anything, other sessions are on "---PR--" (reading)
what does "---P---" mean please ?
Thanks
------------------------------
John Smith
------------------------------
#Informix