Skip to Main Content
  • Questions
  • Understanding reused values for sql_id, address, hash_value, plan_hash_value in v$sqlarea

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: January 13, 2021 - 7:12 pm UTC

Last updated: January 14, 2021 - 4:17 pm UTC

Version: 11.2.0.4.0

Viewed 100+ times

You Asked

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

and we said...

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/

Rating

  (1 rating)

Comments

thank you for the answer

Simon, January 15, 2021 - 7:24 am UTC

Good morning,

I thank you for this "cristal clear" answer.

Best,

Simon

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.