This is a reformulated simple answer to one of my colleague’s question of whether a materialized CTE would affect the functioning of a SQL Profile or a SQL Plan Baseline or both.
A very short answer is : it will not affect neither the first nor the second
A little bit more elaborated answer is:
- it will not affect the use of SQL Profile because the transient name of the materialized SYS TEMP table is not part of the force matching signature of both the SQL query and the SQL profile
- The changing generated name of the materialized SYS TEMP table has no effect on the PHV2 of the generated CBO plan.
An answer with demonstration is:
-- table
create table t_st
as select
rownum n1, mod(rownum,5) n2, trunc((rownum-1/4)) n3, dbms_random.value(20,30) n4
from dual
connect by level <=1e4;
-- query
with got_my_t2 as
(select /*+ materialize */ n1, d1 from t2_st where n1 <= 11)
select
max(n4)
from t_st a
where exists
(select null
from got_my_t2 b
where a.n1 = b.n1);
-- execution plan
select * from table(dbms_xplan.display_cursor);
SQL_ID 53fhbt0gjrwb3, child number 0
-------------------------------------
Plan hash value: 1035791491
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TEMP TABLE TRANSFORMATION | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6667_32BBFD | |
|* 3 | TABLE ACCESS FULL | T2_ST | 11 |
| 4 | SORT AGGREGATE | | 1 |
|* 5 | HASH JOIN RIGHT SEMI | | 11 |
| 6 | VIEW | | 11 |
| 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6667_32BBFD | 11 |
| 8 | TABLE ACCESS FULL | T_ST | 10000 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("N1"<=11)
5 – access("A"."N1"="B"."N1")
To the above setup I am going to add a SQL profile and a SQL plan baseline as shown below:
–- first SQL profile using Oracle script
SQL>@coe_xfr_sql_profile
Parameter 1:
SQL_ID (required)
Enter value for 1: 53fhbt0gjrwb3
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
1035791491 ,014
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 1035791491
SQL>@coe_xfr_sql_profile_53fhbt0gjrwb3_1035791491.sql
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_53fhbt0gjrwb3_1035791491 completed
-- second a SQL Plan Baseline
declare
spm_op pls_integer;
begin
spm_op := dbms_spm.load_plans_from_cursor_cache (sql_id => '53fhbt0gjrwb3');
end;
/
As such, re-executing the initial query will show the above double execution plan protection in action:
with got_my_t2 as
(select /*+ materialize */ n1, d1 from t2_st where n1 <= 11)
select
max(n4)
from t_st a
where exists
(select null
from got_my_t2 b
where a.n1 = b.n1);
Plan hash value: 1035791491
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TEMP TABLE TRANSFORMATION | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6668_32BBFD | |
|* 3 | TABLE ACCESS FULL | T2_ST | 11 |
| 4 | SORT AGGREGATE | | 1 |
|* 5 | HASH JOIN RIGHT SEMI | | 11 |
| 6 | VIEW | | 11 |
| 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6668_32BBFD | 11 |
| 8 | TABLE ACCESS FULL | T_ST | 10000 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("N1"<=11)
5 - access("A"."N1"="B"."N1")
Note
-----
- SQL profile coe_53fhbt0gjrwb3_1035791491 used for this statement
- SQL plan baseline SQL_PLAN_a9tbt2yhxacpz46c3c689 used for this statement
Now that I have built the setup I would like to demonstrate that if the name of the TEMP table transformation
SYS_TEMP_0FD9D6668_32BBFD
changes it will not prevent both the SQL Profile and the SPM baseline from constraining the initial query to stick with the fixed execution plan. Before going further I would like to get the PHV2 of the above execution plan:
SELECT
p.sql_id
,p.plan_hash_value
,p.child_number
,t.phv2
FROM v$sql_plan p
,xmltable('for $i in /other_xml/info
where $i/@type eq "plan_hash_2"
return $i'
passing xmltype(p.other_xml)
columns phv2 number path '/') t
WHERE p.sql_id = '&1'
and p.other_xml is not null;
SQL>@phv2
Enter value for 1: 53fhbt0gjrwb3
SQL_ID PLAN_HASH_VALUE CHILD_NUMBER PHV2
------------- --------------- ------------ ----------
53fhbt0gjrwb3 1035791491 0 1187235465
Let’s now force Oracle to use a new name for the materialized CTE
SQL>alter system flush shared_pool;
System altered.
with got_my_t2 as
(select /*+ materialize */ n1, d1 from t2_st where n1 <= 11)
select
max(n4)
from t_st a
where exists
(select null
from got_my_t2 b
where a.n1 = b.n1);
Plan hash value: 1035791491
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TEMP TABLE TRANSFORMATION | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6669_32BBFD | |
|* 3 | TABLE ACCESS FULL | T2_ST | 11 |
| 4 | SORT AGGREGATE | | 1 |
|* 5 | HASH JOIN RIGHT SEMI | | 11 |
| 6 | VIEW | | 11 |
| 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6669_32BBFD | 11 |
| 8 | TABLE ACCESS FULL | T_ST | 10000 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("N1"<=11)
5 - access("A"."N1"="B"."N1")
Note
-----
- SQL profile coe_53fhbt0gjrwb3_1035791491 used for this statement
- SQL plan baseline SQL_PLAN_a9tbt2yhxacpz46c3c689 used for this statement
As you can see Oracle has derived a new CTE TEMP Table name:
SYS_TEMP_0FD9D6669_32BBFD
which, as expected, has not prevented both the SPM and the SQL Profile from being used. Furthermore, despite the TEMP table name change, the PHV2 of the generated plan is still identical to the first one mentioned above:
SQL>@phv2 Enter value for 1: 53fhbt0gjrwb3 SQL_ID PLAN_HASH_VALUE CHILD_NUMBER PHV2 ------------- --------------- ------------ ---------- 53fhbt0gjrwb3 1035791491 0 1187235465
Bottom Line : don’t worry about your materialized Common Table Expression used in critical queries that you want to stabilize via a SQL profile or a SQL Plan Baseline. Their changing name will not affect these two execution plan stabilizing techniques.
Mohamed Houri’s Oracle Notes 