Most us tend to believe that SQL*Plus is tool from where you issue SQL commands. There is more to it. In one of the recent projects where our spool file was 75GB, tuning LINESIZE and ARRAYSIZE helped us shave 2 hours.
Significance of LINESIZE
- If the LINESIZE is too big, then more memory is allocated resulting in more memory copying and impacting performance significantly
- If the LINESIZE is too small, then columns that do not fit in one single line are put on separate line(s) and impacting performance significantly
Significance of ARRAYSIZE
- Reduce number of SQL*net round trips.
- Reduce the number of Logical IO’s or consistent gets (Laws of diminishing returns come into picture)
- Huge performance benefits if the network latency is high.
- TCP packets size also influences the performance benefits.
- Value of 100-300 provides good performance generally
Tagged: ARRAYSIZE, LINESIZE, Logical IOs, SQL*plus
TwelveC 
Leave a Reply