Re: checking for sql plan changes with DB changes
Date: Tue, 5 Jun 2012 11:26:24 -0500
Message-ID: <CAA2DszwXPpw2edQiFHAk+abAykxeX9MDar6jp+XQu5kBfF2OPg_at_mail.gmail.com>
Hi
You should read MOS note 167086.1. Also, SQL Performance analyzer (SPA) is the tool that you need to perform plan regression analysis. But, SPA would require minimal setup in production to capture tuning sets though.
If you are interested only about stability (and not worried about using 11g new features), you could upgrade the copy of prod database to 11g, set optimizer compatibility to 10.2, collect baselines, enable use of base lines, and set compatibility to 11g. Of course, if the application is not using bind variables, this approach might not be optimal.
Cheers
Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com - Specialists in Performance,
RAC and EBS
Blog: http://orainternals.wordpress.com
OakTable member http://www.oaktable.com and Oracle ACE Director
Co-author of the books: Expert Oracle
Practices<http://tinyurl.com/book-expert-oracle-practices/>,
Pro Oracle SQL, Expert PL/SQL
Practices<http://tinyurl.com/book-expert-plsql-practices>
Join me for next RAC training in Fall
2012<http://www.orainternals.com/services/training/advanced-rac-training/>:
<http://tinyurl.com/book-expert-plsql-practices>
On Tue, Jun 5, 2012 at 9:38 AM, Dba DBA <oracledbaquestions_at_gmail.com>wrote:
> 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
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jun 05 2012 - 11:26:24 CDT