PLAN_HASH_VALUE - How equal (and stable?) are your execution plans - part 2

Back to part 1

In the previous post I've shown some characteristics of the PLAN_HASH_VALUE information provided by Oracle.

Now if you want to have greater control over how a hash value on the execution plan should be calculated, in particular regarding some attributes of the PLAN_TABLE column that are not used to calculate the provided PLAN_HASH_VALUE, e.g. the filter and access predicates, here are some ideas how to do it yourself.

Obviously we need to calculate a hash value across multiple columns and multiple rows from a given PLAN_TABLE-equivalent source, which could be e.g. V$SQL_PLAN or a real PLAN_TABLE. This requires the following:

- A function calculating a hash value from a given input
- The input needs to represent the columns and rows from the PLAN_TABLE
- Therefore ideally we need to combine the columns to a single expression
- And finally combine the expressions from the different rows again to a single expression that can be fed into the hash value function.

In Oracle 10.2 and later all these things can be done with built-in functions. In previous releases some more work and user-defined functions are required, but it's definitely feasible without too much effort.

A function calculating a hash value from a given input

Apart from any user-defined functions that calculate a hash value there are multiple options available provided by Oracle as built-in functions.

In pre-10.2 versions there are only a few built-in functions that calculate a hash value.

One is DBMS_UTILITY.GET_HASH_VALUE which has already been there since pre-9i releases (at least 8i, but likely even earlier). The other one is DBMS_OBFUSCATION_TOOLKIT.MD5 which has been introduced in 9i.

The main difference is that the MD5 function - the name suggests it - calculates a hash value according to the standard MD5 algorithm whereas GET_HASH_VALUE simply returns a 31bit hash value calculated.

Version 10 adds some more functions, in particular the built-in ORA_HASH function and the DBMS_CRYPTO package that basically supersedes the DBMS_OBFUSCATION_TOOLKIT with enhanced functionality.

These functions differ in some details but basically can all be used for the given task.

Here I'm just highlighting some noticeable variations:

