[20200212]使用DBMS_SHARED_POOL.MARKHOT与sql语句3.txt

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

[20200212]使用DBMS_SHARED_POOL.MARKHOT与sql语句3.txt --//测试设置_kgl_hot_object_copies＝101,看看避免冲突的情况下,使用DBMS_SHARED_POOL.MARKHOT效果如何。 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 create table job_times (sid number, sessionid number,time_ela number,method varchar2(20)); --//建立测试脚本m2.txt: $ cat m2.txt set verify off --//host sleep $(echo &&3/50 | bc -l ) insert into job_times values ( sys_context ('userenv', 'sid') ,sys_context ('userenv', 'sessionid'),dbms_utility.get_time ,'&&2') ; commit ; declare v_id number; v_d date; m_rowid varchar2(20); m_data varchar2(32); begin m_rowid := '&3'; for i in 1 .. &&1 loop select ename into m_data from emp where rowid =m_rowid ; end loop; end ; / update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and sessionid=sys_context ('userenv', 'sessionid') and method='&&2'; commit; quit SCOTT@book> select listagg(rowid,',') WITHIN GROUP (order by rowid ) c100 from emp ; C100 ---------------------------------------------------------------------------------------------------- AAAVREAAEAAAACXAAA,AAAVREAAEAAAACXAAB,AAAVREAAEAAAACXAAC,AAAVREAAEAAAACXAAD,AAAVREAAEAAAACXAAE,AAAVR EAAEAAAACXAAF,AAAVREAAEAAAACXAAG,AAAVREAAEAAAACXAAH,AAAVREAAEAAAACXAAI,AAAVREAAEAAAACXAAJ,AAAVREAAEA AAACXAAK,AAAVREAAEAAAACXAAL,AAAVREAAEAAAACXAAM,AAAVREAAEAAAACXAAN 2.测试1： --//测试不使用DBMS_SHARED_POOL.MARKHOT的情况： a='AAAVREAAEAAAACXAAA,AAAVREAAEAAAACXAAB,AAAVREAAEAAAACXAAC,AAAVREAAEAAAACXAAD,AAAVREAAEAAAACXAAE,AAAVREAAEAAAACXAAF,AAAVREAAEAAAACXAAG,AAAVREAAEAAAACXAAH,AAAVREAAEAAAACXAAI,AAAVREAAEAAAACXAAJ,AAAVREAAEAAAACXAAK,AAAVREAAEAAAACXAAL,AAAVREAAEAAAACXAAM,AAAVREAAEAAAACXAAN' $ echo $a | tr ',' '\n' | xargs -I{} -P 14 sqlplus -s -l scott/book @m2.txt 1e6 NOMARKHOTx {} SCOTT@book> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from job_times group by method order by 3 ; METHOD COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA) -------------------- ---------- ---------------------- ------------- NOMARKHOTx 14 2629 36811 3.测试2： --//测试使用DBMS_SHARED_POOL.MARKHOT的情况： SELECT owner ,name ,hash_value ,full_hash_value ,namespace ,child_latch ,property hot_flag ,executions ,invalidations FROM v$db_object_cache WHERE name = 'SELECT ENAME FROM EMP WHERE ROWID =:B1 ' and CHILD_LATCH<>0 order by executions desc ; OWNER NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS ------ ---------------------------------------- ---------- -------------------------------- ---------- ----------- ---------- ---------- ------------- SELECT ENAME FROM EMP WHERE ROWID =:B1 85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA 11140 13071757 0 SYS@book> exec dbms_shared_pool.markhot( hash=>'6ddb0702c4c177cb27ee292f05202b84', namespace=>0, global=>true); PL/SQL procedure successfully completed. $ echo $a | tr ',' '\n' | xargs -I{} -P 14 sqlplus -s -l scott/book @m2.txt 1e6 MARKHOTx {} SYS@book> @ wait P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATUS STATE WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS ---------------- ---------------- ---------------- ---------- ----------------- ---------- ---------- ---------- ---------- ---------------------- -------- ------------------- --------------- --------------- -------------------- 0000000000002B84 00 000000000000003E 11140 0 62 58 23 69 library cache: mutex X ACTIVE WAITED SHORT TIME 2 0 Concurrency 0000000000002B84 00 000000000000003E 11140 0 62 72 13 69 library cache: mutex X ACTIVE WAITED SHORT TIME 2 2 Concurrency 0000000000002B84 0000003A00000000 000000000000003E 11140 249108103168 62 86 3 46 library cache: mutex X ACTIVE WAITED SHORT TIME 3 4 Concurrency 0000000000002B84 00 000000000000003E 11140 0 62 101 3 70 library cache: mutex X ACTIVE WAITED SHORT TIME 3 1 Concurrency 0000000000002B84 00 000000000000003E 11140 0 62 114 3 66 library cache: mutex X ACTIVE WAITED SHORT TIME 2 1 Concurrency 0000000000002B84 00 000000000000003E 11140 0 62 128 3 45 library cache: mutex X ACTIVE WAITED SHORT TIME 3 1 Concurrency 0000000000002B84 0000009C00000000 000000000000003E 11140 670014898176 62 142 3 55 library cache: mutex X ACTIVE WAITED KNOWN TIME 10997 4 Concurrency 0000000000002B84 00 000000000000003E 11140 0 62 156 3 61 library cache: mutex X ACTIVE WAITED SHORT TIME 2 0 Concurrency 0000000000002B84 0000005600000000 000000000000003E 11140 369367187456 62 170 3 39 library cache: mutex X ACTIVE WAITED SHORT TIME 3 4 Concurrency 0000000000002B84 0000009C00000000 000000000000003E 11140 670014898176 62 184 3 41 library cache: mutex X ACTIVE WAITED SHORT TIME 11 4 Concurrency 0000000000002B84 00 000000000000003E 11140 0 62 198 3 59 library cache: mutex X ACTIVE WAITED SHORT TIME 2 0 Concurrency 0000000000002B84 00 000000000000003E 11140 0 62 212 37 63 library cache: mutex X ACTIVE WAITED SHORT TIME 2 0 Concurrency 0000000000002B84 0000003A00000000 000000000000003E 11140 249108103168 62 226 7 53 library cache: mutex X ACTIVE WAITED SHORT TIME 2 1 Concurrency 0000000000002B84 00 000000000000003E 11140 0 62 241 3 60 library cache: mutex X ACTIVE WAITED SHORT TIME 2 1 Concurrency 14 rows selected. --//还是大量的library cache: mutex X等待事件。 SELECT owner ,name ,hash_value ,full_hash_value ,namespace ,child_latch ,property hot_flag ,executions ,invalidations FROM v$db_object_cache WHERE name = 'SELECT ENAME FROM EMP WHERE ROWID =:B1 ' and CHILD_LATCH<>0 order by executions desc ; OWNER NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS ------ ---------------------------------------- ---------- -------------------------------- ---------- ----------- ---------- ---------- ------------- SELECT ENAME FROM EMP WHERE ROWID =:B1 85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA 11140 HOT 13071757 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 285504892 7f0b59ec4b8535eec82fba911104757c SQL AREA 30076 HOTCOPY28 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 4010836214 0cea2fadafa4dd35f7f8efcdef1080f6 SQL AREA 33014 HOTCOPY70 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 1691525416 3d2f9eaeae30daafc5521e5c64d2a128 SQL AREA 41256 HOTCOPY84 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 3448939648 33066bcd4389a8a8b78ccddacd92a480 SQL AREA 42112 HOTCOPY87 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2118854785 c79e876e553b527d68e095217e4b2881 SQL AREA 75905 HOTCOPY59 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 1626819103 fe3d83877f2c5663dd70044f60f74a1f SQL AREA 84511 HOTCOPY98 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA 88906 HOTCOPY11 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2543673867 6dd887a7dad2ef66cb0141e3979d620b SQL AREA 90635 HOTCOPY56 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2236576569 7ace1ec20937d7b7155be95f854f7339 SQL AREA 95033 HOTCOPY73 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2648560675 127d6fcd213a6dd9c2d5d8139dddd423 SQL AREA 119843 HOTCOPY25 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2582763541 c88682c52f8f2765a84d377399f1d815 SQL AREA 120853 HOTCOPY1 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2302797450 ec2f55c6e5d2cd8da2e94ef78941e68a SQL AREA 124554 HOTCOPY42 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2074737225 a3b70cec850d0f06245030157ba9fa49 SQL AREA 129609 HOTCOPY40 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2496819678 14d02282ba345f71d7396b5694d271de SQL AREA 29150 HOTCOPY14 1000000 0 15 rows selected. --//这次全部分散开了。生成14个HOTCOPYXX。 SCOTT@book> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from job_times group by method order by 3 ; METHOD COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA) -------------------- ---------- ---------------------- ------------- NOMARKHOTx 14 2629 36811 MARKHOTx 14 3130 43825 --//效果一样，再次验证使用DBMS_SHARED_POOL.MARKHOT标识热sql语句,效果不大，建议不要使用。 SCOTT@book> select mod(sid,101)+1 ,count(*) from job_times where method='MARKHOTx' group by mod(sid,101) order by 1; MOD(SID,101)+1 COUNT(*) -------------- ---------- 1 1 11 1 14 1 25 1 28 1 40 1 42 1 56 1 59 1 70 1 73 1 84 1 87 1 98 1 14 rows selected. SCOTT@book> SELECT substr(property,8,2) FROM v$db_object_cache WHERE name = 'SELECT ENAME FROM EMP WHERE ROWID =:B1 ' and CHILD_LATCH<>0 order by 1 ; SUBS ---- 1 11 14 25 28 40 42 56 59 70 73 84 87 98 15 rows selected. --//可以发现结果一致，再次验证了设置隐含参数_kgl_hot_object_copies，sql_id语句计算sql_text变为原来sql文本的基础上 --// 加上 . mod(sid,_kgl_hot_object_copies)+1数字的字符串。 $ echo -e -n 'SELECT ENAME FROM EMP WHERE ROWID =:B1 \0.14' | md5sum | sed 's/ -//' | xxd -r -p | od -t x4 | sed -n -e 's/^0000000 //' -e 's/ //gp' 14d02282ba345f71d7396b5694d271de --//FULL_HASH_VALUE与前面的输出完全能对上。 4.收尾: SYS@book> alter system reset "_kgl_hot_object_copies" ; System altered. --//重启还原。