VERY slow sorts

[MikeJones]

Hi all,

I'm running a query that returns 41K rows. If I run it without a sort it takes 9 seconds, If i run it with a sort it takes 49 seconds. Now I now disk sorts are slow but I never knew them to be THAT slow.

So I looked at the temporary tablespace in which I'll be doing all my sorts and it looks like this.

The tablespace is of type temporary, it has one object in there at the moment (I have just run my query with the sort and noone else is using the system) which has a system generated name (4,1770) it is 80 meg in size but has initial and next extent sizes of only 65K. there are a total of 1,280 extents created for it.

Is this my problem? It looks like a good contender to me, if not can anyone point me in the right direction?

If it is the Problem what do I need to do to sort it out?
Queries of this size are common and we have quite a few Meg to play with the temporary tablespace is 500MB in total.

Thanks again to anyone who can help.

Mike.

 

[karluk]

One symptom of setting too small a temp ts extent size is supposed to be a large number of sort extent pool latch waits. Can you query v$latch (I think that's the right view), and see if you have a lot of waits on this latch? Better yet, query v$latch both before and after running your slow query, and see how the numbers change.

 

[MikeJones]

Which row from v$latch should I be interested in all of them? there is one called sort extent pool? It's got the word sort in it?

Thanks,

Mike.

 

[karluk]

V$latch has one row for each type of latch. In your case you are interest in querying

select * from v$latch where name = 'sort extent pool';

That should return one row. I'm not sure that this is a productive line of inquiry, but in general you should try to gather as much information as possible about possible bottlenecks.

 

[carp]

I would want to make sure that the extent size was an integer multiple of SORT_AREA_SIZE.

I would also consider bumping up the size of SORT_AREA_SIZE (adjusting the extent sizes of the TEMPORARY tablespace as I went), and also set SORT_MULTIBLOCK_READ_COUNT so that SORT_AREA_SIZE turns out to be an integer multiple of SORT_MULTIBLOCK_READ_COUNT.

 

[MikeJones]

I've had a wander through a couple of DBA books but they don't seem to give any estimates for anything given a database size.

The DB is an OLTP and is around 8GB in size. The sort I am trying to perform is unlikey, but possible.

The sort area size is 64K
the sort area size retained is 64K
The temp space is 500MB
There is one object in it at 80MB which gets created in 64K chuncks
The sort multi block read is 2
the Shared Pool is 100M

Do these sound OK to you? Should I increase any of them?

I DID try setting the sort_area_size to 500K at session level and running the query it halfed the execution time, but I think setting the parameter this high could swamp the SGA when lots of users are on the system. (i.e. Production)

Thanks again,

Mike.

 

[MikeJones]

BTW,

Karluk,

The Latches seem fine, while there were alot of gets (51K) there were no waits or misses, infact all other columns where zero.

Could it be that the Disk sort is going as fast as it can?

Mike.

 

[sem]

You may try to increase the sort_area_size. It will not swamp the SGA because:


SORT_AREA_SIZE specifies in bytes the maximum amount of memory Oracle will use for a sort. After the sort is complete, but before the rows are returned, Oracle releases memory down to the size specified by the SORT_AREA_RETAINED_SIZE parameter. After the last row is returned, Oracle releases the remainder of the memory.

 

[MikeJones]

Sorry I didn't really explain myself here. I've read a couple of books that say you can make the sort_area_size up to 1 MB, but if you have 100 users doing sorts that's 100MB of memory gone (I also incorrectly said the memory would be taken from the SGA, I've since read that it is taken from the O/S Pool). If to much memory goes the O/S may start paging the actual Oracle DB!!

 

[karluk]

Here is a suggestion, based on Carp's comments, that is likely to do some good: increase initial and next extent in the temporary tablespace to 128k.

Right now the extent sizes of 64k are only slightly larger than the 64k sort area size. But a sort only goes to disk if it's too large to fit in (sort area size) of memory. That means that most of your disk sorts are forced to allocate at least two extents. Only the ones that are just barely over 64k can fit in a single extent.

Going with a 128k extent size will, in general, cut in half the number of extents allocated to each sort. I don't see how that can do any harm, and it may produce a noticable benefit.

 

[MikeJones]

Thanks Karluk,

I've spent last night with my nose in a couple of books (Gur Harrison and an Oracle Press one, I would say who the author is but it's since been swiped from my desk).

They recommended much the same but for completeness this is what I'm changing...

Increase the Sort space to 256K
Increase the initial and next on the temp segment to 256K (Might make this 512K now based on what you have said)
Increase the sort_multiblock_read_count to 8 (was 2)

These won't get done until tonight, but then hopefully I'll have a chance to test it out when the DB is relatively quite.

I'm a bit woried that I'm changing alot of things all at once, and I won't be able to tell what's giving me the best performance boost, but then at another level I'm struggling to identify my disk I/O on our HP box. (AIX is sooo simple)

But thanks and I'll post any benifits, I'm slowly starting to realise what a nightmare DBA's encounter on a day to day basis.

Mike.

 

[carp]

You might also try bumping sort_multiblock_read_count up - maybe up to 8, depending on your db_block_size. Assuming a block size of 4k, this would allow your server process to handle a sort_area_size merge in 2 trips instead of 8.

 

[ljspop]

Hi Mike,

If this is an adhoc or report type query (not something in an application everyone is going to be using), you can do an alter session to adjust your sort area size and not affect the amount allocated by default to all other sessions.

Just another (hopefully useful) option.

Bob Bob Lowell
ljspop@yahoo.com