SQL Command to check I/O bottle neck for sql statement in oracle database







>Sessions that are currently waiting for I/O resources:

SELECT username,

       program,

       machine,

       sql_id

  FROM v$session

 WHERE event LIKE 'db file%read';

> Which SQL statements are using a lots of disks:

 col schema format a20

 SELECT *

  FROM (  SELECT parsing_schema_name Schema, SQL_ID,

                 SUBSTR (sql_text, 1, 75) SQL,

                 disk_reads

            FROM v$sql

        ORDER BY disk_reads DESC)

 WHERE ROWNUM < 20;

 And with the result set of (1) or (2):

 set long 1000

 select SQL_FULLTEXT from v$sql;

O/P: