Historical SQL Monitoring
Oracle 12c has a Performance Hub which includes Monitored SQL in historical mode.  Although I haven't tested it, and based on some open bugs I doubt it works 100% of the time.



For 11g, or when the Performance Hub is not available or not working, I have created the open source program Historical SQL Monitoring (hist_sql_mon):

Here's an example of how to use it:

select hist_sql_mon.hist_sql_mon(
    p_sql_id            =&gt; '2ssrz4j1m39wx',
    p_start_time_filter =&gt; date '2014-09-25',
    p_end_time_filter   =&gt; sysdate - interval '1' day)
from dual;


The primary output is a CLOB containing an execution plan with a count and distinct count of events.

-------------------------------------------------------------
|Id  | Operation              | Name   | Rows  | Cost (%CPU)| Event (count|distinct count)
-------------------------------------------------------------
|  0 | SELECT STATEMENT       |        |       | 83031 (100)|
|  1 |  SORT AGGREGATE        |        |     1 |            |
|  2 |   HASH JOIN RIGHT OUTER|        |  8116K| 83031   (4)| Cpu (25|25)
|  3 |    INDEX FULL SCAN     | I_USER2|   155 |     1   (0)|
|  4 |    NESTED LOOPS OUTER  |        |  8116K| 82993   (4)| Cpu (1|1)
|  5 |     NESTED LOOPS OUTER |        |  8116K| 10702  (24)| Cpu (2|2)
...


The functions also print the SQL statement to DBMS_OUTPUT. The bind variables are replaced with hard-coded values so the query can run anywhere. This can help with debugging or creating your own queries.

----------------------------------
--Historical SQL Monitoring Report
----------------------------------
--Execution plans and ASH data, where there are some samples for a plan_hash_value.
select
    --Add execution metadata.
    case
        when plan_table_output like 'Plan hash value: %' then
...