CODEWITHSUNDEEP
sqloracle-database
Andrew Hicks
Andrew Hicks

Reputation: 642

What is the best way to find the current query time of active queries for a specific user/machine in Oracle SQL Developer for all running queries?

I know how to find out what queries I have running by OSUSER but I am having trouble getting the logic to work out for developing a elapsed time field and could use some help. Here is what I have been looking at:

CREATE OR REPLACE 

MONITOR_ACTIVE_QUERIES VIEW AS 
SELECT 
  USERNAME
, SCHEMANAME
, OSUSER
, MACHINE
, PROGRAM
, SID
, SERIAL#
, TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') AS CurrentTime
, FIRST_LOAD_TIME AS LoadTime
--, USER_IO_WAIT_TIME << not really sure what this is.
, SQL_TEXT

FROM V$SESSION a JOIN V$SQL b
ON a.SQL_ADDRESS = b.ADDRESS
WHERE a.OSUSER IN ('<USER1>', '<USER2>','<USER3>')
AND a.STATUS = 'ACTIVE'

I want to do something where I can take the logic of CurrentTime - LoadTime to calculate a RunTime but keep running into issues. It appears to be data type driven.

I did try this for the CurrentTime: TO_DATE((

SELECT MM || '-' || DD || '-' || YYYY || ' ' || HH24 || ':' || MI || ':' || SS 
   FROM (
    SELECT
      EXTRACT(MONTH FROM SYSTIMESTAMP) AS MM
    , EXTRACT(DAY FROM SYSTIMESTAMP) AS DD
    , EXTRACT(YEAR FROM SYSTIMESTAMP) AS YYYY
    , EXTRACT(HOUR FROM SYSTIMESTAMP) AS HH24
    , EXTRACT(MINUTE FROM SYSTIMESTAMP) AS MI
    , ROUND(EXTRACT(SECOND FROM SYSTIMESTAMP),0) AS SS
    FROM DUAL)),'MM-DD-YYYY HH24:MI:SS') AS CurrentTime

And this for the LoadTime:

SUBSTR(FIRST_LOAD_TIME,6,5) || '-' || SUBSTR(FIRST_LOAD_TIME,0,4) || ' ' || SUBSTR(FIRST_LOAD_TIME,12,8) AS LoadTime

I have even tried to be clever with TO_DATE and TO_CHAR functions, but I still can't figure it out. Anyone have any clever ideas?

Upvotes: 0

Views: 901

Answers (4)

Andrew Hicks
Andrew Hicks

Reputation: 642

I actually found a better way to do this that presents it in time units. See below:

CREATE OR REPLACE VIEW MONITOR_ACTIVE_QUERIES AS
--ALTER SESSION SET NLS_date_format =  'YYYY-MM-DD HH24:MI:SS';
SELECT
  USERNAME
, SCHEMANAME
, OSUSER
, MACHINE
, PROGRAM
, SID
, SERIAL#
, TO_DATE(REPLACE(FIRST_LOAD_TIME, '/', ' '), 'YYYY-MM-DD HH24:MI:SS') AS LoadTime
, lpad(TO_CHAR(TRUNC(24*(sysdate-TO_DATE(REPLACE(FIRST_LOAD_TIME, '/', ' '), 'YYYY-MM-DD HH24:MI:SS'))) ) || TO_CHAR(TRUNC(sysdate) + (sysdate-TO_DATE(REPLACE(FIRST_LOAD_TIME, '/', ' '), 'YYYY-MM-DD HH24:MI:SS')) , ':MI:SS' ) , 10, ' ') AS RunTime
, SQL_TEXT

FROM v$session a JOIN v$sql b
ON a.SQL_ADDRESS = b.ADDRESS

LEFT JOIN (
SELECT SQL_ADDRESS, logon_time
FROM gv$session) s
ON a.SQL_ADDRESS = s.SQL_ADDRESS

WHERE a.OSUSER IN ('<User1>', '<User2>','<User3>')
AND a.STATUS = 'ACTIVE'
ORDER BY RunTime;

--SELECT * FROM MONITOR_ACTIVE_QUERIES;

Upvotes: 0

Andrew Hicks
Andrew Hicks

Reputation: 642

Thank you psaraj12! That helped. I ran your code and it still wasn't working but the comment on NLS_DATE_FORMAT reminded me when I last altered the session to not induce time which I need to get a delta between the two. Running this (see below) resolved the issue. 

ALTER SESSION SET NLS_date_format =  'YYYY-MM-DD HH24:MI:SS';

SELECT 
  USERNAME
, SCHEMANAME
, OSUSER
, MACHINE
, PROGRAM
, SID
, SERIAL#
, TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS CurrentTime
, FIRST_LOAD_TIME                           AS LoadTime
, ROUND((TO_DATE(To_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') - TO_DATE(REPLACE(FIRST_LOAD_TIME, '/', ' '), 'YYYY-MM-DD HH24:MI:SS'))*24*60,2) RunTime
, SQL_TEXT

FROM v$session a JOIN v$sql b
ON a.SQL_ADDRESS = b.ADDRESS
WHERE a.OSUSER IN ('<User1>', '<User2>','<User3>')
AND a.STATUS = 'ACTIVE'

There is still an issue with the format of the RunTime. It is show up as a "1.94" format meaning 194 seconds of run time rather then a "1.34" for 1 minute and 34 seconds format, but I can live with that for now.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You can convert the load time to a date as:

to_date(first_load_time, 'YYYY-MM-DD/HH24:MI:SS')

I'm not sure how you want to represent the difference in time, but you can use:

(sysdate - to_date(first_load_time, 'YYYY-MM-DD/HH24:MI:SS'))

should work.

Upvotes: 1

psaraj12
psaraj12

Reputation: 5072

The below worked for me,You need to do something similar based on your NLS_DATE_FORMAT which will give the running time in minutes

        SELECT username,
           schemaname,
           osuser,
           machine,
           program,
           sid,
           serial#,
           To_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS CurrentTime,
           first_load_time                           AS LoadTime,
           (To_date(To_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),
           'YYYY-MM-DD HH24:MI:SS')
           - To_date(Replace(first_load_time, '/', ' '), 'YYYY-MM-DD HH24:MI:SS')*24*60
                                                     running_time,
           sql_text
    FROM   v$session a
           join v$sql b
             ON a.sql_address = b.address
    WHERE  a.osuser IS NOT NULL
           AND a.status = 'ACTIVE'

Upvotes: 1

Related Questions