CODEWITHSUNDEEP
sqloracle-databaseoracle11g
pepuch
pepuch

Reputation: 6516

Oracle SQL - check sql data size

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?

Upvotes: 3

Views: 1177

Answers (2)

Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

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

Upvotes: 4

Sachin
Sachin

Reputation: 1003

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.

Upvotes: 2

Related Questions