Home » SQL & PL/SQL » SQL & PL/SQL » old hash value is used to identify same sql and Plan hash value is to identify sql plans? (19.0.0 windows 2016)
old hash value is used to identify same sql and Plan hash value is to identify sql plans? [message #683595] Fri, 05 February 2021 04:30
juniordbanewbie
Messages: 250
Registered: April 2014
Senior Member
Dear all,

this is a portion of my sprepsql report

Old SQL hash value

STATSPACK SQL report for Old Hash Value: 331523240  Module: JDBC Thin Client

DB Name         DB Id    Instance     Inst Num Release     RAC Host
------------ ----------- ------------ -------- ----------- --- ----------------
SEMI       21020567556 semi             1 12.1.0.2.0  NO  cambridge-db.ph

 Start Id     Start Time         End Id      End Time       Duration(mins)
--------- ------------------- --------- ------------------- --------------
   161620 01-Feb-21 09:20:17     161621 01-Feb-21 09:35:17           15.00

First        First           Last           Plan
 Snap Id     Snap Time      Active Time    Hash Value        Cost
--------- --------------- --------------- ------------ ----------
   161619 01-Feb-21 09:05 01-Feb-21 09:05   3102409505     19856
   161620 01-Feb-21 09:20 01-Feb-21 09:10    756203027     65633
   161620 01-Feb-21 09:20 01-Feb-21 09:06    775540703     47144
   161620 01-Feb-21 09:20 01-Feb-21 09:09   1165489873     39925
   161621 01-Feb-21 09:35 01-Feb-21 09:24   3938073074    100457
   161623 01-Feb-21 10:05 01-Feb-21 09:59   3849461396    312067
   161624 01-Feb-21 10:20 01-Feb-21 13:08   3346063325    309586
   161629 01-Feb-21 11:35 01-Feb-21 09:05   3102409505
   161712 02-Feb-21 08:20 01-Feb-21 09:06    775540703
   161712 02-Feb-21 08:20 01-Feb-21 13:08   3346063325
   161712 02-Feb-21 08:20 01-Feb-21 09:24   3938073074
I wanted to confirm the following facts,

old hash value is to indicated the sql statement
plan hash value is to indicated the sql plan id.

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SQL.html#GUID-2B9340D7-4AA8-4894-94C0-D5990F67BE75

Quote:

Numeric representation of the current SQL plan for this cursor. Comparing one PLAN_HASH_VALUE to another easily identifies whether or not two plans are the same (rather than comparing the two plans line by line).
old hash value is for pre-10g and statspack,

so if I'm not not using pre-10g and statspack to check whether the sql is the same or not is to check that hash value? same hash value means same sql but it does not mean same plan (different plan hash value), right?
Previous Topic: Alternative approach for a query run programmatically.
Next Topic: Regexp_like
Goto Forum:
  


Current Time: Mon Apr 19 08:31:17 CDT 2021