5

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

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
shermy
  • 68
  • 1
  • 2
  • 8

2 Answers2

14

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.

APC
  • 144,005
  • 19
  • 170
  • 281
jva
  • 2,797
  • 1
  • 26
  • 41
  • 1
    Wow, that's amazing. Autotrace actually *knew* that you were interested in bytes sent and marked the lines with asterisks? You'd think Oracle would at least fix the bug that caused the misalignment of numbers though. :-) +1 for a better answer than mine. – paxdiablo Sep 11 '09 at 13:59
  • Thanks for the answer, indeed the autotrace is great. Still have one more issue - I use SQL Developer and it seems that there are some unreturned results (the ones that I need :(). When I set autotrace ON and run the SELECT, I get the following results: Statistics 685 recursive calls 3 db block gets 22744 consistent gets 247 physical reads 0 redo size 0 workarea executions - multipass 0 parse time cpu 0 parse time elapsed 0 frame signature mismatch 2 execute count Any tips and tricks on how this can be further tuned to retrieve the required results? – shermy Sep 14 '09 at 11:23
  • 1
    Note that "bytes sent via SQL*Net to client" represents the total amount of traffic from the server to the client, which includes more than just the result set itself. e.g. SELECT null FROM DUAL; => 411 bytes sent via SQL*Net to client (tested on Oracle 11.1) – Jeffrey Kemp Sep 14 '09 at 13:39
0

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.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953