Calculating Execution Time of Query in Oracle

In this post I am going to show how to calculate execution time of Query in Oracle Database. When user submits the query oracle parses the query and compute Hash Value for the statement.
In V$SQL view we are having HASH_VALUE column which is unique identifier for the statement. From Oracle Database version 10g, V$SQL view contains SQL_ID column which is also unique identifier for the statement. We can use any of these two for query identification.

Which View shows the execution time for statement ?
Below are the views which from which we can get execution time of statement.
1. V$SQL
2. V$SQLSTATS
3. V$SQLAREA

The above listed views contains the below columns.

Column Name Description
SQL_ID SQL identifier of the parent cursor in the library cache
EXECUTIONS Total Number of executions that took place on this object since it was brought into the library cache
ELAPSED_TIME Total Elapsed time (in microseconds) used by this cursor for parsing, executing, and fetching
CPU_TIME Total CPU time (in microseconds) used by this cursor for parsing, executing, and fetching

Now, I am queering against V$SQL view for SQL_ID f9cxmm1nw1300. I have taken SQL_ID from AWR report.

SQL> SELECT SQL_ID,EXECUTIONS,ELAPSED_TIME,CPU_TIME FROM
V$SQL
WHERE SQL_ID='f9cxmm1nw1300';

SQL_ID 	       EXECUTIONS ELAPSED_TIME CPU_TIME
-------------  ---------- ------------ --------
f9cxmm1nw1300  19825		 1931      1496

Query has 19825 executions. Elapsed Time and CPU Time from the above query are in microseconds. We need to divide these values by 1000000 to get the values in Seconds. Now this is the total time in seconds for 19825 executions. To Calculate the Time require per execution in seconds we need to divide the Elapsed_Time and CPU_Time Columns with Executions column.

You can use below query to find out the execution time of the query. Just replace the SQL_ID with your SQL_ID.

SELECT 
	SQL_ID,
	EXECUTIONS,
	ELAPSED_TIME/1000000 TOTAL_ELAPSED_TIME_SEC,
	ELAPSED_TIME/1000000/EXECUTIONS ELAPSED_TIME_SEC_PER_EXEC,
	CPU_TIME/1000000 TOTAL_CPU_TIME_SEC,
	CPU_TIME/1000000/EXECUTIONS CPU_TIME_SEC
FROM
	V$SQL
WHERE
	SQL_ID='f9cxmm1nw1300';

To calculate the execution time from the AWR tables, you can use below query.

SELECT
	SNAP_ID,
	SQL_ID,
	EXECUTIONS_DELTA EXECUTIONS,
	ELAPSED_TIME_DELTA/1000000 TOTAL_ELAPSED_TIME_SEC,
	ELAPSED_TIME_DELTA/1000000/EXECUTIONS_DELTA ELAPSED_TIME_SEC_PER_EXEC,
	CPU_TIME_DELTA/1000000 TOTAL_CPU_TIME_SEC,
	CPU_TIME_DELTA/1000000/EXECUTIONS_DELTA CPU_TIME_SEC
FROM
	DBA_HIST_SQLSTAT
WHERE
	SQL_ID='f9cxmm1nw1300';

Don’t forget to share this post with your friends.

Calculating Execution Time of Query in Oracle

One thought on “Calculating Execution Time of Query in Oracle

Leave a Reply