Can SQLPlus Fetch More Than One Row at a Time?

oraclesqlplus

We have a task running every day dumping results of a query into a CSV-file — as the stdout of sqlplus running a query.

The number of records is many millions (don't ask), and the job takes over two hours.

In trying to improve the timings, I wrote a C-program (using Oracle's ofetch() call), but the performance didn't change… Bumping up the Session Data Unit (SDU) size (to 1GB! on the client — not sure, where the server would clamp it) didn't help.

I then did some more research and found, that ofen(), if used to capture multiple rows at a time, can improve performance dramatically — and changed my C-program to use it instead.

Fetching 4K rows at a time, the timings went down to 21 minutes — a factor of 10x reduction — causing much rejoicing.

Before we go through the trouble of installing my new executable on production servers, maybe, the stock sqlplus can also do the same multi-row fetching — if supplied with the proper command-line option(s)?

Can it? We're using Oracle-11 although, if this feature is available in the client version 12, we could upgrade the client-side…

Best Answer

SET ARRAYSIZE has a default of 15

"Sets the number of rows that SQL*Plus will fetch from the database at one time.

Valid values are 1 to 5000. A large value increases the efficiency of queries and subqueries that fetch many rows, but requires more memory. Values over approximately 100 provide little added performance. ARRAYSIZE has no effect on the results of SQL*Plus operations other than increasing efficiency."

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqpug/SET-system-variable-summary.html#GUID-773AEA4E-060E-4C1E-B1A0-52F7DE084AE8

There are additional tweaks that can be done (including the SDU) in either the sqlnet.ora (changing the DEFAULT_SDU_SIZE ) or in the connection string. That can be defined in tnsnames.ora but you can also put it as part of a versbose connection string such as

sqlplus usr/pwd@(DESCRIPTION=(SDU=11280)(ADDRESS=(PROTOCOL=TCP)(HOST=svr)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=svc)))

Put in your appropriate values for usr,pwd,svr and svc https://docs.oracle.com/cd/E18283_01/network.112/e10836/performance.htm

Related Solutions

Retype information every time I make a mistake in SQLPlus

Several ways.

You can set the EDITOR environment variable before running SQL*Plus (Assuming Unix), to allow use of an external text editor (vi, by way of example):

export EDITOR=vi

Then type ed in SQL*Plus to edit the previous query in the vi editor. Of course, you may prefer nano, pico, emacs, vim etc etc.

If you don't wish to set an environment variable, you can add define _editor=vi to your $ORACLE_HOME/sqlplus/admin/glogin.sql file instead.

My favourite way of achieving SQL*Plus edit heaven (in Unix) is by using rlwrap. This adds readline support to SQL*Plus and allows you to easily edit within SQL*Plus itself.

Install rlwrap from your distributions package repository (or download & compile from the website here), then add the following to your shells startup script (.bashrc/.bash_profile or similar):

alias sqlplus="rlwrap -i sqlplus"

Oracle – How to Make SQLPlus Output Appear in One Line

It is not enough to force sqlplus not to wrap lines. It is also necessary to tell the viewer that you use to view the spool file not to wrap lines. If your viewer is less then -S the option you have to use according to https://superuser.com/questions/272818/how-to-turn-off-word-wrap-in-less . On Unix/Linux you can use head -1 output.txt to get the first line of a file and so check if this is as expected or you can use od -c output.txt|head to see where the line breaks are actually placed in your output file.

If you display LONG columns and their values contain line breaks then multiple lines will be printed for these column values and you cannot overrule this with sqlplus settings.

The following sqlplus commands maybe usefull:

SET LINESIZE linesize the length of the line. In most cases the maximum value for linesize is 32767. You can find out your maximum value if you set LINESIZE to an invalid value and check the error message so SET LINESIZE 0 may give SP2-0267: linesize option 0 out of range (1 through 32767) (1)
SET TRIMSPOOL ON otherwise every line in the spoolfile is filled up with blanks until the linesize is reached.
SET TRIMOUT ON otherwise every line in the output is filled up with blanks until the linesize is reached.
SET WRAP OFF Truncates the line if its is longer then LINESIZE. This should not happen if linesize is large enough.
SET TERMOUT OFF suppresses the printing of the results to the output. The lines are still written to the spool file. This may accelerate the exectution time of a statement a lot.
SET PAGESIZE 0 to set an infinite pagesize and avoid headings , titles and so on.
There are some other SET parameters concerning output (NUMWIDTH, NUMFORMAT, LONG, COLSEP) and performance (ARRAYSIZE, LONGCHUNKSIZE).

You have to use the COLUMNcommand to format individual columns.

E.g. column name format a30 will format the column name in the output to a maximum length of 30 characters.

If you want the display size not be fix but it should be equal to the size of the actual value of a columns in a row then the only way I know is that you change the select clause of your statement to get the desired result and use the string concatenation operator || , e.g.

select emp_id||' '||first_name||' '||last_name
from emp;

A full description of all variable can be found in SQL*Plus User's Guide and Reference.

If you want to reuse some settings (or COLUMN definitions) you can store them in a file and run this file when you need them again. You even can run this file automatically if you start sqlplus.

(1) "How to Find the LINESIZE Maximum Value (It is System Dependent) (Doc ID 1547262.1)"

Best Answer

Related Solutions

Retype information every time I make a mistake in SQLPlus

Oracle – How to Make SQLPlus Output Appear in One Line

Related Question