Re: checking for sql plan changes with DB changes

  • From: wblanchard@xxxxxxxxxxxxxxx
  • To: oracledbaquestions@xxxxxxxxx
  • Date: Tue, 5 Jun 2012 09:55:27 -0500

You can use SQL Tuning Sets to move the queries between instances for 


From:   Dba DBA <oracledbaquestions@xxxxxxxxx>
To:     ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Date:   06/05/2012 09:39 AM
Subject:        checking for sql plan changes with DB changes
Sent by:        oracle-l-bounce@xxxxxxxxxxxxx

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


Although this e-mail and any attachments are believed to be free of any virus 
or other defect which might affect any computer system, it is the 
responsibility of the recipient to check that it is virus-free and the sender 
accepts no responsibility or liability for any loss, injury, damage, cost or 
expense arising in any way from receipt or use thereof by the recipient.

The information contained in this electronic mail message is confidential 
information and intended only for the use of the individual or entity named 
above, and may be privileged.  If the reader of this message is not the 
intended recipient, you are hereby notified that any dissemination, 
distribution or copying of this communication is strictly prohibited.  If you 
have received this transmission in error, please  contact the sender 
immediately, delete this material from your computer and destroy all related 
paper media.  Please note that the documents transmitted are not intended to be 
binding until a hard copy has been manually signed by all parties.
Thank you.


Other related posts: