Re: checking for sql plan changes with DB changes
Date: Tue, 5 Jun 2012 09:55:27 -0500
Message-ID: <OFFEDA1AD3.273812B2-ON86257A14.0051D523-86257A14.0051FC86_at_oshkoshcorp.com>
You can use SQL Tuning Sets to move the queries between instances for testing.
docs.oracle.com/cd/B19306_01/server.102/b14211/sql_tune.htm
WGB From: Dba DBA <oracledbaquestions_at_gmail.com> To: ORACLE-L <oracle-l_at_freelists.org> Date: 06/05/2012 09:39 AM
Subject: checking for sql plan changes with DB changes Sent by: oracle-l-bounce_at_freelists.org
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-l
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.
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jun 05 2012 - 09:55:27 CDT