Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Location
    Kansas, USA
    Posts
    12

    Unanswered: Elapsed time for a particular query

    Hello all,

    My question is whether anyone might know how to find out how long a particular query has been running, or its start time. Note that this is not the same thing as the time the session began, or connected to the database. Consider a session that stays persistent and executes many queries over its lifetime. I'm trying to find out the execution, or start time for a particular query executing by this session. I'm looking for a data dictionary solution, not one that involves debugging from the application side, as this is a 3rd party application.

    Thanks very much,

    -Adam vonNieda

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    v$sqlarea
    CPU_TIME
    ELAPSED_TIME


    v$sql
    CPU_TIME
    ELAPSED_TIME
    Last edited by The_Duck; 09-09-03 at 11:13.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jul 2003
    Location
    Kansas, USA
    Posts
    12
    Duck,

    Thanks for the reply.

    Let's consider a query that has executed 500 times with exactly the same SQL, therefore having one copy in v$sql / v$sql_area. ELAPSED_TIME and CPU_TIME are going to show cumalative values.

    -Adam

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    for the average you could divide by 500

    anyways, I would suggest turning sql_trace/timed_statistics on for that session, run tkprof on the trace-file and you will get accurate timing for each query run.

    if you need more data on how to do that for an active session, let me know.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jul 2003
    Location
    Kansas, USA
    Posts
    12
    Thanks anyway. I'm looking for an accurate solution via the data dictionary, maybe there isn't one at this time.

    -Adam

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    match sid/session you want with v$session_longops ??
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Jul 2003
    Location
    Kansas, USA
    Posts
    12
    Looks like LAST_CALL_ET in V$SESSION might be the ticket. I've used it in the past to measure how long sessions have been inactive, but looks like it will show the elapsed time since the last query (last call) began as well.

    Regards,

    -Adam

  8. #8
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    This is something I use to monitor session idle time
    --
    --Monitor Session Idle Time
    --
    col sid format 999
    col username format a10 truncated
    col status format a1 truncated
    col logon format a17
    col idle format a9
    col program format a30 truncated

    select
    sid,username,status,
    to_char(logon_time,'dd-mm-yy hh:mi:ss') "LOGON",
    floor(last_call_et/3600)||':'||
    floor(mod(last_call_et,3600)/60)||':'||
    mod(mod(last_call_et,3600),60) "IDLE",
    program
    from
    v_$session
    where
    type='USER'
    order by last_call_et;

    ----- This gives a little more info on the inactive user ---
    select logon_time, last_call_et "time inactive", substr(s.username,1,15) username,
    substr(s.machine,1,15), substr(s.program,1,20), s.sid session_id, s.status,
    substr(sql_text,1,75), ss.value "CPU used",
    trunc(buffer_gets/(executions+1)) "BUFF-EXEC",
    trunc(buffer_gets/(rows_processed+1)) "BUFF-ROWS",first_load_time, executions, parse_calls,
    disk_reads, buffer_gets, rows_processed
    from v$session s,
    v$sesstat ss,
    v$statname sn,
    v$sqlarea sa
    where s.sid = ss.sid
    and s.username is not null
    and ss.statistic# = sn.statistic#
    and sn.name = 'CPU used by this session'
    and s.sql_address = sa.address
    and s.sql_hash_value = sa.hash_value and last_call_et > 7200
    order by last_call_et asc;

    -------- This gives me info on a long running query ---
    set linesize 132
    set pages 200
    col pct_done for 999.00
    col username for a15
    col MINS_REMAINING for 999,999.00
    select sid, serial#, username,
    to_char(start_time, 'hh24:mi:ss') start_time,
    SOFAR, TOTALWORK,
    (SOFAR/TOTALWORK) * 100 pct_done,
    time_remaining/60 MINS_REMAINING,
    to_char(last_update_time, 'hh24:mi:ss') last_update
    from V$SESSION_LONGOPS
    where time_remaining > 0
    ;
    hth
    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •