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?
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
You can get the length of all the data being received, and that even after the executions of query(no pre-assumptions).
LEN NUMBER := 0;
FOR C_DATA IN (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')
LOOP
LEN = LEN + LENGTH(C_DATA.col1) + LENGTH(C_DATA.col2) + LENGTH(C_DATA.col3);
END LOOP;
PS: You have used same column name for both table "a" and "c" i.e.col1, which may throw error while assigning values into the cursor. To resolve this, the cursore would be declared explicitly or REF cursor is to be used.
