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.
The above listed views contains the below columns.
|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.