Monday 24 August 2015
How to restore an old execution plan, using dbms_spm.load_plans_from_sqlset
A real scenario: one specific query changed its execution plan and started to run for a very long time. We could not find any reason for this, after chacking statistics, indexes status and so on. We could not even revert to the old good plan using hints.
Luckily, we still had the AWR snapshots where we had the good execution plan, so we could follow the steps below:
1) Identify when did it run correctly the last time and write down the AWR specific snapshots.
2) Create an sql tuning set, based on this AWR snapshots.
3) Load this sql set.
4) Load SQL execution plans from SQL set into SQL baseline.
A very good site which is decribing all these steps is:
https://aprakash.wordpress.com/2012/07/05/loading-sql-plan-into-spm-using-awr/
Luckily, we still had the AWR snapshots where we had the good execution plan, so we could follow the steps below:
1) Identify when did it run correctly the last time and write down the AWR specific snapshots.
2) Create an sql tuning set, based on this AWR snapshots.
3) Load this sql set.
4) Load SQL execution plans from SQL set into SQL baseline.
A very good site which is decribing all these steps is:
https://aprakash.wordpress.com/2012/07/05/loading-sql-plan-into-spm-using-awr/
Subscribe to:
Posts (Atom)