The DBMS_OBFUSCATION_TOOLKIT.MD5 is not very well documented and quite cumbersome to use. It cannot be used directly from SQL (you get always "ORA-06553: PLS-307: too many declarations of 'MD5' match this call), and it throws an exception when passing a NULL value. Therefore it's probably best to be wrapped by a user-defined function, e.g. something like this:

create or replace function hashkey(in_string in varchar2) return varchar2 as
begin
if in_string is null then
return to_char(null);
else
return rawtohex(utl_raw.cast_to_raw(sys.dbms_obfuscation_toolkit.md5(input_string=>in_string)));
end if;
end;
/

Note that this version of MD5 uses a string as input but seems to return a raw value, but VARCHAR2 as data type. Therefore some cumbersome conversions are required (cast to raw and then raw to hex) to get actual VARCHAR2 output.

The DBMS_CRYPTO package offers some overloaded versions of the HASH function. It's interesting to note that DBMS_CRYPTO.HASH offers the capability to process LOBs as input to calculate the hash value.

The most interesting function due to its simplicity and versatility is the ORA_HASH function. One special feature is that it is capable of taking nested tables as input in addition to the normal built-in scalar data types.

Here's a simple performance comparison of the different functions (performed on 11.1.0.7 Win32):

SQL>
SQL> drop function hashkey;

Function dropped.

Elapsed: 00:00:00.04
SQL>
SQL> create or replace function hashkey(in_string in varchar2) return varchar2 as
2 begin
3 if in_string is null then
4 return to_char(null);
5 else
6 return rawtohex(utl_raw.cast_to_raw(sys.dbms_obfuscation_toolkit.md5(input_string=>in_string)));
7 end if;
8 end;
9 /

Function created.

Elapsed: 00:00:00.04

SQL> drop table random_data purge;

Table dropped.

Elapsed: 00:00:00.04
SQL>
SQL> exec dbms_random.seed(0)

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL>
SQL> create table random_data
2 as
3 select
4 dbms_random.string('A', 100) as the_data
5 from
6 dual
7 connect by
8 level <= 100000;

Table created.

Elapsed: 00:00:31.30
SQL>
SQL> insert /*+ append */ into random_data
2 select * from random_data;

100000 rows created.

Elapsed: 00:00:01.17
SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL>
SQL> insert /*+ append */ into random_data
2 select * from random_data;

200000 rows created.

Elapsed: 00:00:01.06
SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL>
SQL> select count(the_data)
2 from (
3 select
4 the_data
5 from
6 random_data
7 );

COUNT(THE_DATA)
---------------
400000

Elapsed: 00:00:01.66
SQL>
SQL> select count(the_data)
2 from (
3 select
4 the_data
5 from
6 random_data
7 );

COUNT(THE_DATA)
---------------
400000

Elapsed: 00:00:01.14
SQL>
SQL> select max(the_hash)
2 from (
3 select
4 ora_hash(the_data) as the_hash
5 from
6 random_data
7 );

MAX(THE_HASH)
-------------
4294934713

Elapsed: 00:00:02.09
SQL>
SQL> select max(the_hash)
2 from (
3 select
4 ora_hash(the_data) as the_hash
5 from
6 random_data
7 );

MAX(THE_HASH)
-------------
4294934713

Elapsed: 00:00:01.54
SQL>
SQL> select max(the_hash)
2 from (
3 select
4 dbms_utility.get_hash_value(the_data, 0, 2147483647) as the_hash
5 from
6 random_data
7 );

MAX(THE_HASH)
-------------
2147451066

Elapsed: 00:00:04.48
SQL>
SQL> select max(the_hash)
2 from (
3 select
4 dbms_utility.get_hash_value(the_data, 0, 2147483647) as the_hash
5 from
6 random_data
7 );

MAX(THE_HASH)
-------------
2147451066

Elapsed: 00:00:04.08
SQL>
SQL> select max(the_hash)
2 from (
3 select
4 hashkey(the_data) as the_hash
5 from
6 random_data
7 );

MAX(THE_HASH)
--------------------------------------------------------------------------------
FFFF54FE7872DBDD4E870FFC20C1DDE0

Elapsed: 00:00:15.23
SQL>
SQL> select max(the_hash)
2 from (
3 select
4 hashkey(the_data) as the_hash
5 from
6 random_data
7 );

MAX(THE_HASH)
--------------------------------------------------------------------------------
FFFF54FE7872DBDD4E870FFC20C1DDE0

Elapsed: 00:00:14.19
SQL>
SQL> select max(the_hash)
2 from (
3 select
4 dbms_crypto.hash(utl_raw.cast_to_raw(the_data), 2) as the_hash
5 from
6 random_data
7 );

MAX(THE_HASH)
--------------------------------------------------------------------------------
FFFF54FE7872DBDD4E870FFC20C1DDE0

Elapsed: 00:00:14.42
SQL>
SQL> select max(the_hash)
2 from (
3 select
4 dbms_crypto.hash(utl_raw.cast_to_raw(the_data), 2) as the_hash
5 from
6 random_data
7 );

MAX(THE_HASH)
--------------------------------------------------------------------------------
FFFF54FE7872DBDD4E870FFC20C1DDE0

Elapsed: 00:00:13.66
SQL>

So it's obvious that the ORA_HASH function has the least overhead (no PL/SQL context switch required), and the standard MD5 based functions do have the most overhead, but the calculated hash values are 128bit hashes and therefore are much more robust regarding hash collisions, but that doesn't really matter in our case here.

For our particular case here the DBMS_CRYPTO.HASH and the ORA_HASH function are best suited because they are capable of handling large concatenations. In case of DBMS_CRYPTO.HASH this is done via support for CLOBs, in case of ORA_HASH it's the support for collections.

The input needs to represent the columns and rows from the PLAN_TABLE

Concatenating the columns of interest to build a single expression from a PLAN_TABLE row is straightforward, however it poses the potential issue that some of the attributes that are likely to be included are of VARCHAR2(4000) type, so a simple VARCHAR2 based concatenation is not possible. You either need to use a CLOB instead or somehow shorten the expression. In order to avoid costly temporary LOB concatenation operations I prefer the latter and use again a hash function on the large columns to shorten the expression significantly, allowing to represent the whole row expression in a single VARCHAR2(4000).

Building a single expression for an execution plan out of the row expressions is again something that can be addressed by well-known techniques that transform rows to columns (basically a pivot operation which is explicitly supported by the PIVOT operator introduced in 11g), or in this particular case you could call it a "Rows to string" or "String Aggregation" operation.

There are several ways how to accomplish this using SQL, some of them are e.g. demonstrated on the SQL snippets site.

In this particular case again we have the potential issue that the resulting string could exceed the 4,000 bytes limit imposed by the VARCHAR2 data type, and therefore some of the available techniques might not be appropriate.

For instance the "hierarchical query" approach for string aggregation will fail with "result of string concatenation too long" in the SYS_CONNECT_BY_PATH function used to concatenate the string in such cases.

Using nested tables, the build-in 10g COLLECT aggregate function and a custom TO_STRING function that is capable of handling and generating CLOBs can not be used either because Oracle doesn't guarantee the order of aggregates and therefore the order of the aggregated strings might not be the same for the same input data leading to different hash values.


In 10g there are a couple of favorable approaches to this:

1. Use the MODEL clause to perform the aggregation. This allows to control the aggregation process and therefore can prevent any error caused by strings longer than 4,000 characters/bytes. Furthermore the model clause implicitly defines an order of processing by the dimensions defined in the model. The order is important in our case, since the concatenated values need to be ordered always in the same way, otherwise the same execution plan might result in different hash values.

2. Use some other built-in aggregation method capable of handling CLOBs, e.g. the XMLType based XMLAGG function can return a CLOB from the resulting XMLType (but depending on what you do be aware of specialties like the treatment of XML special characters like ampersand, less than, greater than, single-quote and double-quote, which can be addressed using the DBMS_XMLGEN.CONVERT function, which is in our case not an issue, since we simply what to generate a hash value and are not interested in any special character treatment as along as it is done consistently).

3. Use a custom aggregation function, similar to Tom Kyte's STRAGG custom aggregate function. This one also needs special treatment to address the requirement to order the aggregates in a deterministic way.

4. Use nested tables and the ORA_HASH function. This seems to be the most straightforward solution in 10g, since it addresses the sort order issue due to the implicit ordering performed by the ORA_HASH function, avoids any costly LOB operations and requires the least coding effort.

In pre-10g versions there are only a couple of options left:

1. The MODEL clause is not supported on pre-10g

2. Use some other built-in aggregation method capable of handling CLOBs, e.g. the XMLAGG function is supported in 9i

3. Use a custom aggregation function

4. ORA_HASH is not supported either

One particular issue in 9i is that it doesn't provide a built-in HASH function that supports large concatenations. Neither DBMS_CRYPTO.HASH nor ORA_HASH are supported.

Examples

So here are some examples for the aforementioned options. If you're going to use V$SQL_PLAN as source then note that in order to minimize the latch contention and the risk of inconsistencies when reading from V$SQL_PLAN (Oracle doesn't guarantee any read consistency on dynamic performance views) you might want to populate a (global temporary) table from the V$SQL_PLAN or use at least the (still undocumented) MATERIALIZE hint of the subquery factoring WITH clause to generate a global temporary table on the fly from the contents.

