ORACLE SESSION Monitoring SQL

  SELECT   ROUND (BITAND (s.ownerid, 65535)) parent_session_sid,
           ROUND (BITAND (s.ownerid, 16711680) / 65536) parent_session_instid,
           s.INST_ID,
           s.SADDR,
           s.SID,
           s.SERIAL#,
           s.AUDSID,
           s.PADDR,
           s.USER#,
           s.USERNAME,
           s.COMMAND,
           s.OWNERID,
           s.TADDR,
           s.LOCKWAIT,
           s.STATUS,
           s.SERVER,
           s.SCHEMA#,
           s.SCHEMANAME,
           s.OSUSER,
           s.PROCESS,
           s.MACHINE,
           s.PORT,
           s.TERMINAL,
           UPPER (s.PROGRAM) PROGRAM,
           s.TYPE,
           s.SQL_ADDRESS,
           s.SQL_HASH_VALUE,
           s.SQL_ID,
           s.SQL_CHILD_NUMBER,
           s.PREV_SQL_ADDR,
           s.PREV_HASH_VALUE,
           s.PREV_SQL_ID,
           s.PREV_CHILD_NUMBER,
           s.PLSQL_ENTRY_OBJECT_ID,
           s.PLSQL_ENTRY_SUBPROGRAM_ID,
           s.PLSQL_OBJECT_ID,
           s.PLSQL_SUBPROGRAM_ID,
           s.MODULE,
           s.MODULE_HASH,
           s.ACTION,
           s.ACTION_HASH,
           s.CLIENT_INFO,
           s.FIXED_TABLE_SEQUENCE,
           s.ROW_WAIT_OBJ#,
           s.ROW_WAIT_FILE#,
           s.ROW_WAIT_BLOCK#,
           s.ROW_WAIT_ROW#,
           s.LOGON_TIME,
           s.LAST_CALL_ET,
           s.PDML_ENABLED,
           s.FAILOVER_TYPE,
           s.FAILOVER_METHOD,
           s.FAILED_OVER,
           s.RESOURCE_CONSUMER_GROUP,
           s.PDML_STATUS,
           s.PDDL_STATUS,
           s.PQ_STATUS,
           s.CURRENT_QUEUE_DURATION,
           s.CLIENT_IDENTIFIER,
           s.BLOCKING_SESSION_STATUS,
           s.BLOCKING_INSTANCE,
           s.BLOCKING_SESSION,
           s.SEQ#,
           s.EVENT#,
           s.EVENT,
           s.P1TEXT,
           s.P1,
           s.P1RAW,
           s.P2TEXT,
           s.P2,
           s.P2RAW,
           s.P3TEXT,
           s.P3,
           s.P3RAW,
           s.WAIT_CLASS_ID,
           s.WAIT_CLASS#,
           s.WAIT_CLASS,
           s.WAIT_TIME,
           s.SECONDS_IN_WAIT,
           s.STATE,
           s.SERVICE_NAME,
           s.SQL_TRACE,
           s.SQL_TRACE_WAITS,
           s.SQL_TRACE_BINDS,
           s.ECID,
           stat.cpu - stat.CPU_this_call_start cpu_this_call,
           stat.CPU,
           stat.UGA_memory,
           stat.PGA_memory,
           stat.Commits,
           stat.Rollbacks
    FROM   GV$SESSION S,
           (  SELECT   ss.inst_id stat_inst_id,
                       ss.sid stat_sid,
                       SUM(DECODE (sn.name,
                                   'CPU used when call started', ss.VALUE,
                                   0))
                          CPU_this_call_start,
                       SUM(DECODE (sn.name,
                                   'CPU used by this session', ss.VALUE,
                                   0))
                          CPU,
                       SUM (DECODE (sn.name, 'session uga memory', ss.VALUE, 0))
                          uga_memory,
                       SUM (DECODE (sn.name, 'session pga memory', ss.VALUE, 0))
                          pga_memory,
                       SUM (DECODE (sn.name, 'user commits', ss.VALUE, 0))
                          commits,
                       SUM (DECODE (sn.name, 'user rollbacks', ss.VALUE, 0))
                          rollbacks
                FROM   GV$sesstat ss, GV$statname sn
               WHERE   ss.STATISTIC# = sn.STATISTIC# AND ss.inst_id = sn.inst_id
                       AND (   sn.name = 'CPU used when call started'
                            OR sn.name = 'CPU used by this session'
                            OR sn.name = 'session uga memory'
                            OR sn.name = 'session pga memory'
                            OR sn.name = 'user commits'
                            OR sn.name = 'user rollbacks')
            GROUP BY   ss.inst_id, ss.sid) stat
   WHERE   (    (s.USERNAME IS NOT NULL)
            AND (NVL (s.osuser, 'x') <> 'SYSTEM')
            AND (s.TYPE <> 'BACKGROUND'))
           AND ( (stat.stat_inst_id = s.inst_id) AND (stat.stat_sid = s.sid))
ORDER BY   "PROGRAM", OWNERID