Connor, thanx a lot!
could you provide some explanation for these points, please:

1)
>So there is obviously a cpu and filesystem IO cost to formatting and spooling results
> I suspect it might be the file formatting/writing time where the biggest gains can be made.

could you propose a test case with measurable results which can clearly prove that suspect, e.g.:

oracle reads a table XX seconds with YY CPU (fast)
sql*net transfers results XX seconds with YY CPU (fast)
...
client (e.g. sql*plus) uses YY CPU & ZZ fileIO *and is slow* - here is bottleneck

or there is nothing better measured than simple "run several sql*plus spools simultaneously", which you've done?


2) do I understand correctly that Pro*C program is the "fastest known file extractor to text" for Oracle?

what about CTAS to external table (datapump)?
where it goes in range of:

- utl_file (slowest but really handy when it is "fast enough")
- sqlplus
- C

3) may the extract of one table (any rowsource) be done in parallel with some automatic director without the need to rewrite query?
how?
AFAIK there was some trick with (pipelined?) function to run several sessions on same source table but "take only that rows which correspond to session depending on number of parallel sessions requested"


4) about sql*net compression -
where to find documentation and guidelines to use it properly on wide tables (more than 1 column) and is it usable with jdbc?

for i in ( select * from my_table ) loop
  null;
end loop;

Connor, thanx a lot for reply!
You still has not answered on these points:

2) what about CTAS to external table (datapump)?
where it goes in range of:

- utl_file (slowest but really handy when it is "fast enough")
- sqlplus
- C (fastest known)

3) may the extract of one table (any rowsource) be done in parallel with some automatic director without the need to rewrite query?
how?
AFAIK there was some trick with (pipelined?) function to run several sessions on same source table but "take only that rows which correspond to session depending on number of parallel sessions requested"


4) about sql*net compression -
where to find documentation and guidelines to use it properly on wide tables (more than 1 column) and is it usable with jdbc?

Connor, one more question in addition to previous post: why you didn't mention using TRACE in finding the bottleneck?

for example, if TKPROF shows the following:

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  resmgr:pq queued                                1        0.00          0.00
  os thread startup                              32        0.17          3.52
  latch free                                      3        0.00          0.00
  PX Deq: Join ACK                               32        0.01          0.01
  PX Deq: Parse Reply                            32        0.01          0.02
  SQL*Net message to client                 1044264        0.00         11.01
  PX Deq: Execute Reply                      273951        0.06         10.22
  SQL*Net message from client               1044264        1.25       3496.23
  SQL*Net more data to client                    10        0.00          0.00
  resmgr:internal state change                    2        0.10          0.20
  PX Deq: Signal ACK RSG                         32        0.00          0.00
  PX Deq: Signal ACK EXT                         32        0.00          0.00
  PX Deq: Slave Session Stats                    32        0.00          0.00
********************************************************************************

 

doesn't this CLEARLY and INSTANTLY mean that most of time is spent OUTSIDE the database , as Tom mentioned here:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1154234872764#1639863671441

"Say you are running a pro*c app you wrote, its a batch program. Its taking too long to run. You look and see that the sql*net message from client is HUGE -- guess what that means. In this case, it means the database is not the bottleneck, its spent alot of time WAITING for you to tell it to do something. In this case, this is a non-idle condition, we were not idle (as an entire process -- client+database)....

"