For the following examples a simple table has been created via CTAS:

create table my_sql_plan
as
select * from v$sql_plan;

Note that the examples cover all available columns from V$SQL_PLAN (Version 10.2.0.4 in that case) starting with the OPERATION column up to and except for the OTHER_XML column.

This means that you get different hash values even if e.g. only the BYTES or CPU_COST columns are different. For a reasonable analysis this is probably too granular and you should omit those columns that you don't want to consider for the hash value calculation.

1. Calculating a plan hash value using the MODEL clause for string aggregation

select
hash_value
, plan_hash_value
, child_number
, sys.dbms_crypto.hash(aggr1 || aggr2 || aggr3 || dummy_clob, 2) as the_hash
from (
select
hash_value
, plan_hash_value
, child_number
, aggr1
, aggr2
, aggr3
from (
select
hash_value
, plan_hash_value
, child_number
, id
, dense_rank() over (order by hash_value, plan_hash_value, child_number) as rnk
, sys.dbms_crypto.hash(utl_raw.cast_to_raw(
operation
|| '-' || options
|| '-' || object_node
|| '-' || object#
|| '-' || object_owner
|| '-' || object_name
|| '-' || object_alias
|| '-' || object_type
|| '-' || optimizer
|| '-' || id
|| '-' || parent_id
|| '-' || depth
|| '-' || position
|| '-' || search_columns
|| '-' || cost
|| '-' || cardinality
|| '-' || bytes
|| '-' || other_tag
|| '-' || partition_start
|| '-' || partition_stop
|| '-' || partition_id
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(other, 1, 2000)), 2)
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(other, 2001)), 2)
|| '-' || distribution
|| '-' || cpu_cost
|| '-' || io_cost
|| '-' || temp_space
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(access_predicates, 1, 2000)), 2)
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(access_predicates, 2001)), 2)
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(filter_predicates, 1, 2000)), 2)
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(filter_predicates, 2001)), 2)
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(projection, 1, 2000)), 2)
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(projection, 2001)), 2)
|| '-' || time
|| '-' || qblock_name
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(remarks, 1, 2000)), 2)
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(remarks, 2001)), 2)
)
, 2) as hash_path_row
from (
select
*
from
my_sql_plan
)
)
model
return updated rows
partition by (
rnk
)
dimension by (
id
)
measures (
cast(hash_path_row as varchar2(4000)) as aggr1
, cast('' as varchar2(4000)) as aggr2
, cast('' as varchar2(4000)) as aggr3
, hash_value
, plan_hash_value
, child_number
)
rules
iterate (10000)
until presentv(aggr1[ITERATION_NUMBER + 2], 1, 2) = 2 (
aggr1[0] = aggr1[0] ||
case
when length(aggr1[0]) + length(aggr1[ITERATION_NUMBER + 1]) + 1 > 4000
or length(aggr2[0]) + length(aggr1[ITERATION_NUMBER + 1]) + 1 > 4000
then ''
else '-' || aggr1[ITERATION_NUMBER+1]
end,
aggr2[0] = aggr2[0] ||
case
when length(aggr1[0]) + length(aggr1[ITERATION_NUMBER + 1]) + 1 > 4000
and length(aggr2[0]) + length(aggr1[ITERATION_NUMBER + 1]) + 1 < 4000
then '-' || aggr1[ITERATION_NUMBER+1]
else ''
end,
aggr3[0] = aggr3[0] ||
case
when length(aggr2[0]) + length(aggr1[ITERATION_NUMBER + 1]) + 1 > 4000
then '-' || aggr1[ITERATION_NUMBER+1]
else ''
end
)
),
(
select
substr(other_xml, 1, 0) as dummy_clob
from
my_sql_plan
where
rownum <= 1
);

