good evening,
I have a sql statement with the following information in v$sqlarea
select sql_id, address, hash_value, plan_hash_value from v$sqlarea where sqltext=<string to identify my query>
sql_id |address |hash_value|plan_hash_value
cv65zdurrtfus|00000000FCAA9560|2944187224|3149222761
I remove this object from the shared pool with the command because I want to recompute the exec plan for my sql statement
exec sys.dbms_shared_pool.purge('00000000FCAA9560,2944187224','c');I redo my previous select statement on v$sqlarea and it retuns 0 row so I'm happy with that.
Then I execute my original sql and last I redo my select statement on v$sqlarea and it returns one row with the same values
sql_id |address |hash_value|plan_hash_value
cv65zdurrtfus|00000000FCAA9560|2944187224|3149222761
I was wondering how identical ids were generated, i was expecting new values even though at the end I have the expected result.
Thanks for your feedback.
Simon
The SQL ID is a hash of the text of the SQL statement. Similarly, the hash_value is a hash of the statement itself. You expect these to be the same every time you run a statement.
The plan_hash_value is a hash of the execution plan. This will change if you get a different plan.
So after purging the statement you've still got the same plan, meaning this was likely wasted effort.
The plan hash does exclude some aspects though. It's possible for two plans to have the same shape (same access methods and joins), but vary in details such as predicates. See this from Jonathan Lewis for an example:
https://jonathanlewis.wordpress.com/2021/01/14/between/