Re: sort_area_size

@nnibale-

As usual, we see "expert" replies that don't actually tell you anything. Time for Ricky to step in.

The purpose of sort area size is to provide memory to your oracle session's process for sorting and to avoid disk IO. You get a SQL result set, sort in memory (hopefully) and provide the rows for the application to fetch. Since a SQL result set does not always fit into that sort memory, we sometimes have to make multiple passes and put the intermediate results into a temporary tablespace. Sort area size and temporary tablespaces are therefore related in an important way.

Also, at the operating system or "disk farm" level, there is a maximum amount of data that can be returned in a single multi-block fetch. When you do full table scans you prefer to do multi-block reads to minimize disk activity and we want to minimize disk reads at all times, right?

You need to find out what that max multi-block IO is, in terms of bytes, from your system administrator or hardware vendor. If you make the sort area size an exact multiple of that value, you will optimize your sort memory. Each multi-block IO will exactly fit into your sort memory. Any oddball size over that will be wasted. Of course, you might find that some IO fetches aren't "max'ed out" by Oracle for a variety of reasons, and you get smaller reads, but the important thing is that an IO request's data has someplace in memory to fit. You can make the sort area size exactly equal to the IO chunk size, or any multiple to optimize IO. Anything smaller than that will cause you to have to do an extra IO. So, an exact multiple (1 is considered a valid multiple) of the max IO chunk is the right number to start with.

Now, for the temporary tablespace. First, make sure your tablespace is of type "temporary". Later, when you get to 8i, be sure to recreate it as a locally managed tablespace - for reasons that we will save for another thread. Also, if you have lots of concurrent users that do lots of sorts, you will probably want multiple temporary tablespaces and assign the users across all of them. You want the temporary tablespace to have uniform extents. That is, INITIAL = NEXT. You also want that extent size to be an exact multiple of the sort area size, so that you only have to do 1 IO at a time and don't have any stuff in memory spill over an extent size. The other good reason for uniform extents is that any extent left by one user can be used by another user. No worry about fragmentation, ever.

Carrying on with sizing the temporary tablespace...

Unfortunately, in the temporary tablespace, we have that stinky segment header to contend with. The initial extent will include that, so the first user to do a sort will have that segment header included and they might have to get and use an extra extent just to accommodate the spillover forced by the segment header. So, to truly optimize the temporary tablespace, you can set the extents to be a multiple of sort area size PLUS 1 block. You will always waste some space in each sort extent, but the disk IO will be saved.

Now to truly tune sort area size, do some timings on your applications and also grab some performance statistics. The easiest way to do that is install and use Statspack. It started shipping with 8i and is a free tool, intended to replace the old bstat/estat scripts. You can grab a copy from the Oracle technical network site or probably Metalink. The 8.1.6 version has a script to retro-fit to 8.0.5. Install it, know it, live by it.

Run some application code for a test period, or a normal busy production period and get Statspack snaps at the beginning and end of the period. Observe response from the viewpoint of the application. Run and study the statspack report for that period and see how many disk sorts you did versus sorts in memory. If you had any disk sorts at all, try increasing the sort area size to the next IO chunk multiple. Note that you can do an 'alter session set sort_area_size = <size in bytes>' without bouncing the instance, but you have to do it for each tested session, from that session. Might be tough for an application server.

With the higher size, test again and measure response. Did end-to-end response get better, from the standpoint of the user? Did the disk sorts go away? If no noticeable change is experienced, put the sort area size back where it was and you are done. Otherwise, you can bump sort area size higher and test again. Of course, at some point the memory burden on the platform can become onerous. Certainly, if you see signs of memory starvation from a tool like vmstat - like heavy paging or swapping you have gone too far. Either back off sort area size or buy more physical memory. That enters the realm of a business decision, doesn't it?

Because this can be set for each user, you might have several "classes" of users, some of whom to OLTP work and other who do DSS stuff. You might find that the DSS people benefit from a larger sort area, so you might want to see about modifying their applications, if possible, to do the "alter session" thing as they log on. In 8i, you can create a logon trigger to do this for you.

Anyway, this is the short path to tuning sorts, from my experience.

• ricky

"@nnibale" wrote:

> hi,
>
> how to determinate the "good" value for the parameter sort_area_size ?
> ( oracle 8.0.4 on digital unix 4.0c with 2Go ram)
>
> Regards.
>
> @nnib
Received on Sat Apr 07 2001 - 13:28:15 CDT