When developing code, usually temporary created objects like tables and indexes have been created just to test some scenario and check behaviour.
Very often, in case test passed reside much longer than it was planned.
For that reason, at some point in time, it’s needed to change object names to deploy code to upper environments, and finally to production.
So far no issue, syntax to perform such changes is straightforward:
ALTER TABLE … RENAME TO…
ALTER INDEX … RENAME TO …
But what about if you are using extended hints/outlines in your code?
Here is example of explain plan with outline.
SQL_ID 5fb97696fa7hz
Plan hash value: 3740834449
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| | | |
| 1 | NESTED LOOPS | | 1 | 218 | 2 (50)| 00:00:01 | | |
| 2 | SORT UNIQUE | | 1 | 14 | 1 (0)| 00:00:01 | | |
| 3 | INDEX FULL SCAN | RPM_ME_ITEM_GTT_I1 | 1 | 14 | 1 (0)| 00:00:01 | | |
| 4 | PARTITION HASH ITERATOR | | 167K| 32M| 0 (0)| | KEY | KEY |
|* 5 | TABLE ACCESS STORAGE FULL| XX_RPM_PRE_ME_ITEM_LOC | 167K| 32M| 0 (0)| | KEY | KEY |
----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
3 - SEL$5DA710D3 / RPM_ME_ITEM_GTT@SEL$2
5 - SEL$5DA710D3 / XX_RPM_PRE_ME_ITEM_LOC@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
OPT_PARAM('optimizer_dynamic_sampling' 4)
ALL_ROWS
NO_PARALLEL
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
INDEX(@"SEL$5DA710D3" "RPM_ME_ITEM_GTT"@"SEL$2" ("RPM_ME_ITEM_GTT"."ITEM" "RPM_ME_ITEM_GTT"."ZONE_ID"))
FULL(@"SEL$5DA710D3" "XX_RPM_PRE_ME_ITEM_LOC"@"SEL$1")
LEADING(@"SEL$5DA710D3" "RPM_ME_ITEM_GTT"@"SEL$2" "XX_RPM_PRE_ME_ITEM_LOC"@"SEL$1")
USE_NL(@"SEL$5DA710D3" "XX_RPM_PRE_ME_ITEM_LOC"@"SEL$1")
SEMI_TO_INNER(@"SEL$5DA710D3" "RPM_ME_ITEM_GTT"@"SEL$2")
END_OUTLINE_DATA
*/
In the next step I’ll rename table XX_RPM_PRE_ME_ITEM_LOC to RPM_PRE_ME_ITEM_LOC and explain it again.
SQL_ID c51pwfjvcmr75
Plan hash value: 997273142
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 290 (100)| | | |
| 1 | NESTED LOOPS | | 1 | 101 | 290 (2)| 00:00:04 | | |
| 2 | SORT UNIQUE | | 1 | 14 | 1 (0)| 00:00:01 | | |
| 3 | INDEX FULL SCAN | RPM_ME_ITEM_GTT_I1 | 1 | 14 | 1 (0)| 00:00:01 | | |
| 4 | PARTITION HASH ITERATOR | | 24 | 2088 | 288 (1)| 00:00:04 | KEY | KEY |
|* 5 | TABLE ACCESS STORAGE FULL| RPM_PRE_ME_ITEM_LOC | 24 | 2088 | 288 (1)| 00:00:04 | KEY | KEY |
-------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
3 - SEL$5DA710D3 / RPM_ME_ITEM_GTT@SEL$2
5 - SEL$5DA710D3 / RPM_PRE_ME_ITEM_LOC@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
OPT_PARAM('optimizer_dynamic_sampling' 4)
ALL_ROWS
NO_PARALLEL
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
INDEX(@"SEL$5DA710D3" "RPM_ME_ITEM_GTT"@"SEL$2" ("RPM_ME_ITEM_GTT"."ITEM" "RPM_ME_ITEM_GTT"."ZONE_ID"))
FULL(@"SEL$5DA710D3" "RPM_PRE_ME_ITEM_LOC"@"SEL$1")
LEADING(@"SEL$5DA710D3" "RPM_ME_ITEM_GTT"@"SEL$2" "RPM_PRE_ME_ITEM_LOC"@"SEL$1")
USE_NL(@"SEL$5DA710D3" "RPM_PRE_ME_ITEM_LOC"@"SEL$1")
SEMI_TO_INNER(@"SEL$5DA710D3" "RPM_ME_ITEM_GTT"@"SEL$2")
END_OUTLINE_DATA
*/
As you can see, in execution plan, block name / object alias section and Outline data we can see a new table name.
It means that if you are using Outlines to fix the execution plan, you need to take care of new object names to fix the new SQL with renamed objects (in this case just removing XX_ part in all places between BEGIN_OUTLINE_DATA and END_OUTLINE_DATA).
All this is possible because Query block name / Object Alias remain the same, which includes transformed parts of the query.
Anyway, you should always double check Outline section to be absolutely sure that new query will have the same execution plan as original one.
Unlike Query block names and Object alias, sql_id and plan_hash_value both have a new hash values.
Action is also required in case you are using SQL Profile or SQL Plan baseline, but those two are not transparent as hints/Outlines, which is one of advantages of hinting approach.
Summary:
Changing object names automatically invalidate hints, Outlines, SQL Profiles and SQL Baselines.
Positive side of hints/outlines approach is you can see it (in V$SESSION for example, AWR …), while the SQL Profiles and SQL Baselines remain hidden.
To achieve stability when you already have acceptable performances, you should disable changes in execution plan (fixing a plan) no matter which technology you are going to use (all of them have pros and cons as usual).
If you are not using any of mentioned technology, no action is required, but at the cost of very likely changes in execution plan.
Action required when changing object names is in most cases trivial (as in presented example) , and achieved stability and performances are much more valuable in large Database systems as opposed to do nothing and let Oracle to transparently generate a new execution plan with a new object names.
Comments