Oracle SQL - check size of sql data
We are working on a DB located thousands of miles from our location and we want to check if we have any performance or sql transfer issues (we are not db specialists). The question is how to check the size of the data returned by the sql query? Is it possible? Imagine 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?
source to share
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 output of the plan table
The explain plan should give you an estimate of the number of bytes received
A simpler and more accurate way to enable the AUTOTRACE option from SQL * Plus, this 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';
This should give some statistics:
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
source to share
You can get the length of all received data and even after the query has been executed (without 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 used the same column name for the " a
" and " c
" ie tables col1
, which may cause an error when assigning values to the cursor. To solve this problem, the cursor will be declared explicitly or the REF cursor will be used.
source to share