【openGauss】openGauss查询实时会话信息

---

一、【openGauss】openGauss查询实时会话信息

/* openGauss查询实时会话信息 */
SELECT pid
      ,a.sessionid "sessionId"
      ,datname "db_name"
      ,client_addr "clientAddress"
      ,usename "userName"
      ,to_char(xact_start ::TIMESTAMP(0), 'YYYY-MM-DD HH24:MI:SS') "transactionStartTime"
      ,to_char(query_start ::TIMESTAMP(0), 'YYYY-MM-DD HH24:MI:SS') "queryStart"
      ,round(extract(epoch FROM((clock_timestamp() - xact_start)))) "transactionRunningTime"
      ,CASE
         WHEN waiting = 'f' THEN ''
         ELSE waiting
       END AS waiting
      ,wait_event
      ,w.block_sessionid "blockSessionId"
      ,state
      ,a.query
      ,application_name "appName"
      ,a.unique_sql_id "uniqueSqlId"
      ,to_char(a.backend_start ::TIMESTAMP(0), 'YYYY-MM-DD HH24:MI:SS') "backendStart"
      ,coalesce(m.used_mem, 0) "usedMem"
  FROM pg_stat_activity a
 INNER JOIN pg_thread_wait_status w
    ON (a.sessionid = w.sessionid)
  LEFT JOIN gs_session_memory m
    ON (split_part(m.sessid, '.', 2) = a.sessionid)
 WHERE a.pid <> pg_backend_pid()
   AND a.client_addr IS NOT NULL
   AND a.usename NOT IN ('hisAdmin','monitor','root','dbAdmin'
                        ,'eidAdmin'
                        ,'hisadmin'
                        ,'omm'
                        ,'Ruby'
                        ,'rdsAdmin'
                        ,'__rds_pg_profile_user_'
                        ,'system user'
                        ,'slave'
                        ,'event_scheduler'
                        ,'mysql_backup'
                        ,'rdsRepl'
                        ,'rdsHa'
                        ,'rdsMetric'
                        ,'rdsBackup')
 ORDER BY xact_start;