In an earlier post I described the process that I go through to check the plans of production SQL queries to see if they change when you do a system upgrade or patch. Today I’ve been following that process to prepare for a production patch from 220.127.116.11 to 18.104.22.168. I extracted 2000 queries from production and got their plans on both our patched 22.214.171.124 test instance and our un-patched 126.96.36.199 production instance.
Unfortunately, most of the plans changed. The reason in this case is that we had to change an init parameter to resolve an issue we found in testing. Now most of the plans are different. Ack!
But, the good thing is that I can run all of the queries (no updates, deletes, or inserts) against our test database and see which ones run for a long time. Then I can check their plan and runtime on production. That will at least help me catch any major gotchas.
I’ve done all this before, but the thing I’m doing differently this time is using this setting in my sqlplus script that tests all the production SQL against our test database:
SET AUTOTRACE TRACEONLY
What is cool about this setting is that it will still give you the plan and statistics but won’t list out all the returned rows. I have no idea how much output the queries return but all I really want is their elapsed time and plans.
So, like I said in my previous post this process isn’t perfect but it may help me detect any big plan changes caused by our patch and init change and address them before going to production.