有相同sql_id的sql语句

              
1.不同用户执行相同的sql语句   

1）在同一个数据库中执行  'select * from tab01'   
[oracle@dbserver ~]$ sqlplus u1/u1         
SQL> create table tab01(name varchar2(6));         
         
Table created.         
         
SQL> insert into tab01 values('a');         
         
1 row created.         
         
SQL> select * from tab01;         
         
NAME         
------         
a    

     
SQL> disc 

 ...
SQL>conn u2/u2     

Connected.
SQL> create table tab01(age number);         
         
Table created.         
         
SQL> insert into tab01 values(1);         
         
1 row created.         
         
SQL> select * from tab01;         
         
       AGE         
----------         
1         
         
SQL> column sql_text format a20         
         
SQL> set linesize 150    
SQL> select sql_text,sql_id,hash_value,parsing_user_id,parsing_schema_id,address,plan_hash_value,child_address from v$sql where sql_text='select * from tab01';         
         
SQL_TEXT             SQL_ID        HASH_VALUE PARSING_USER_ID PARSING_SCHEMA_ID ADDRESS  PLAN_HASH_VALUE CHILD_AD         
-------------------- ------------- ---------- --------------- ----------------- -------- --------------- --------         
select * from tab01  5b42g2fkrrzss 2776366872              87                87 40FB1988      2044041692 40FAC494         
select * from tab01  5b42g2fkrrzss 2776366872              88                88 40FB1988      2044041692 3BDB56F8  

  v$sql中有相同sql_text的记录条目数量取决于执行此sql的用户数。

      
2）在另一个不同版本的数据库中执行 'select * from tab01'         
SQL> select sql_text,sql_id,hash_value,parsing_user_id,parsing_schema_id,address,plan_hash_value,child_address from v$sql where sql_text='select * from tab01';

SQL_TEXT             SQL_ID        HASH_VALUE PARSING_USER_ID PARSING_SCHEMA_ID ADDRESS  PLAN_HASH_VALUE CHILD_AD
-------------------- ------------- ---------- --------------- ----------------- -------- --------------- --------
select * from tab01  5b42g2fkrrzss 2776366872               0                 0 30953C34      2044041692 309711C8         
         

相同的sql语句，他们的sql_id,hash_value，plan_hash_value相同。

sql_id + child_address 可以标识唯一的一条sql语句

2.同一个用户在不同的session中执行相同的sql语句        
conn u2         
SQL> select userenv('sid') from dual;         
         
USERENV('SID')         
--------------         
48         
         
SQL> select age from tab01;         
         
       AGE         
----------         
1         
打开另一个session 用u2连接         
SQL> select userenv('sid') from dual;         
         
USERENV('SID')         
--------------         
40         
           
SQL> select age from tab01;         
         
       AGE         
----------         
1         
         
SQL> column sql_text format a25         
SQL> select sql_text,sql_id,hash_value,parsing_user_id,parsing_schema_id,address,plan_hash_value,child_address from v$sql where sql_text='select age from tab01';         
         
SQL_TEXT                  SQL_ID        HASH_VALUE PARSING_USER_ID PARSING_SCHEMA_ID ADDRESS  PLAN_HASH_VALUE CHILD_AD         
------------------------- ------------- ---------- --------------- ----------------- -------- --------------- --------         
select age from tab01     c73nv2tczaxug 1509259087              88                88 48CFAA3C      2044041692 48E199A4 

v$sql中只有一条与此sql语句对应的记录。

 

4.当删除表tab01后，v$sql中在表tab01上的sql语句的记录也就没有了

SQL> select sql_text,sql_id,hash_value,parsing_user_id,parsing_schema_id,address,plan_hash_value,child_address from v$sql where sql_text='select * from tab01';

no rows selected

SQL> create table tab01(age number);

Table created.

SQL> insert into tab01 values(1);

1 row created.

SQL> select * from tab01;

       AGE
----------
         1

SQL> select sql_text,sql_id,hash_value,parsing_user_id,parsing_schema_id,address,plan_hash_value,child_address from v$sql where sql_text='select * from tab01';

SQL_TEXT             SQL_ID        HASH_VALUE PARSING_USER_ID PARSING_SCHEMA_ID ADDRESS  PLAN_HASH_VALUE CHILD_AD
-------------------- ------------- ---------- --------------- ----------------- -------- --------------- --------
select * from tab01  5b42g2fkrrzss 2776366872              31                31 30953C34      2044041692 309711C8

SQL> drop table tab01;

Table dropped.

SQL> select sql_text,sql_id,hash_value,parsing_user_id,parsing_schema_id,address,plan_hash_value,child_address from v$sql where sql_text='select * from tab01';

no rows selected

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24756186/viewspace-740987/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24756186/viewspace-740987/