Note that a dummy CLOB is used for the final string concatenation. Obviously other hash functions instead of DBMS_CRYPTO.HASH could be used but are limited by the 4,000 bytes maximum size of VARCHAR2 (except for ORA_HASH, see below). Due to the RAW input type the columns exceeding 2,000 bytes are split apart, since a RAW value can have a maximum of 2,000 bytes. However I've encountered different behaviour, under some circumstances the conversion failed when more than 2,000 bytes were in the VARCHAR2 data, but some other tests were successfully able to work on converted VARCHAR2 values larger than 2,000 bytes.

2. Calculating a plan hash value using the XMLAGG function for string aggregation

select
hash_value
, plan_hash_value
, child_number
, sys.dbms_crypto.hash(
substr(
dbms_xmlgen.convert(
extract(
xmlagg(
xmlelement("V", '-' || rawtohex(hash_path_row)
)
order by rawtohex(hash_path_row)
), '/V/text()'
).getclobval()
, 1
)
, 2
)
, 2
) as the_hash
from (
select
hash_value
, plan_hash_value
, child_number
, hash_path_row
from (
select
hash_value
, plan_hash_value
, child_number
, id
, dense_rank() over (order by hash_value, plan_hash_value, child_number) as rnk
, sys.dbms_crypto.hash(utl_raw.cast_to_raw(
operation
|| '-' || options
|| '-' || object_node
|| '-' || object#
|| '-' || object_owner
|| '-' || object_name
|| '-' || object_alias
|| '-' || object_type
|| '-' || optimizer
|| '-' || id
|| '-' || parent_id
|| '-' || depth
|| '-' || position
|| '-' || search_columns
|| '-' || cost
|| '-' || cardinality
|| '-' || bytes
|| '-' || other_tag
|| '-' || partition_start
|| '-' || partition_stop
|| '-' || partition_id
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(other, 1, 2000)), 2)
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(other, 2001)), 2)
|| '-' || distribution
|| '-' || cpu_cost
|| '-' || io_cost
|| '-' || temp_space
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(access_predicates, 1, 2000)), 2)
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(access_predicates, 2001)), 2)
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(filter_predicates, 1, 2000)), 2)
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(filter_predicates, 2001)), 2)
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(projection, 1, 2000)), 2)
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(projection, 2001)), 2)
|| '-' || time
|| '-' || qblock_name
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(remarks, 1, 2000)), 2)
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(remarks, 2001)), 2)
)
, 2) as hash_path_row
from (
select
*
from
my_sql_plan
)
)
)
group by
hash_value
, plan_hash_value
, child_number;

