V$SQL table elapsed time

oracle

Does the elapsed_time column in the v$sql table indicate the time per query exeuction or is it a sum of all executions?

Best Answer

From the 11.2 Database Reference documentation:

Elapsed time (in microseconds) used by this cursor for parsing, executing, and fetching

Here is a demonstration:

SQL> SELECT sql_text, elapsed_time FROM v$sql WHERE sql_text like '%lewap %';

SQL_TEXT
-----------------------------------------------------------------------------
ELAPSED_TIME
------------
SELECT sql_text, elapsed_time FROM v$sql WHERE sql_text like '%lewap %'
        5112


SQL> SELECT sql_text, elapsed_time FROM v$sql WHERE sql_text like '%lewap %';

SQL_TEXT
-----------------------------------------------------------------------------
ELAPSED_TIME
------------
SELECT sql_text, elapsed_time FROM v$sql WHERE sql_text like '%lewap %'
       26332


SQL> SELECT sql_text, elapsed_time FROM v$sql WHERE sql_text like '%lewap %';

SQL_TEXT
-----------------------------------------------------------------------------
ELAPSED_TIME
------------
SELECT sql_text, elapsed_time FROM v$sql WHERE sql_text like '%lewap %'
       65886


SQL> SELECT sql_text, elapsed_time FROM v$sql WHERE sql_text like '%lewap %';

SQL_TEXT
-----------------------------------------------------------------------------
ELAPSED_TIME
------------
SELECT sql_text, elapsed_time FROM v$sql WHERE sql_text like '%lewap %'
      103225

If you need the timing for one execution you could set timing on in an SQL*Plus session, turn on tracing with TIMED_STATISTICS, or add a comment to the SQL so that there is only one execution in v$sql.

Related Question