SqlDbx
You are not logged in.
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
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
It depends on version of Oracle
Offline
It does fails on 11g
Offline
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
Does not work consistently.
ORA-01427: single-row subquery returns more than one row
Offline
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