Re: Multiple user threads

On Thu, 15 Jul 2004 11:58:49 -0400, Bjorn De Waele wrote: PDQ threads assigned to a session usually are not released. The session keeps them assigned in case they are needed again. I suppose this cuts overhead in heavily PDQ dependent sessions. It should be harmless. However, if the PDQ is the result of procedure compilation and you normally run with PDQPRIORITY 0, then sessions using the procedures are using more resources than you counted on, which is a different problem. I see you found clients setting PDQPRIORITY 5, that would explain the threads, yes. Art S. Kagel > Hello Art, > > thanks a lot for your answer yesterday. Right now it works fine. We have > running a ci process (to capture data from terminals in production). This > process does a lot of update, inserts and selects. If I enable pdqpriority 5 > then I still have very much user threads from this user. This isn't that > annoying but the strange thing is (we're working in our test environment) > that all to threads stay open for a very long time even after we don't send > data to the database. I'm just afraid that this will result in problems in > future. If we do a phew new queries, there are still many threads open but > we receive and update our data well towards the database. > > Do you have an explanation for this behaviour ? I see also that many off the > threads are waiting for locks(onstat -k). > > Thanks in advance, > > Bjorn > > "Art S. Kagel" <kagel@bloomberg.net> wrote in message > news:pan.2004.07.15.08.04.27.629024.1355@bloomberg.net... >> On Wed, 14 Jul 2004 17:32:28 -0400, Bjorn De Waele wrote: >> >> The system catalog's stored procedure header table is sysprocedures, the > body >> of the proc source, the compiled byte code, and the query plan byte code > are >> all stored in sysprocbody with different values in the datakey column. >> However, you can just use: >> dbschema -d mydatabase -f [all|<procname>] and similarly if you prefer >> using myschema: myschema -d mydatabase -f [[all|*]|[<procspec>|<procname>]>> (procspec uses MATCHES style wildcarding). FYI, there is no indication of >> what the PDQPRIORITY was at compile time either in the dbschema/myschema >> output or in the system catalog tables. >> >> Art S. Kagel >> >> > Hello Art, >> > >> > thank your for your answer. I'll check tomorrow if there are any stored >> > procedures. >> > >> > I can't use my winsql tool right now. Do you know in which table I can > see >> > my stored procs ? >> > >> > Thanks, >> > >> > Bjorn >> > >> > >> > "Art S. Kagel" <kagel@bloomberg.net> wrote in message >> > news:pan.2004.07.14.16.06.43.735485.1355@bloomberg.net... >> >> On Wed, 14 Jul 2004 12:07:01 -0400, Bjorn De Waele wrote: >> >> >> >> This mystery is usually caused by having compiled stored procedures > with a >> >> non-zero PDQPRIORITY in effect. This compiles in a run-time > PDQPRIORITY >> > for >> >> the procedure which you likely do not want. Recompile all stored >> > procedures >> >> (UPDATE STATISTICS FOR PROCEDURE procname;) in a session with > PDQPRIORITY >> > set >> >> to zero and this effect should disappear. >> >> >> >> This is why my dostats utility has separate flags to set PDQPRIORITY > for >> >> updating stats for tables and for compiling stored procedures. >> >> >> >> Art S. Kagel >> >> >> >> > Hello everybody, >> >> > >> >> > >> >> > we're using version 9.40.T7 >> >> > Our max_pdqpriority is 100. >> >> > >> >> > Right now, I see much this behaviour with onstat -u. Informix creates >> > plenty >> >> > of user threads. If I set with the max_pdqpriority with onmode -D to > 0. >> >> > Then I get a single user session which shows me sometimes 5 lock, >> > sometimes >> >> > 20 (while queries are running). >> >> > The weird thing is that I don't use set pdqpriority on client side. >> > We're >> >> > finetuning our server and playing around with the settings. I'm only >> > afraid >> >> > that those threads stay open (as I can see, I see lots of cond >> > waits(mc_1) >> >> > etc). And that they can affect performance. Is this a normal > behaviour >> > ? Do >> >> > you need additional stats ? I also see locks but I already received > all >> > my >> >> > data. >> >> > >> >> > Thanks, >> >> > >> >> > Bjorn >> >> > >> >> > onstat -u output.>> >> > >> >> > >> >> > 3fa9e224 Y------ 84 informix NB070148 40a498b8 0 1 0 >> > 0 >> >> > 3fa9e838 Y--P--- 70 informix FSYS326 40f17380 0 1 33 >> > 0 >> >> > 3faa1eec Y------ 84 informix NB070148 410d0018 0 1 0 >> > 0 >> >> > 3faa373c Y------ 84 informix NB070148 40fa81a8 0 1 0 >> > 0 >> >> > 3faa4978 Y------ 84 informix NB070148 408dc480 0 1 0 >> > 0 >> >> > 3faa61c8 Y------ 84 informix NB070148 40892228 0 1 0 >> > 0 >> >> > 413f3018 Y------ 84 informix NB070148 41787c80 0 1 0 >> > 0 >> >> > 413f362c Y------ 84 informix NB070148 40a49650 0 1 0 >> > 0 >> >> > 413f4e7c Y------ 84 informix NB070148 40892228 0 1 0 >> > 0 >> >> > 413f5aa4 Y------ 84 informix NB070148 41c966e8 0 1 0 >> > 0 >> >> > 413f60b8 Y------ 84 informix NB070148 408dc480 0 1 0 >> > 0 >> >> > 413f66cc Y------ 84 informix NB070148 410d0018 0 1 0 >> > 0 >> >> > 413f6ce0 Y------ 84 informix NB070148 40a49650 0 1 0 >> > 0 >> >> > 413f7908 Y--P--- 83 informix NB070148 4163bc58 0 1 1514 >> > 0 >> >> > 413f7f1c Y------ 84 informix NB070148 410d0638 0 1 0 >> > 0 >> >> > 413f8530 Y------ 84 informix NB070148 3fd31ee0 0 1 0 >> > 0 >> >> > 413f8b44 Y--P--- 84 informix NB070148 41843920 0 1 3 >> > 70 >> >> > 413f9158 Y------ 84 informix NB070148 40efc4d8 0 1 0 >> > 0 >> >> > 413f976c Y------ 84 informix NB070148 40a496f8 0 1 0 >> > 0 >> >> > 413f9d80 Y------ 84 informix NB070148 40a496f8 0 1 0 >> > 0 >> >> > 413fafbc Y------ 84 informix NB070148 40a496f8 0 1 0 >> > 0 >> >> > 413fc1f8 Y------ 84 informix NB070148 3fd31f88 0 1 0 >> > 0 >> >> > 413fda48 Y------ 84 informix NB070148 40a496f8 0 1 0 >> > 0 >> >> > 413ffec0 Y------ 84 informix NB070148 419dc7e8 0 1 0 >> > 0 >> >> > 414010fc Y------ 84 informix NB070148 41787cd0 0 1 0 >> > 0 >> >> > 41401d24 Y------ 84 informix NB070148 40849f08 0 1 0 >> > 0 >> >> > 41402f60 Y------ 84 informix NB070148 408dc480 0 1 0 >> > 0 >> >> > 41403574 Y------ 84 informix NB070148 41136b58 0 1 0 >> > 0 >> >> > 414047b0 Y------ 84 informix NB070148 410d0638 0 1 0 >> > 0 >> >> > 414096b4 Y------ 84 informix NB070148 40a496f8 0 1 0 >> > 0 >> >> > 4140d37c Y------ 84 informix NB070148 40a496f8 0 1 0 >> > 0 >> >> > 4140dfa4 Y------ 84 informix NB070148 41136b58 0 1 0 >> > 0