unable to get sort overflow count down (UDB 8.1) 
Author Message
 unable to get sort overflow count down (UDB 8.1)

brief summary. have a set of star schemas being hit with a constant
query load against an 8.1 server on an 8-way AIX 5.1 box. Seem unable
to get the sort overflow to sort ratio below 50%. Have defined
multi-column clustered indexes on the dimensions based on the workload
as well as on the fact table. Also left single column indexes for the
dimension columns in the fact tables. The plans indicate that the
indexes I set up are being used and hash joins are employed.

I have tried to raise sort heap to lessen the overflows and believe I
have sizes > row size + 8 * # rows from the various plans where a sort
would occur.

Problem is, the ratio does not seem to drop and if I raise it much my
queries seem to slow down horribly.

Realise this is vague but anything ringing a bell?



Sat, 30 Jul 2005 10:31:59 GMT
 unable to get sort overflow count down (UDB 8.1)

Quote:

> brief summary. have a set of star schemas being hit with a constant
> query load against an 8.1 server on an 8-way AIX 5.1 box. Seem unable
> to get the sort overflow to sort ratio below 50%. Have defined
> multi-column clustered indexes on the dimensions based on the workload
> as well as on the fact table. Also left single column indexes for the
> dimension columns in the fact tables. The plans indicate that the
> indexes I set up are being used and hash joins are employed.

> I have tried to raise sort heap to lessen the overflows and believe I
> have sizes > row size + 8 * # rows from the various plans where a sort
> would occur.

> Problem is, the ratio does not seem to drop and if I raise it much my
> queries seem to slow down horribly.

> Realise this is vague but anything ringing a bell?

Also check the snapshot about hash information (# of, overflow, ...).
Hash join will also use sortheap (a little aggresive).
Have you turn on intral_parallelism? Which degree?


Sat, 30 Jul 2005 12:17:20 GMT
 unable to get sort overflow count down (UDB 8.1)
A thought:

Have you raised SHEAPTHRES to a meaningful level?

Paul.

Quote:

> I have tried to raise sort heap to lessen the overflows and believe I
> have sizes > row size + 8 * # rows from the various plans where a sort
> would occur.

> Problem is, the ratio does not seem to drop and if I raise it much my
> queries seem to slow down horribly.

> Realise this is vague but anything ringing a bell?



Sun, 31 Jul 2005 17:33:55 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. db2batch returns different row counts for same query (UDB 8.1)

2. Problems with installation DB2 UDB 8.1 WE for Linux

3. Problems with installation DB2 UDB 8.1 WE for Linux

4. db2 udb 8.1 developer and workgroup edition differences

5. Upgrade from DB2 UDB 7.2 to 8.1 on Redhat

6. Isolation Level problems with UDB 8.1 on w2k

7. replication support in db2 udb personal edition v 8.1

8. DB2 UDB PE Version 8.1 & Linux

9. DB2 UDB LUW 8.1: type-2 index reporting with system catalogs

10. Web Control Center (WebCC) on DB2 UDB 8.1

11. UDB NT 8.1 And Access

12. If I uninstall 7.2 from my PC and install 8.1 can I administrate remote 7.2 UDB


 
Powered by phpBB® Forum Software