OraFAQ Forum: SQL & PL/SQL » old hash value is used to identify same sql and Plan hash value is to identify sql plans?

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Code: [Select all] [Show/ hide]

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

Code: [Select all] [Show/ hide]

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?

Report message to a moderator

Previous Topic:	Alternative approach for a query run programmatically.
Next Topic:	Regexp_like

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 01 08:09:26 CDT 2025