OraFAQ Forum: Performance Tuning » Run time versus Elapsed time

Home » RDBMS Server » Performance Tuning » Run time versus Elapsed time (Oracle 9.2.0.8)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Run time versus Elapsed time [message #348694]

Wed, 17 September 2008 11:41

shrinika
Messages: 306
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>

Report message to a moderator

Re: Run time versus Elapsed time [message #348704 is a reply to message #348694]

Wed, 17 September 2008 13:23

BlackSwan
Messages: 26766
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

shrinika
Messages: 306
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.

Report message to a moderator

Previous Topic:	Index rebuild
Next Topic:	Performance Test: IS NOT NULL check

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 09:40:13 CST 2024