Have you ever waited on a parallelized statement? Parallel execution is said to be fast, efficient, system-exhausting. Far from that! There’s a 10.2.0.4 x86_64 system with 16 cores and over 600MB/s write-IO ability, one statement running, one CPU burning, one developer waiting. For days.
Finally, at the end of the week, the admin is involved. Because people know, that the time when admins are working most efficiently is weekends and nights.
Analyzing the MERGE that was running:
MERGE into TABLE USING (SELECT FROM (SELECT FROM (SELECT FROM view on a join on TABLE and ANOTHER_TABLE)));
Now I know what the Oracle statement parser and the cost-based optimizer are paid for! Looking on the processes spawned, I saw many many of auto-parallelized parallel query processes from the selects and the view in brackets. But all of them were waiting with “PX Deq Credit: send blkd”. Research at Oracle’s confirmed the first guess: There’s a parallel data producer waiting for a consumer, it’s a classical idle event.
And there has been one of 33 processes that was waiting for CPU – I found my single burner. But the question arises, why does this stuff NOT WRITE in parallel, as well?
Testing that on a 4-core blade server makes it look as if it works there: All CPUs have been burning. So what’s the difference between the boxes? DB version was 10.2.0.3, but I refused to believe in that.
So I tried to simplify my life and started DDL and DML testing with a huge table (about 80 millions rows in each partition of four). DDL with CREATE TABLE PARALLEL 32 AS SELECT * FROM TABLE; showed exactly this behaviour: One consumer, lots of waiting producers.
Next one: INSERT INTO TABLE SELECT * FROM ANOTHER_TABLE; – same again. Absolutely reproductible! But why? Looking into the docs delivered several limitations on parallel DML: My test table contained a user defined object (data)type, and since the producer was a SELECT *, it has had no chance avoiding a write into this column – that’s the end of parallel DML.
I’ve been euphoric! Found it, got it, looked into the original MERGE – and there has been no object (data) type. But now I knew where to look: A trigger on the target table blocks parallel writing of DML as well. Ok, let’s disable it. CLANG – same again. As long as the trigger exists, it prevents us from parallelization.
Two lessons for my developers learned:
- avoid object types as field data type
- avoid triggers on loaded tables like hell
Useful information for all admins:
SQL> select * from v$pq_sesstat;
or
SQL> alter session force parallel DML parallel 32;
SQL> explain plan for INSERT /*+ APPEND PARALLEL("TABLE", 32) */ \
INTO TABLE SELECT /*+ PARALLEL("ANOTHER_TABLE", 32) */ * \
FROM ANOTHER_TABLE;
SQL> select * from table(DBMS_XPLAN.DISPLAY);
The latter automatically shows the extended version for parallel execution as soon as PX is enabled for that session.
Thanks to all guys on the oracle-l freelists-Mailinglist, who helped to solve this issue! You are great, folks!
Best,
Usn