Oracle SQL - check sql data size

Question

We work on DB located thousand miles from our location and we want to check if we have problems with sql performance or transfer (we aren't db specialists). The question is - how to check size of data returned by sql query? Is it possible? Imagine that we have this query:

SELECT a.col1, b.col2, c.col1
FROM a
INNER JOIN b on b.a_id=a.id
LEFT JOIN c on c.b_id=b.id
WHERE a.somecol='data';

How much data needs to be transferred from dba to our application?

Answer 1

Run an explain plan on the query

EXPLAIN PLAN FOR SELECT a.col1, b.col2, c.col1 FROM a INNER JOIN b ON
b.a_id=a.id LEFT JOIN c ON c.b_id=b.id WHERE a.somecol='data'; 

Display the plan table output

The explain plan should give you an estimate on the number of bytes accessed

---

An easier and more accurate way to be to enable AUTOTRACE option from SQL*Plus, that should give you a similar figure

SET AUTOTRACE TRACEONLY


SELECT a.col1, b.col2, c.col1
FROM a
INNER JOIN b on b.a_id=a.id
LEFT JOIN c on c.b_id=b.id
WHERE a.somecol='data';

That should give some stats:

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         44  consistent gets
          0  physical reads
          0  redo size
      24849  bytes sent via SQL*Net to client
        408  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         66  rows processed