Note that I've added the DBMS_XMLGEN.CONVERT function for convenience to demonstrate its usage to overcome the handling of XML special characters like ampersand, greater than etc. Again different hash functions instead of DBMS_CRYPTO.HASH could be used but are limited by the 4,000 bytes maximum size of VARCHAR2 (except for ORA_HASH, see below).

3. Calculating a plan hash value using a user-defined aggregation function for string aggregation

drop function aggregate_concat_ord;

drop type agg_concat_ord;

drop type table_of_varchar;

create or replace type table_of_varchar as table of varchar2(4000);
/

create or replace type agg_concat_ord as object
(
vals table_of_varchar,
static function ODCIAggregateInitialize (init_context IN OUT agg_concat_ord) return number,
member function ODCIAggregateIterate (self IN OUT agg_concat_ord, this_value IN varchar2) return number,
member function ODCIAggregateTerminate (self IN agg_concat_ord, result OUT clob, flags IN number) return number,
member function ODCIAggregateMerge (self IN OUT agg_concat_ord, merge_context IN agg_concat_ord) return number
);
/

create or replace type body agg_concat_ord is
static function ODCIAggregateInitialize (init_context in out agg_concat_ord) return number is
begin
init_context := agg_concat_ord (null);
init_context.vals := table_of_varchar();
return ODCIConst.Success;
end;
member function ODCIAggregateIterate (self in out agg_concat_ord, this_value in varchar2) return number is
begin
self.vals.extend;
self.vals (self.vals.last) := this_value;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate (self IN agg_concat_ord, result OUT clob, flags IN number ) return number is
begin
for r in (select column_value from table (self.vals) order by 1) loop
result := result || r.column_value;
end loop;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge (self IN OUT agg_concat_ord, merge_context IN agg_concat_ord) return number is
i integer;
begin
i := merge_context.vals.first;
while i is not null loop
self.vals.extend;
self.vals (self.vals.last) := merge_context.vals (i);
i := merge_context.vals.next (i);
end loop;
return ODCIConst.Success;
end;
end;
/

create or replace function aggregate_concat_ord (input varchar2) return clob parallel_enable
aggregate using agg_concat_ord;
/

select
hash_value
, plan_hash_value
, child_number
, sys.dbms_crypto.hash(substr(aggregate_concat_ord('-' || rawtohex(hash_path_row)), 2), 2) as the_hash
from (
select
hash_value
, plan_hash_value
, child_number
, hash_path_row
from (
select
hash_value
, plan_hash_value
, child_number
, id
, dense_rank() over (order by hash_value, plan_hash_value, child_number) as rnk
, sys.dbms_crypto.hash(utl_raw.cast_to_raw(
operation
|| '-' || options
|| '-' || object_node
|| '-' || object#
|| '-' || object_owner
|| '-' || object_name
|| '-' || object_alias
|| '-' || object_type
|| '-' || optimizer
|| '-' || id
|| '-' || parent_id
|| '-' || depth
|| '-' || position
|| '-' || search_columns
|| '-' || cost
|| '-' || cardinality
|| '-' || bytes
|| '-' || other_tag
|| '-' || partition_start
|| '-' || partition_stop
|| '-' || partition_id
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(other, 1, 2000)), 2)
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(other, 2001)), 2)
|| '-' || distribution
|| '-' || cpu_cost
|| '-' || io_cost
|| '-' || temp_space
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(access_predicates, 1, 2000)), 2)
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(access_predicates, 2001)), 2)
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(filter_predicates, 1, 2000)), 2)
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(filter_predicates, 2001)), 2)
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(projection, 1, 2000)), 2)
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(projection, 2001)), 2)
|| '-' || time
|| '-' || qblock_name
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(remarks, 1, 2000)), 2)
|| '-' || dbms_crypto.hash(utl_raw.cast_to_raw(substr(remarks, 2001)), 2)
)
, 2) as hash_path_row
from (
select
*
from
my_sql_plan
)
)
)
group by
hash_value
, plan_hash_value
, child_number;

Note that the user-defined aggregation function has two noteworthy characteristics: It ensures an order of the aggregates and it generates a CLOB value.
Again different hash functions could be used instead of DBMS_CRYPTO.HASH with the known limitations.

