Tuning Very Large SELECTs in SQLPlus

A forums question about the performance of a SELECT query in 10g led from database server performance to network bottlenecks to client performance.
We finally diagnosed it as an issue with SQLPlus *formatting* the output. The output had a rather large row size and the default values of PAGESIZE and LINESIZE being what they are, SQLPlus was spending rather too much time putting in Column Headers, splitting rows across lines etc.
So much so that while the server process took less than 2seconds (from the 10046 trace file), the SQLPlus client showed the query as taking 15minutes.
To the user, this would be a "Database Server Performance Issue". However, to the Performance Analyst, the Database was perfectly fine, returning the output in under 2seconds ! Where were the 15minutes being spent ?

Oracle's SQLPlus client provides many formatting options. Of these, PAGESIZE 'n' determines the number of lines of output after which Oracle would create new "Page Header", inserting Column Headers etc. LINESIZE 'l' determines the number of characters in a row that are printed in one line, with the result that a row retrieving more than 'l' characters gets split across multiple lines (thus, soon hitting the 'n' limit of PAGESIZE).

Whenever I run queries, I normally use PAGESIZE600 and LINESIZE132. The defaults are inadequate for most output to be properly readable.

Another parameter ARRAYSIZE allows you to specify how many Rows will SQLPlus request from the database server at each fetch. With small rows, you can set a larger ARRAYSIZE. (Note : Do not go overboard with ARRAYSIZE. If you set it to too large a value and you are retreiving "slightly large" rows, the resultset (arraysize 'n' rows X rowsize 'm' bytes) may exceed the SQLPlus Buffer size and you'd get an error in the midst of fetching your data.

Here I've created a Test case to show how these parameters can impact "perceived" performance.

I start with a table MY_TEST_TABLE which has the same structure as DBA_OBJECTS but has been populated by repeatedly querying DBA_OBJECTS, getting a total of 377,280 rows of 98byte each. The table is 42MB (in terms of the sum of allocated extents).

SQL> select num_rows,avg_row_len from user_tables where table_name = 'MY_TEST_TABLE';

  NUM_ROWS AVG_ROW_LEN
---------- -----------
    377280          98

SQL>
SQL> select blocks, bytes/1048576 from user_segments where segment_name = 'MY_TEST_TABLE';

    BLOCKS BYTES/1048576
---------- -------------
      5376            42

SQL>

I now query this table using the defaults for ARRAYSIZE, PAGESIZE and LINESIZE.

With default ARRAYSIZE, PAGESIZE and LINESIZE

ARRAYSIZE 15
PAGESIZE  14
LINESIZE  80

select * from my_test_table;
...
377280 rows selected.

Elapsed: 00:08:58.68

Statistics
----------------------------------------------------------
        482  recursive calls
          0  db block gets
      30143  consistent gets
       5263  physical reads
          0  redo size
   42462373  bytes sent via SQL*Net to client
     277129  bytes received via SQL*Net from client
      25153  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
     377280  rows processed


Output file size : 397655892 bytes

I have done 25,152 round trips -- or array fetches (377,280 rows / arraysize of 15 : 377280/15 = 25152).
Note that this also increases consistent gets as some blocks are fetched repeatedly. (If the 'n'th fetch stopped at the 10th row in block 'X', then the 'n+1'th fetch would do another 'consistent get' of block 'X' for the remaining rows.


What happens if I "tune" the ARRAYSIZE and reduce the Formatting overheads imposed by PAGESIZE and LINESIZE ?

With "tuned" values for ARRAYSIZE, PAGESIZE and LINESIZE

set ARRAYSIZE 100
SET LINESIZE 132
SET PAGESIZE 6000

select * from my_test_table;
...
377280 rows selected.

Elapsed: 00:04:00.85



Statistics
----------------------------------------------------------
        480  recursive calls
          0  db block gets
       9049  consistent gets
       5263  physical reads
          0  redo size
   38485879  bytes sent via SQL*Net to client
      41960  bytes received via SQL*Net from client
       3774  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
     377280  rows processed

Output file size : 200917333 bytes

The query completes in less than half the time and does fewer round-trips between the client SQLPlus and the Database Server.
I have done 3,773 round trips -- or array fetches (377,280 rows / arraysize of 100 : 377280/100 = 3773).
'consistent gets' has also reduced signficantly because the number of occassions when I had to request the same block again (having read only some of the rows at the last fetch) are fewer, with larger and fewer fetches.

Had I done an event 10046 trace, I would have seen the individual Fetches as well.
Array processing overheads can be signficant if there is a very slow network between the client and the server and the number of round trips is very high -- each round trip suffering the latency of the network.

Note : The output file size also reduces because there are fewer Column Header entries in the output.