[20171110]sql語句相同sql_id可以不同嗎

[20171110]sql語句相同sql_id可以不同嗎.txt

--//提一個問題,就是sql語句相同sql_id可以不同嗎?
--//使用dbms_shared_pool.markhot就可以做到.

1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> select * from dept where deptno=10;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

--//sql_id='4xamnunv51w9j',可以執行多次,避免sql語句退出共享池.

SCOTT@book> select name,hash_value, full_hash_value, namespace, child_latch, property  hot_flag, executions, invalidations from v$db_object_cache where name='select * from dept where deptno=10';
NAME                                     HASH_VALUE FULL_HASH_VALUE                  NAMESPACE            CHILD_LATCH HOT_FLAG   EXECUTIONS INVALIDATIONS
---------------------------------------- ---------- -------------------------------- -------------------- ----------- ---------- ---------- -------------
select * from dept where deptno=10        911274289 1431c45dbddbb9e74eaa74d53650f131 SQL AREA                       0                     7             0
select * from dept where deptno=10        911274289 1431c45dbddbb9e74eaa74d53650f131 SQL AREA                   61745                     7             0

--//確定FULL_HASH_VALUE='1431c45dbddbb9e74eaa74d53650f131'.

2.設定markhot.

SYS@book> exec dbms_shared_pool.markhot( hash=>'1431c45dbddbb9e74eaa74d53650f131', namespace=>0, global=>true);
PL/SQL procedure successfully completed.

SCOTT@book> select name,hash_value, full_hash_value, namespace, child_latch, property  hot_flag, executions, invalidations from v$db_object_cache where name='select * from dept where deptno=10';
NAME                                     HASH_VALUE FULL_HASH_VALUE                  NAMESPACE            CHILD_LATCH HOT_FLAG   EXECUTIONS INVALIDATIONS
---------------------------------------- ---------- -------------------------------- -------------------- ----------- ---------- ---------- -------------
select * from dept where deptno=10        911274289 1431c45dbddbb9e74eaa74d53650f131 SQL AREA                       0 HOT                 6             0
select * from dept where deptno=10        911274289 1431c45dbddbb9e74eaa74d53650f131 SQL AREA                   61745 HOT                 6             0

--//HOT_FLAG='HOT'.

--//退出會話在執行如下:

SCOTT@book> select * from dept where deptno=10;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

 

SCOTT@book> select name,hash_value, full_hash_value, namespace, child_latch, property  hot_flag, executions, invalidations from v$db_object_cache where name='select * from dept where deptno=10';
NAME                                     HASH_VALUE FULL_HASH_VALUE                  NAMESPACE            CHILD_LATCH HOT_FLAG             EXECUTIONS INVALIDATIONS
---------------------------------------- ---------- -------------------------------- -------------------- ----------- -------------------- ---------- -------------
select * from dept where deptno=10       2941143312 5196d0b7fe72e5ea7c59eeb2af4e4910 SQL AREA                       0 HOTCOPY11                     3             0
select * from dept where deptno=10       2941143312 5196d0b7fe72e5ea7c59eeb2af4e4910 SQL AREA                   18704 HOTCOPY11                     3             0
select * from dept where deptno=10        911274289 1431c45dbddbb9e74eaa74d53650f131 SQL AREA                       0 HOT                           6             0
select * from dept where deptno=10        911274289 1431c45dbddbb9e74eaa74d53650f131 SQL AREA                   61745 HOT                           6             0

--//可以發現FULL_HASH_VALUE多了一個5196d0b7fe72e5ea7c59eeb2af4e4910.HOT_FLAG='HOTCOPY11'.

SCOTT@book> select sql_id,sql_text,executions,length(sql_text),ora_hash(sql_text) from v$sqlarea where sql_text = 'select * from dept where deptno=10' ;
SQL_ID        SQL_TEXT                                                     EXECUTIONS LENGTH(SQL_TEXT) ORA_HASH(SQL_TEXT)
------------- ------------------------------------------------------------ ---------- ---------------- ------------------
7sqgfqarnwk8h select * from dept where deptno=10                                    3               34          156172166
4xamnunv51w9j select * from dept where deptno=10                                    7               34          156172166

--//可以發現兩個sql_id不一樣,函式ora_hash(sql_text)的結果一樣.而執行的sql語句相同.