YAPT

Yet Another PX Test (if only to give readers something different to comment on)

Jonathan Lewis had suggested larger extents and freelist segment space management (instead of ASSM) could benefit the PX tests I’ve been running. I’m pretty sure it was Jeff Moss that suggested bigger blocks would be more appropriate for the type of Data Warehouse environment that would be most likely to use PX. I tried all three in combination on the E10K last week and was very impressed by the improvement. Instead of 8Kb blocks and 1Mb extents, I used 32Kb blocks and 32Mb extents. Let’s try a quick table this time instead of a graph 😉

Using just the Hash Join example and only testing DOPs of between 1 and 16, there’s an improvement of about 10-25%. I knew there would be some improvement but was quite surprised just how much. Of course, this is just on this particular test on this particular configuration (as I keep saying) and I should have tested each change individually but my time is pretty limited at the moment because we have a big go-live month at work. I think it does indicate how important the space allocation method is when processing large volumes of data quickly with PX – those percentage improvements would be pretty noticeable to the users.