Hash Value
jessy wijaya, January 30, 2022 - 4:21 pm UTC
i have question about hash values.
oracle use cursor sharing only when the sql is exactly the same right?
not when the hash values are the same.
sorry for late response
January 31, 2022 - 2:36 am UTC
Even more than both... For example:
User SCOTT runs:
select * from emp
Use MIKE runs:
select * from emp
They are identical SQL and will have identical hash values. THey *might* be the same SQL, but if Mike has his *own* table called EMP, then they refer to different objects.
(This is why we have what is called a "soft parse", ie, I've found something in the cache, but I still need to validate that it is indeed reusable or is actually a different SQL)
Reply
jessy wijaya, January 31, 2022 - 4:27 am UTC
Oracle doesnt soft parse if the "EMP" object from 1st query and second query is different correct?
February 02, 2022 - 4:20 am UTC
Well, we don't *know* if that second EMP is indeed a different EMP, so we must soft parse.
Where we can avoid soft parsing is by holding cursors open, so our client holds a pointer to the object in the shared pool so that when it comes to re-using it, we know by definition that it does not need any more "analysis".
Thats why PLSQL is so good - if you trace statements from PLSQL, you'll often see a parse count of 1, and an execution count of >1. We parsed it once, held it open and never had to parse (hard or soft) again