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;
这两者可以相互转换,在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> show user
USER is "TEST"
SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
146
SQL> select 4 from dual;
--------------
146
SQL> select 4 from dual;
4
----------
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> 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
-------------------- ------------- ---------- --------------- ----------------- -------- --------------- -------- ---------- -------------------- -------------------- -------------
------
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';
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
-------------------- ------------- ---------- --------------- -------- -------- ---------- -------------------- ------------------- -------------------
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;
SQL> show user
USER is "TEST"
SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
141
--------------
141
SQL> select 4 from dual;
4
----------
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';
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
-------------------- ------------- ---------- --------------- ----------------- -------- --------------- -------- ---------- -------------------- -------------------- -------------
------
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';
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
-------------------- ------------- ---------- --------------- -------- -------- ---------- -------------------- ------------------- -------------------
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
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
13-07-04/13:20:16
t1
相同sql语句,相同操作对象,不同用户
SQL> conn / as sysdba
Connected.
SQL> select 4 from dual;
Connected.
SQL> select 4 from dual;
4
----------
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';
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
-------------------- ------------- ---------- --------------- ----------------- -------- --------------- -------- ---------- -------------------- -------------------- -------------
------
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';
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
-------------------- ------------- ---------- --------------- -------- -------- ---------- -------------------- ------------------- -------------------
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语句,不同操作对象 : v$sql和v$sqlarea只对应一条sql记录。
SESSION1
SQL> show user;
USER is "TEST"
USER is "TEST"
SQL> select count(*) from t1;
COUNT(*)
----------
200
----------
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';
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
-------------------- ------------- ---------- --------------- ----------------- -------- --------------- -------- ---------- -------------------- -------------------- -------------
------
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';
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 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博客 ” ,链接:http://blog.itpub.net/24756186/viewspace-764590/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24756186/viewspace-764590/