Parallel with 11G
Sigh….IF I could only live in “Uncle Larry’s” perfect database world where I have more control over what choices are made from the conception of the database’s life and know exactly what goals a development group has vs. Oracle.
How much easier a DBA’s life would be if we could just say, “Sorry, Oracle’s new feature would work well only if you completely redesign the logic of your process in code A, B and C of our main systems that have naturally grown in complexity over the years. Of course, you don’t mind adjusting all priorities for the next six months while we accomplish this, right Management?”
The lead Mart Developer and I have been doing a bit of testing in a new development arena with 11g and in memory parallel tuning. We created the database, the developer brought over the data he wanted to work with, starting with a smaller, 10% dataset to start with. We set up the parameters just how Oracle’s recommendations stated, except for queuing- We’ve tested this in another 11g environment and decided this was a poor choice until we are sure we have all hints in our old code out of the way that could impact with this parameter.
The mart developer removed all hard-coded hints from the CTAS statement, ensure he had only 10% of the standard data we usually worked with and executed the statement- 32 threads, excellent performance and we were thrilled!
So thrilled, the developer pulled over all the tables the CTAS uses instead of the 10% we were working with. Now, the thing that’s difficult to remember when going from hard-coded parallel hints to automatic parallel, is the development server has less resources than our production environment and more databases to compete with for resources. Oracle is a lot smarter than we are and knows a poor choice when it sees one. When it has the object and system statistics to know that performing a CTAS in parallel of this much data, with this little resources available, Oracle will make the right choice and no parallel is seen…
My lead mart developer was, of course, not happy and quite frustrated. He wants to know why there isn’t parallel- I review the explain plan and note in the statement that the process has been forced to serial. Oracle has made it’s choice and it’s choice does not involve parallel.
Where does this leave a database administrator that has in the past explained to many that we were utilizing too many resources towards parallel that with 11g will have the new challenge of the database being a bit more conservative with parallel and being left to explain why you are not over-allocating, but not allocating at all?
At least I have a number of months to work with the developers to force more processes to run with an even lighter footprint on the database than has been done in the past.. 🙂