Home » RDBMS Server » Performance Tuning » prev_hash_value not changing in v$session (Oracle 10.2.0.4.0 on RHEL)
prev_hash_value not changing in v$session [message #498570] Thu, 10 March 2011 05:23 Go to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Hello

From 1 session {sid=314} (sqlplus) I am executing various queries and from other session I am querying sql_hash_value and prev_hash_value for the session with sid=314

However I found that the prev_hash_value for the first session which is executing various queries is not at all changing whereas it's sql_hash_value is constantly changing

What could be the reason?


SQL> select sql_hash_value,prev_hash_value from v$session where sid=314;

SQL_HASH_VALUE PREV_HASH_VALUE
-------------- ---------------
             0      3383928886
SQL> select sql_hash_value,prev_hash_value from v$session where sid=314;

SQL_HASH_VALUE PREV_HASH_VALUE
-------------- ---------------
    1451895371      3383928886
SQL> select sql_hash_value,prev_hash_value from v$session where sid=314;

SQL_HASH_VALUE PREV_HASH_VALUE
-------------- ---------------
    1451895371      3383928886
SQL> select sql_hash_value,prev_hash_value from v$session where sid=314;

SQL_HASH_VALUE PREV_HASH_VALUE
-------------- ---------------
    1284848732      3383928886
SQL> select sql_hash_value,prev_hash_value from v$session where sid=314;

SQL_HASH_VALUE PREV_HASH_VALUE
-------------- ---------------
    1284848732      3383928886
SQL> select sql_hash_value,prev_hash_value from v$session where sid=314;

SQL_HASH_VALUE PREV_HASH_VALUE
-------------- ---------------
    1284848732      3383928886
SQL> select sql_hash_value,prev_hash_value from v$session where sid=314;

SQL_HASH_VALUE PREV_HASH_VALUE
-------------- ---------------
    1122213218      3383928886



Regards,
OraKaran
Re: prev_hash_value not changing in v$session [message #498575 is a reply to message #498570] Thu, 10 March 2011 05:37 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The PREV field is not reliable.

In 10g, do not use hash value but sql_id.

Regards
Michel

[Updated on: Thu, 10 March 2011 05:47]

Report message to a moderator

Re: prev_hash_value not changing in v$session [message #498586 is a reply to message #498575] Thu, 10 March 2011 06:18 Go to previous messageGo to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Thanks Michel

Yes I will use sql_id only.

Was checking on 9i and simultaneously on 10g if sql_hash_value represents hash_value of currently executing statement then whether prev_hash_value gives hash_value of earlier executed statement

Which is not the real case as I observed and read in the following link
http://yong321.freeshell.org/oranotes/SqlHashValueAndPrevHashValue.txt

Thanks for corroborating my consideration on not using prev_hash_value in 10g

Regards,
OraKaran

Re: prev_hash_value not changing in v$session [message #498588 is a reply to message #498586] Thu, 10 March 2011 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use PREV value only when current one is NULL.

Regards
Michel
Re: prev_hash_value not changing in v$session [message #498592 is a reply to message #498588] Thu, 10 March 2011 06:58 Go to previous messageGo to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Thanks Michel

Quote:

Use PREV value only when current one is NULL.


Yes! That's more appropriate

BTW you mean current one is 0 not NULL. Right?

Regards,
OraKaran
Re: prev_hash_value not changing in v$session [message #498606 is a reply to message #498592] Thu, 10 March 2011 08:20 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When NVL(sql_id or hash_value, 0) = 0. Laughing

Regards
Michel

[Updated on: Thu, 10 March 2011 08:23]

Report message to a moderator

Re: prev_hash_value not changing in v$session [message #498613 is a reply to message #498606] Thu, 10 March 2011 09:04 Go to previous message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Right you are! Smile

Thanks and Regards,
OraKaran
Previous Topic: Slow Performance
Next Topic: query issue
Goto Forum:
  


Current Time: Sat Oct 25 01:40:18 CDT 2014

Total time taken to generate the page: 0.10504 seconds