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.
Databases use server resources: CPU, RAM, disk I/O. Unless your server is physically partitioned (e.g. Solaris Zones) or you are using VMs it is a really bad idea to host multiple databases on the same server. You are just creating an environment where contention will be rife and tuning will be even trickier than normal.
Oracle RDBMS has always been designed to host multiple schemas in the one database, and for different applications that is the way to go. This is one of the main differences between Oracle and (say) MS SQL Server or MySQL.
Best Answer
You might be able to construct something like this using database links and views, but performance of those views in the development environment would likely be horrendous. That said, in general it is considered an extremely poor security practice to have production data be visible to non-production application instances, or to share any infrastructure (servers, network, storage) with them. In most computing environments it is specifically prohibited by best practice, if not by law, depending on the type of data being stored.
In most of the places I have worked, according to the rules developers were not even allowed read-access to production data for any reason; no links between dev and production were allowed; and no copies of production data could be placed in dev environments. Any exceptions required senior management and security approval from multiple organizations and generally meant that dev environments wound up secured to the same level as production.