Maximo Open Forum

 View Only

  • 1.  Many sleeping threads in sys.dm_exec_sessions

    Posted 06-15-2023 05:55
    Edited by Deepa P 06-15-2023 09:37

    Hi Everyone,

    From past 2 days, database team has informed that there is huge cpu utilisation for maximo db. They have informed that many sleeping sessions they found for our maximo prod database which might be causing the issue.

    I executed the below query and found the same:

    Select* from sys.dm_exec_sessions where status like '%sleeping%' and open_transaction_count=1 and is_user_process=1 and login_name like '...'

    Mostly the records shows from maximo prod hostname and login_user is mxe.db.user.

    The db team wants us( application team) to find out why there are so many sleeping sessions from maximo db. Can anyone suggest what things should I check to find out what is actually causing these sleeping sessions?

    I feel like sessions are not getting closed after use.

    Please suggest 


    #Administration
    #Analytics
    #Architecture
    #Assets
    #CivilInfrastructure
    #Customizations
    #EndUser
    #EverythingMaximo
    #HSE/OilandGas
    #Infrastructure
    #Integrations
    #Inventory
    #IoT
    #LifeScience/Calibration
    #Linear
    #MaximoApplicationSuite
    #MaximoForAviation
    #MaximoUserGroups
    #Mobility
    #Nuclear
    #Procurement
    #Reporting
    #Scheduling
    #Security
    #ServiceProvider
    #Spatial
    #Transportation
    #Utilities
    #WorkCenters
    #WorkManagement

    ------------------------------
    Deepa P
    ------------------------------



  • 2.  RE: Many sleeping threads in sys.dm_exec_sessions

    Posted 06-16-2023 09:05

    Most likely users are not logging out correctly from their session.  In a web session, users will use the red "X" at the top right hand corner of the browzer instead of the icon that Maximo furnishes for this purpose.  This leaves active sessions on the server.



    ------------------------------
    Mark Fresa
    ------------------------------

    Original Message


  • 3.  RE: Many sleeping threads in sys.dm_exec_sessions

    Posted 06-16-2023 09:16

    Hi Mark,

    Thank you for your response.

    But why do I see login_name as the database username( that is defined in mxe.db.user) and not individual userid name?



    ------------------------------
    Deepa P
    ------------------------------

    Original Message


  • 4.  RE: Many sleeping threads in sys.dm_exec_sessions

    Posted 06-16-2023 09:53

    Check the maxsession table in the maximo schema.  That will give you the name of the user.




    Original Message


  • 5.  RE: Many sleeping threads in sys.dm_exec_sessions

    Posted 06-16-2023 10:28

    In a situation like that, I would want to know if there are any sessions that are blocking other sessions, what table(s) the blocking sessions are blocking on, and what processes create transactions against that table. You may have to kill these blocking sessions, losing whatever data changes they had been trying to make. Clearly, this is less than ideal.

    I would also review my scripts (and Java, if applicable) to make sure that every objSet = mxserver.getMboSet("OBJECTNAME", userInfo) (note the call being against MXServer, not an Mbo, and the passing of a UserInfo) has a corresponding objSet.close() and that any direct SQL connections acquired are freed.

    I haven't heard of Maximo locking up a SQL Server database in over a decade, so I would be suspecting the problem to be locally caused and remedied.



    ------------------------------
    Jason Uppenborn
    Cohesive
    ------------------------------

    Original Message


  • 6.  RE: Many sleeping threads in sys.dm_exec_sessions

    Posted 06-19-2023 10:17

    Hi Deepa,

    it sounds like the system has a number of connection leaks. I have seen this situation before.

    If the system is on Maximo 7.5.0.3 then there should be a new logger called :

    dbconnection

    switch that to INFO and then use the JVM as normal. It should start logging details about any database connections that have been leaked.

    I discuss this logger in this blog article:

    https://www.linkedin.com/pulse/db-connections-mbosets-understanding-which-piece-code-mark-robbins/

    If you are on later versions of 7.6 then the full list of leaked connections is logged when the JVM is shutdown.

    they will be logged to the Systemout.log file.

    you would need to submit those logs to IBM Support or get help from people who are experienced in dealing with them, like me.

    The best solution is to get the system patched to the latest Maximo fix pack. I, and others, have been reporting a lot of the DB connection leaks and IBM have been patching them so going to the latest fix pack will help.

    you said this:

    "But why do I see login_name as the database username( that is defined in mxe.db.user) and not individual userid name?" - this is because Maximo creates a pool of DB connections using the user defined in there. Any single connection in the pool can be used by multiple users over time.

    Mark F mentioned users not closing their sessions properly - that creates a different problem that i discuss in this article - https://www.linkedin.com/pulse/maximo-users-login-counts-accidentally-overloading-jvm-mark-robbins/

    That is unlikely to be directly causing the sleeping database sessions that you refer to.



    ------------------------------
    mark robbins
    Cohesive
    IBM Champion 2017-2023 Inclusive
    See my blog on Maximo support related topics here:
    https://www.linkedin.com/pulse/maximo-support-advice-from-non-ibm-engineer-article-mark-robbins/
    ------------------------------

    Original Message


Related Content