I have a rather sluggish DB2 V8.1 database being hit by a number of large complex ( and probably rather inefficient ) queries. I'm looking at various ways of speeding it up.

The server is a single quad core processor machine with Intraparallelism turned on. I know that with large queries and multiple processors Intraparallelism would be useful but does it have any benefits ( or even downsides) running with multpile cores ??

Should I turn it off ?

Intraparallelism allows DB2 to run some queries in parallel, thus speeding up the overall query. You need multiple processors (cores) for this to work.

You will probably get more bang for the buck by tuning the queries and adding appropriate indexes. Look at the access plans for the queries and see what can be changed to improve performance.

Andy

Only certain queries can take advantage of intrapartition parallelism. For your 4 cores, any large tables that will have table scans should have 4 containers. You should also use large page sizes and prefetch for these tablespaces (put your smaller tables in a different tablespace). With 4 containers, you prefetch size should be 4 times your extent size.

There are other db and dbm parms that need to be set besides setting intra_parallel at the dbm level. See this for details:
https://publib.boulder.ibm.com/infoc.../t0004886.html

Thanks for the replies.

I'll be doing reorgs/runstats during the weekend on some of the larger tables which may hopefully improve things as well as looking more into some of the things you suggested..

With regards to tuning SQL, are there any decent websites/books on the subject that you can recommend ?