2010-08-05

no parallel hint when sorting or grouping

an useful article http://dbakevlar.blogspot.com/2010/02/lead-developer-and-i-have-been-working.html


I love parallel. I am thrilled with the performance gains I can attain when hashing large tables when accessing multiple partitions. I do not love parallel when someone decides to put a group by or order by into the statement. If any sorting has to occur on the statement, I've been seeing some odd behavior in about 80% of the executions. It appears that when sorting, it's not Oracle sorting the data all at once, but sorting one parallel slave's data set at a time. This creates an escalation in temp space usage that can become insanely difficult for Oracle to process efficiently and performance degrades immensely.

Moral:
Carefully test any parallel processing when sorting is involved. You may not receive performance benefits as expected.

Niciun comentariu:

Trimiteți un comentariu