Negative would indicate that the "io_interconnect_bytes" figure is excessive. The ratio formula assumes that the only thing going across the interconnect is data your query needs, thus the comparison with 'io_cell_offload_eligible_bytes'
And for many queries that will indeed be the case, eg
--
-- My "dba_objects" has 30340 rows, so I'm multiplying to get to around 1,000,000 rows
--
SQL> create table t1s as select a.*, rownum as id from dba_objects a,
2 ( select 1 from dual connect by level < 1000000 / 30340 );
SQL> create table t2s as select * from t1s;
Table created.
SQL> create table t3s as select * from t1s;
Table created.
SQL> create table t4s as select * from t1s;
Table created.
SQL> create table t5s as select * from t1s;
Table created.
SQL>
SQL> begin
2 for i in (
3 select /*+ findme3 */ t1.owner, t2.object_id, t3.object_type, t4.object_name, t5.created
4 from t1s t1, t2s t2, t3s t3,t4s t4, t5s t5
5 where t1.id =t2.id
6 and t2.id =t3.id (+)
7 and t3.id = t4.id(+)
8 and t4.id =t5.id (+)
9 )
10 loop
11 null;
12 end loop;
13 end;
14 /
PL/SQL procedure successfully completed.
SQL> select sql_id, child_number from gv$sql where sql_text like 'SELECT%findme3%';
SQL_ID CHILD_NUMBER
------------- ------------
4qcy6k7kjmbmt 0
SQL> select sql_id,child_number,ceil(cpu_time/1000000) cpu_time, ceil(elapsed_time/1000000) elapsed_time,
2 io_cell_offload_eligible_bytes as eligible,
3 io_interconnect_bytes as actual,
4 round( (100 * ( io_cell_offload_eligible_bytes -io_interconnect_bytes))
5 /io_cell_offload_eligible_bytes ,2) as IO_Saved
6 from gv$sql
7 where sql_id ='4qcy6k7kjmbmt'
8 and child_number=0;
SQL_ID CHILD_NUMBER CPU_TIME ELAPSED_TIME ELIGIBLE ACTUAL IO_SAVED
------------- ------------ ---------- ------------ ---------- ---------- ----------
4qcy6k7kjmbmt 0 2 2 787570688 102411880 87
Let me take the demo and now crank up the size of the tables
SQL> create table t1 as select a.*, rownum as id from dba_objects a,
2 ( select 1 from dual connect by level < 28673294 / 30340 );
Table created.
SQL> create table t2 as select * from t1;
Table created.
SQL> create table t3 as select * from t1;
Table created.
SQL> create table t4 as select * from t1;
Table created.
SQL> create table t5 as select * from t1;
Table created.
SQL>
SQL> begin
2 for i in (
3 select /*+ findme2 */ t1.owner, t2.object_id, t3.object_type, t4.object_name, t5.created
4 from t1, t2, t3,t4, t5
5 where t1.id =t2.id
6 and t2.id =t3.id (+)
7 and t3.id = t4.id(+)
8 and t4.id =t5.id (+)
9 )
10 loop
11 null;
12 end loop;
13 end;
14 /
PL/SQL procedure successfully completed.
SQL>
SQL> select sql_id, child_number from gv$sql where sql_text like 'SELECT%findme2%';
SQL_ID CHILD_NUMBER
------------- ------------
c4ncm7k1fqzgs 0
SQL> select sql_id,child_number,ceil(cpu_time/1000000) cpu_time, ceil(elapsed_time/1000000) elapsed_time,
2 io_cell_offload_eligible_bytes as eligible,
3 io_interconnect_bytes as actual,
4 round( (100 * ( io_cell_offload_eligible_bytes -io_interconnect_bytes))
5 /io_cell_offload_eligible_bytes ,2) as IO_Saved
6 from gv$sql
7 where sql_id ='c4ncm7k1fqzgs'
8 and child_number=0;
SQL_ID CHILD_NUMBER CPU_TIME ELAPSED_TIME ELIGIBLE ACTUAL IO_SAVED
------------- ------------ ---------- ------------ ---------- ---------- ----------
c4ncm7k1fqzgs 0 113 119 2.2580E+10 3.0096E+10 -33.29
and now the formula looks wrong.
The difference is that now just "real" data goes across the interconnect. We might need to read/write temporary data. Look at the stats for the two queries above
SQL> select * from v$sqlstats
2 where sql_id = '4qcy6k7kjmbmt'
3 @pr
==============================
SQL_TEXT : SELECT /*+ findme3 */ T1.OWNER, T2.OBJECT_ID, T3.OBJECT_TYPE, T4.OBJECT_NAME, T5.CREATED FROM T1S
T1, T2S T2, T3S T3,T4S T4, T5S T5 WHERE T1.ID =T2.ID AND T2.ID =T3.ID (+) AND T3.ID = T4.ID(+) AND T4.ID =T5.ID (+)
SQL_FULLTEXT : SELECT /*+ findme3 */ T1.OWNER, T2.OBJECT_ID, T3.OBJECT_TYPE, T4.OBJECT_NAME, T5.CREATED FROM T1S
T1, T2S T2, T3S T3,T4S T4, T5S T5 WHERE T1.ID =T2.ID AND T2.ID =T3.ID (+) AND T3.ID = T4.ID(+) AND T4.ID =T5.ID (+)
SQL_ID : 4qcy6k7kjmbmt
LAST_ACTIVE_TIME : 17-JUN-20
LAST_ACTIVE_CHILD_ADDRESS : 000000026CA5D590
PLAN_HASH_VALUE : 3020598475
PARSE_CALLS : 1
DISK_READS : 96139
DIRECT_WRITES : 0 <==== NOTHING DUMPED TO TEMP
DIRECT_READS : 96139
BUFFER_GETS : 98890
ROWS_PROCESSED : 1000000
SERIALIZABLE_ABORTS : 0
FETCHES : 10001
EXECUTIONS : 1
END_OF_FETCH_COUNT : 1
LOADS : 1
VERSION_COUNT : 1
INVALIDATIONS : 0
PX_SERVERS_EXECUTIONS : 0
CPU_TIME : 1894227
ELAPSED_TIME : 1981066
AVG_HARD_PARSE_TIME : 25714
APPLICATION_WAIT_TIME : 5169
CONCURRENCY_WAIT_TIME : 0
CLUSTER_WAIT_TIME : 0
USER_IO_WAIT_TIME : 73023
PLSQL_EXEC_TIME : 0
JAVA_EXEC_TIME : 0
SORTS : 0
SHARABLE_MEM : 52680
TOTAL_SHARABLE_MEM : 52680
TYPECHECK_MEM : 0
IO_CELL_OFFLOAD_ELIGIBLE_BYTES: 787570688
IO_INTERCONNECT_BYTES : 102411880
PHYSICAL_READ_REQUESTS : 805
PHYSICAL_READ_BYTES : 787570688
PHYSICAL_WRITE_REQUESTS : 0
PHYSICAL_WRITE_BYTES : 0
EXACT_MATCHING_SIGNATURE : 15423674400639829405
FORCE_MATCHING_SIGNATURE : 15423674400639829405
IO_CELL_UNCOMPRESSED_BYTES : 641465903
IO_CELL_OFFLOAD_RETURNED_BYTES: 102411880
DELTA_PARSE_CALLS : 1
DELTA_DISK_READS : 96139
DELTA_DIRECT_WRITES : 0
DELTA_DIRECT_READS : 96139
DELTA_BUFFER_GETS : 98890
DELTA_ROWS_PROCESSED : 1000000
DELTA_FETCH_COUNT : 10001
DELTA_EXECUTION_COUNT : 1
DELTA_PX_SERVERS_EXECUTIONS : 0
DELTA_END_OF_FETCH_COUNT : 1
DELTA_CPU_TIME : 1894227
DELTA_ELAPSED_TIME : 1981066
DELTA_APPLICATION_WAIT_TIME : 5169
DELTA_CONCURRENCY_TIME : 0
DELTA_CLUSTER_WAIT_TIME : 0
DELTA_USER_IO_WAIT_TIME : 73023
DELTA_PLSQL_EXEC_TIME : 0
DELTA_JAVA_EXEC_TIME : 0
DELTA_SORTS : 0
DELTA_LOADS : 0
DELTA_INVALIDATIONS : 0
DELTA_PHYSICAL_READ_REQUESTS : 805
DELTA_PHYSICAL_READ_BYTES : 787570688
DELTA_PHYSICAL_WRITE_REQUESTS : 0
DELTA_PHYSICAL_WRITE_BYTES : 0
DELTA_IO_INTERCONNECT_BYTES : 102411880
DELTA_CELL_OFFLOAD_ELIG_BYTES : 787570688
DELTA_CELL_UNCOMPRESSED_BYTES : 641465903
CON_ID : 225
CON_DBID : 1119628177
OBSOLETE_COUNT : 0
AVOIDED_EXECUTIONS : 0
DELTA_AVOIDED_EXECUTIONS : 0
PL/SQL procedure successfully completed.
SQL> select * from v$sqlstats
2 where sql_id = 'c4ncm7k1fqzgs'
3 @pr
==============================
SQL_TEXT : SELECT /*+ findme2 */ T1.OWNER, T2.OBJECT_ID, T3.OBJECT_TYPE, T4.OBJECT_NAME, T5.CREATED FROM T1,
T2, T3,T4, T5 WHERE T1.ID =T2.ID AND T2.ID =T3.ID (+) AND T3.ID = T4.ID(+) AND T4.ID =T5.ID (+)
SQL_FULLTEXT : SELECT /*+ findme2 */ T1.OWNER, T2.OBJECT_ID, T3.OBJECT_TYPE, T4.OBJECT_NAME, T5.CREATED FROM T1,
T2, T3,T4, T5 WHERE T1.ID =T2.ID AND T2.ID =T3.ID (+) AND T3.ID = T4.ID(+) AND T4.ID =T5.ID (+)
SQL_ID : c4ncm7k1fqzgs
LAST_ACTIVE_TIME : 17-JUN-20
LAST_ACTIVE_CHILD_ADDRESS : 000000037D139930
PLAN_HASH_VALUE : 2294840145
PARSE_CALLS : 1
DISK_READS : 3574555
DIRECT_WRITES : 818261 <==== LOTS OF TEMP NEEDED
DIRECT_READS : 3574555
BUFFER_GETS : 2759119
ROWS_PROCESSED : 28666575
SERIALIZABLE_ABORTS : 0
FETCHES : 286666
EXECUTIONS : 1
END_OF_FETCH_COUNT : 1
LOADS : 1
VERSION_COUNT : 1
INVALIDATIONS : 0
PX_SERVERS_EXECUTIONS : 0
CPU_TIME : 112444822
ELAPSED_TIME : 118497074
AVG_HARD_PARSE_TIME : 29178
APPLICATION_WAIT_TIME : 35242
CONCURRENCY_WAIT_TIME : 223
CLUSTER_WAIT_TIME : 0
USER_IO_WAIT_TIME : 7817524
PLSQL_EXEC_TIME : 0
JAVA_EXEC_TIME : 0
SORTS : 0
SHARABLE_MEM : 52680
TOTAL_SHARABLE_MEM : 52680
TYPECHECK_MEM : 0
IO_CELL_OFFLOAD_ELIGIBLE_BYTES: 22579560448
IO_INTERCONNECT_BYTES : 30096064328
PHYSICAL_READ_REQUESTS : 30880
PHYSICAL_READ_BYTES : 29282951168
PHYSICAL_WRITE_REQUESTS : 6450
PHYSICAL_WRITE_BYTES : 6703300608
EXACT_MATCHING_SIGNATURE : 10184766243625374875
FORCE_MATCHING_SIGNATURE : 10184766243625374875
IO_CELL_UNCOMPRESSED_BYTES : 21658331939
IO_CELL_OFFLOAD_RETURNED_BYTES: 16689373000
DELTA_PARSE_CALLS : 0
DELTA_DISK_READS : 0
DELTA_DIRECT_WRITES : 0
DELTA_DIRECT_READS : 0
DELTA_BUFFER_GETS : 0
DELTA_ROWS_PROCESSED : 0
DELTA_FETCH_COUNT : 0
DELTA_EXECUTION_COUNT : 0
DELTA_PX_SERVERS_EXECUTIONS : 0
DELTA_END_OF_FETCH_COUNT : 0
DELTA_CPU_TIME : 0
DELTA_ELAPSED_TIME : 0
DELTA_APPLICATION_WAIT_TIME : 0
DELTA_CONCURRENCY_TIME : 0
DELTA_CLUSTER_WAIT_TIME : 0
DELTA_USER_IO_WAIT_TIME : 0
DELTA_PLSQL_EXEC_TIME : 0
DELTA_JAVA_EXEC_TIME : 0
DELTA_SORTS : 0
DELTA_LOADS : 0
DELTA_INVALIDATIONS : 0
DELTA_PHYSICAL_READ_REQUESTS : 0
DELTA_PHYSICAL_READ_BYTES : 0
DELTA_PHYSICAL_WRITE_REQUESTS : 0
DELTA_PHYSICAL_WRITE_BYTES : 0
DELTA_IO_INTERCONNECT_BYTES : 0
DELTA_CELL_OFFLOAD_ELIG_BYTES : 0
DELTA_CELL_UNCOMPRESSED_BYTES : 0
CON_ID : 225
CON_DBID : 1119628177
OBSOLETE_COUNT : 0
AVOIDED_EXECUTIONS : 0
DELTA_AVOIDED_EXECUTIONS : 0