Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Mar 3 '13 at 14:35

This question came from our site for professional and enthusiast programmers.

2  
Look at oracle AWR, ADDM, or Statspack for collecting performance data. You can also extract some stats from ths SGA with various system views, such as v$SQLAREA. –  Leor A Mar 2 '13 at 16:25
3  
Are you licensed to use the tuning and diagnostic packs for Oracle? –  Chris Saxon Mar 3 '13 at 19:00

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.