I am an Oracle Database Specialist in Singapore.
get an rss feed of this blog at http://hemantoracledba.blogspot.com/feeds/posts/default?alt=rss
follow me on twitter : @HemantKChitale

Search My Oracle Blog

Custom Search

04 June, 2008

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).

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 ?

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.