Q and A

Asked and Answered

In your article on OLTP and data warehouse performance tuning , you write: "In a DW database, you nearly always want to use an INTRA_PARALLEL value of YES". Is that correct? IBM is telling us to turn it off, although in my last EEE shop, we saw significant query improvements when we turned intra-parallel on. When is intra-parallelism appropriate in a DSS environment and when isn't it?

Scott Hayes responds:

As your experience tells you, you are right in wanting to use Intra_Parallel=Yes in a data warehouse environment. IBM might be steering you away from YES because this causes shared memory sorts instead of private sorts; therefore, SHEAPTHRES and SORTHEAP have to be more carefully tuned (POST THRESHOLD SORTS aren't allowed in a shared memory sort/Intra_Parallel=YES environment). I use a chart in my SORT & IO Tuning talk that clearly shows enormous elapsed time improvements with intra_parallel=YES. However, YES will suck the life out of your CPUs, so you shouldn't try it if your hardware is already running at 100 percent CPU usage.

See a complete archive of reader/author Q&As .