Mohamed Houri’s Oracle Notes

September 23, 2017

SQL Plan Baseline, SQL Profile and materialized CTE

Filed under: Materialized view,Sql Plan Managment — hourim @ 5:32 pm

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.

Advertisements

2 Comments »

  1. Hi Mohamed,
    Thank you for this post.
    You said “Let’s now force Oracle to use a new name for the materialized CTE”,
    how did you force it?

    Comment by ahmed aangour — September 25, 2017 @ 4:16 am | Reply

  2. Hi Ahmed

    Sorry for this delay in answering you. Oracle will normally compile a new TEMP table transformation name at each hard parse. This is why I forced a hard parse by flushing the shared pool

    SQL> with a as(select /*+ materialize */ rownum n1 from dual
      2        connect by level <=5)
      3  select * from a;
    
            N1
    ----------
             1
             2
             3
             4
             5
    
    SQL> start xpsimp
    
    --------------------------------------------------------------------------------------
    | Id  | Operation                                | Name                      | Rows  |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                         |                           |       |
    |   1 |  TEMP TABLE TRANSFORMATION               |                           |       |
    |   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D661B_35D96D |       |
    |   3 |    COUNT                                 |                           |       |
    |   4 |     CONNECT BY WITHOUT FILTERING         |                           |       |
    |   5 |      FAST DUAL                           |                           |     1 |
    |   6 |   VIEW                                   |                           |     1 |
    |   7 |    TABLE ACCESS FULL                     | SYS_TEMP_0FD9D661B_35D96D |     1 |
    --------------------------------------------------------------------------------------
    
    SQL> alter system flush shared_pool;
    
    System altered.
    
    SQL> with a as(select /*+ materialize */ rownum n1 from dual
      2        connect by level <=5)
      3  select * from a;
    
            N1
    ----------
             1
             2
             3
             4
             5
    
    SQL> start xpsimp
    
    --------------------------------------------------------------------------------------
    | Id  | Operation                                | Name                      | Rows  |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                         |                           |       |
    |   1 |  TEMP TABLE TRANSFORMATION               |                           |       |
    |   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D661C_35D96D |       |
    |   3 |    COUNT                                 |                           |       |
    |   4 |     CONNECT BY WITHOUT FILTERING         |                           |       |
    |   5 |      FAST DUAL                           |                           |     1 |
    |   6 |   VIEW                                   |                           |     1 |
    |   7 |    TABLE ACCESS FULL                     | SYS_TEMP_0FD9D661C_35D96D |     1 |
    --------------------------------------------------------------------------------------
    

    Best regards

    Comment by hourim — September 30, 2017 @ 12:41 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: