SET line 800
SET pages 100
col sid format 9990
col SERIAL# format 999990
col top_io_machine format a8
col top_io_program format a10
col top_io_sql format a50
SET colsep |
SELECT /*+ rule */
aa.sid AS "sid",
aa.serial# AS "SERIAL#",
aa.VALUE AS "CPU",
bb.VALUE AS "MEN-PGA",
cc.VALUE AS "I/O-Phy-read",
cc.machine AS top_io_machine,
cc.program AS top_io_program,
cc.sql_text AS top_io_sql
FROM (SELECT d.sql_text, c.machine,
c.program, c.sid,
c.serial#, a.NAME,
b.VALUE
FROM v$statname a, v$sesstat b, v$session c, v$sqlarea d
WHERE NAME = 'CPU used by this session'
AND a.statistic# = b.statistic#
AND c.sid = b.sid
AND c.status = 'ACTIVE'
AND d.address = c.sql_address) aa,
(SELECT d.sql_text, c.machine,
c.program, c.sid,
c.serial#, a.NAME,
b.VALUE
FROM v$statname a, v$sesstat b, v$session c, v$sqlarea d
WHERE NAME = 'session pga memory'
AND a.statistic# = b.statistic#
AND c.sid = b.sid
AND c.status = 'ACTIVE'
AND d.address = c.sql_address) bb,
(SELECT d.sql_text, c.machine,
c.program, c.sid,
c.serial#, a.NAME,
b.VALUE
FROM v$statname a, v$sesstat b, v$session c, v$sqlarea d
WHERE NAME = 'physical reads'
AND a.statistic# = b.statistic#
AND c.sid = b.sid
AND c.status = 'ACTIVE'
AND d.address = c.sql_address) cc
WHERE aa.sid = bb.sid
AND aa.sid = cc.sid
AND aa.serial# = bb.serial#
AND aa.serial# = cc.serial#
ORDER BY 5 ASC, 4 ASC, 3 ASC
