ん~。いまいちまだ怪しい感じですがメモ的に…。
EE(Enterprises Edition)のみで利用可能な方法ですが。
v$active_session_history を利用して、指定した時間帯においてどの処理(セッションID、SQL ID別)でどれだけのIOが発生したのかを集計するSQLを考えてみました。
SELECT
SESSION_ID,
users.USERNAME,
ash.SQL_ID,
sqltext.COMMAND_TYPE,
to_char(min(sample_time),'yyyy/mm/dd hh24:mi:ss') as sample_time_min,
to_char(max(sample_time),'yyyy/mm/dd hh24:mi:ss') as sample_time_max,
sum(DELTA_READ_IO_REQUESTS) as READ_IO_REQUESTS,
sum(DELTA_WRITE_IO_REQUESTS) as WRITE_IO_REQUESTS,
sum(DELTA_READ_IO_BYTES) as READ_IO_BYTES,
sum(DELTA_WRITE_IO_BYTES) as WRITE_IO_BYTES,
sum(DELTA_INTERCONNECT_IO_BYTES) as INTERCONNECT_IO_BYTES
FROM
v$active_session_history ash
left join v$SQLTEXT sqltext on(ash.SQL_ID=sqltext.SQL_ID)
left join dba_users users on(ash.user_id=users.user_id)
WHERE
sample_time >= to_timestamp('201509111600','YYYYMMDDHH24MI') AND
sample_time <= to_timestamp('201509111700','YYYYMMDDHH24MI') AND
session_type='FOREGROUND'
GROUP BY
SESSION_ID,
users.USERNAME,
ash.SQL_ID,
sqltext.COMMAND_TYPE
ORDER BY
SESSION_ID,
ash.SQL_ID
おまw それ違うぞw というアドバイスぜひ頂けるとありがたいです。