Real-life parallels

February 15th, 2006 by Peter Scott

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.

Posted in Data Warehousing, Oracle Database |

Comments

  1. Doug Burns Says:

    February 15th, 2006 at 10:58 pm

    Pete,

    That’s a pretty timely blog from my point of view as I’m just finishing off tons of tests for the PX paper I’m presenting at the Hotsos Symposium.

    I’m also interested in your comments about users having a fierce resistance to inconsistent response times. That was an issue I raised in my previous paper. Although Oracle is only doing what is for the wider good of the system, it’s simply unacceptable to users.

    Cheers,

    Doug