Almost one year to date since my last blog. A year has gone but it seems like yesterday. Don't ask where I was and what I was doing, it seems both are a mystery to me as well!
This summer I had the chance to migrate some 11gR2 databases to Exadata. A "souped-up" box with absolutely no spinning disks, all 100% storage on Flash! And one would think anything would just run at blazing speeds on this, you just load it up and go home, but.. ahem.. it all depends.
While I/O intensive operations were really blazing, for example tablespace creations or full table scans, etc, other normal OLTP transactions that were mostly CPU bound not so much. And I think it is ok, since Exadata was primarily an appliance created to destroy the longest pole in a DB's tent, I/O operations.
But the gotcha for us was a very small change. After we migrated the number of processes on the new 11.2.0.4 DB on Exadata just broke out like an avalanche, completely bringing the Exadata server to its knees. All hell broke lose, especially the bean counters who had paid for these expensive machines now watching it unable to take the load that the old 11.2.0.3 non-Exadata 5-node RAC could. Oracle SR's were flying all over the place. And of course we know how fast those get resolved!!
Any way the issue though simple was this - the degree on 1 index of a table with 7 rows was set to DEFAULT. And we know that setting degree to DEFAULT means that Oracle will usually spawn (#of Thread per CPU) X (number of CPUs) parallel query processes. But the beauty was that in 11.2.0.3 the setting of DEFAULT on the index was totally ignored and the execution plan did not use any parallelism. But come to 11.2.0.4 the DEFAULT was indeed used, but hear this the Degree of Parallelism (DOP) that the optimizer came up with was like 32,000. Imagine a scenario where we had at least 15000 executions of simple SQL each with a DOP of 32,000!! Not even a benevolent Providence can help you here let alone a poor Exadata!