[20200212]使用DBMS_SHARED_POOL.MARKHOT与sql的计算2.txt

来源：这里教程网时间：2026-03-03 15:00:30 作者：

[20200212]使用DBMS_SHARED_POOL.MARKHOT与sql的计算2.txt --//昨天的测试：http://blog.itpub.net/267265/viewspace-2675128/ =>[20200211]使用DBMS_SHARED_POOL.MARKHOT与sql_id的计算.txt --//补充测试设置隐含参数_kgl_hot_object_copies的情况： 1.环境: SCOTT@book> 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 SYS@book> alter system set "_kgl_hot_object_copies"=101 scope=spfile ; System altered. --//必须重启才生效，重启数据库略。 SYS@book> @ hide _kgl_hot_object_copies NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD ---------------------- ----------------------------------- ------------- ------------- ------------ ----- --------- _kgl_hot_object_copies Number of copies for the hot object FALSE 101 101 FALSE FALSE SCOTT@book> select * from dept where deptno=10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK --//sql_id='4xamnunv51w9j',可以执行多次,避免sql语句退出共享池. 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 6 0 select * from dept where deptno=10 911274289 1431c45dbddbb9e74eaa74d53650f131 SQL AREA 61745 6 0 --//确定FULL_HASH_VALUE='1431c45dbddbb9e74eaa74d53650f131'. $ echo -e -n 'select * from dept where deptno=10\0' | md5sum | sed 's/ -//' | xxd -r -p | od -t x4 | sed -n -e 's/^0000000 //' -e 's/ //gp' 1431c45dbddbb9e74eaa74d53650f131 --//对比完全能对上。 2.测试： --//首先使用DBMS_SHARED_POOL.MARKHOT标记。 SYS@book> exec dbms_shared_pool.markhot( hash=>'1431c45dbddbb9e74eaa74d53650f131', namespace=>0, global=>true); PL/SQL procedure successfully completed. --//以scott登录 --//session 1: SCOTT@book> @ spid SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50 ---------- ---------- ------------------------ --------- ------ ------- ---------- -------------------------------------------------- 295 7 63251 DEDICATED 63252 21 4 alter system kill session '295,7' immediate; SCOTT@book> select * from dept where deptno=10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK --//session 2: SYS@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) ------------- ------------------------------------------------------------ ---------- ---------------- ------------------ atpswbzn2drda select * from dept where deptno=10 1 34 156172166 4xamnunv51w9j select * from dept where deptno=10 6 34 156172166 --//sql_id=atpswbzn2drda select 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 3894861226 6b47602c1c49a6baacd71c5fe826ddaa SQL AREA 0 HOTCOPY94 1 0 select * from dept where deptno=10 3894861226 6b47602c1c49a6baacd71c5fe826ddaa SQL AREA 56746 HOTCOPY94 1 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 --//HOT_FLAG='HOTCOPY94' $ ./sql_id.sh 'select * from dept where deptno=10\0.94' sql_text = select * from dept where deptno=10\0.94 full_hash_value(16) = 6b47602c1c49a6baacd71c5fe826ddaa hash_value(10) = 3894861226 sql_id(32) = atpswbzn2drda sql_id(32) = atpswbzn2drda sql_id(32) = atpswbzn2drda --//sql_idm以及full_hash_value完全对上。 3.验证看看是否通过sid取模确定HOT_FLAG。 --//session 1: SCOTT@book> @ spid SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50 ---------- ---------- ------------------------ --------- ------ ------- ---------- -------------------------------------------------- 295 7 63251 DEDICATED 63252 21 4 alter system kill session '295,7' immediate; SCOTT@book> select mod(295 ,101)+1 from dual ; MOD(295,101)+1 -------------- 94 --//OK，完全正确。 4.总结： --//sql语句使用使用DBMS_SHARED_POOL.MARKHOT标记热的sql_id时，full_hash_value和sql_id的计算,就是在原来sql语句的基础 --//上加上 . mod(sid,cpu_count/2)+1数字的字符串，注前面的测试有误，应该是cpu_count/2而不是cpu_count，特此更正。 --//原链接：http://blog.itpub.net/267265/viewspace-2675128/ => [20200211]使用DBMS_SHARED_POOL.MARKHOT与sql_id的计算.txt --//如果设置隐含参数_kgl_hot_object_copies，变为加上 . mod(sid,_kgl_hot_object_copies)+1数字的字符串。