scrip to check the query execution time in oracle database

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='dx647nxstqu260';

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='dx647nxstqu260';