Resolution
Below is the background query used by Spotlight to display the data grid of I/O time of Session tab.
SELECT s.sid || '_' || s.serial# sid_serial,
s.sid,
s.serial#,
s.machine,
s.program,
s.module,
s.osuser,
NVL (
DECODE (TYPE,
'BACKGROUND', 'SYS (' || b.ksbdpnam || ')',
s.username),
SUBSTR (p.program, INSTR (p.program, '(')))
username,
NVL (SUM (DECODE (ec.subcategory, 'Single-block read', total_waits)),
0)
single_block_read_waits,
NVL (SUM (DECODE (ec.subcategory, 'Control file I/O', total_waits)),
0)
control_file_waits,
NVL (SUM (DECODE (ec.subcategory, 'Multi-block read', total_waits)),
0)
multi_block_read_waits,
NVL (SUM (DECODE (ec.subcategory, 'Direct path I/O', total_waits)), 0)
direct_path_waits,
NVL (SUM (total_waits), 0) total_io_waits,
NVL (
SUM(DECODE (ec.subcategory,
'Single-block read', time_waited_micro / 1000)),
0)
single_block_read_time,
NVL (
SUM(DECODE (ec.subcategory,
'Control file I/O', time_waited_micro / 1000)),
0)
control_file_time,
NVL (
SUM(DECODE (ec.subcategory,
'Multi-block read', time_waited_micro / 1000)),
0)
multi_block_read_time,
NVL (
SUM(DECODE (ec.subcategory,
'Direct path I/O', time_waited_micro / 1000)),
0)
direct_path_time,
NVL (SUM (time_waited_micro / 1000), 0) total_io_time,
NULL db_time,
NULL db_cpu,
io.block_changes + io.consistent_changes block_changes,
io.block_gets + io.consistent_gets logical_reads,
io.physical_reads,
p.spid
FROM v$session s,
v$process p,
x$ksbdp b,
quest_soo_event_categories ec,
v$session_event se,
v$sess_io io
WHERE s.paddr = p.addr
AND b.inst_id(+) = USERENV ('INSTANCE')
AND p.addr = b.ksbdppro(+)
AND ec.topcategory = 'Disk I/O'
AND se.event = ec.name
AND se.sid = s.sid
AND io.sid = s.sid
GROUP BY s.sid,
s.serial#,
s.machine,
s.program,
s.module,
s.osuser,
NVL (
DECODE (TYPE,
'BACKGROUND', 'SYS (' || b.ksbdpnam || ')',
s.username),
SUBSTR (p.program, INSTR (p.program, '('))),
io.block_changes,
io.consistent_changes,
io.block_gets,
io.consistent_gets,
io.physical_reads,
p.spid
