Determine SQL Result Set Size in KB

Question

I hope to find how I can get the kb size of the result set in OracleDB. I am not a system administrator, but I often execute queries that return more than 100 thousand lines, and I need to find a way to determine what the total size of kb is. thanks

Answer 1

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 

Answer 2

Typically, you should replace your list of columns with count(*) to return the number of rows.

I'm not sure how well this works on really complex queries with many joins and such, but for simpler queries, everything should be fine. Replace:

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

with

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

This will give you a row counter before running a real query. Just keep in mind that there is a chance that the data may change between your account request and the actual request (hopefully not too much). Knowing the properties of the data will allow you to approach kilobytes from the number of lines.