SET FLUSH OFF, SET ARRAYSIZE ...

Hi,
I am trying to get every possible bit of performance from our system
(HP T-series, 10CPU, 2GB RAM, around 500GB EMC farmes, Oracle 7.3.2,
Financials).
Many of our jobs are flat file extracts, sometimes they use SPOOL <filename> in SQL*Plus (with SET TERMOUT OFF), sometimes they simply send the output to stdout (with SET TERMOUT ON), something like this

sqlplus / @script | compress

We have to use this (stdout) because of the 2GB limit on the spool file size imposed by SQL*Plus.

My testing (on development box with nothing else running) shows that spool file is always more efficient way of creating extract than stdout.
For example, it takes 59 seconds of clock time (51 second of CPU time combined
sqlplus and oracle shadow process) to create extract file with 500000 lines
(line size 75) bytes when using SPOOL,

while it takes 88 sec (77 sec CPU) to create the same file with piping to STDOUT (SET FLUSH ON - default),
or 67 sec (57 sec CPU) with SET FLUSH OFF (testing was done by running SELECT statement without any WHERE,
ORDER BY, GROUP BY). The difference can be explained by the size of the IO buffer - bigger for spool files,
smaller for STDOUT, although it is only a guess.

So, my question is: What is the point of having SET FLUSH ON? The manual says
"Use OFF only when you run a command file non-interactively (that is, when you do not
need to see output and/or prompts until the command file finishes running." However, I tried to some stuff interactively with SET FLUSH OFF and everything was fine.
I was able to see all messages (like "10 rows updated" or "exact fetch returned more than requested number of rows") immediatelly. So, why not to put it (SET FLUSH OFF) in GLOGIN.SQL?

Another mysterious parameter is ARRAYSIZE. Basically, there is nothing mysterious in the
parameter itself, everyone who knows how to use host arrays in Pro*C understands it.
The default is 20, and my testing shows that it is possible to improve performance by 5 - 10%
by increasing it to 100. What is puzzling however is that this increase often leads to the
effor message: "Buffer overflow. Reduce ARRSYSIZE or increase MAXDATA" But MAXDATA is already set to its max value (60,000). Sometimes I am able to increase ARRAYSIZE up to 1000, sometimes even 30 leads to this error. I don't think it is directly related to the sum of column lendths, or number of columns.
Any ideas?

Regards,
Vsevolod, OCP 7.3, 8.0, 8i

"Just think of them as little horses" Received on Wed Nov 17 1999 - 16:38:31 CST