Re: sort_area_size

Ricky Sanchez wrote:
>
> @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

An effective way to get to an appropriate sort size is to set events 10032 and 10033 judiciously and scrutinising the output...

hth

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"