http://www.itpub.net/thread-909366-1-1.html

> Are sql_hash_value and pre_hash_value in v$session really the currently running and previously run SQL?
> 
> Run in session 1:
> select max(sid) from v$mystat;
>   MAX(SID)
> ----------
>         10
> 
> Run in session 2 (Why do SQL_HASH_VALUE PREV_HASH_VALUE have the same value? SQL_HASH_VALUE should be 0.):
> SQL> select sid, sql_hash_value, prev_hash_value from v$session where sid=10;
> 
>        SID SQL_HASH_VALUE PREV_HASH_VALUE
> ---------- -------------- ---------------
>         10     3448634418      3448634418
> 
> In session 1:
> SQL> select max(sid) from v$session;
> 
>   MAX(SID)
> ----------
>         10
> 
> In session 2 (again, the same value):
> SQL> select sid, sql_hash_value, prev_hash_value from v$session where sid=10;
> 
>        SID SQL_HASH_VALUE PREV_HASH_VALUE
> ---------- -------------- ---------------
>         10     1199255712      1199255712

I found two bugs that are very interesting and they were closed as "not a bug". Bug 2856124 has these words:

'The columns are providing a view on the internal service layer
rather than a "user" view and are therefore dependent upon the way that
specific internal calls are executed byt the kernel itself rather than a
"user" call.'

It seems that Oracle doesn't think these columns are supposed to be used as documentation (incorrectly) states. I think it's better to say that these columns store hash values of the SQLs the session is running or recently ran but Oracle doesn't guarantee the timing accuracy from a user's point of view.

Bug 2827089 has a long message at the end:

***************** BEGIN QUOTE *****************

I don't think that this is a bug. The problem is that the select against
v$session and v$sql is executing within OUR session and is the only open
cursor at the time.
.
During the parse stage the previous/current are different, at execution time
we copy the current to the previous (so they are now the same) and at cursor
unmap current is copied to previous and then current set to zero. The select
is retrieving the information from the v$ tables during execute and is
catching this data when it is temporarily set to the same value.
.
When another session selects this information instead, the difference between
the current and previous can be seen although it may also be possible to catch
the point where they are temporarily set to the same value.
.
I am going to close this as "not a bug" because this is expected behaviour.
There is a point in the manipulation of the current and previous where they
may become temporarily the same value if we have a single cursor. Other
sessions may be lucky and also execute at just the right point to see the same
result but typically they will see a difference because they will miss this
window. 

***************** END QUOTE *****************

In essense, it states that during parse, the two hash values are different. 
During execution, they're the same. After execution and if unmap (an operation 
sometimes you see in SQL trace) is done, sql_hash_value is set to 0.

Although Oracle doesn't think it's a bug, the behavior actually has changed! You 
must be testing in 9i judging by your low SID. (Please tell us Oracle version 
whenever you post a question.) I tried your test in 9i (9.2.0.1, the same 
version as in the Bug) and 10g (10.2.0.1). I can reproduce it in 9i but not in 
10g, where sql_hash_value in the test is correctly set to 0 and prev_hash_value 
is the SQL that just ran. This means unmap is done in 10g as it should be, but not done in 9i.

(If you do need to trigger an unmap manually, you can desc nosuchtable, which 
basically breaks the open cursor for this session. Make sure nosuchtable is 
indeed non-existing.)

[Other references:
Jonathan Lewis's message at Metalink thread 621938.992
An old bug: 1249631]

Yong Huang