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:
- different predicate part
- different plan_hash_value because of generated CBO transformation view names
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
Mohamed Houri’s Oracle Notes 