Hi All,

my application is running very slow also cpu usage is also pretty high i had taken a database snapshot which says number of hash joins are in 100's and in some queries its close to 2000 ..i guess this is not a good sign because indexes are not being defined properly ..and db2 is creating hashing table during the execution of query.....what i conclude is number of hash joins should be reduced ...that will bring down the cpu utilisation as well ...

my question is ....am i correct in drawing in this inference ? if so ..what should be done ? (using db2advis tool to define proper indexes ) if not ..why so many hash joins ?

Thanks

1. "application is running very slow" and "cpu usage is also pretty high" are not necessarily related.

2. hash joins and the use of indexes (or presumed lack thereof) are not necessarily related

3. hash joins are not necessarily a bad thing

4. defining proper indexes is always a good thing

You should also look at the visual query plan for the queries which your application is basically executing.... maybe you can get something over there as well... then maybe you can decide whether your indexes , if present, are being used

As far as your CPU utilization is concerned, it is very open-ended as to what the problem can be.... as there can be a possibility that application server too is present on the same box which will be utilizing the system resources...

Hi oracle10gsingh

Whats the value of sortheap ? If it's default try to increase it and make sure you have also increased sortheap_threshold

thanks