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

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

[20200126]使用DBMS_SHARED_POOL.MARKHOT与sql语句.txt --//以前的测试：http://blog.itpub.net/267265/viewspace-2146632/=>[20171031]markhot.txt --//当时自己对于使用DBMS_SHARED_POOL.MARKHOT太不理解，实际上就是减少争用，我当时的测试是实际上反而更慢。 --//设置MARKHOT后，出现library cache: mutex X. (P1= 11140)实际上我自己没有注意看一些细节。 --//emp表有14条记录，而查询 SELECT sql_id,sql_text,executions,length(sql_text) FROM v$sqlarea WHERE sql_text LIKE '%SELECT ENAME FROM EMP WHERE ROWID = :B1%' AND sql_text NOT LIKE '%sqlarea%'; --//输出是10条记录，也就是生成的hotcopy还不够多，这样反而更慢，从executions的次数也可以看出问题。 --//重新测试看看： 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' $ echo $a | tr ',' '\n' | xargs -I{} -P 14 sqlplus -s -l scott/book @m2.txt 1e6 NOMARKHOT {} SCOTT@book> select * from job_times order by TIME_ELA; SID SESSIONID TIME_ELA METHOD ---------- ---------- ---------- -------------------- 325 20921041 2350 NOMARKHOT 170 20921030 2361 NOMARKHOT 255 20921036 2369 NOMARKHOT 184 20921031 2401 NOMARKHOT 281 20921040 2528 NOMARKHOT 128 20921028 2534 NOMARKHOT 241 20921035 2576 NOMARKHOT 198 20921033 2603 NOMARKHOT 297 20921038 2624 NOMARKHOT 212 20921032 2649 NOMARKHOT 142 20921029 2665 NOMARKHOT 226 20921034 2739 NOMARKHOT 267 20921037 2996 NOMARKHOT 310 20921039 3009 NOMARKHOT 14 rows selected. --//最快的与最慢的相差7秒之多。 3.测试2： --//确定sql_id,查询检索共享池很容易获得： --//sql_id='2gvj95w2k0aw4',hash_value=85994372 SCOTT@book> select hash_value, full_hash_value, namespace, child_latch, property hot_flag, executions, invalidations from v$db_object_cache where hash_value=85994372; HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS ---------- -------------------------------- --------- ----------- -------- ---------- ------------- 85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA 0 53247117 0 85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA 11140 53799285 0 --//FULL_HASH_VALUE= 6ddb0702c4c177cb27ee292f05202b84. SCOTT@book> select distinct name from v$db_object_cache where hash_value=85994372; NAME ---------------------------------------- SELECT ENAME FROM EMP WHERE ROWID =:B1 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 MARKHOT {} SCOTT@book> select * from job_times where METHOD ='MARKHOT' order by TIME_ELA; SID SESSIONID TIME_ELA METHOD ---------- ---------- ---------- -------------------- 281 20921054 2797 MARKHOT 255 20921050 2800 MARKHOT 267 20921053 2814 MARKHOT 170 20921044 2876 MARKHOT 325 20921052 2878 MARKHOT 184 20921046 2902 MARKHOT 297 20921051 2954 MARKHOT 241 20921048 2990 MARKHOT 212 20921047 3052 MARKHOT 226 20921049 3171 MARKHOT 310 20921055 3268 MARKHOT 142 20921043 3459 MARKHOT 17 20921056 3714 MARKHOT 198 20921045 3716 MARKHOT 14 rows selected. 4.对比: 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 --//可以发现标记MARKHOT反而执行时间过长。也就是反而不执行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 ' order by executions desc ; --//注意B1后面有1个空格。 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 67246853 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA 0 HOT 66572231 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA 88906 HOTCOPY11 2993565 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA 0 HOTCOPY11 2993382 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 1054289511 767295bfca293b63b6f7cb623ed72e67 SQL AREA 77415 HOTCOPY4 1999784 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 1054289511 767295bfca293b63b6f7cb623ed72e67 SQL AREA 0 HOTCOPY4 1999784 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 4251304214 88e9b0a03994a83e3b1dd47ffd65c116 SQL AREA 114966 HOTCOPY2 1999158 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 4251304214 88e9b0a03994a83e3b1dd47ffd65c116 SQL AREA 0 HOTCOPY2 1999114 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA 0 HOTCOPY6 1998950 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA 88684 HOTCOPY6 1998940 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA 0 HOTCOPY3 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 1051678227 0cbac329d8cbd103bdf0b37e3eaf5613 SQL AREA 87571 HOTCOPY10 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 1051678227 0cbac329d8cbd103bdf0b37e3eaf5613 SQL AREA 0 HOTCOPY10 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA 57756 HOTCOPY5 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA 0 HOTCOPY5 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA 11508 HOTCOPY9 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA 0 HOTCOPY9 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA 116044 HOTCOPY3 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA 0 HOTCOPY7 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA 7234 HOTCOPY7 1000000 0 20 rows selected. --//可以看出我仅仅建立有10个HOTCOPY，从EXECUTIONS次数也可以看出不平衡。奇怪没有HOT_FLAG='HOTCOPY1',遇到有几次有的情况。 --//从EXECUTIONS可以看出出现争用的情况。 5.继续测试： echo $a | tr ',' '\n' | xargs -I{} -P 10 sqlplus -s -l scott/book @m2.txt 1e6 MARKHOT10 {} SCOTT@book> select * from job_times where METHOD ='MARKHOT10' order by TIME_ELA; SID SESSIONID TIME_ELA METHOD ---------- ---------- ---------- -------------------- 198 20921068 2563 MARKHOT10 281 20921070 2610 MARKHOT10 226 20921069 2630 MARKHOT10 142 20921071 2647 MARKHOT10 198 20921060 2660 MARKHOT10 226 20921064 2706 MARKHOT10 281 20921066 2708 MARKHOT10 142 20921059 2713 MARKHOT10 170 20921058 2717 MARKHOT10 184 20921061 2722 MARKHOT10 212 20921062 2729 MARKHOT10 241 20921063 2753 MARKHOT10 255 20921065 2768 MARKHOT10 267 20921067 2769 MARKHOT10 14 rows selected. 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 MARKHOT10 14 2693 37695 MARKHOT 14 3099 43391 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 ' 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 67246853 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA 0 HOT 66572231 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA 88906 HOTCOPY11 6993500 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA 0 HOTCOPY11 6993316 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 1054289511 767295bfca293b63b6f7cb623ed72e67 SQL AREA 0 HOTCOPY4 3999779 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 1054289511 767295bfca293b63b6f7cb623ed72e67 SQL AREA 77415 HOTCOPY4 3999779 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA 0 HOTCOPY6 3998950 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA 88684 HOTCOPY6 3998940 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA 0 HOTCOPY7 3000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA 7234 HOTCOPY7 3000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 4251304214 88e9b0a03994a83e3b1dd47ffd65c116 SQL AREA 114966 HOTCOPY2 2999158 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 4251304214 88e9b0a03994a83e3b1dd47ffd65c116 SQL AREA 0 HOTCOPY2 2999114 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA 57756 HOTCOPY5 2000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA 116044 HOTCOPY3 2000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA 11508 HOTCOPY9 2000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA 0 HOTCOPY5 2000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA 0 HOTCOPY9 2000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA 0 HOTCOPY3 2000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 1051678227 0cbac329d8cbd103bdf0b37e3eaf5613 SQL AREA 0 HOTCOPY10 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 1051678227 0cbac329d8cbd103bdf0b37e3eaf5613 SQL AREA 87571 HOTCOPY10 1000000 0 20 rows selected. --//还是出现分配不平均的情况，从HOT_FLAG='HOTCOPY10'的EXECUTIONS=1000000就可以看出来。 $ echo $a | tr ',' '\n' | xargs -I{} -P 7 sqlplus -s -l scott/book @m2.txt 1e6 MARKHOT7 {} --//注：这样仅仅打开7个并行sqlplus，基本分2次执行。 SCOTT@book> select * from job_times where METHOD ='MARKHOT7' order by TIME_ELA; SID SESSIONID TIME_ELA METHOD ---------- ---------- ---------- -------------------- 170 20921073 2647 MARKHOT7 198 20921080 2650 MARKHOT7 226 20921085 2653 MARKHOT7 184 20921083 2654 MARKHOT7 212 20921081 2655 MARKHOT7 198 20921075 2657 MARKHOT7 241 20921082 2662 MARKHOT7 142 20921084 2663 MARKHOT7 212 20921078 2668 MARKHOT7 170 20921079 2672 MARKHOT7 241 20921077 2716 MARKHOT7 184 20921074 2728 MARKHOT7 142 20921072 2761 MARKHOT7 226 20921076 2787 MARKHOT7 14 rows selected. 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 MARKHOT7 14 2684 37573 MARKHOT10 14 2693 37695 MARKHOT 14 3099 43391 6.总结: --//不知道是否可以得出DBMS_SHARED_POOL.MARKHOT标识热的sql语句，并不能获得良好的性能效果，还不如不用。 --//这是我节前跟别人的讨论，我个人不建议使用这样方式解决这类问题。 --//首先问开发为什么要频繁执行这样的sql语句，是否可以通过注解等方式分散这样的sql语句。 --//我在重复看测试，为什么使用DBMS_SHARED_POOL.MARKHOT标识热的sql语句反而更慢，我减少并发数量一样没有快于不使用的情况。 --//今天上班重复测试： --//不使用DBMS_SHARED_POOL.MARKHOT标识热的sql语句时，看到的等待事件是： SCOTT@book> @ wait P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATUS STATE WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS ---------------- ---------------- ---------------- ---------- -------------- ------------ ---------- ---------- ---------- ------------- -------- ------------------- --------------- --------------- -------------------- 0000000005202B84 000000000000000B 0000000900000000 85994372 11 38654705664 30 11 78 cursor: pin S ACTIVE WAITED SHORT TIME 2 3 Concurrency 0000000005202B84 0000008000000007 0000000300000000 85994372 549755813895 12884901888 44 11 32 cursor: pin S ACTIVE WAITED SHORT TIME 3 5 Concurrency 0000000005202B84 000000000000000A 0000000900000000 85994372 10 38654705664 58 11 79 cursor: pin S ACTIVE WAITED SHORT TIME 2 0 Concurrency 0000000005202B84 000001270000000B 0000000300000000 85994372 1267015352331 12884901888 72 9 53 cursor: pin S ACTIVE WAITED SHORT TIME 1 3 Concurrency 0000000005202B84 0000000000000009 0000000300000000 85994372 9 12884901888 86 9 75 cursor: pin S ACTIVE WAITED SHORT TIME 9 2 Concurrency 0000000005202B84 0000000000000009 0000000300000000 85994372 9 12884901888 101 7 76 cursor: pin S ACTIVE WAITED SHORT TIME 2 1 Concurrency 0000000005202B84 0000008000000006 0000000300000000 85994372 549755813894 12884901888 114 5 78 cursor: pin S ACTIVE WAITED SHORT TIME 2 7 Concurrency 0000000005202B84 0000002C0000000C 0000000900000000 85994372 188978561036 38654705664 128 5 78 cursor: pin S ACTIVE WAITED SHORT TIME 2 5 Concurrency 0000000005202B84 0000003A00000008 0000000900000000 85994372 249108103176 38654705664 142 5 60 cursor: pin S ACTIVE WAITED SHORT TIME 3 4 Concurrency 0000000005202B84 0000003A00000008 0000000300000000 85994372 249108103176 12884901888 156 5 64 cursor: pin S ACTIVE WAITED SHORT TIME 12 4 Concurrency 0000000005202B84 000000000000000D 0000000900000000 85994372 13 38654705664 170 5 78 cursor: pin S ACTIVE WAITED SHORT TIME 2 1 Concurrency 0000000005202B84 0000002C00000008 0000000300000000 85994372 188978561032 12884901888 184 5 47 cursor: pin S ACTIVE WAITED SHORT TIME 4 15 Concurrency 0000000005202B84 000000AA00000008 0000000900000000 85994372 730144440328 38654705664 198 5 70 cursor: pin S ACTIVE WAITED KNOWN TIME 11008 7 Concurrency 0000000005202B84 0000000000000007 0000000300000000 85994372 7 12884901888 295 9 91 cursor: pin S ACTIVE WAITED SHORT TIME 1 2 Concurrency 14 rows selected. --//不使用DBMS_SHARED_POOL.MARKHOT标识热的sql语句时，看到的等待事件是： SYS@book> exec dbms_shared_pool.markhot( hash=>'6ddb0702c4c177cb27ee292f05202b84', namespace=>0, global=>true); PL/SQL procedure successfully completed. SCOTT@book> @ wait P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATUS STATE WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS ---------------- ---------------- ---------------- ---------- -------------- ------------ ---------- ---------- ---------- ---------------------- -------- ------------------- --------------- --------------- -------------------- 00000000A28C1C42 000000C600000000 0000000300000000 2727091266 850403524608 12884901888 30 17 1756 cursor: pin S ACTIVE WAITED SHORT TIME 1 0 Concurrency 0000000099F1D815 00 0000000300000000 2582763541 0 12884901888 72 11 1452 cursor: pin S ACTIVE WAITED SHORT TIME 1 0 Concurrency 00000000B4DBC54C 000000AA00000000 0000000300000000 3034301772 730144440320 12884901888 86 11 165 cursor: pin S ACTIVE WAITED SHORT TIME 1 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 101 9 148 latch: shared pool ACTIVE WAITED SHORT TIME 57 1 Concurrency 00000000A28C1C42 00 0000000300000000 2727091266 0 12884901888 114 7 1238 cursor: pin S ACTIVE WAITED SHORT TIME 1 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 128 7 189 latch: shared pool ACTIVE WAITED SHORT TIME 50 1 Concurrency 0000000099F1D815 00 0000000300000000 2582763541 0 12884901888 156 7 869 cursor: pin S ACTIVE WAITED SHORT TIME 3 0 Concurrency 00000000B4DBC54C 00 0000000300000000 3034301772 0 12884901888 170 7 1941 cursor: pin S ACTIVE WAITED SHORT TIME 2 0 Concurrency 0000000000002B84 0000003A00000000 000000000000003E 11140 249108103168 62 184 7 197 library cache: mutex X ACTIVE WAITED SHORT TIME 8 0 Concurrency 00000000A28C1C42 00 0000000300000000 2727091266 0 12884901888 198 7 950 cursor: pin S ACTIVE WAITED SHORT TIME 1 0 Concurrency 0000000000002B84 0000006500000000 000000000000003E 11140 433791696896 62 241 1 204 library cache: mutex X ACTIVE WAITED SHORT TIME 1 1 Concurrency 0000000000002B84 00 000000000000003E 11140 0 62 295 21 171 library cache: mutex X ACTIVE WAITED SHORT TIME 2 1 Concurrency 12 rows selected. SCOTT@book> @ wait P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATUS STATE WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS ---------------- ---------------- ---------------- ---------- -------------- ------------ ---------- ---------- ---------- ---------------------- -------- ------------------- --------------- --------------- -------------------- 00000000A28C1C42 000000C600000000 0000000300000000 2727091266 850403524608 12884901888 30 17 290 cursor: pin S ACTIVE WAITED SHORT TIME 2 0 Concurrency 0000000000002B84 0000012700000000 000000000000003E 11140 1267015352320 62 58 13 41 library cache: mutex X ACTIVE WAITED SHORT TIME 4 1 Concurrency 0000000099F1D815 00 0000000300000000 2582763541 0 12884901888 72 11 487 cursor: pin S ACTIVE WAITED SHORT TIME 2 0 Concurrency 0000000000002B84 0000006500000000 000000000000003E 11140 433791696896 62 86 11 57 library cache: mutex X ACTIVE WAITED SHORT TIME 2 1 Concurrency 0000000000002B84 00 000000000000003E 11140 0 62 101 9 35 library cache: mutex X ACTIVE WAITED SHORT TIME 3 2 Concurrency ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 00000000A28C1C42 0000000000000001 0000000300000000 2727091266 1 12884901888 114 7 233 cursor: pin S ACTIVE WAITED SHORT TIME 3 0 Concurrency 0000000000002B84 00 000000000000003E 11140 0 62 128 7 47 library cache: mutex X ACTIVE WAITED SHORT TIME 3 1 Concurrency 0000000000002B84 00 000000000000003E 11140 0 62 142 7 41 library cache: mutex X ACTIVE WAITED SHORT TIME 2 0 Concurrency 0000000000002B84 00 000000000000003E 11140 0 62 156 7 97 library cache: mutex X ACTIVE WAITED SHORT TIME 1 2 Concurrency 0000000000002B84 0000006500000000 000000000000003E 11140 433791696896 62 170 7 146 library cache: mutex X ACTIVE WAITED SHORT TIME 4 1 Concurrency ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 0000000000002B84 0000012700000000 000000000000003E 11140 1267015352320 62 184 7 51 library cache: mutex X ACTIVE WAITED SHORT TIME 2 0 Concurrency 0000000000002B84 00 000000000000003E 11140 0 62 198 7 81 library cache: mutex X ACTIVE WAITED SHORT TIME 2 0 Concurrency 0000000000002B84 0000006500000000 000000000000003E 11140 433791696896 62 241 1 43 library cache: mutex X ACTIVE WAITED SHORT TIME 7 1 Concurrency 0000000000002B84 00 000000000000003E 11140 0 62 295 21 42 library cache: mutex X ACTIVE WAITED SHORT TIME 3 0 Concurrency 14 rows selected. --//遇到大量的library cache: mutex X等待事件。 SCOTT@book> @ ev_name 'library cache: mutex X' EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS ---------- ---------- ---------------------------------------- -------------------- -------------------- -------------------- ------------- ----------- -------------------- 289 1646780882 library cache: mutex X idn value where 3875070507 4 Concurrency --//参数P1,P2,P3表示什么呢？ 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 ' 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 39941116 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA 0 HOT 39532761 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA 7234 HOTCOPY7 2971797 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA 0 HOTCOPY7 2964992 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA 88906 HOTCOPY11 1997489 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA 0 HOTCOPY11 1997360 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2582763541 c88682c52f8f2765a84d377399f1d815 SQL AREA 120853 HOTCOPY1 1996477 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2582763541 c88682c52f8f2765a84d377399f1d815 SQL AREA 0 HOTCOPY1 1995152 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA 116044 HOTCOPY3 1975972 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA 0 HOTCOPY3 1886386 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA 0 HOTCOPY6 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA 88684 HOTCOPY6 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 3708080158 2b2d6a42e3f5dc2950f01eb8dd04d01e SQL AREA 0 HOTCOPY8 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 3708080158 2b2d6a42e3f5dc2950f01eb8dd04d01e SQL AREA 53278 HOTCOPY8 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 4251304214 88e9b0a03994a83e3b1dd47ffd65c116 SQL AREA 0 HOTCOPY2 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 4251304214 88e9b0a03994a83e3b1dd47ffd65c116 SQL AREA 114966 HOTCOPY2 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA 0 HOTCOPY9 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA 57756 HOTCOPY5 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA 0 HOTCOPY5 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA 11508 HOTCOPY9 1000000 0 20 rows selected. --//对比前面下划线内容。 --//P1=11140，正好对应HOT_FLAG='HOT'的CHILD_LATCH. --//P2=433791696896, 433791696896 = 0x6500000000 --//P3=62 ,P2,p3不知道表示什么。猜测P2对应阻塞的会话，0x65 = 101,注意看++++的下划线内容。 --//而P2＝1267015352320 = 0x12700000000, 能力有限无法推测。 --//CHILD_LATCH值似乎与FULL_HASH_VALUE有关。另外写一篇blog. SELECT sql_id ,sql_text ,executions ,LENGTH (sql_text) ,ORA_HASH (sql_text) ,hash_value FROM v$sqlarea WHERE sql_text = 'SELECT ENAME FROM EMP WHERE ROWID =:B1 '; SQL_ID SQL_TEXT EXECUTIONS LENGTH(SQL_TEXT) ORA_HASH(SQL_TEXT) HASH_VALUE ------------- -------------------------------------- ---------- ---------------- ------------------ ---------- 8jc98afj8s722 SELECT ENAME FROM EMP WHERE ROWID =:B1 2992985 39 1145033045 2727091266 2gvj95w2k0aw4 SELECT ENAME FROM EMP WHERE ROWID =:B1 40873893 39 1145033045 85994372 dqk9v3d8mnb7n SELECT ENAME FROM EMP WHERE ROWID =:B1 1000000 39 1145033045 1362767092 51w0yr3fh9n0y SELECT ENAME FROM EMP WHERE ROWID =:B1 1000000 39 1145033045 3708080158 3v5kmvygyxscw SELECT ENAME FROM EMP WHERE ROWID =:B1 1000000 39 1145033045 2683232668 1rvbzkq5y6qmc SELECT ENAME FROM EMP WHERE ROWID =:B1 1000000 39 1145033045 2346932844 cjh845cj9fqua SELECT ENAME FROM EMP WHERE ROWID =:B1 1999111 39 1145033045 580344650 3q7fngzyqbh8q SELECT ENAME FROM EMP WHERE ROWID =:B1 1000000 39 1145033045 4251304214 3mtu372udrjac SELECT ENAME FROM EMP WHERE ROWID =:B1 1983886 39 1145033045 3034301772 ahm9rffcz3q0p SELECT ENAME FROM EMP WHERE ROWID =:B1 1997056 39 1145033045 2582763541 10 rows selected. --//视乎如果使用DBMS_SHARED_POOL.MARKHOT设置后，如果生成的HOTCOPY达不到并发数量，就存在library cache: mutex X ACTIVE等待事件。 --//执行EXECUTIONS=1000000仅仅5条。其它大于1000000的（不算执行次数40873893），有4条(视乎计数不对，正常应该是后面都是0.占9个会话。)，5+9＝14. --//也就是还是存在争用的情况。 --//有机会测试更多的会话是否效果更好一些，有点长，另外写一篇blog。 SELECT LISTAGG (value_string, ',') WITHIN GROUP (ORDER BY value_string) c200 FROM (SELECT value_string FROM V$SQL_BIND_CAPTURE WHERE sql_id IN ('8jc98afj8s722' ,'dqk9v3d8mnb7n' ,'51w0yr3fh9n0y' ,'3v5kmvygyxscw' ,'1rvbzkq5y6qmc' ,'cjh845cj9fqua' ,'3q7fngzyqbh8q' ,'3mtu372udrjac' ,'ahm9rffcz3q0p')); C200 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- AAAVREAAEAAAACXAAA,AAAVREAAEAAAACXAAB,AAAVREAAEAAAACXAAD,AAAVREAAEAAAACXAAF,AAAVREAAEAAAACXAAG,AAAVREAAEAAAACXAAH,AAAVREAAEAAAACXAAI,AAAVREAAEAAAACXAAJ,AAAVREAAEAAAACXAAK