You can get the I/O stats in the plan. Look at the Buffers column for the logical I/O/consistent gets.
Note that PL/SQL standardizes SQL within it, so when searching for the statement in v$sql it's all in uppercase (apart from literals and quoted identifiers) and unnecessary whitespace removed:
alter session set statistics_level = all;
begin
for rws in ( select * from hr.employees ) loop
null;
end loop;
end;
/
select p.*
from v$sql s, table (
dbms_xplan.display_cursor (
s.sql_id, s.child_number, 'ALLSTATS LAST'
)
) p
where s.sql_text = 'SELECT * FROM HR.EMPLOYEES ';
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 107 |00:00:00.01 | 7 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 7 |
-----------------------------------------------------------------------------------------Why i need to do it (get the data) if i wont be able to read it ?Getting the data is part of executing queries. Until you've fetched all the rows the query is incomplete. So any performance measures can be misleading.
How you're fetching the data also affects performance. Here's an example playing with the fetch size (arraysize in SQL*Plus).
Getting one row/fetch does lots more work (63 buffers) than when fetching 1,000 at a time (6 buffers):
set serveroutput off
set array 1
set feed only
select * from hr.employees;
set feed on
select *
from table(dbms_xplan.display_cursor( format => 'ALLSTATS LAST'));
Plan hash value: 1445457117
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 107 |00:00:00.01 | 63 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 63 |
-----------------------------------------------------------------------------------------
set array 1000
set feed only
select * from hr.employees;
set feed on
select *
from table(dbms_xplan.display_cursor( format => 'ALLSTATS LAST'));
Plan hash value: 1445457117
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 107 |00:00:00.01 | 6 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 |
-----------------------------------------------------------------------------------------