[20200211]使用DBMS_SHARED_POOL.MARKHOT与sql语句2.txt

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

[20200211]使用DBMS_SHARED_POOL.MARKHOT与sql语句2.txt --//上午测试使用DBMS_SHARED_POOL.MARKHOT标识热sql语句，发现实际上更慢。 --//是否是我测试连接会话太少，加大测试量看看。 --//链接：http://blog.itpub.net/267265/viewspace-2675118/=>[20200126]使用DBMS_SHARED_POOL.MARKHOT与sql语句.txt 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 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' SYS@book> exec dbms_shared_pool.markhot( hash=>'6ddb0702c4c177cb27ee292f05202b84', namespace=>0, global=>true); PL/SQL procedure successfully completed. $ echo $a $a $a $a $a $a| tr ', ' '\n\n' | xargs -I{} -P 84 sqlplus -s -l scott/book @m2.txt 4e5 MARKHOT {} --//注意tr ', '逗号后面有1个空格。 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 1054289511 767295bfca293b63b6f7cb623ed72e67 SQL AREA 77415 HOTCOPY4 7889752 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 879431168 935723b8e76ce7f46c9524b7346b0e00 SQL AREA 69120 HOTCOPY12 6197136 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA 88906 HOTCOPY11 6102859 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 3708080158 2b2d6a42e3f5dc2950f01eb8dd04d01e SQL AREA 53278 HOTCOPY8 5842227 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA 11508 HOTCOPY9 5838937 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 4251304214 88e9b0a03994a83e3b1dd47ffd65c116 SQL AREA 114966 HOTCOPY2 5829690 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA 116044 HOTCOPY3 5741466 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA 7234 HOTCOPY7 5603564 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA 88684 HOTCOPY6 5379941 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA 57756 HOTCOPY5 5366078 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 1051678227 0cbac329d8cbd103bdf0b37e3eaf5613 SQL AREA 87571 HOTCOPY10 5245620 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2582763541 c88682c52f8f2765a84d377399f1d815 SQL AREA 120853 HOTCOPY1 4561987 0 12 rows selected. --//仅仅生成12个sql_id.注意没有HOT_FLAG=HOT,可以被刷出共享池了。 --//奇怪字段HOT_FLAG显示的HOTCOPYXX,后面的数字最大12.或者应该是 mod(sid,cpu_count/2)+1,看来前面的测试有误。 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) -------------------- ---------- ---------------------- ------------- MARKHOT 84 5860 492215 3.测试2： --//继续测试不使用DBMS_SHARED_POOL.MARKHOT的情况： a='AAAVREAAEAAAACXAAA,AAAVREAAEAAAACXAAB,AAAVREAAEAAAACXAAC,AAAVREAAEAAAACXAAD,AAAVREAAEAAAACXAAE,AAAVREAAEAAAACXAAF,AAAVREAAEAAAACXAAG,AAAVREAAEAAAACXAAH,AAAVREAAEAAAACXAAI,AAAVREAAEAAAACXAAJ,AAAVREAAEAAAACXAAK,AAAVREAAEAAAACXAAL,AAAVREAAEAAAACXAAM,AAAVREAAEAAAACXAAN' SYS@book> exec dbms_shared_pool.unmarkhot( hash=>'6ddb0702c4c177cb27ee292f05202b84', namespace=>0, global=>true); PL/SQL procedure successfully completed. $ echo $a $a $a $a $a $a| tr ', ' '\n\n' | xargs -I{} -P 84 sqlplus -s -l scott/book @m2.txt 4e5 NOMARKHOT {} 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) -------------------- ---------- ---------------------- ------------- NOMARKHOT 84 5375 451458 MARKHOT 84 5860 492215 --//还是不使用DBMS_SHARED_POOL.MARKHOT效果更好。 --//看来要使用DBMS_SHARED_POOL.MARKHOT必须有更多的CPU才可行。 4.继续我发现有1个隐含参数可以控制hot数量。 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 TRUE 0 0 FALSE FALSE SYS@book> alter system set "_kgl_hot_object_copies"=23 scope=spfile ; System altered. --//必须重启才生效。 SYS@book> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SYS@book> startup ORACLE instance started. Total System Global Area 643084288 bytes Fixed Size 2255872 bytes Variable Size 205521920 bytes Database Buffers 427819008 bytes Redo Buffers 7487488 bytes Database mounted. Database opened. 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 23 23 FALSE FALSE SYS@book> exec dbms_shared_pool.markhot( hash=>'6ddb0702c4c177cb27ee292f05202b84', namespace=>0, global=>true); PL/SQL procedure successfully completed. --//先要执行'SELECT ENAME FROM EMP WHERE ROWID =:B1 '语句获得hash值，不然报错，找不到对象。 $ echo $a $a $a $a $a $a| tr ', ' '\n\n' | xargs -I{} -P 84 sqlplus -s -l scott/book @m2.txt 4e5 MODIFY {} 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) -------------------- ---------- ---------------------- ------------- NOMARKHOT 84 5375 451458 MARKHOT 84 5860 492215 MODIFY 84 5866 492761 --//问题照旧。 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 2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA 57756 HOTCOPY5 2376593 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 1054289511 767295bfca293b63b6f7cb623ed72e67 SQL AREA 77415 HOTCOPY4 1989065 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 3233207171 eb871620fcbe3434c79e2665c0b6d383 SQL AREA 54147 HOTCOPY16 1985272 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2582763541 c88682c52f8f2765a84d377399f1d815 SQL AREA 120853 HOTCOPY1 1981125 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA 88684 HOTCOPY6 1976634 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 3492648563 8d651d1b5e6152636190413bd02d9673 SQL AREA 104051 HOTCOPY17 1972980 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2159114629 bd2a9fd75309aaef9ce808dd80b17985 SQL AREA 96645 HOTCOPY15 1596766 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA 116044 HOTCOPY3 1595704 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 293343694 8b69d2d58627e62a20a5434b117c11ce SQL AREA 4558 HOTCOPY19 1594337 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA 7234 HOTCOPY7 1593345 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2373487536 4b725dd36b3084a43d0fc69a8d788bb0 SQL AREA 35760 HOTCOPY21 1593193 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2496819678 14d02282ba345f71d7396b5694d271de SQL AREA 29150 HOTCOPY14 1593099 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 1724855008 95db377359aad060a01cca1266cf32e0 SQL AREA 78560 HOTCOPY18 1591348 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 4206576078 8381913ebc5dbe17bbe2ddbafabb41ce SQL AREA 82382 HOTCOPY20 1583056 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2264846016 3d03c18a8227f757cf0e6a5886fecec0 SQL AREA 52928 HOTCOPY23 1198483 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 1522006932 cc360b48be5bd0ce4e8dd54a5ab7fb94 SQL AREA 129940 HOTCOPY22 1198435 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 1051678227 0cbac329d8cbd103bdf0b37e3eaf5613 SQL AREA 87571 HOTCOPY10 1197761 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 3650006106 3ad214cdf323271020dd88eed98eac5a SQL AREA 44122 HOTCOPY13 1196945 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 4251304214 88e9b0a03994a83e3b1dd47ffd65c116 SQL AREA 114966 HOTCOPY2 799795 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA 11508 HOTCOPY9 799705 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA 88906 HOTCOPY11 799363 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 879431168 935723b8e76ce7f46c9524b7346b0e00 SQL AREA 69120 HOTCOPY12 799176 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 3708080158 2b2d6a42e3f5dc2950f01eb8dd04d01e SQL AREA 53278 HOTCOPY8 400000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA 11140 HOT 84 0 24 rows selected. $ echo $a | tr ',' '\n' | xargs -I{} -P 14 sqlplus -s -l scott/book @m2.txt 1e6 MODIFY14 {} 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) -------------------- ---------- ---------------------- ------------- MODIFY14 14 2971 41591 NOMARKHOT 84 5375 451458 MARKHOT 84 5860 492215 MODIFY 84 5866 492761 --//我前面的测试结果，可以看出基本没效果。 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) -------------------- ---------- ---------------------- ------------- NOMARKHOT 14 2600 36404 MARKHOT 14 3099 43391 SCOTT@book> select mod(sid,23) ,count(*) from job_times where method='MODIFY14' group by mod(sid,23); MOD(SID,23) COUNT(*) ----------- ---------- 1 1 6 1 13 2 5 2 18 1 0 2 16 1 19 1 15 2 10 1 10 rows selected. --//还是存在冲突。 4.总结: --//通过测试可以发现使用DBMS_SHARED_POOL.MARKHOT标识热sql语句,效果不大，建议不要使用。