http://www.eygle.com/archives/2012/05/oracle_sqlid_hash_value.html

Oracle SQLID 与 Hash_value 算法及转换
这两者可以相互转换，在Oracle 10g中，提供了一个包函数，用于转换：
SQL> select sql_id,hash_value,dbms_utility.SQLID_TO_SQLHASH(sql_id) convert from v$sql where rownum <9;

相同sql语句，相同操作对象，相同用户，不同会话 :

SESSION1
SQL> show user
USER is "TEST"

SQL> select userenv('sid') from dual;

USERENV('SID')
--------------
146

SQL> select 4 from dual;

4
----------
4

SQL>
SQL> select sql_text,sql_id,hash_value,parsing_user_id,parsing_schema_id,address,plan_hash_value,child_address,executions,FIRST_LOAD_TIME,LAST_LOAD_TIME,to_char(last_active_time,'y
yyy-mm-dd/hh24:mi:ss') active_time from v$sql where sql_text='select 4 from dual';

SQL_TEXT SQL_ID HASH_VALUE PARSING_USER_ID PARSING_SCHEMA_ID ADDRESS PLAN_HASH_VALUE CHILD_AD EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME ACTIVE_TIME
-------------------- ------------- ---------- --------------- ----------------- -------- --------------- -------- ---------- -------------------- -------------------- -------------
------
select 4 from dual a5dastgn7bdh6 3900028422 42 42 2B5D17A8 1388734953 2C937E20 1 2013-07-04/13:14:17 2013-07-04/13:14:17 2013-07-04/13
:14:15

SQL> select sql_text,sql_id,hash_value,parsing_user_id,address,LAST_ACTIVE_CHILD_ADDRESS,executions,FIRST_LOAD_TIME,to_char(last_load_time,'yyyy-mm-dd/hh24:mi:ss') load_time,to_cha
r(last_active_time,'yyyy-mm-dd/hh24:mi:ss') active_time from v$sqlarea where sql_text='select 4 from dual';

SQL_TEXT SQL_ID HASH_VALUE PARSING_USER_ID ADDRESS LAST_ACT EXECUTIONS FIRST_LOAD_TIME LOAD_TIME ACTIVE_TIME
-------------------- ------------- ---------- --------------- -------- -------- ---------- -------------------- ------------------- -------------------
select 4 from dual a5dastgn7bdh6 3900028422 42 2B5D17A8 2C937E20 1 2013-07-04/13:14:17 2013-07-04/13:14:17 2013-07-04/13:14:15

SESSION2
SQL> show user
USER is "TEST"
SQL> select userenv('sid') from dual;

USERENV('SID')
--------------
141

SQL> select 4 from dual;

4
----------
4

SQL> select sql_text,sql_id,hash_value,parsing_user_id,parsing_schema_id,address,plan_hash_value,child_address,executions,FIRST_LOAD_TIME,LAST_LOAD_TIME,to_char(last_active_time,'y
yyy-mm-dd/hh24:mi:ss') active_time from v$sql where sql_text='select 4 from dual';

SQL_TEXT SQL_ID HASH_VALUE PARSING_USER_ID PARSING_SCHEMA_ID ADDRESS PLAN_HASH_VALUE CHILD_AD EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME ACTIVE_TIME
-------------------- ------------- ---------- --------------- ----------------- -------- --------------- -------- ---------- -------------------- -------------------- -------------
------
select 4 from dual a5dastgn7bdh6 3900028422 42 42 2B5D17A8 1388734953 2C937E20 2 2013-07-04/13:14:17 2013-07-04/13:14:17 2013-07-04/13
:15:19

SQL> select sql_text,sql_id,hash_value,parsing_user_id,address,LAST_ACTIVE_CHILD_ADDRESS,executions,FIRST_LOAD_TIME,to_char(last_load_time,'yyyy-mm-dd/hh24:mi:ss') load_time,to_cha
r(last_active_time,'yyyy-mm-dd/hh24:mi:ss') active_time from v$sqlarea where sql_text='select 4 from dual';

SQL_TEXT SQL_ID HASH_VALUE PARSING_USER_ID ADDRESS LAST_ACT EXECUTIONS FIRST_LOAD_TIME LOAD_TIME ACTIVE_TIME
-------------------- ------------- ---------- --------------- -------- -------- ---------- -------------------- ------------------- -------------------
select 4 from dual a5dastgn7bdh6 3900028422 42 2B5D17A8 2C937E20 2 2013-07-04/13:14:17 2013-07-04/13:14:17 2013-07-04/13:15:19

SQL> select sql_text,sql_id,cpu_time,hash_value,parsing_user_id,parsing_schema_id,address,plan_hash_value,child_address,executions,FIRST_LOAD_TIME,LAST_LOAD_TIME,to_char(last_activ
e_time,'yyyy-mm-dd/hh24:mi:ss') active_time from v$sql where sql_text='select count(*) from t1';

SQL_TEXT SQL_ID CPU_TIME HASH_VALUE PARSING_USER_ID PARSING_SCHEMA_ID ADDRESS PLAN_HASH_VALUE CHILD_AD EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME AC
TIVE_TIME
-------------------- ------------- ---------- ---------- --------------- ----------------- -------- --------------- -------- ---------- -------------------- -------------------- --
-----------------
select count(*) from 5bc0v4my7dvr5 2999 4235652837 42 42 30AD4D94 3724264953 2CA9F134 5 2013-07-03/15:15:20 2013-07-03/15:15:20 20
13-07-03/15:20:58
t1

select count(*) from 5bc0v4my7dvr5 3000 4235652837 42 42 30AD4D94 3724264953 2C9F2110 7 2013-07-03/15:15:20 2013-07-03/15:17:41 20
13-07-04/13:20:16
t1

相同sql语句，相同操作对象，不同用户

SQL> conn / as sysdba
Connected.
SQL> select 4 from dual;

4
----------
4

SQL> select sql_text,sql_id,hash_value,parsing_user_id,parsing_schema_id,address,plan_hash_value,child_address,executions,FIRST_LOAD_TIME,LAST_LOAD_TIME,to_char(last_active_time,'y
yyy-mm-dd/hh24:mi:ss') active_time from v$sql where sql_text='select 4 from dual';

SQL_TEXT SQL_ID HASH_VALUE PARSING_USER_ID PARSING_SCHEMA_ID ADDRESS PLAN_HASH_VALUE CHILD_AD EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME ACTIVE_TIME
-------------------- ------------- ---------- --------------- ----------------- -------- --------------- -------- ---------- -------------------- -------------------- -------------
------
select 4 from dual a5dastgn7bdh6 3900028422 42 42 2B5D17A8 1388734953 2C937E20 3 2013-07-04/13:14:17 2013-07-04/13:14:17 2013-07-04/13
:18:25

SQL> select sql_text,sql_id,hash_value,parsing_user_id,address,LAST_ACTIVE_CHILD_ADDRESS,executions,FIRST_LOAD_TIME,to_char(last_load_time,'yyyy-mm-dd/hh24:mi:ss') load_time,to_cha
r(last_active_time,'yyyy-mm-dd/hh24:mi:ss') active_time from v$sqlarea where sql_text='select 4 from dual';

SQL_TEXT SQL_ID HASH_VALUE PARSING_USER_ID ADDRESS LAST_ACT EXECUTIONS FIRST_LOAD_TIME LOAD_TIME ACTIVE_TIME
-------------------- ------------- ---------- --------------- -------- -------- ---------- -------------------- ------------------- -------------------
select 4 from dual a5dastgn7bdh6 3900028422 42 2B5D17A8 2C937E20 3 2013-07-04/13:14:17 2013-07-04/13:14:17 2013-07-04/13:18:25

SQL>

相同sql语句，不同操作对象： v$sql和v$sqlarea只对应一条sql记录。

SESSION1

SQL> show user;
USER is "TEST"

SQL> select count(*) from t1;

COUNT(*)
----------
200

SQL> select sql_text,sql_id,hash_value,parsing_user_id,parsing_schema_id,address,plan_hash_value,child_address,executions,FIRST_LOAD_TIME,LAST_LOAD_TIME,to_char(last_active_time,'y
yyy-mm-dd/hh24:mi:ss') active_time from v$sql where sql_text='select 4 from dual';

SQL_TEXT SQL_ID HASH_VALUE PARSING_USER_ID PARSING_SCHEMA_ID ADDRESS PLAN_HASH_VALUE CHILD_AD EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME ACTIVE_TIME
-------------------- ------------- ---------- --------------- ----------------- -------- --------------- -------- ---------- -------------------- -------------------- -------------
------
select 4 from dual a5dastgn7bdh6 3900028422 42 42 2B5D17A8 1388734953 2C937E20 3 2013-07-04/13:14:17 2013-07-04/13:14:17 2013-07-04/13
:18:25

SQL> select sql_text,sql_id,hash_value,parsing_user_id,address,LAST_ACTIVE_CHILD_ADDRESS,executions,FIRST_LOAD_TIME,to_char(last_load_time,'yyyy-mm-dd/hh24:mi:ss') load_time,to_cha
r(last_active_time,'yyyy-mm-dd/hh24:mi:ss') active_time from v$sqlarea where sql_text='select 4 from dual';

SQL_TEXT             SQL_ID        HASH_VALUE PARSING_USER_ID ADDRESS LAST_ACT EXECUTIONS FIRST_LOAD_TIME      LOAD_TIME           ACTIVE_TIME
-------------------- ------------- ---------- --------------- -------- -------- ---------- -------------------- ------------------- -------------------
select 4 from dual   a5dastgn7bdh6 3900028422              42 2B5D17A8 2C937E20          3 2013-07-04/13:14:17 2013-07-04/13:14:17 2013-07-04/13:18:25

SESSION2

select sql_text,sql_id,hash_value,parsing_user_id,parsing_schema_id,address,plan_hash_value,child_address,executions,FIRST_LOAD_TIME,LAST_LOAD_TIME,to_char(last_active_time,'yyyy-mm-dd/hh24:mi:ss') active_time from v$sql where sql_text='select count(*) from t1';

select sql_text,sql_id,hash_value,parsing_user_id,address,LAST_ACTIVE_CHILD_ADDRESS,executions,FIRST_LOAD_TIME,to_char(last_load_time,'yyyy-mm-dd/hh24:mi:ss') load_time,to_char(last_active_time,'yyyy-mm-dd/hh24:mi:ss') active_time from v$sqlarea where sql_text='select count(*) from t1';

select sql_text,sql_id,cpu_time,hash_value,parsing_user_id,parsing_schema_id,address,plan_hash_value,child_address,executions,FIRST_LOAD_TIME,LAST_LOAD_TIME,to_char(last_active_time,'yyyy-mm-dd/hh24:mi:ss') active_time from v$sql where sql_text='select count(*) from t11';

begin
for i in 1..100 loop
    insert into t11 values (i);
end loop;
commit;
end;
/

select cpu_time,executions from v$sql where sql_text='select count(*) from t11';

ITPub博客

aa

最新文章