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 11.2.0.1 to 11.2.0.3. I extracted 2000 queries from production and got their plans on both our patched 11.2.0.3 test instance and our un-patched 11.2.0.1 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.
– Bobby
Hi Bobby,
Bear in mind that autotrace uses explain plan undercover, and explain plan can lie. See: http://kerryosborne.oracle-guy.com/2010/02/autotrace-lies/
Joaquín González Carrasco
Joaquin thanks for your reply. You are correct about autotrace. It just does explain plan and uses dbms_xplan.display to get the plan out of plan_table. It would be better if it did dbms_xplan.display_cursor to show the real plan. Here is a way that I have tried to get the real plan of the most recently executed sql statement in a test script:
select * from table(dbms_xplan.display_cursor(null,null,’ALL’));
In my case I’m testing 2000 sql statements to see if any of them run for a long time. If I find certain ones that look like an issue then I’ll be sure to get their real plan using display_cursor on both the patched test system and the unpatched production one.
– Bobby