本文共 3677 字，大约阅读时间需要 12 分钟。
[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 ProductionSCOTT@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语句相同.
转载地址：http://ongvl.baihongyu.com/