[20200213]使用DBMS_SHARED_POOL.MARKHOT标识热对象2.txt

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

[20200213]使用DBMS_SHARED_POOL.MARKHOT标识热对象2.txt --//链接 [20200212]使用DBMS_SHARED_POOL.MARKHOT标识热对象.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 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 --//rename job_times to job_times_20200212; create table job_times (sid number, sessionid number,time_ela number,method varchar2(20)); --//建立脚本bb.txt: $ cat bb.txt SELECT owner ,name ,hash_value ,full_hash_value ,namespace ,child_latch ,property hot_flag ,executions ,invalidations FROM v$db_object_cache WHERE name = 'DBMS_APPLICATION_INFO' AND owner = 'SYS' order by executions desc ; --//建立测试脚本m3.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; begin for i in 1 .. &&1 loop execute immediate 'begin dbms_application_info.set_client_info(''mutex'');end;'; 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 2.测试： --//不使用DBMS_SHARED_POOL.MARKHOT的情况 $ seq 1 | xargs -I{} -P 1 sqlplus -s -l scott/book @m3.txt 1e6 p=01 {} >/dev/null $ seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/book @m3.txt 1e6 p=50 {} >/dev/null 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) -------------------- ---------- ---------------------- ------------- p=01 1 3795 3795 p=50 50 42495 2124729 3.测试2: --//使用DBMS_SHARED_POOL.MARKHOT的情况,首先使用dbms_shared_pool.markhot设置，注意必须sys用户执行： SYS@book> exec dbms_shared_pool.markhot('SYS','DBMS_APPLICATION_INFO',1); PL/SQL procedure successfully completed. SYS@book> exec dbms_shared_pool.markhot('SYS','DBMS_APPLICATION_INFO',2); PL/SQL procedure successfully completed. SYS@book> @ bb.txt OWNER NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS ------ --------------------- ---------- -------------------------------- ---------------- ----------- ---------- ---------- ------------- SYS DBMS_APPLICATION_INFO 539807965 eab253aef59cd250bd8b8a13202cd0dd BODY 53469 HOT 51000108 0 SYS DBMS_APPLICATION_INFO 2876716960 5a81de0b29b19e757e67708dab7737a0 TABLE/PROCEDURE 79776 HOT 0 0 --//使用DBMS_SHARED_POOL.MARKHOT的情况: $ seq 1 | xargs -I{} -P 1 sqlplus -s -l scott/book @m3.txt 1e6 markhot_p=01 {} >/dev/null $ seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/book @m3.txt 1e6 markhot_p=50 {} >/dev/null SYS@book> @ wait P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATUS STATE WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS ---------------- ---------------- ---------------- ---------- ------------- ----------- --- ------- ----- ---------------------- -------- ------------------- --------------- --------------- ----------- 00000000736BF107 0000014700000000 000000000000005F 1936453895 1404454305792 95 3 15 2878 library cache: mutex X ACTIVE WAITED SHORT TIME 9 0 Concurrency 00000000A381897E 0000005800000000 000000000000005A 2743175550 377957122048 90 4 11 2896 library cache: mutex X ACTIVE WAITED KNOWN TIME 11000 0 Concurrency 00000000A381897E 0000000400000000 0000000000000004 2743175550 17179869184 4 16 21 2765 library cache: mutex X ACTIVE WAITING 871 0 Concurrency 000000008658911B 000000D40000001F 0000000900000000 2253951259 910533066783 38654705664 17 21 1952 cursor: pin S ACTIVE WAITED SHORT TIME 3 0 Concurrency 000000008658911B 000000AA00000022 0000000900000000 2253951259 730144440354 38654705664 31 25 1840 cursor: pin S ACTIVE WAITED SHORT TIME 16 0 Concurrency 000000008658911B 000000AA00000022 0000000900000000 2253951259 730144440354 38654705664 32 11 1855 cursor: pin S ACTIVE WAITED KNOWN TIME 10990 0 Concurrency 00000000C1106001 0000002000000000 0000000000000004 3239075841 137438953472 4 44 17 1735 library cache: mutex X ACTIVE WAITING 14499 0 Concurrency 000000008658911B 0000006600000024 0000000300000000 2253951259 438086664228 12884901888 45 13 1380 cursor: pin S ACTIVE WAITED KNOWN TIME 10927 0 Concurrency 000000008658911B 000000660000001E 0000000900000000 2253951259 438086664222 38654705664 46 23 2856 cursor: pin S ACTIVE WAITED SHORT TIME 1270 0 Concurrency 00000000998B45B3 0000002E00000000 000000000000005A 2576041395 197568495616 90 58 5 2994 library cache: mutex X ACTIVE WAITED SHORT TIME 11 0 Concurrency 000000008658911B 000000AA00000022 0000000800000000 2253951259 730144440354 34359738368 59 25 1865 cursor: pin S ACTIVE WAITED SHORT TIME 55 0 Concurrency 000000008658911B 000000AA00000022 0000000900000000 2253951259 730144440354 38654705664 72 15 1051 cursor: pin S ACTIVE WAITED SHORT TIME 47 0 Concurrency 000000008658911B 000000AA00000022 0000000900000000 2253951259 730144440354 38654705664 73 11 1751 cursor: pin S ACTIVE WAITED SHORT TIME 33 0 Concurrency 0000000087DBFB16 0000014600000000 000000000000005F 2279340822 1400159338496 95 86 15 1792 library cache: mutex X ACTIVE WAITING 13610 0 Concurrency 0000000085F80D48 000000000000000F 00 2247626056 15 0 87 11 2821 latch free ACTIVE WAITED SHORT TIME 968 0 Other 00000000A381897E 0000000400000000 000000000000005F 2743175550 17179869184 95 88 29 2766 library cache: mutex X ACTIVE WAITING 1054 0 Concurrency 000000000BFF257A 0000001100000000 000000000000005F 201270650 73014444032 95 101 15 1951 library cache: mutex X ACTIVE WAITED KNOWN TIME 21985 0 Concurrency 000000003864323C 00 0000000000000004 946090556 0 4 102 11 1835 library cache: mutex X ACTIVE WAITED SHORT TIME 2 0 Concurrency 000000008658911B 000000AA00000022 0000000800000000 2253951259 730144440354 34359738368 114 15 1806 cursor: pin S ACTIVE WAITED KNOWN TIME 10995 0 Concurrency 000000008658911B 000000AA00000022 0000000900000000 2253951259 730144440354 38654705664 115 11 1793 cursor: pin S ACTIVE WAITED SHORT TIME 41 0 Concurrency 00000000C1106001 0000002000000000 0000000000000004 3239075841 137438953472 4 128 15 1803 library cache: mutex X ACTIVE WAITING 7735 0 Concurrency 000000008658911B 000000AA00000022 0000000900000000 2253951259 730144440354 38654705664 129 11 1268 cursor: pin S ACTIVE WAITED KNOWN TIME 47493 0 Concurrency 00000000998B45B3 00 000000000000005F 2576041395 0 95 142 3 3225 library cache: mutex X ACTIVE WAITED KNOWN TIME 9999 0 Concurrency 000000008658911B 000000AA00000022 0000000800000000 2253951259 730144440354 34359738368 143 23 2051 cursor: pin S ACTIVE WAITED KNOWN TIME 17992 0 Concurrency 000000008658911B 000000AA00000022 0000000900000000 2253951259 730144440354 38654705664 156 3 1137 cursor: pin S ACTIVE WAITED SHORT TIME 2497 0 Concurrency 000000003AA00BDC 00 0000000000000004 983567324 0 4 157 23 1592 library cache: mutex X ACTIVE WAITED KNOWN TIME 12991 0 Concurrency 0000000087DBFB16 0000014600000000 000000000000005F 2279340822 1400159338496 95 170 15 1775 library cache: mutex X ACTIVE WAITING 13815 0 Concurrency 00000000736BF107 0000014700000000 000000000000005F 1936453895 1404454305792 95 171 11 2784 library cache: mutex X ACTIVE WAITED KNOWN TIME 10975 0 Concurrency 00000000A381897E 0000005800000000 0000000000000004 2743175550 377957122048 4 184 15 2980 library cache: mutex X ACTIVE WAITED KNOWN TIME 22993 0 Concurrency 000000000A5A39E3 00 0000000000000004 173685219 0 4 185 11 1985 library cache: mutex X ACTIVE WAITED SHORT TIME 3 0 Concurrency 000000008658911B 000000AA00000022 0000000900000000 2253951259 730144440354 38654705664 198 3 1826 cursor: pin S ACTIVE WAITED KNOWN TIME 24983 0 Concurrency 000000008658911B 000000D500000023 0000000800000000 2253951259 914828034083 34359738368 199 23 1834 cursor: pin S ACTIVE WAITED KNOWN TIME 26277 0 Concurrency 00000000C1106001 0000002000000000 0000000000000004 3239075841 137438953472 4 212 27 1861 library cache: mutex X ACTIVE WAITING 8894 0 Concurrency 000000008658911B 000000AA00000022 0000000300000000 2253951259 730144440354 12884901888 213 19 1395 cursor: pin S ACTIVE WAITED SHORT TIME 53 0 Concurrency 00000000B623A40E 00 000000000000005A 3055789070 0 90 226 5 3035 library cache: mutex X ACTIVE WAITED SHORT TIME 2 0 Concurrency 00000000AC5B02CB 0000013700000000 000000000000005A 2891645643 1335734829056 90 227 23 1958 library cache: mutex X ACTIVE WAITED SHORT TIME 1734 0 Concurrency 000000008658911B 000000AA00000022 0000000800000000 2253951259 730144440354 34359738368 241 13 1699 cursor: pin S ACTIVE WAITED KNOWN TIME 10984 0 Concurrency 0000000087DBFB16 0000014600000000 0000000000000004 2279340822 1400159338496 4 242 11 1990 library cache: mutex X ACTIVE WAITING 13993 0 Concurrency 00000000736BF107 00 0000000000000004 1936453895 0 4 255 3 3134 library cache: mutex X ACTIVE WAITED KNOWN TIME 11132 0 Concurrency 00000000A381897E 0000000400000000 0000000000000004 2743175550 17179869184 4 256 23 2847 library cache: mutex X ACTIVE WAITING 1468 0 Concurrency 000000008658911B 000000AA00000022 0000000900000000 2253951259 730144440354 38654705664 267 3 3103 cursor: pin S ACTIVE WAITED KNOWN TIME 19957 0 Concurrency 00000000A381897E 0000000400000000 000000000000005F 2743175550 17179869184 95 268 23 2826 library cache: mutex X ACTIVE WAITING 1486 0 Concurrency 000000008658911B 000000AA00000022 0000000900000000 2253951259 730144440354 38654705664 281 15 2029 cursor: pin S ACTIVE WAITED KNOWN TIME 21981 0 Concurrency 000000008658911B 000000030000002B 0000000900000000 2253951259 12884901931 38654705664 282 11 1823 cursor: pin S ACTIVE WAITED SHORT TIME 2 0 Concurrency 000000004B7B95E8 00 0000000000000004 1266390504 0 4 295 15 1795 library cache: mutex X ACTIVE WAITED KNOWN TIME 8534 0 Concurrency 000000008658911B 000000030000002B 0000000900000000 2253951259 12884901931 38654705664 298 21 3191 cursor: pin S ACTIVE WAITED SHORT TIME 2 0 Concurrency 00000000998B45B3 00 000000000000005A 2576041395 0 90 310 3 2955 library cache: mutex X ACTIVE WAITED SHORT TIME 3 0 Concurrency 000000008658911B 000000E20000002B 0000000800000000 2253951259 970662608939 34359738368 311 23 1863 cursor: pin S ACTIVE WAITED SHORT TIME 4 0 Concurrency 000000008658911B 000000490000001E 0000000300000000 2253951259 313532612638 12884901888 326 11 1802 cursor: pin S ACTIVE WAITED KNOWN TIME 9898 0 Concurrency 000000009C565232 00 0000000000000004 2622902834 0 4 327 13 3247 library cache: mutex X ACTIVE WAITED KNOWN TIME 5212 0 Concurrency 50 rows selected. --//等待事件主要是cursor: pin S，library cache: mutex X。 SYS@book> @ bb.txt OWNER NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS ------ --------------------- ---------- -------------------------------- ---------------- ----------- ---------- ---------- ------------- SYS DBMS_APPLICATION_INFO 539807965 eab253aef59cd250bd8b8a13202cd0dd BODY 53469 HOT 51000108 0 SYS DBMS_APPLICATION_INFO 3055789070 261372a7214e6f59d503b0b8b623a40e BODY 107534 HOTCOPY11 6000012 0 SYS DBMS_APPLICATION_INFO 1860572659 e23f4ff2543c4b31a46fd97e6ee615f3 BODY 5619 HOTCOPY5 6000012 0 SYS DBMS_APPLICATION_INFO 2622902834 57d0d16d19e33101289e28b79c565232 BODY 21042 HOTCOPY4 6000012 0 SYS DBMS_APPLICATION_INFO 1266390504 7e76255c8a06bcbdec2d02864b7b95e8 BODY 103912 HOTCOPY8 4000009 0 SYS DBMS_APPLICATION_INFO 2279340822 a720613c72be1066682c35b487dbfb16 BODY 129814 HOTCOPY3 4000008 0 SYS DBMS_APPLICATION_INFO 459535560 d0f1e4979ba988bc5c341a6c1b63f4c8 BODY 128200 HOTCOPY10 4000008 0 SYS DBMS_APPLICATION_INFO 1797491121 bf0c8ea515dc6500abad3ebd6b2389b1 BODY 100785 HOTCOPY12 4000008 0 SYS DBMS_APPLICATION_INFO 201270650 9b56e047795d868ea4ea9ec50bff257a BODY 75130 HOTCOPY6 4000008 0 SYS DBMS_APPLICATION_INFO 2971963479 238e8f68f06d46070b6d9e6eb1249057 BODY 36951 HOTCOPY7 4000008 0 SYS DBMS_APPLICATION_INFO 3239075841 be89c6bcab68bb6d90a880e1c1106001 BODY 24577 HOTCOPY9 4000008 0 SYS DBMS_APPLICATION_INFO 983567324 1d27d5e0cd5bd1677e144e9b3aa00bdc BODY 3036 HOTCOPY2 3000006 0 SYS DBMS_APPLICATION_INFO 1796761353 ac6bcb94cda09c0bae60f1ff6b186709 BODY 26377 HOTCOPY1 2000004 0 SYS DBMS_APPLICATION_INFO 1936453895 81a03530b8ba88d28e796054736bf107 TABLE/PROCEDURE 127239 HOTCOPY4 0 0 SYS DBMS_APPLICATION_INFO 2876716960 5a81de0b29b19e757e67708dab7737a0 TABLE/PROCEDURE 79776 HOT 0 0 SYS DBMS_APPLICATION_INFO 3718463526 a030bcad743361ffa50d3418dda34026 TABLE/PROCEDURE 81958 HOTCOPY9 0 0 SYS DBMS_APPLICATION_INFO 2576041395 fa36a728837d7c8deaced5a6998b45b3 TABLE/PROCEDURE 83379 HOTCOPY11 0 0 SYS DBMS_APPLICATION_INFO 2743175550 2b1f30628b268445c855ebdda381897e TABLE/PROCEDURE 100734 HOTCOPY5 0 0 SYS DBMS_APPLICATION_INFO 3652451954 26cf0f1c5fd83145cc78dfd7d9b3fe72 TABLE/PROCEDURE 130674 HOTCOPY3 0 0 SYS DBMS_APPLICATION_INFO 946090556 6ebaf88caae458a48760e5a33864323c TABLE/PROCEDURE 12860 HOTCOPY7 0 0 SYS DBMS_APPLICATION_INFO 173685219 64babbc5d18572a06016bd320a5a39e3 TABLE/PROCEDURE 14819 HOTCOPY6 0 0 SYS DBMS_APPLICATION_INFO 185977409 b69976529943f2f4379406ae0b15ca41 TABLE/PROCEDURE 117313 HOTCOPY2 0 0 SYS DBMS_APPLICATION_INFO 3662569346 b962b50e871c43493b9f21f0da4e5f82 TABLE/PROCEDURE 24450 HOTCOPY1 0 0 SYS DBMS_APPLICATION_INFO 1039286727 ad12b04477bef525ed10c6963df241c7 TABLE/PROCEDURE 16839 HOTCOPY8 0 0 SYS DBMS_APPLICATION_INFO 2891645643 ebd72d40d0d53a9bc1af2332ac5b02cb TABLE/PROCEDURE 66251 HOTCOPY12 0 0 SYS DBMS_APPLICATION_INFO 4120446936 2e29e2f020979f681006ff17f59907d8 TABLE/PROCEDURE 67544 HOTCOPY10 0 0 26 rows selected. SCOTT@book> show parameter cpu_count NAME TYPE VALUE --------- ------- ----- cpu_count integer 24 --//取模是cpu_count/2. SCOTT@book> Select mod(sid,12)+1,count(*) from job_times where method='markhot_p=50' group by mod(sid,12) order by 2 desc; MOD(SID,12)+1 COUNT(*) ------------- ---------- 5 6 11 6 4 6 3 4 6 4 9 4 8 4 7 4 12 4 10 3 2 3 1 2 12 rows selected. --//注意看前面HOT_FLAG=HOTCOPY11 ,HOTCOPY5,HOTCOPY4的执行次数，可以发现基本吻合。 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) -------------------- ---------- ---------------------- ------------- markhot_p=01 1 3678 3678 p=01 1 3795 3795 markhot_p=50 50 11471 573527 p=50 50 42495 2124729 --//对比使用begin /*+ &&3 */ dbms_application_info.set_client_info(''mutex'');end;的情况。 --//链接：http://blog.itpub.net/267265/viewspace-2675369/ =>[20200212]使用DBMS_SHARED_POOL.MARKHOT标识热对象.txt 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_p=01 1 3639 3639 p=01 1 3719 3719 markhot_p=50 50 10784 539216 p=50 50 40326 2016294 --//可以加入注解/*+ &&3 */快一点点。不会出现cursor: pin S等待事件。总结: --//如果应用频繁多个会话调用存储过程，可以使用DBMS_SHARED_POOL.MARKHOT标识热对象，减少争用提高性能。