Parameter Wednesday: DBM CFG – INTRA_PARALLEL

You may also like...

7 Responses

  1. Brian says:

    I’m considering enabling this on an OLTP system (mixed use.. reporting.. batch). If I set the DFT_DEGREE to a fixed value (say 4, system has 24 CPUs), will there still be a hit on the optimizer? I would understand if it were set to ANY, then the optimizer has to figure out which degree to use.

    • Ember Crooks says:

      If using it on a mixed-use system, generally the best way to go is to set DFT_DEGREE to 1. This way, queries that you do not specify a degree of parallelism for will get 1. This works best because we are most likely to be unable to change the SQL from OLTP applications, while we are more likely to be able to change those from DSS applications. Also, it is usually better to err on the side of too little parallelism rather than too much parallelism. Usually the performance of the OLTP workload is slightly more critical than the DSS workload. The OLTP workload will absolutely be impacted if it is forced to use a degree of 4 for singleton-row queries. ANY is always an option, but I prefer not to use it for a mixed workload.

  2. Norman Wong says:

    There is a change in DB2 V10.1 that allows this to be changed at a connection level either through a call to a System Stored Procedure (admin_set_intra_parallel) or have WLM change it. This allows systems with a mixed workload of OLTP and query to co-exist in the same database. Haven’t tried it myself, but that’s the design goal.

    Would be interesting to hear of field experiences.

  3. rafia says:

    I have set INTRA_PARALLEL = yes , DFT_DEGREE=ANY, and MAX_QUERYDEGREE=1, still in the output of db2exfmt it shows None for Intra parallelism, however based on the above parameter settings I think it should show Intra-partition parallelism.
    My DB2 version is Express-C V10.5 and running on 4 core machine with 64-bit ubuntu. Also my DB is single partitioned.
    Does anybody have any clues why intra-partition is not working?
    I’ve also checked with restarting the instance and rebinding all packages.

    • Ember Crooks says:

      Have you tried different queries? Not all queries will benefit from parallelism, and the optimizer will choose whether to use it or not for each one.

      • rafia says:

        I’ve tried almost 10 benchmark queries to test this. The weird thing when I ran these queries on different system (i.e. with different RAM and no of cores) then intra partition parallelism was used. Do you have some clue about this behaviour?

Leave a Reply

Your email address will not be published. Required fields are marked *