checking for sql plan changes with DB changes
Date: Tue, 5 Jun 2012 10:38:19 -0400
Message-ID: <CAE-dsOKKapZdrHeSxcBsS8xsDa=b4ii5nbUms5==eu=QQG+u0g_at_mail.gmail.com>
Oracle 10.2.0.5. I know about application testing in 11g. I have not used. We are not going to 11g until next year. We have a large database with a vast number of queries. The application is over 15 years old. So simply adding an index can cause a plan to fail in part due to an old hint. This is a big project, so you need tickets, approval, time set aside, to make any legacy changes. Those are generally not given. so we can't go back and take out old hints, etc... there is new work coming in.
I am hoping there is an easy way to do this.
We regularly take EMC BCV copies of production. So I have an entire production database with the same memory settings, etc... to work with and I can get a new copy as needed. We use this for alot of things such as performance testing, writing complex queries against real data, and testing new releases to make sure all the DDL works.
Is there a way for me to export the sql queries from prod and import them to my BCV copy without running them? If I do an 'explain plan for' the query seems to get saved in v$sql with the 'explain plan for' and a new sql_id. Basically I want to get teh queries to compile and then diff the plans between prod and our BCV copy to see which plans changed. Then from there look at them to see if they will be a problem. We have a vast number of queries, so running them is a problem (just getting viable parameters to run them is not realistic on top of writing a script to do all this). This way if a legacy query is going to be a problem, I can have a sql profile ready to go out with the release.
is it at all possible to do this? I am hoping to catch more plan changes before they go to prod. I know I can go to the AWR to get the plan history of most of the queries(this does not store everything).
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jun 05 2012 - 09:38:19 CDT