Mohamed Houri’s Oracle Notes

January 5, 2020

PHV2

Filed under: Tuning — hourim @ 2:47 pm

PLAN_HASH_VALUE_2 (Phv2) includes the hash value of the execution(PLAN_HASH_VALUE) and the hash value of its predicate part. Whenever, I have a doubt about the difference between two similar execution plans I use the following query to get their corresponding Phv2:

-- phv2.sql
SELECT
  p.sql_id
 ,p.plan_hash_value
 ,p.child_number
 ,t.phv2
FROM 
  gv$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 = '&sql_id'
AND p.other_xml is not null;

Phv2 is important when trying to understand why an enabled and accepted SPM baseline plan has not been used. Indeed, the CBO will use the SPM plan provided it will be able to produce, at query execution time, an execution plan having a PHV2 that equals the PLAN_ID of the SPM plan stored in the SPM baseline as shown below in the CBO-SPM plan selection diagram:

There exist different reasons which make two execution plans having the same plan_hash_value but a different Phv2 of which I can list two:

There is, though, an effort deployed by Oracle to make Phv2 independent from the generated CBO view names like it is already the case for the materialized CTE SYS_TEMP table transformation view as explained by Dominic Brooks in this blog post.

That’s said, a couple of weeks ago I observed an interesting practical real life case :

SQL> @phv2
Enter value for sql_id: 9m80uw87u330r

SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER       PHV2
------------- --------------- ------------ ----------
9m80uw87u330r      1858552262            1  513024182
9m80uw87u330r      1858552262            0  513024182

As you can see, the above parent SQL_ID has two child cursors n°0 and n°1 having the same PLAN_HASH_VALUE and the same PHV2. But they are performing differently as shown by the following execution statistics where child n°0 consumes more physical I/O then child n°1 :

SQL> @sqlstats
Enter value for sql_id: 9m80uw87u330r

     CHILD PLAN_HASH_VALUE   AVG_GETS   AVG_PIOS  AVG_ETIME     EXECS
---------- --------------- ---------- ---------- ---------- ---------
         0      1858552262    1786506    1275800 453.184926         1
         1      1858552262    3408017       4095 18.6310735         4

I would have loved to direct you to the excellent presentation done by Mauro Pagano about same plan but different performance to have an idea about the performance difference between these two child cursors. But despite they have the same PLAN_HASH_VALUE and the same PHV2 they are, in this case, two really different execution plans as the followings prove:

SQL> select * from table(dbms_xplan.display_cursor('9m80uw87u330r',null));

SQL_ID  9m80uw87u330r, child number 0
-------------------------------------
Plan hash value: 1858552262
------------------------------------------------------------------------------------
| Id  | Operation                     | Name                    | Rows  | Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                         |       |          |
|   1 |  CONCATENATION                |                         |       |          |
|*  2 |   HASH JOIN                   |                         |     1 | 00:03:17 |
|*  3 |    TABLE ACCESS FULL          | UB_XYZBLTYVG_VALLEE_PAD | 46302 | 00:00:01 |
|*  4 |    TABLE ACCESS BY INDEX ROWID| RB_SALA                 |    34M| 00:01:50 | --> Notice the presence of *
|*  5 |     INDEX RANGE SCAN          | RB_SALA_CER_IDX         |    34M| 00:00:05 |
|*  6 |   HASH JOIN                   |                         |     1 | 00:09:03 |
|*  7 |    TABLE ACCESS FULL          | UB_XYZBLTYVG_VALLEE_PAD | 46302 | 00:00:01 |
|*  8 |    TABLE ACCESS BY INDEX ROWID| RB_SALA                 |    93M| 00:05:02 |
|*  9 |     INDEX RANGE SCAN          | RB_SALA_CER_IDX         |    93M| 00:00:15 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("D"."SUB_ACCT"<>(-:SYS_B_21))
   5 - access("D"."STATE"=:SYS_B_15 AND "D"."CERMETHOD"=:SYS_B_16)
       filter("D"."CREATEDBY"<>:SYS_B_20)
   9 - access("D"."STATE"=:SYS_B_14)
       filter(("D"."CREATEDBY"<>:SYS_B_20 AND (LNNVL("D"."STATE"=:SYS_B_15) OR
              LNNVL("D"."CERMETHOD"=:SYS_B_16))))
Note
-----
   - SQL profile prf_9m80uw87u330r_2229773696 used for this statement
   - SQL patch "PATCH_9m80uw87u330r" used for this statement


SQL_ID  9m80uw87u330r, child number 1
-------------------------------------
Plan hash value: 1858552262
------------------------------------------------------------------------------------
| Id  | Operation                     | Name                    | Rows  | Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                         |       |          |
|   1 |  CONCATENATION                |                         |       |          |
|*  2 |   HASH JOIN                   |                         |     1 | 00:04:58 |
|*  3 |    TABLE ACCESS FULL          | UB_XYZBLTYVG_VALLEE_PAD | 46302 | 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID| RB_SALA                 |    34M| 00:03:30 | --> Notice the absence of *
|*  5 |     INDEX RANGE SCAN          | RB_SALA_CER_IDX         |    34M| 00:00:05 |
|*  6 |   HASH JOIN                   |                         |     1 | 00:13:38 |
|*  7 |    TABLE ACCESS FULL          | UB_XYZBLTYVG_VALLEE_PAD | 46302 | 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID| RB_SALA                 |    93M| 00:09:37 |
|*  9 |     INDEX RANGE SCAN          | RB_SALA_CER_IDX         |    93M| 00:00:13 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("D"."STATE"=:SYS_B_15 AND "D"."CERMETHOD"=:SYS_B_16)
       filter(("D"."CREATEDBY"<>:SYS_B_20 AND "D"."SUB_ACCT"<>(-:SYS_B_21)))   
   9 - access("D"."STATE"=:SYS_B_14)
       filter(("D"."CREATEDBY"<>:SYS_B_20 AND "D"."SUB_ACCT"<>(-:SYS_B_21) AND
              (LNNVL("D"."STATE"=:SYS_B_15) OR LNNVL("D"."CERMETHOD"=:SYS_B_16))))