4. Calculating a plan hash value using the ORA_HASH and a user-defined type for string aggregation

create or replace type ntt_varchar2 as table of varchar2(4000);
/

select
hash_value
, plan_hash_value
, child_number
, ora_hash(cast(collect(to_char(hash_path_row, 'TM')) as ntt_varchar2)) as the_hash
from (
select
hash_value
, plan_hash_value
, child_number
, hash_path_row
from (
select
hash_value
, plan_hash_value
, child_number
, id
, dense_rank() over (order by hash_value, plan_hash_value, child_number) as rnk
, ora_hash(
operation
|| '-' || options
|| '-' || object_node
|| '-' || object#
|| '-' || object_owner
|| '-' || object_name
|| '-' || object_alias
|| '-' || object_type
|| '-' || optimizer
|| '-' || id
|| '-' || parent_id
|| '-' || depth
|| '-' || position
|| '-' || search_columns
|| '-' || cost
|| '-' || cardinality
|| '-' || bytes
|| '-' || other_tag
|| '-' || partition_start
|| '-' || partition_stop
|| '-' || partition_id
|| '-' || ora_hash(other)
|| '-' || distribution
|| '-' || cpu_cost
|| '-' || io_cost
|| '-' || temp_space
|| '-' || ora_hash(access_predicates)
|| '-' || ora_hash(filter_predicates)
|| '-' || ora_hash(projection)
|| '-' || time
|| '-' || qblock_name
|| '-' || ora_hash(remarks)
) as hash_path_row
from (
select
*
from
my_sql_plan
)
)
)
group by
hash_value
, plan_hash_value
, child_number;

This example avoids any costly LOB operations. The string aggregation is performed by the COLLECT function that is available from 10g on. Note that the sort order issue caused by the COLLECT function (actually the lack of any defined order within the aggregate) is addressed by the ORA_HASH function that obviously sorts the passed data in case a collection is passed. This can be indirectly noticed when omitting the CAST operation to the NTT_VARCHAR2 custom collection type.

Here are some variations suitable for 9i databases:

1. Using a custom HASHKEY function built on top of DBMS_OBFUSCATION_TOOLKIT and the user-defined aggregation function from above

create or replace function hashkey(in_string in varchar2) return varchar2 as
begin
if in_string is null then
return to_char(null);
else
return rawtohex(utl_raw.cast_to_raw(sys.dbms_obfuscation_toolkit.md5(input_string=>in_string)));
end if;
end;
/

select
hash_value
--, plan_hash_value
, child_number
, hashkey(substr(aggregate_concat_ord('-' || hash_path_row), 2, 4000)) as the_hash
from (
select
hash_value
--, plan_hash_value
, child_number
, hashkey(
operation
|| '-' || options
|| '-' || object_node
|| '-' || object#
|| '-' || object_owner
|| '-' || object_name
|| '-' || optimizer
|| '-' || id
|| '-' || parent_id
|| '-' || depth
|| '-' || position
|| '-' || search_columns
|| '-' || cost
|| '-' || cardinality
|| '-' || bytes
|| '-' || other_tag
|| '-' || partition_start
|| '-' || partition_stop
|| '-' || partition_id
|| '-' || hashkey(other)
|| '-' || distribution
|| '-' || cpu_cost
|| '-' || io_cost
|| '-' || temp_space
|| '-' || hashkey(access_predicates)
|| '-' || hashkey(filter_predicates)
) as hash_path_row
from
(select * from my_sql_plan
)
)
group by
hash_value
--, plan_hash_value
, child_number;

Note that we have to restrict the final hash generation to the first 4,000 bytes, since the built-in hash functions in 9i don't support LOBs or collections. This means that without a user-defined hash function that supports LOBs or collections in 9i we can't differentiate between plans that have the same first 4,000 characters after aggregation. Since this is very unlikely it should matter only in exceptional cases. Furthermore the V$SQL_PLAN view in 9i doesn't cover the PLAN_HASH_VALUE function, therefore you would need to get that e.g. from V$SQL in case you want to show that.

Obviously the 9i V$SQL_PLAN also misses some of the columns that have been added in 10g (OBJECT_ALIAS, OBJECT_TYPE, PROJECTION, TIME, QBLOCK_NAME, REMARKS etc.).

2. Using an ordered collection and a custom TO_STRING function for string aggregation and the DBMS_UTILITY.GET_HASH_VALUE hash function

