Home » RDBMS Server » Performance Tuning » Run time versus Elapsed time (Oracle 9.2.0.8)
Run time versus Elapsed time [message #348694] Wed, 17 September 2008 11:41 Go to next message
shrinika
Messages: 266
Registered: April 2008
Senior Member
Hello All,

My below query display two columns.

1. RUN_TIME_MINUTS (time between transaction start time
and commit time).

2. ELAPSED_TIME_MINUTS(Actual time used to run the transaction.)

In the query output, the last record, Elapsed time is 7.23103082 seconds. The run time is 3.65 seconds.
The elapsed time should be always less then the run time. But it is not here.

Am i missing some thing here?
Can any one help me to understand?


Wrote file afiedt.buf

  1  select
  2  to_number(sysdate-to_date(v$transaction.start_time,'MM/DD/YY HH24:MI:SS'))*24*60 run_time_Minut
  3  (elapsed_time/1000000)/60 elapsed_time_Minuts
  4  from v$transaction ,
  5  V$SESSION ,
  6  V$SQLAREA
  7  WHERE( (V$SESSION.SQL_ADDRESS = V$SQLAREA.ADDRESS
  8  and V$SESSION.sql_hash_value = V$SQLAREA.HASH_VALUE )
  9  OR ( V$SESSION.PREV_SQL_ADDR = V$SQLAREA.ADDRESS
 10  and V$SESSION.PREV_hash_value = V$SQLAREA.HASH_VALUE ))
 11  and v$transaction.ses_addr = V$SESSION.saddr
 12* and sysdate-to_date(v$transaction.start_time,'MM/DD/YY HH24:MI:SS') > 2/(60*24)
SQL> /

RUN_TIME_MINUTS ELAPSED_TIME_MINUTS
--------------- -------------------
        1382.75           .00739915
     1682.38333          .000582317
     1682.38333            .0036056
     620.583333          .000582317
     3.03333333          .184708567
           3.65          7.23103082

6 rows selected.

SQL> 
Re: Run time versus Elapsed time [message #348704 is a reply to message #348694] Wed, 17 September 2008 13:23 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
I have a couple of general comments.

1) around lines #2 & #3 the SQL appears it might have been truncated.

2) I am not sure how Oracle actually maintains ELAPSED_TIME in V$SQLAREA, but I do believe that a SQL can be shared/used by multiple sessions.
I suspect that ELAPSED_TIME is NOT session specific.

[Updated on: Wed, 17 September 2008 13:23] by Moderator

Report message to a moderator

Re: Run time versus Elapsed time [message #348723 is a reply to message #348704] Wed, 17 September 2008 15:40 Go to previous message
shrinika
Messages: 266
Registered: April 2008
Senior Member
anacedent,
around lines #2 & #3 the SQL appears it might have been truncated.


I checked the v$transaction.start_time in the database. The date field is not truncated.

Yes. you are right. The v$sqlarea.elapsed_time is not session specific. I also have the same suspection.

Thanks for the clarification.
Previous Topic: Index rebuild
Next Topic: Performance Test: IS NOT NULL check
Goto Forum:
  


Current Time: Fri Dec 09 02:04:05 CST 2016

Total time taken to generate the page: 0.05215 seconds