Real-life parallels

If you read AskTom you will probably know that there is no such thing as a universal go-faster switch in Oracle. Everything is “maybe faster, maybe slower and your mileage may vary”.

Over the past week or so we have been user-testing a revised parallelism setting on a 3 TB DW system. Originally, all of the tables had a fixed degree of parallel and we noticed that the CPUs were mainly idle throughout the day. As we can’t bank CPU for time of need we wanted to see if we could increase the degree of parallel and use more CPU in the process. We trialled changing the parallelism to default and let Oracle balance the load.

So, was it faster or slower? The answer was both. Some queries really benefited from 40 parallel processes but for some IO waits kicked in and slowed things to a crawl. And the other complaint from users was predictability of query run time. If you run a query on 40 slaves then the next time you run it you get just two then users do notice.