11g中的v$session里的SQL_HASH_VALUE SQL_EXEC_ID SQL_ID

zzboyabcd

11g中的v$session里的SQL_EXEC_ID与SQL_ID有什么区别？不都是用来标识正在运行的sql的吗？


SQL> select v.SQL_HASH_VALUE, v.SQL_EXEC_ID ,v.SQL_ID from v$session  v where v.USERNAME='SCOTT';

SQL_HASH_VALUE SQL_EXEC_ID SQL_ID
-------------- ----------- -------------
    4123906163    16777216 4jp2y6zuwvn3m

这三个值，我认为SQL_HASH_VALUE不能唯一确定正在运行的sql语句，而SQL_EXEC_ID和SQL_ID都可以唯一确定正在运行的sql语句，这样理解 对吗？

还有，11g引入的SQL_EXEC_ID，比起以前的SQL_ID有什么新的作用呢？

非常谢谢大家啊。

viadeazhu

hash value不能唯一确定，sql_id我认为从理论上来说也无法唯一确定，只是能表示更多的sql。
因为hash value是个10位的数字，sql_id是个13为的a-z 0-9的字符串，那么根据抽屉原则，当sql数量达到一定程度之后，是可能发生几个sql共享一个hash value或sql_id的情况。

而且，sql_id仅仅是一种全新表示的hash value而已，sql_id为oracle 内部计算出的hash值的后32位,hash_value为此hash值的后16位。
在11G我们也可以通过dbms_utility.SQLID_TO_SQLHASH来将sql_id转换为hash value。

而对于SQL_EXEC_ID，他代表某个sql的某次执行，不是指某个sql，因为某个sql会多次执行：
SQL_EXEC_ID
execution identifier;
NULL if SQL_ID is NULL or if the execution of that SQL has not yet started (see V$SQL_MONITOR)

从 V$SQL_MONITOR中查出：
SQL_EXEC_ID
Execution identifier.
Together, the three columns SQL_ID, SQL_EXEC_START, and SQL_EXEC_ID represent the execution key.
The execution key is used to uniquely identify one execution of the SQL statement.

netbanker

i believe generally speaking hash_value and sql_id can all point to the unique sql in oralce, but hash_value is used for backward compatibility plus all library cache still uses it.  but in each sql_id you may have different plan_hash_value based on various plan.

as viadeazhu mentioned, SQL_EXEC_ID  is just a key relation with V$SQL_MONITOR. SQL_ID+ SQL_CHILD_NUMBER could be used more precisely to point the specific sql text + cursor plan

viadeazhu

原帖由 netbanker 于 2010-6-2 01:57 发表
i believe generally speaking hash_value and sql_id can all point to the unique sql in oralce, but hash_value is used for backward compatibility plus all library cache still uses it.  but in each sql_id you may have different plan_hash_value based on various plan.

as viadeazhu mentioned, SQL_EXEC_ID  is just a key relation with V$SQL_MONITOR. SQL_ID+ SQL_CHILD_NUMBER could be used more precisely to point the specific sql text + cursor plan

我不认为hash_value或者sql_id能定位“unique SQL”，只能说能定位unique sqltext，因为hash_value和sql_id都是根据一个sql_text算出来的。
不过我同意“SQL_ID+ SQL_CHILD_NUMBER could be used more precisely to point the specific sql text + cursor plan ”

如果我们有两个不同的schema下有个同名的table，然后分别run两个一样的SQL TEXT：
SQL> conn hao/hao
Connected.
SQL> select count(*) from testbyhao;

  COUNT(*)
----------
     36350

SQL> select count(*) from testbyhao;

  COUNT(*)
----------
     36350

SQL> conn haozhu_user/haozhu_user
Connected.
SQL> select count(*) from testbyhao;

  COUNT(*)
----------
     36349


其实这是两个SQL，不过具有相同的sql text而已。
于是在v$sql中有两条记录，都具有相同的sql_id和sql_text。
SQL> select SQL_TEXT,SQL_ID,EXECUTIONS,CHILD_NUMBER from v$sql where HASH_VALUE=4189264772;

SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID        EXECUTIONS CHILD_NUMBER
------------- ---------- ------------
select count(*) from testbyhao
45xbbz7wv66w4          2            0

select count(*) from testbyhao
45xbbz7wv66w4          1            1

netbanker

in 小V's case, even same sql_id can have different sql text. I tried sql profile in 11g,   what I got is for different variable values I input I can still force it to use the same sql_id since sql profile is the same even though the sql text is obviously different (it is not binded).  That is why I said it is only generally speaking, sql_id, hash_value is enough to know the SQL.