SQL*Plus and Performance….What ?

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
  1. If the LINESIZE is too big, then more memory is allocated resulting in more memory copying and impacting  performance significantly
  2. 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
  1. Reduce number of SQL*net round trips.
  2. Reduce the number of Logical IO’s or consistent gets (Laws of diminishing returns come into picture)
  3. Huge performance benefits if the network latency is high.
  4. TCP packets size also influences the performance benefits.
  5. Value of 100-300 provides good performance generally
Advertisements

Tagged: , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: