There are a number of ways to do this. For statements still in the cache, you can find aggregated information from the v$sql* views.
For example, here's a query that takes ~27s:
SQL> with /* slow */ rws as (
2 select rownum x from dual connect by level <= 1000
3 )
4 select count(*) from rws, rws, rws;
COUNT(*)
----------
1000000000
SQL>
SQL> select sql_id, first_load_time, last_load_time, elapsed_time, cpu_time
2 from v$sql
3 where sql_text like 'with /* slow */ rws as (%';
SQL_ID FIRST_LOAD_TIME LAST_LOAD_TIME ELAPSED_TIME CPU_TIME
------------- ------------------- ------------------- ------------ ----------
f3mdy8usdm8j4 2017-02-14/07:01:31 2017-02-14/07:01:31 27976167 27914000
The times are in microsecond and are cumulative. So if you have executed the same statement multiple times, you can divide these by the executions to get an average:
SQL> with /* slow */ rws as (
2 select rownum x from dual connect by level <= 1000
3 )
4 select count(*) from rws, rws, rws;
COUNT(*)
----------
1000000000
SQL>
SQL> select sql_id, first_load_time, last_load_time, elapsed_time, elapsed_time/executions avg_elapsed, cpu_time/executions avg_cpu
2 from v$sql
3 where sql_text like 'with /* slow */ rws as (%';
SQL_ID FIRST_LOAD_TIME LAST_LOAD_TIME ELAPSED_TIME AVG_ELAPSED AVG_CPU
------------- ------------------- ------------------- ------------ ----------- ----------
f3mdy8usdm8j4 2017-02-14/07:01:31 2017-02-14/07:01:31 55617239 27808619.5 27775000
If you're licensed for the Diagnostics and Tuning packs, you can do better than this though. The SQL monitor records each instance of queries longer than 5 seconds:
SQL> select to_char(sql_exec_start, 'dd-Mon hh24:mi'), elapsed_time, cpu_time
2 from v$sql_monitor
3 where sql_id = 'f3mdy8usdm8j4';
TO_CHAR(SQL_ ELAPSED_TIME CPU_TIME
------------ ------------ ----------
14-Feb 07:01 27963910 27904000
14-Feb 07:02 27635004 27633000
Oracle keeps these details for at least a minute, possibly longer. It depends upon how many statements it's monitoring!
And you can also get historic details from AWR. You can use this to find the slowest statements in a given time period:
select sql_id, elapsed_time_delta/executions_delta avg_elapsed
from sys.dba_hist_sqlstat
where snap_id = :snap;