SqlDbx Forum

SqlDbx

You are not logged in.

#1 2014-08-12 09:46:48

garbuya
Member

Suggestion: Admin-Processes improvement

It would be nice if this option displayed a little bit more information. For example, executed SQL, number of executed cursors, duration of transaction, number of processed rows, undo information, etc.

This is the fixed query I'm using to display some useful information in Oracle 11g. Maybe you can use it

SELECT
   TRIM ( SESS.OSUSER )                  "OS User"
  ,TRIM ( SESS.USERNAME )                "Ora User"
  ,NVL(TRIM(SESS.SCHEMANAME),'------')   "Schema"
  ,TRIM(TO_CHAR(SESS.SID))               "SID"
  ,NVL(VPX.QCSID, SESS.SID)        "Originated SID"
  ,TRIM(TO_CHAR(PRCS.SPID))              "Unix PID"
  ,TO_CHAR(NVL((SELECT MIN(V.LOGON_TIME) FROM V$SESSION V WHERE V.PLSQL_ENTRY_SUBPROGRAM_ID IS NOT NULL AND V.AUDSID=SESS.AUDSID),SESS.LOGON_TIME),'MM/DD HH24:MI:SS') "Sess Start"
  ,TO_CHAR(NVL((SELECT MIN(V.SQL_EXEC_START) FROM V$SESSION V WHERE V.SCHEMANAME IS NULL AND V.SQL_ADDRESS=SESS.SQL_ADDRESS),SESS.LOGON_TIME),'MM/DD HH24:MI:SS') "Exec Start"
  ,TO_CHAR(CAST(NUMTODSINTERVAL(SQLAREA.ELAPSED_TIME/1000000/GREATEST(1,SQLAREA.PX_SERVERS_EXECUTIONS),'SECOND') AS INTERVAL DAY(2) TO SECOND(3))) "Elapsed Time"
  ,TO_CHAR(CAST(NUMTODSINTERVAL(SQLAREA.CPU_TIME/1000000/GREATEST(1,SQLAREA.PX_SERVERS_EXECUTIONS),'SECOND') AS INTERVAL DAY(2) TO SECOND(3))) "CPU Time"
  ,TRIM(TO_CHAR(SQLAREA.EXECUTIONS,'999,999,999'))      "Execs"
  ,TRIM(TO_CHAR(SQLAREA.ROWS_PROCESSED,'999,999,999'))  "Rows"
  ,TRIM(TO_CHAR(TRAN.USED_UREC,'999,999,999'))          "Undo Recs"
  ,ROUND(SPCE.BYTES_USED/1024/1024,1)                   "Allocated Space MB"
  ,SQLAREA.MODULE                                       "Module"
  ,SESS.STATUS                                          "Session Status"
  ,TRAN.STATUS                                          "Transaction Status"
  ,SESS.WAIT_CLASS                                      "Wait Class"
  ,SESS.STATE                                           "Session State"
  ,SESS.EVENT                                           "Event"
  ,SQLAREA.SQL_FULLTEXT                                 "SQLText"
FROM    V$SESSION       SESS
INNER JOIN V$SQLAREA    SQLAREA
   ON SESS.SQL_ADDRESS     = SQLAREA.ADDRESS
INNER JOIN V$PROCESS  PRCS
   ON SESS.PADDR = PRCS.ADDR
LEFT JOIN (SELECT SORT.SESSION_ADDR
                 ,SORT.TABLESPACE
                 ,SUM (SORT.BLOCKS * TSPS.BLOCK_SIZE) BYTES_USED
           FROM V$SORT_USAGE SORT
               ,DBA_TABLESPACES TSPS
           WHERE SORT.TABLESPACE = TSPS.TABLESPACE_NAME
           GROUP BY SORT.SESSION_ADDR
                   ,SORT.TABLESPACE
         ) SPCE
  ON  SPCE.SESSION_ADDR  = SESS.SADDR
LEFT JOIN  V$TRANSACTION  TRAN
   ON  TRAN.ADDR         = SESS.TADDR
LEFT JOIN V$PX_SESSION VPX
   ON VPX.SID = SESS.SID
ORDER BY SESS.OSUSER
        ,SESS.USERNAME
        ,SESS.AUDSID
        ,SESS.OWNERID DESC
        ,NVL(SESS.SCHEMANAME,' ')
        ,SESS.SID
;

Last edited by garbuya (2016-01-06 17:01:45)

Offline

#2 2014-08-12 21:52:20

sqldbxhelp
Administrator

Re: Suggestion: Admin-Processes improvement

Looks useful, but fails in some instances for line:
  ,(SELECT V.SID FROM V$SESSION V WHERE V.PREV_SQL_ID IS NOT NULL AND V.AUDSID=SESS.AUDSID) "Originated SID"

Offline

#3 2014-08-13 09:20:49

garbuya
Member

Re: Suggestion: Admin-Processes improvement

It depends on version of Oracle

Offline

#4 2014-08-13 09:27:18

sqldbxhelp
Administrator

Re: Suggestion: Admin-Processes improvement

It does fails on 11g

Offline

#5 2015-07-19 16:47:23

garbuya
Member

Re: Suggestion: Admin-Processes improvement

Modifications:
Add to the end
left join V$PX_SESSION VPX
   ON vpx.sid = SESS.SID   

Select NVL(VPX.QCSID, SESS.sid)  "Originated SID"

Offline

#6 2015-07-20 11:42:25

sqldbxhelp
Administrator

Re: Suggestion: Admin-Processes improvement

Does not work consistently.
ORA-01427: single-row subquery returns more than one row

Offline

#7 2015-07-20 12:25:38

garbuya
Member

Re: Suggestion: Admin-Processes improvement

You need to replace
,(SELECT V.SID FROM V$SESSION V WHERE V.PREV_SQL_ID IS NOT NULL AND V.AUDSID=SESS.AUDSID) "Originated SID"
with
,NVL(VPX.QCSID, SESS.sid)   "Originated SID"

If in very rear case it returns dup keys, then just repeat this query

See the corrected query in my first post

Last edited by garbuya (2016-01-06 17:03:01)

Offline

Board footer

Powered by FluxBB