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

Primary thread for the session https://www.oninit.com/onstat/pda.php?id=onstat%20-g%20ses Cheers Paul Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC

Yes already checked, but i don"t where others flags are "-" !! there s only the 4th position "P" , so i'm wondering what is the session doing !! ------------------------------ John Smith ------------------------------

What does the stack show, AFAIK it is no 'legal' to have a session without a thread Cheers Paul Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC

I think the question is that is the flags show that there is a primary connection, but the session is not reading, not writing, and not waiting on a condition...then what's it doing? I think that you find that the session is sleeping. If you run onstat -g ses <sid>, and look at the thread, it is likely to show "sleeping" - at least that's what I am seeing. The dbworker thread seems to be in that state, which makes sense. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

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 ------------------------------

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 ------------------------------

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 ------------------------------

  What does the stack show ? Cheers Paul Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC

   What does the stack show ? Cheers Paul Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC

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 ------------------------------

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 ------------------------------

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 ------------------------------

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 gloIndividual 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 ------------------------------

John: So, the smoking gun may be the "PDQ is set to 10%". That may cause sessions to go into a wait state that is not documented by any flags in onstat. The onstat -g mgm report will tell the tale. Here's the thing to remember about PDQ. It controls the percentage of PDQ resources that are assigned to a session. Obviously there are only 100% available. However, that's not the whole story. Just because all sessions run at PDQPRIORITY 10 doesn't necessarily mean that 10 such sessions can run concurrently. Yes, it does mean that no more than 10 such sessions can run concurrently, but other factors may limit that to fewer than 10. Here are the limiting factors: PDQ 10 means that 10% of the CPU VPs are assigned to that session. If you have only 5 CPU VPs then only 5 such sessions can run because each one will get one CPU VP assigned to it. You do have 20 CPU VPs, so each PDQ 10 session will get 2 VPs assigned to it. No limit in your case (but note that PDQ values lower than 5 will not allow more than 20 such sessions to run at the same time). MGM memory is allocated in blocks of memory known as "quanta". The number of quanta is controlled by DS_MAX_QUERIES. If that is set to fewer than 10 then fewer than 10 session can run at the same time regardless of what their PDQPRIORITY settings are. For this reason I always set DS_MAX_QUERIES to 100. If DS_MAX_QUERIES is set to 100 then each quanta contains 1% of DS_TOTAL_MEMORY and a session with PDQ 10 will be allocated 10 quanta of memory. So, perhaps your setting for DS_MAX_QUERIES is set such that it is limiting you to fewer than 10 concurrent PDQ positive settings (the number of CPU VPs and the DS_MAX_QUERIES do not affect PDQ 0 sessions). Or, perhaps you have more than 10 concurrently running sessions with PDQ 10 set attempting to run so one or more of them has to wait! Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Art is correct that AIX is different; it doesn't support the –P option. You can emulate it by doing: ``` mpstat 5 5 | egrep "cpu|ALL" ```

Thanks Tom! ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

I should note that what I wrote is true for AIX 6.1; not sure if it has changed in newer versions.

How do I find that this thread is waiting for IO, wait condition, wait latch or database lock ------------------------------ ZhiWei Cui GBASE ShiJiaZhuang ------------------------------

OK, to track locks, latches, or waits, start be determining the thread address(es) for the session of interest with onstat -u. That's the first column in the onstat -u report. You can filter on session id in the third column: onstat -u |fgrep 1412346bf98a0 ---P--- 14123 informix - 0 0 2 352 101 Then, to find locks held by that session: $ onstat -k|fgrep46bf98a0 44199358 0 46bf98a0 0 S 100002 204 0 sysmaster:informix.sysdatabases 44199688 0 46bf98a0 44199358 HDR+S 100002 201 0 sysmaster:informix.sysdatabases If the session is waiting for a lock then the thread address will appear in the 2nd column instead of in the third column and the third column will be the thread address of a thread in the session that is holding the lock which you can look up in onstat -u again. As for waiting on conditions or mutexes, use onstat -g wai and look for the same address in the rstcb (3rd) column of that report: $ onstat -g wai|fgrep46c00d38 365 488faa58 46c00d38 1 cond wait netnorm 8cpu sqlexec The above is a user session (name = "sqlexec") waiting on the "netnorm" condition which means that it is waiting for the user session to make a request (new SQL, fetch, etc.). This is an ER thread waiting for a mutex: 61 485e5028 46bfe060 3 mutex wait RingBuf_t: 8cpu CDRGeval0 You can look up conditions with onstat -g con and mutexes with onstat -g lmx ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------