First demonstration
In this first demonstration, the hash value for three similar SQL statements is displayed: all three statements select an aggregate function from the same table.
create table tq84_sql_hash_value (a number, b number);
explain plan for select sum(a) from tq84_sql_hash_value;
select * from dbms_xplan.display(format=>'basic');
--
-- Plan hash value: 746934648
--
-- --------------------------------------------------
-- | Id | Operation | Name |
-- --------------------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | SORT AGGREGATE | |
-- | 2 | TABLE ACCESS FULL| TQ84_SQL_HASH_VALUE |
-- --------------------------------------------------
explain plan for select sum(b) from tq84_sql_hash_value;
select * from dbms_xplan.display(format=>'basic');
--
-- Plan hash value: 746934648
--
-- --------------------------------------------------
-- | Id | Operation | Name |
-- --------------------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | SORT AGGREGATE | |
-- | 2 | TABLE ACCESS FULL| TQ84_SQL_HASH_VALUE |
-- --------------------------------------------------
explain plan for select count(*) from tq84_sql_hash_value;
select * from dbms_xplan.display(format=>'basic');
--
-- Plan hash value: 746934648
--
-- --------------------------------------------------
-- | Id | Operation | Name |
-- --------------------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | SORT AGGREGATE | |
-- | 2 | TABLE ACCESS FULL| TQ84_SQL_HASH_VALUE |
-- --------------------------------------------------
select sum (a) from tq84_sql_hash_value;
select sum (b) from tq84_sql_hash_value;
select count(*) from tq84_sql_hash_value;
select
sql.sql_id,
sql.hash_value sql_hash_value,
pln.hash_value sql_hash_value_pln,
pln.plan_hash_value,
pln.full_plan_hash_value
from
v$sqlarea sql join
v$sql_plan pln on sql.sql_id = pln.sql_id and
pln.id = 0
where
sql.sql_text like 'select %tq84_sql_hash_value';
--
-- SQL_ID SQL_HASH_VALUE SQL_HASH_VALUE_PLN PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE
-- ------------- -------------- ------------------ --------------- --------------------
-- dd02cgu2gwpxh 2231261104 2231261104 746934648 132718768
-- 2rj6urdm5q82s 1717248088 1717248088 746934648 132718768
-- bvjzvxp8z2vhj 1374776849 1374776849 746934648 132718768
CREATE TABLE and/or INSERT statements
If multiple tables (i. e. tables different names) are created with the same statement, the execution plan is the same for all statements:
create table tq84_dual_one as select * from dual;
create table tq84_dual_two as select * from dual;
select
sql.sql_id,
pln.plan_hash_value
from
v$sqlarea sql join
v$sql_plan pln on sql.sql_id = pln.sql_id and
pln.id = 0
where
sql.sql_text like 'create table tq84_dual% as select * from dual';
--
-- SQL_ID PLAN_HASH_VALUE
-- ------------- ---------------
-- 9q2zctwzk1jrh 1501550473
-- 7x3snfqfw9qfq 1501550473
The same is also true for insert statements:
create table tq84_dest_1(c1 number, c2 varchar2(10));
create table tq84_dest_2(c1 number, c2 varchar2(10));
create table tq84_src (c1 number, c2 varchar2(10));
insert into tq84_dest_1 select * from tq84_src;
insert into tq84_dest_2 select * from tq84_src;
select
sql.sql_id,
pln.plan_hash_value
from
v$sqlarea sql join
v$sql_plan pln on sql.sql_id = pln.sql_id and
pln.id = 0
where
sql.sql_text like 'insert into tq84_dest_%from tq84_src';
--
-- SQL_ID PLAN_HASH_VALUE
-- ------------- ---------------
-- gpfq9dup6a6zr 3670527110
-- fp0q14paf291t 3670527110
Object names have no influence for plan operations LOAD AS SELECT and REMOTE
The object names or objects that are targeted with the plan operations
REMOTE and
LOAD AS SELECT have no influence on the execution plan hash.
In order to demonstrate this, two tables are created at a remote and on a local database:
create database link tq84_remote_db connect to …;
begin
dbms_utility.exec_ddl_statement@tq84_remote_db('create table tq84_src_1 (a number, b varchar2(10))');
dbms_utility.exec_ddl_statement@tq84_remote_db('create table tq84_src_2 (c date , e blob )');
end;
/
create table tq84_dest_1(a number);
create table tq84_dest_2(b number);
The two plans to load tq84_dest_1 and tq84_dest_2 have (obviously) a different object name for the load as select operator, but the same hash value (2098243032):
explain plan for insert /*+ append */ into tq84_dest_1 select count(*) from tq84_src_1@tq84_remote_db;
select * from dbms_xplan.display(format=>'basic');
Plan hash value: 2098243032
--
-- --------------------------------------------------------
-- | Id | Operation | Name |
-- --------------------------------------------------------
-- | 0 | INSERT STATEMENT | |
-- | 1 | LOAD AS SELECT | TQ84_DEST_1 |
-- | 2 | OPTIMIZER STATISTICS GATHERING | |
-- | 3 | REMOTE | |
-- --------------------------------------------------------
explain plan for insert /*+ append */ into tq84_dest_2 select count(*) from tq84_src_1@tq84_remote_db;
select * from dbms_xplan.display(format=>'basic');
--
-- Plan hash value: 2098243032
--
-- --------------------------------------------------------
-- | Id | Operation | Name |
-- --------------------------------------------------------
-- | 0 | INSERT STATEMENT | |
-- | 1 | LOAD AS SELECT | TQ84_DEST_2 |
-- | 2 | OPTIMIZER STATISTICS GATHERING | |
-- | 3 | REMOTE | |
-- --------------------------------------------------------
The same hash value is also produced when selecting from the «other» remote table (i. e. tq84_src_2):
explain plan for insert /*+ append */ into tq84_dest_1 select count(*) from tq84_src_2@tq84_remote_db;
explain plan for insert /*+ append */ into tq84_dest_2 select count(*) from tq84_src_2@tq84_remote_db;