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

有相同sql_id的sql語句

相關文章