Note
-----
   - SQL profile prf_9m80uw87u330r_2229773696 used for this statement
   - SQL patch "PATCH_9m80uw87u330r" used for this statement

I have deleted from the two execution plans predicates n°2,3,6, 7 and 8 since they are not playing any role in this context.

Two important points can be pointed out here:

  • The first interesting point to emphasize is that there is no filter predicate on the RB_SALA table at line id n° 4 in the second execution plan (absence of *)
  •  The second difference resides in the filter predicates n° 5 and 9 of the second execution plan. They have an extra clause on the SUB_ACCT column.

The drastic performance enhancement I brought to this query, was particularly due to the elimination of the filter predicate n°4 on the big table RB_SALA. Filtering at the index level reduces the total physical I/O consumption (4,095 versus 1,275,800) making the execution time of cursor child n°1 very interesting (18 seconds versus 453).

If you want to know the whole story, then here’s what I did:

One of my clients asked me to look at a critical query that was not very well performing following an upgrade from 11gR2 to 12cR1. This query was using the execution plan of child n°0. I knew immediately that if I can get rid of the filter predicate from the RB_SALA at line n°4 I will make this query performing very well. Therefore, I created the following index :

SQL> create /*+ parallel(4) */ index RB_SALA_CER_IDX_MHO on RB_SALA(STATE, CERMETHOD, CREATEDBY, SUB_ACCT) compress 3;

By the way spot how I managed to create the index using parallelism without having to unset the DOP of the created index afterward.

For the sake of completeness there exist in this application the following index which is used by a SQL Profile

RB_SALA_CER_IDX on RB_SALA(STATE, CERMETHOD, CREATEDBY);

I disabled the SQL patch and the SQL Profile and asked the client to launch the query, which unfortunately refused to use the new index and was, this time, dramatically slow.

After several failed tests trying to make the CBO using the new index I decided to use the brute force

prompt renaming existing RB_SALA_CER_IDX index used in SQL Profile
alter index RB_SALA_CER_IDX rename to RB_SALA_CER_IDX_OLD;
--
prompt setting existing RB_SALA_CER_IDX index invisible
alter index RB_SALA_CER_IDX_OLD invisible;
--
prompt rename newly created index to the old existing one so that it will be used by the SQL Profile 
alter index RB_SALA_CER_IDX_MHO rename to RB_SALA_CER_IDX;

With the above three commands I produced the performant and acceptable execution plan represented by child n°1 having the same PLAN_HASH_VALUE and the same PHV2 as that of the bad performing execution plan

How to model this case

Here’s an easy way to model this case:

SQL> create table t1 as select rownum n1, trunc((rownum -1)/3) n2 , 'xY' vc 
    from dual connect by level <=10; 

SQL> create index t1_idx on t1(n2);

SQL> select * from t1 where n2=0 and vc='xY';

        N1         N2 VC
---------- ---------- --
         1          0 xY
         2          0 xY
         3          0 xY

SQL_ID  gzfq0fm0jf4v9, child number 0
-------------------------------------
select * from t1 where n2=0 and vc='xY'

Plan hash value: 1775246573
--------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |       |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1     |     3 |
|*  2 |   INDEX RANGE SCAN                  | T1_IDX |     3 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("VC"='xY')
   2 - access("N2"=0)

SQL> @Phv2
Enter value for sql_id: gzfq0fm0jf4v9

SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER       PHV2
------------- --------------- ------------ ----------
gzfq0fm0jf4v9      1775246573            0  409377851

SQL> alter index t1_idx invisible;

SQL> alter index t1_idx rename to t1_idx_old;

SQL> create index t1_idx on t1(n2, vc);

SQL> select * from t1 where n2=0 and vc='xY';

        N1         N2 VC
---------- ---------- --
         1          0 xY
         2          0 xY
         3          0 xY

SQL_ID  gzfq0fm0jf4v9, child number 0
-------------------------------------
select * from t1 where n2=0 and vc='xY'

Plan hash value: 1775246573
--------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |       |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1     |     3 |
|*  2 |   INDEX RANGE SCAN                  | T1_IDX |     3 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("N2"=0 AND "VC"='xY')

SQL> @phv2
Enter value for sql_id: gzfq0fm0jf4v9

SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER       PHV2
------------- --------------- ------------ ----------
gzfq0fm0jf4v9      1775246573            0  409377851

Bottom Line

Don’t be surprised to see two non-identical execution plans having the same PLAN_HASH_VALUE, the same PHV2, generating the same rows but performing differently. This occurs particularly when

  •  a filter predicate on table access operation is deleted
  •  a filter predicate is enriched by an extra condition

2 Comments »

  1. Very useful and clear article. Is PHV2 persisted in some way, or is it only available in memory? Thank you very much.

    Comment by gerardo — March 14, 2020 @ 3:34 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply to Tuning issues – LEARNING DBA Cancel 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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Create a free website or 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.

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: