checking for sql plan changes with DB changes

  • From: Dba DBA <oracledbaquestions@xxxxxxxxx>
  • To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 5 Jun 2012 10:38:19 -0400

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).


--
//www.freelists.org/webpage/oracle-l


Other related posts: