Determine the size of a SQL result set in KB

Question

I am hoping to find how I can get the kb size of a result set in OracleDB. I am not an sysadmin, but often run queries that return over 100k rows and I would need to find a way to determine what is the total kb size. thank you

Answer 1

Generally, you would replace your column list with a count(*) to return the row count.

I'm not sure how well that would work on really complicated queries with many joins and such but, for simpler queries, it should be fine. Replace:

select a,b,c from t where a > 7;

with

select count(*) from t where a > 7;

That will give you the row count before you run the real query. Just keep in mind there's a chance the data may change between your count query and real query (hopefully not too much). Knowledge of the data properties will allow you to approximate kilobytes from row count.

Answer 2

In SQL*Plus:

SET AUTOTRACE ON

SELECT *
FROM emp
WHERE rownum <= 100;

        27  recursive calls
         0  db block gets
        19  consistent gets
         4  physical reads
         0  redo size
     **11451  bytes sent via SQL*Net to client**
       314  bytes received via SQL*Net from client
         8  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
       100  rows processed

To use AUTOTRACE requires the PLUSTRACE role, which is not granted by default. Find out more.