Re: sort_area_size

Good point, Nuno. All the measurements must be normalized for db_block_size. Sort_area_size is set in bytes, which must be a clean multiple of block size. And, the IO chunk size from the operating system will be expressed in bytes usually, but if it is given in blocks, make sure you verify that this is the os block and that it is indeed 512 bytes per block for that operating system.

While on the subject, db_file_multiblock_read_count needs to be set, in Oracle blocks, to the equivalent IO chunk I have been talking about. If this is not set properly, two things are affected: 1) the wrong multiblock read will be requested by Oracle, either too small or too large and 2) the Cost Based Optimizer uses this value as part of the consideration as to the relative cost of a full table scan.

Set it too high and full table scans look cheap to cbo and you will find yourself doing more fts than you ought to. Of course, understating db_file_multiblock_read_count will cause the multiblock reads to be smaller and you will do too many IOs. So, be sure to be dead on accurate when setting this parameter.

Also, on the subject of the logon trigger, this feature can be a great help for diagnostics. You can use it to set events that are specific to one user without affecting others. Especially nice with application servers where you don't get a convenient user session with which to inteverene. You have to use something like v$session and/or v$process to do some "who the hell am I" type of logic, which will always be specific to your environment. That is, look for a particular username, machine name, whatever and emit some "alter session..." ddl.

• ricky

Nuno Souto wrote:

> On Sat, 07 Apr 2001 18:28:15 GMT, Ricky Sanchez <rsanchez_at_more.net>
> wrote:
>
> >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.
>
> And don't forget to look at the database block size. It's pointless to
> ask for an extent size that is not an exact multiple of it, it will
> cause even more confusion as to what is allocated where and when.
>
> Reminds me of the default tablespace allocation I found of (initial
> 5K, next 5K) in a database with a block size of 16K. No confusion
> whatsoever as to what the objects where being allocated with...
>
> >
> >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.
>
> That is one very nice piece of info and makes a lot of sense! I wish
> all my PS clients where using 8i. With third party "locked code"
> apps, this trigger is worth its weight in gold!
>
> Cheers
> Nuno Souto
> nsouto_at_bigpond.net.au.nospam
> http://www.users.bigpond.net.au/the_Den/index.html
Received on Sun Apr 08 2001 - 08:32:56 CDT