Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

The Meaning of elapsed time in sql

784287Aug 24 2010 — edited Aug 24 2010
hi all

A simple question about elapsed time in v$sql.

I know it means that a total time when a query is executing.

I think that

elapsed time = cpu time + user i/o wait time + application_wait_time
+ concurrency_wait_time + cluster_wait_time + plsql_exec_time + java_exec_time

of course elapsed time couldn't be exactly same with a sum of above stuff, because its value is accumulated value.

but i have some weird data. its value is bigger 130% than the sum. i have no idea where other 30% comes from.

its value is too big to ignore.

does anybody have a idea?

(it is single core environment)

thanks in advance..
This post has been answered by Salman Qureshi on Aug 24 2010
Jump to Answer

Comments

618702
Dear JoeMae,

Please see v$sql;

http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/dynviews_3042.htm#REFRN30246
ELAPSED_TIME 	
NUMBER 	
Elapsed time (in microseconds) used by this cursor for parsing, executing, and fetching
The keyword here is the "used by this cursor".

All the events or more that you have mentioned should be included to the elapsed time.

Regards.

Ogan
784287
Dear Ogan

Thank you for your answer.

but I don't know what i got except all i mentioned(the sum),

If you have some other stuff, please let me know it...

elapsed_time = cpu time(one of the main time, almost 35% in my case) + user i/o wait time( secondary main time, almost 30% in my case )
+ other stuff (5%, these are all in v$sql ) + 30%(i don't know what it is)

Regards.
618702
Dear JoeMae,

I think below link may address your concerns;

http://www.dba-oracle.com/m_sql_execute_elapsed_time.htm

Ogan
Salman Qureshi
Hello,
What is java execution time? I dont think this is included in queries elapes time and likewise your PL/SQL execution should not be the part of elapsed time of your SQL

Salman
784287
Thank you your answer.

I've just listed v$sql columns.

and i thought elapsed time might be a sum of other times on v$sql.

but it isn't. there is quite big difference.

I know it is easy to calculate the elapsed time of query...

but I don't know which stuff in v$sql made of it.

i thought

elapsed_time = parse time(mainly cpu) + execution time(cpu + user i/o) + fetch time(mainly user i/o) +etc...

but the result is so different what i expected.

also i think it doesn't make a sense if elapsed_time couldn't be explained by the sum of other stuff in v$sql.

regards
Salman Qureshi
Answer
Hi,
If you trace the session (10046) of some application, following will be the result in trace file for each SQL statement
SELECT "COSTCODE","INSERTDATETIME","LOTID","SOURCELOTID","STAGE","SUPPLIERID"
FROM
 "ACTL_PARTINFO_CUST" "A" WHERE "SUPPLIERID"='RD1' AND "INSERTDATETIME"=
  TO_DATE('2010-08-18 07:05:00', 'yyyy-mm-dd hh24:mi:ss') AND "STAGE"=:1 AND
  "LOTID" LIKE 'C%' AND "COSTCODE"='A' AND "SOURCELOTID" LIKE 'F%'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 300372      3.27      69.85          0          0          0           0
Fetch   300371   1916.42    6551.42          0  100023544          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   600744   1919.69    6621.27          0  100023544          0           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61  (DWDB_REPORTING)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                  300371        0.02          0.64
  SQL*Net message from client                300371        5.33       4860.35
  latch: cache buffers chains                  7199        0.38         49.62
  latch free                                     99        0.04          0.72
  latch: enqueue hash chains                      5        0.03          0.07
Here you can see the total elapsed time at database side including wait events.

Salman
Marked as Answer by 784287 · Sep 27 2020
784287
Thank you for your answer.

I've finally realized that v$sql doesn't show every wait event.

so i could say that (elapsed_time - other time stuff in v$sql ) would be the sum of other wait events such like system i/o, etc...

thanks for your help!!!
1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 21 2010
Added on Aug 24 2010
#general-database-discussions, #sql, #time
7 comments
15,997 views