CODEWITHSUNDEEP
sqloracle-database
david
david

Reputation: 115

Oracle: Find total query run time for multiple query runs

I want to find total time taken for a query to run (execution time + fetch time). I am using the query below. Is this the correct way?

The sql_text I am using takes a few milliseconds to return results. I am running the sql_text multiple times. V$SQL provides me a single query run, number of executions, and first load time. How can I find total time taken each time the query runs?

select ELAPSED_TIME from V$SQL where sql_text = ""

Edit1: My queries vary very slightly, example I am inserting different integers. From V$SQL, these queries have different SQL_ID but I am not able to find them in AWR. Also, is ELAPSED_TIME a summation of all query executions or an average or elapsed time of last query executed?

Upvotes: 1

Views: 1488

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21095

The column ELAPSED_TIME as documented here contains:

Elapsed time (in microseconds) used by this cursor for parsing, executing, and fetching. If the cursor uses parallel execution, then ELAPSED_TIME is the cumulative time for the query coordinator, plus all parallel query slave processes.

The important thing is that the statistics are related to the cursor.

So if the cursor is flushed and reloaded the total value is reset and start fom zero.

To get a more complete picture you may use AWR which basically takes periodic snashot of the statistics and stores them for reporting.

Final remark - typical approach to reference a SQL statement is to use SQL_ID (and not the full text - SQL_ID is basically a hash code if the text). You will find the SQL_IDin V$SQL as well.

Upvotes: 3

Related Questions