What queries is Oracle executing, how frequently, and time-taken? I have a (Java) web-application, that of course executes many queries (selects, inserts, updates, deletes) to the Oracle Database.

I want to find out the following for last 7 days:

    SQLQuery          Times-Executed   Average-Resp-Time    Average-Rows-Returned
====================================================================================
    SELECT whatever     981            330ms                1201
    UPDATE whatever     45             99ms                 0


Is this something Oracle can give me? If so, how can I get that?
If not Oracle, should I look at JDBC driver?
Note: Using AWR data requires you to have the Diagnostics Pack license option purchased. It may be possible to run the queries below without having the necessary license. Be sure to check with your DBA/licensing manager before using these.

The Automatic Workload Repository (AWR) contains details of the activity on your database. These are stored in various dba_hist* views. 

The kind of information you're looking for is primarily available in the dba_hist_sqlstat view. By joining this to dba_hist_snapshot you can get the information available in a given period:

select * 
from   dba_hist_sqlstat t, dba_hist_snapshot s
where  t.snap_id = s.snap_id
and    t.dbid = s.dbid
and    t.instance_number = s.instance_number
and    s.begin_interval_time between trunc(sysdate)-1 and trunc(sysdate) -- yesterday's stats


This view holds various *_elapsed columns, which state the increase in the given snap (time period). e.g.:


ELAPSED_TIME_DELTA
CPU_TIME_DELTA
ROWS_PROCESSED_DELTA
BUFFER_GETS_DELTA
DISK_READS_DELTA
PARSE_CALLS_DELTA
EXECUTIONS_DELTA


Times are in microseconds. You can combine these to get average time/execution stats and so on. For example, this gives you the average time/execution for a given sql statement:

select sql_id, sum(t.elapsed_time_delta/1000000)/sum(t.executions_delta)
from   dba_hist_sqlstat t, dba_hist_snapshot s
where  t.snap_id = s.snap_id
and    t.dbid = s.dbid
and    t.instance_number = s.instance_number
and    s.begin_interval_time between trunc(sysdate)-1 and trunc(sysdate) -- yesterday's stats
group  by sql_id


The sql_id is a hash of the text of the SQL itself. You can find the actual statement from:

select * from dba_hist_sqltext where sql_id = :sqlid


Note that only "highest consuming" statements are retained in AWR. So if the query you're interested in isn't the slowest, most executed, most disk access, etc. it may not be in the AWR data. You can get around this by "coloring" sql_ids of interest. This ensures they stay in AWR for as long as your retention period is set. You can do this by running:

exec DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL(:sqlid);


If you just want an overview of your system in a time period (including "heaviest" SQL statements), the AWR report itself gives this. You can find an intro to this on oracle-base.