Hi,
I am running a query on v$sql table to get the elapsed time , i used the below query.
select sql_id,executions,first_load_time,last_load_time,plan_hash_value,elapsed_time,(elapsed_time/executions)/1000000 avg_time_seconds from v$sql
where sql_id = 'dkgw6c6duyp2t'
and (elapsed_time/executions)/1000000>=8-- to fetch the records which took more than 8 seconds
This is the output of the query:
SQL_ID EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME PLAN_HASH_VALUE ELAPSED_TIME AVG
------------- ---------- ------------------- ------------------- --------------- ------------ ----------
dkgw6c6duyp2t 1 2019-03-13/11:51:57 2019-03-29/12:00:55 2385351720 18548260 18.54826
dkgw6c6duyp2t 3 2019-03-13/11:51:57 2019-03-27/17:56:45 2385351720 31096743 10.365581
dkgw6c6duyp2t 1 2019-03-13/11:51:57 2019-03-28/07:06:06 2385351720 14533925 14.533925
dkgw6c6duyp2t 1 2019-03-13/11:51:57 2019-03-29/07:22:48 2385351720 10480118 10.480118
dkgw6c6duyp2t 1 2019-03-13/11:51:57 2019-03-29/07:31:31 2385351720 19424301 19.424301
dkgw6c6duyp2t 9 2019-03-13/11:51:57 2019-03-29/08:41:37 4152429253 117733632 13.0815147.
Now i want to know why the first record is taking 18.54 seconds and what is the data dictionary view i want to check . Our application maximum time is 10 seconds if the execution crosses 10 seconds then the application will hang. Can you please suggest.