create or replace type ntt_varchar2 as table of varchar2(4000);
/

create or replace function to_string (
nt_in in ntt_varchar2,
delimiter_in in varchar2 default ','
) return clob is
v_idx pls_integer;
v_str clob;
v_dlm varchar2(10);
begin
v_idx := nt_in.first;
while v_idx is not null loop
v_str := v_str || v_dlm || nt_in(v_idx);
v_dlm := delimiter_in;
v_idx := nt_in.next(v_idx);
end loop;

return v_str;
end to_string;
/

with a as (
select
hash_value
--, plan_hash_value
, child_number
, dbms_utility.get_hash_value(
operation
|| '-' || options
|| '-' || object_node
|| '-' || object#
|| '-' || object_owner
|| '-' || object_name
|| '-' || optimizer
|| '-' || id
|| '-' || parent_id
|| '-' || depth
|| '-' || position
|| '-' || search_columns
|| '-' || cost
|| '-' || cardinality
|| '-' || bytes
|| '-' || other_tag
|| '-' || partition_start
|| '-' || partition_stop
|| '-' || partition_id
|| '-' || dbms_utility.get_hash_value(other, 0, 2147483647)
|| '-' || distribution
|| '-' || cpu_cost
|| '-' || io_cost
|| '-' || temp_space
|| '-' || dbms_utility.get_hash_value(access_predicates, 0, 2147483647)
|| '-' || dbms_utility.get_hash_value(filter_predicates, 0, 2147483647)
, 0, 2147483647) as hash_path_row
from
(select * from my_sql_plan
)
)
select
hash_value
--, plan_hash_value
, child_number
, dbms_utility.get_hash_value(
substr(to_string(cast(multiset(
select
hash_path_row
from
a
where
a.hash_value = b.hash_value
--and a.plan_hash_value = b.plan_hash_value
and a.child_number = b.child_number
order by
1) as ntt_varchar2), '-'), 1, 4000), 0, 2147483647) as the_hash
from (
select distinct
hash_value
--, plan_hash_value
, child_number
from
a
) b;

Again this needs to be limited to the first 4,000 bytes, and the PLAN_HASH_VALUE in 9i is missing. Furthermore the COLLECT aggregate function is not supported in 9i, so we need to generate the collections using the MULTISET subquery method.

Finally here's an example what you can do with these new hash values:

select
hash_value
, plan_hash_value
, child_number
, the_hash
, case when plan_hash_value = next_plan_hash_value and the_hash != next_the_hash then 'DIFF!' end as are_hashs_diff
from (
select
hash_value
, plan_hash_value
, child_number
, the_hash
, lead(plan_hash_value, 1) over (partition by hash_value, plan_hash_value order by child_number) as next_plan_hash_value
, lead(the_hash, 1) over (partition by hash_value, plan_hash_value order by child_number) as next_the_hash
from (
select
hash_value
, plan_hash_value
, child_number
, ora_hash(cast(collect(to_char(hash_path_row, 'TM')) as ntt_varchar2)) as the_hash
from (
select
hash_value
, plan_hash_value
, child_number
, hash_path_row
from (
select
hash_value
, plan_hash_value
, child_number
, id
, dense_rank() over (order by hash_value, plan_hash_value, child_number) as rnk
, ora_hash(
operation
|| '-' || options
|| '-' || object_node
|| '-' || object#
|| '-' || object_owner
|| '-' || object_name
|| '-' || object_alias
|| '-' || object_type
|| '-' || optimizer
|| '-' || id
|| '-' || parent_id
|| '-' || depth
|| '-' || position
|| '-' || search_columns
|| '-' || cost
|| '-' || cardinality
|| '-' || bytes
|| '-' || other_tag
|| '-' || partition_start
|| '-' || partition_stop
|| '-' || partition_id
|| '-' || ora_hash(other)
|| '-' || distribution
|| '-' || cpu_cost
|| '-' || io_cost
|| '-' || temp_space
|| '-' || ora_hash(access_predicates)
|| '-' || ora_hash(filter_predicates)
|| '-' || ora_hash(projection)
|| '-' || time
|| '-' || qblock_name
|| '-' || ora_hash(remarks)
) as hash_path_row
from (
select
*
from
my_sql_plan
)
)
)
group by
hash_value
, plan_hash_value
, child_number
)
);

This will mark all child cursors for the same statement that have the same PLAN_HASH_VALUE but according to your new hash value are different. The results can be quite surprising.