Tuning Performance of Database

ADDM - Automatic Database Diagnostic Monitor

To take snaphot
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('TYPICAL');

to run the report ADDM  
@?/rdbms/admin/addmrpt


I/O

I/O On Solaris and Linux -link
iostat 5
note: not useful if your datafile has been configured on ASM, just for file system

I/O - To check hot file in your database since startup
select
substr(a.name,1,40) "Name",
a.bytes,
b.phyrds,
b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#
order by b.phyrds desc; 

note: v$filestat This view supplements the v$datafile view and contain information about the number of physical reads and writes. 

I/O per session 
SELECT sid, total_waits, time_waited
    FROM v$session_event
   WHERE event='db file sequential read'
     and total_waits>0

   ORDER BY 3,2