How cool is this part IIa
Alberto Dell'era said previously Is perhaps big_table ordered on disk by "id", ie by the PK of t1,t2,t3 ? If yes, I wonder what would be the results if it got randomly "disordered" (re-created using a CTAS with "order by dbms_random").
What a difference. Instead of adding an order by dbms_random, I updated the primary key of BIG_TABLE to be random and re-ran the test with the constraints on (the check and primary key constraint).
Very promising, I had a “fake example” with the sorted data for the index, it was:
- insert, no error logging, 10.1 seconds elapsed, 7.36 cpu seconds
- insert, error logging, 47.6 seconds elapsed, 42.55 cpu seconds
- procedural code, 21 seconds elapsed, 17.99 cpu seconds
It is (after randomizing the sorted order of the primary key)
- insert, no error logging, 30 seconds elapsed, 26.56 cpu seconds
- insert, error logging, 68 seconds elapsed, 47.02 cpu seconds,
- procedural code, 54 seconds elapsed, 40.56 cpu seconds
Obviously, there is more to test! The LOG ERRORS still generated more redo — but less than 2x the redo (not 5 times). It generated only a little more undo and the latch gets were very comparable this time. Alberto – thanks for picking up on that one.
What I’d like to do is re-run the stuff with sorted/not sorted data.
Add a test to have 5% of the data fail (randomly), 10%, 15%, 20% and 25%. I believe anything above 25% indicates a serious failure in the ETL process and so wouldn’t be real world.
Check out update, delete and merge times similarly. We’ll work that out over the course of next week.
Hats off to Alberto!