Home » Server Options » RAC & Failsafe » Check execution plan chang of SQL who have sql_plan_baseline on RAC Environnement
Check execution plan chang of SQL who have sql_plan_baseline on RAC Environnement [message #590860] Tue, 23 July 2013 10:25 Go to next message
tarlapeta
Messages: 2
Registered: July 2013
Location: Marseille
Junior Member
Hello,

I would oversee the execution plans of query who have a SQL_PLAN_BASELINE in a RAC environment.
For several reasons (changing the object type, for example), the PLAN_HASH_VALUE a SQL_ID can be changed although it was declared a Profile.
I want to find the sql that regularly monitors gv$sql view and give back the sql with sql_profile who have a modified execution plan.
I think about two method :
- maintaining a small repository with sql_id / plan_hash_values
- Either by check tables dba_hist * (knowing that in the last 24 hours, a total cycle sql query is necessarily played).

The problem is that:
- An instance can be stopped and thus the v $ sql can empty.
- A service can be switched from one instance to another

and i'm a bad dba !

Thank you for your help.

Regards
Re: Check execution plan chang of SQL who have sql_plan_baseline on RAC Environnement [message #590861 is a reply to message #590860] Tue, 23 July 2013 10:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SQL statement do NOT spontaneously change.
When a Duhveloper changes a SQL statement, they should be responsible to check for any change in EXPLAIN PLAN.
Not every problem has a technical solution.
You have a procedural problem, which should be resolved by procedural changes.
Why are you frequently changing application SQL statements?
Re: Check execution plan chang of SQL who have sql_plan_baseline on RAC Environnement [message #590922 is a reply to message #590861] Wed, 24 July 2013 02:39 Go to previous messageGo to next message
tarlapeta
Messages: 2
Registered: July 2013
Location: Marseille
Junior Member
If the world was perfect, nobody need dba !! Wink

So , you can have a perfect process plan, nobody is perfect and can forget something !!!

Supervision, dba tools etc etc is for prevent human fault ...

Thanks for your response and sql code
Re: Check execution plan chang of SQL who have sql_plan_baseline on RAC Environnement [message #591042 is a reply to message #590922] Wed, 24 July 2013 08:49 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Compare source code changes utilizing source code repository.
Previous Topic: Storage Options for building Test RAC
Next Topic: cluster interconnect
Goto Forum:
  


Current Time: Thu Mar 28 18:15:46 CDT 2024