idle session consuming lots of cpu time, but doing nothing apparently

Hi Folks I am trying to identify any global perf issue on a system where users suddenly complain about a drastical loss of performance "with apparently NO CHANGE done on the system". This is about a series of heavy load tasks that, according to customer's 'feeling', took around 4 hours to complete 'before' and now 17 hours. I don't have any solid figures on those response times before but have to rely on 'impressions' Obviously, no one changed anything in the system's configuration, that I can believe, or not... Analyzing a full day of activity with bunches on onstat catches, I can see that I have an idle session starting at 9:20 AM and finishes by 12:30 PM that (according to sqltrace running during the whole period): does NOTHING, i.e no query at all caught by sqltrace. Nevertheless, onstat -g cpu for that session's sqlexec thread reports 258 secs of cpu time. Is this expected ??? tracking onstat -g ath for that thread every 5 minutes says cond wait norm, which is expected, but it is normal that the thread consumes that much time ? this is ifmx_14.10.FC2 red hat 6 VMWARE ESX in a government cloud ( not sure at all about how much dedicated are the system resources, for me all shared .... ) Any clue ? Eric Vercelletto Data Management Architect and Owner / Begooden IT Consulting KandooERP Founder and CTO IBM Champion 2013,2014,2015,2016,2017,2018,2019,2020 Tel: +33(0) 298 51 3210 Mob : +33(0)626 52 50 68 skype: begooden-it Google Hangout: eric.vercelletto@begooden-it.com Email: eric.vercelletto@begooden-it.com www : http://www.vercelletto.com www https://kandooerp.org #Informix

Hi Éric Have you been able to identify when during the life of this session the CPU seems to be consumed based on onstat -g cpu output ? Also did you check the overall system performance statistics for instance with nmon ? Maybe other processes on the server are using CPU or I/o resources or you could have memory issues. Regards Fabrice ------------------------------ Fabrice PLATEL ------------------------------

Is "Estimate Compression" thread activated ? I noticed a similar cpu consumption when the sysadmin task "Estimate Compression" was scanning all rows in all tables. In some versions this taks is enabled by default, even if you have a Workgroup license. Mit freundlichen Grüßen / Kind regards Gerd Kaluzinski DataOps Application Consultant IBM Cloud and Cognitive Software ------------------------------ IBM Deutschland Mies-van-der-Rohe-Straße 6 80807 München Phone: +49-175-228 1983 E-Mail: gerd.kaluzinski@de.ibm.com ------------------------------ IBM Deutschland GmbH Vorsitzender des Aufsichtsrats: Sebastian Krause Geschäftsführung: Gregor Pillen (Vorsitzender), Agnes Heftberger, Gabriele Schwarenthorer, Christian Noll, Nicole Reimer, Frank Theisen Sitz der Gesellschaft: Ehningen / Registergericht: Amtsgericht Stuttgart, HRB 14562 / WEEE-Reg.-Nr. DE 99369940

What have you got for NETTYPE settings and what protocol is this connection? ------------------------------ Jon Ritson ------------------------------

Hi JJ Those connections are tcp running on NET nettype Is there any remote possibility that sqltrace "forgets" catching all queries during close to 4 hours for one or 2 sessions among 40 ? I catch millions of queries during that lapse, but none for this session... Eric Vercelletto Data Management Architect and Owner / Begooden IT Consulting KandooERP Founder and CTO IBM Champion 2013,2014,2015,2016,2017,2018,2019,2020 Tel: +33(0) 298 51 3210 Mob : +33(0)626 52 50 68 skype: begooden-it Google Hangout: eric.vercelletto@begooden-it.com Email: eric.vercelletto@begooden-it.com www : http://www.vercelletto.com www https://kandooerp.org

Hi Eric, did you exclude memory swapping/paging on your system? Best Regards Henri ------------------------------ Henri Cujass leolo IT, CTO Germany IBM Champion 2021 ------------------------------

Hi Eric, I had the same problem two months ago with version 12.10FC14 in a Solaris virtual environment. I was able to find a multi sessions with a sql query that has the wait status, these sql used PDQ, and looked like they were waiting for each other because the session had not received a flag. I asked the developers to either lower the PDQ or stop using the PDQ in a sql, and then we got better performance. Note that the PDQ in the server is 40 and sql-PDQ = 5. onstat -g mgm shows which sessions use PDQ. It got better performance but the problem remains, when customer ran "update statistics medium force" against some table we got even better performance, but the day after when an auto stat was run during the night, so got worse performance, there was a defect on FC14 related to statistics so we upgraded to FC15, but it did not help, last week I disabled "AUTO_STAT_MODE 0" even though it is part of "AUTO_TUNE", and aus which is run via sysadmin table ph_task id (44,45), and we have a lot better performance and we run a manual "update statistics" have downloaded Arts dostats and will try to compile it in Solaris (hate Solaris :)). in addition to that, i have found some tables that have a large record size that do not have indexes that show high page readings. I found later the read and write for "cached" "BUFFERPOOL for 4K" is around 80%. I have not changed yet, but I am considering doubling the number of buffers. by the way it is rare for a system to be slow by itself, something must have changed :). hope this can be helpful for you, good luck. // Layth ------------------------------ Layth Yousif ------------------------------

I think the cloud platform is more likely. Once, I had an ER server with four nodes, and the transaction time was usually less than 10 milliseconds. But one night, the transaction response time of two nodes reached 40 milliseconds, and the other two nodes were OK. I studied it all night and it didn't work. Finally, migrate the two virtual machines with abnormal database to the completely idle host. During this period, the database is always in the online state. About 30 minutes after the migration, the transaction response time of the two ers also reached less than 10 milliseconds. ------------------------------ ZhiWei Cui GBASE ------------------------------