Worse performance when using parallel dml.
374322Nov 24 2010 — edited Nov 30 2010Greetings All, I am running Oracle 11.2.0.2 on RedHat Linux 64 Bit. 64 Gb Ram, 2 Sockets, 8 cores, Raid 0 array (15 15k disks).
I have a simple operation that loads up a table from which the data is sources from several other tables in the database.
The target table is created and then altered to allow for parallel operations which have proven to be quite beneficial when I read from the table (25 million rows).
However, when the table is being initially loaded if I issue an alter session to enable parallel dml the table load takes 2000 seconds.
If I do the same load but I do not turn on parallel dml (serialized as seen in enterprise manager) the entire table load takes about 80 seconds.
If I look at the load operation in enterprise manager with parallel dml I see massive wait events taking place, specifically I see:
"enq: CR - block range reuse ckpt"
This metric is dominating the graph on the "Performance" tab for "Average Active Sessions". It actually shows up here as "Other", however when I drill into the graph I see that it is all about:
"enq: CR - block range reuse ckpt"
My question is this:
Why is Parallel Query (which is supposed to help) killing the performance of this data load?
Thanks, L.