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博客

有相同sql_id的sql语句

最新文章