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

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

[20200212]使用DBMS_SHARED_POOL.MARKHOT标识热对象.txt --//生产系统频繁调度执行如下语句： > @ sqlid 6mnrdrgdys4uc SQL_ID SQLTEXT ------------- ----------------------------------------------- 6mnrdrgdys4uc BEGIN :1 := TAUDIT.recieveallaclaudit(:2); END; > set numw 12 > @ d_buffer 6mnrdrgdys4uc 10 EXECUTIONS1 BUFFER_GETS1 ELAPSED_TIME1 ROWS_PROCESSED1 ------------ ------------ ------------- --------------- 739300907 64 114385121112 739300907 ... sleep 10 , waiting .... EXECUTIONS2 BUFFER_GETS2 ELAPSED_TIME2 ROWS_PROCESSED2 ------------ ------------ ------------- --------------- 739301163 64 114385158246 739301163 总buffer_gets 每次buffer_gets 执行次数总执行时间每次执行时间总处理记录数平均处理记录数 ------------- --------------- ------------ ------------ ------------ ------------ -------------- 0 0 256 37134 145.0546875 256 1 --//平均每秒25.6次，而且现在是疫情的特殊时期。测试看看是否能使用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> @ 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 create table job_times (sid number, sessionid number,time_ela number,method varchar2(20)); --//建立测试脚本m1.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 /*+ &&3 */ 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.测试1： --//不使用DBMS_SHARED_POOL.MARKHOT的情况 $ seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/book @m1.txt 1e6 p=50 {} >/dev/null SCOTT@book> @ wait P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATUS STATE WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS ---------------- ---------------- ---------------- ---------- -------------- --- ---- ------- ----- ---------------------- -------- ----------------- --------------- --------------- ----------- 00000000AB7737A0 000000E300000000 000000000000005F 2876716960 974957576192 95 2 19 41701 library cache: mutex X ACTIVE WAITING 1 0 Concurrency 00000000AB7737A0 0000007200000000 0000000000000004 2876716960 489626271744 4 3 9 45150 library cache: mutex X ACTIVE WAITING 8988 0 Concurrency 00000000AB7737A0 0000000200000000 000000000000005F 2876716960 8589934592 95 17 1 42018 library cache: mutex X ACTIVE WAITING 5442 0 Concurrency 00000000AB7737A0 000000B800000000 000000000000005F 2876716960 790273982464 95 19 1 43192 library cache: mutex X ACTIVE WAITED SHORT TIME 17 0 Concurrency 00000000202CD0DD 0000011A00000000 0000000000000004 539807965 1211180777472 4 30 11 44654 library cache: mutex X ACTIVE WAITED KNOWN TIME 10706 0 Concurrency 00000000AB7737A0 000000D500000000 0000000000000004 2876716960 914828034048 4 31 33 44408 library cache: mutex X ACTIVE WAITED SHORT TIME 9 0 Concurrency 00000000AB7737A0 000000B900000000 000000000000005A 2876716960 794568949760 90 32 9 45539 library cache: mutex X ACTIVE WAITING 7122 0 Concurrency 00000000AB7737A0 00 000000000000005A 2876716960 0 90 44 5 40673 library cache: mutex X ACTIVE WAITING 18026 0 Concurrency 00000000AB7737A0 000000B900000000 0000000000000004 2876716960 794568949760 4 45 11 42796 library cache: mutex X ACTIVE WAITING 7148 0 Concurrency 00000000AB7737A0 0000010B00000000 0000000000000004 2876716960 1146756268032 4 47 1 42967 library cache: mutex X ACTIVE WAITING 6630 0 Concurrency 00000000AB7737A0 000000D500000000 000000000000005F 2876716960 914828034048 95 58 3 41502 library cache: mutex X ACTIVE WAITING 214 0 Concurrency 00000000AB7737A0 0000009D00000000 0000000000000004 2876716960 674309865472 4 59 11 43328 library cache: mutex X ACTIVE WAITING 8467 0 Concurrency 00000000AB7737A0 00 000000000000005A 2876716960 0 90 72 1 43973 library cache: mutex X ACTIVE WAITED SHORT TIME 6 0 Concurrency 00000000AB7737A0 000000D500000000 000000000000005A 2876716960 914828034048 90 73 9 41753 library cache: mutex X ACTIVE WAITED SHORT TIME 8 0 Concurrency ... 50 rows selected. --//大量的library cache: mutex X等待事件。看来热对象使用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 = 'DBMS_APPLICATION_INFO' AND owner = 'SYS' order by executions desc ; --//保存bb.txt,这样可以反复执行与测试。 OWNER NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS ------ --------------------- ---------- -------------------------------- ---------------- ----------- ---------- ---------- ------------- SYS DBMS_APPLICATION_INFO 539807965 eab253aef59cd250bd8b8a13202cd0dd BODY 53469 51000115 0 SYS DBMS_APPLICATION_INFO 2876716960 5a81de0b29b19e757e67708dab7737a0 TABLE/PROCEDURE 79776 0 0 --//0xd0dd = 53469，0x137a0 = 79776 ,FULL_HASH_VALUE 的后17bit等于CHILD_LATCH。 --//前面的library cache: mutex X等待事件P1对于hash_value,P2应该是阻塞的会话sid(猜测) 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=50 50 40326 2016294 --//补充测试仅仅1个会话执行的情况： $ seq 1 | xargs -I{} -P 1 sqlplus -s -l scott/book @m1.txt 1e6 p=01 {} >/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 3719 3719 p=50 50 40326 2016294 --//可以看出没有阻塞的情况，单个会话执行仅仅需要37秒，非常快的执行效率。 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 52000121 0 SYS DBMS_APPLICATION_INFO 3239075841 be89c6bcab68bb6d90a880e1c1106001 BODY 24577 HOTCOPY9 1 0 SYS DBMS_APPLICATION_INFO 3718463526 a030bcad743361ffa50d3418dda34026 TABLE/PROCEDURE 81958 HOTCOPY9 0 0 SYS DBMS_APPLICATION_INFO 2876716960 5a81de0b29b19e757e67708dab7737a0 TABLE/PROCEDURE 79776 HOT 0 0 --//第3个参数标识namesspace，1-> PACKAGE 2-> PACKAGE BODY 可以查询： select distinct kglhdnsp,kglhdnsd,kglobtyd from x$kglob order by 1; SELECT * FROM v$db_object_cache WHERE name = 'SYS' and owner='DBMS_APPLICATION_INFO'; --//使用DBMS_SHARED_POOL.MARKHOT的情况: $ seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/book @m1.txt 1e6 markhot_p=50 {} >/dev/null SCOTT@book> @ wait P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATUS STATE WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS ---------------- ---------------- ---------------- ---------- -------------- ---------- ---------- ---------- ---------- ---------------------- -------- ------------------- --------------- --------------- -------------------- 00000000D9B3FE72 00 0000000000000004 3652451954 0 4 2 25 473 library cache: mutex X ACTIVE WAITED SHORT TIME 2 0 Concurrency 00000000736BF107 0000005700000000 0000000000000004 1936453895 373662154752 4 3 15 764 library cache: mutex X ACTIVE WAITED KNOWN TIME 21988 0 Concurrency 000000000A5A39E3 00 000000000000005F 173685219 0 95 17 7 1805 library cache: mutex X ACTIVE WAITED SHORT TIME 31 0 Concurrency 000000003DF241C7 00 000000000000005A 1039286727 0 90 19 3 144 library cache: mutex X ACTIVE WAITED SHORT TIME 17 0 Concurrency 00000000DDA34026 000000D400000000 000000000000005F 3718463526 910533066752 95 32 15 622 library cache: mutex X ACTIVE WAITING 4742 0 Concurrency 00000000998B45B3 0000002E00000000 0000000000000004 2576041395 197568495616 4 34 1 3353 library cache: mutex X ACTIVE WAITING 91 0 Concurrency 00000000998B45B3 00 0000000000000004 2576041395 0 4 46 21 3111 library cache: mutex X ACTIVE WAITED SHORT TIME 2 0 Concurrency 000000006B2389B1 000000E300000000 000000000000005F 1797491121 974957576192 95 47 45 327 library cache: mutex X ACTIVE WAITED KNOWN TIME 11416 0 Concurrency 00000000998B45B3 00 0000000000000004 2576041395 0 4 58 9 3658 library cache: mutex X ACTIVE WAITED SHORT TIME 3 0 Concurrency 0000000085F80AC8 000000000000000F 00 2247625416 15 0 59 25 482 latch free ACTIVE WAITED KNOWN TIME 12989 0 Other 000000006B186709 0000009C00000000 0000000000000004 1796761353 670014898176 4 72 15 602 library cache: mutex X ACTIVE WAITED KNOWN TIME 15967 0 Concurrency 000000000B15CA41 000000F100000000 0000000000000004 185977409 1035087118336 4 73 11 369 library cache: mutex X ACTIVE WAITED KNOWN TIME 13939 0 Concurrency 00000000736BF107 00 000000000000005A 1936453895 0 90 87 11 808 library cache: mutex X ACTIVE WAITED SHORT TIME 3 0 Concurrency 000000000A5A39E3 0000010D00000000 000000000000005F 173685219 1155346202624 95 89 11 1915 library cache: mutex X ACTIVE WAITED KNOWN TIME 8977 0 Concurrency 000000000A5A39E3 0000010D00000000 000000000000005F 173685219 1155346202624 95 101 9 1826 library cache: mutex X ACTIVE WAITED KNOWN TIME 10973 0 Concurrency 000000003864323C 00 0000000000000004 946090556 0 4 102 11 671 library cache: mutex X ACTIVE WAITED SHORT TIME 3 0 Concurrency 00000000DDA34026 000000D400000000 000000000000005F 3718463526 910533066752 95 104 9 584 library cache: mutex X ACTIVE WAITING 4805 0 Concurrency 00000000B1249057 00 000000000000005A 2971963479 0 90 114 3 650 library cache: mutex X ACTIVE WAITED SHORT TIME 1252 0 Concurrency 00000000DDA34026 000000D400000000 0000000000000004 3718463526 910533066752 4 116 19 606 library cache: mutex X ACTIVE WAITING 4944 0 Concurrency 00000000B623A40E 00 0000000000000004 3055789070 0 4 118 1 3821 library cache: mutex X ACTIVE WAITED SHORT TIME 2 0 Concurrency 00000000F59907D8 0000012900000000 000000000000005F 4120446936 1275605286912 95 129 11 191 library cache: mutex X ACTIVE WAITED KNOWN TIME 11976 0 Concurrency 00000000998B45B3 00 0000000000000004 2576041395 0 4 130 15 3349 library cache: mutex X ACTIVE WAITED KNOWN TIME 21844 0 Concurrency 00000000998B45B3 000000D600000000 000000000000005A 2576041395 919123001344 90 142 3 3075 library cache: mutex X ACTIVE WAITED SHORT TIME 2 0 Concurrency 000000006B186709 0000009C00000000 0000000000000004 1796761353 670014898176 4 144 13 716 library cache: mutex X ACTIVE WAITED KNOWN TIME 15971 0 Concurrency 00000000DA4E5F82 00 000000000000005A 3662569346 0 90 156 3 627 library cache: mutex X ACTIVE WAITED SHORT TIME 2 0 Concurrency 0000000087DBFB16 0000000200000000 0000000000000004 2279340822 8589934592 4 158 13 365 library cache: mutex X ACTIVE WAITED KNOWN TIME 17972 0 Concurrency 00000000736BF107 00 0000000000000004 1936453895 0 4 171 11 648 library cache: mutex X ACTIVE WAITED KNOWN TIME 5789 0 Concurrency 00000000A381897E 000000B800000000 0000000000000004 2743175550 790273982464 4 172 13 807 library cache: mutex X ACTIVE WAITED KNOWN TIME 10536 0 Concurrency 00000000A381897E 00 000000000000005A 2743175550 0 90 184 9 689 library cache: mutex X ACTIVE WAITED KNOWN TIME 12635 0 Concurrency 000000000A5A39E3 00 0000000000000004 173685219 0 4 185 11 2031 library cache: mutex X ACTIVE WAITED KNOWN TIME 20630 0 Concurrency 00000000B1249057 00 0000000000000004 2971963479 0 4 198 3 619 library cache: mutex X ACTIVE WAITED SHORT TIME 2 0 Concurrency 000000003DF241C7 00 000000000000005A 1039286727 0 90 199 15 162 library cache: mutex X ACTIVE WAITED SHORT TIME 2452 1 Concurrency 00000000DDA34026 0000006800000000 000000000000005F 3718463526 446676598784 95 212 25 694 library cache: mutex X ACTIVE WAITED KNOWN TIME 25982 0 Concurrency 00000000B623A40E 00 000000000000005F 3055789070 0 95 214 19 3312 library cache: mutex X ACTIVE WAITING 64 0 Concurrency 00000000998B45B3 00 000000000000005F 2576041395 0 95 226 9 3573 library cache: mutex X ACTIVE WAITED SHORT TIME 1855 0 Concurrency 00000000AC5B02CB 0000003B00000000 0000000000000004 2891645643 253403070464 4 227 11 522 library cache: mutex X ACTIVE WAITED KNOWN TIME 10994 1 Concurrency 000000003AA00BDC 0000004900000000 000000000000005F 983567324 313532612608 95 241 3 372 library cache: mutex X ACTIVE WAITED KNOWN TIME 21996 0 Concurrency 00000000736BF107 00 000000000000005A 1936453895 0 90 243 13 762 library cache: mutex X ACTIVE WAITED KNOWN TIME 7662 0 Concurrency 000000006EE615F3 00 000000000000005F 1860572659 0 95 256 11 707 library cache: mutex X ACTIVE WAITED SHORT TIME 3 0 Concurrency 000000000BFF257A 00 0000000000000004 201270650 0 4 257 13 1899 library cache: mutex X ACTIVE WAITED SHORT TIME 2 0 Concurrency 00000000A381897E 00 0000000000000004 2743175550 0 4 268 11 751 library cache: mutex X ACTIVE WAITED SHORT TIME 1711 0 Concurrency 000000000A5A39E3 00 000000000000005A 173685219 0 90 269 13 1781 library cache: mutex X ACTIVE WAITED SHORT TIME 2 0 Concurrency 000000000BFF257A 0000010D00000000 0000000000000004 201270650 1155346202624 4 281 13 2144 library cache: mutex X ACTIVE WAITED KNOWN TIME 18999 0 Concurrency 000000003864323C 000000C600000000 000000000000005F 946090556 850403524608 95 282 11 622 library cache: mutex X ACTIVE WAITED SHORT TIME 3 0 Concurrency 0000000085F80988 000000000000000F 00 2247625096 15 0 297 17 249 latch free ACTIVE WAITED SHORT TIME 13 0 Other 00000000B623A40E 00 000000000000005A 3055789070 0 90 298 13 3533 library cache: mutex X ACTIVE WAITED SHORT TIME 2 0 Concurrency 00000000B623A40E 00 000000000000005F 3055789070 0 95 310 7 3379 library cache: mutex X ACTIVE WAITING 5511 0 Concurrency 000000006B186709 0000009C00000000 0000000000000004 1796761353 670014898176 4 312 3 599 library cache: mutex X ACTIVE WAITED KNOWN TIME 17536 0 Concurrency 000000003AA00BDC 0000004900000000 0000000000000004 983567324 313532612608 4 325 3 397 library cache: mutex X ACTIVE WAITED KNOWN TIME 21994 0 Concurrency 0000000087DBFB16 00 000000000000005A 2279340822 0 90 326 17 447 library cache: mutex X ACTIVE WAITED SHORT TIME 2539 0 Concurrency 50 rows selected. 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 52000121 0 SYS DBMS_APPLICATION_INFO 3055789070 261372a7214e6f59d503b0b8b623a40e BODY 107534 HOTCOPY11 6599785 0 SYS DBMS_APPLICATION_INFO 201270650 9b56e047795d868ea4ea9ec50bff257a BODY 75130 HOTCOPY6 4794825 0 SYS DBMS_APPLICATION_INFO 1860572659 e23f4ff2543c4b31a46fd97e6ee615f3 BODY 5619 HOTCOPY5 3030284 0 SYS DBMS_APPLICATION_INFO 2971963479 238e8f68f06d46070b6d9e6eb1249057 BODY 36951 HOTCOPY7 3021922 0 SYS DBMS_APPLICATION_INFO 1796761353 ac6bcb94cda09c0bae60f1ff6b186709 BODY 26377 HOTCOPY1 3007850 0 SYS DBMS_APPLICATION_INFO 3239075841 be89c6bcab68bb6d90a880e1c1106001 BODY 24577 HOTCOPY9 2985346 0 SYS DBMS_APPLICATION_INFO 2622902834 57d0d16d19e33101289e28b79c565232 BODY 21042 HOTCOPY4 2971504 0 SYS DBMS_APPLICATION_INFO 2279340822 a720613c72be1066682c35b487dbfb16 BODY 129814 HOTCOPY3 2378864 0 SYS DBMS_APPLICATION_INFO 983567324 1d27d5e0cd5bd1677e144e9b3aa00bdc BODY 3036 HOTCOPY2 2366500 0 SYS DBMS_APPLICATION_INFO 1797491121 bf0c8ea515dc6500abad3ebd6b2389b1 BODY 100785 HOTCOPY12 2353244 0 SYS DBMS_APPLICATION_INFO 1266390504 7e76255c8a06bcbdec2d02864b7b95e8 BODY 103912 HOTCOPY8 1693515 0 SYS DBMS_APPLICATION_INFO 459535560 d0f1e4979ba988bc5c341a6c1b63f4c8 BODY 128200 HOTCOPY10 1624187 0 SYS DBMS_APPLICATION_INFO 946090556 6ebaf88caae458a48760e5a33864323c TABLE/PROCEDURE 12860 HOTCOPY7 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 185977409 b69976529943f2f4379406ae0b15ca41 TABLE/PROCEDURE 117313 HOTCOPY2 0 0 SYS DBMS_APPLICATION_INFO 1936453895 81a03530b8ba88d28e796054736bf107 TABLE/PROCEDURE 127239 HOTCOPY4 0 0 SYS DBMS_APPLICATION_INFO 3652451954 26cf0f1c5fd83145cc78dfd7d9b3fe72 TABLE/PROCEDURE 130674 HOTCOPY3 0 0 SYS DBMS_APPLICATION_INFO 4120446936 2e29e2f020979f681006ff17f59907d8 TABLE/PROCEDURE 67544 HOTCOPY10 0 0 SYS DBMS_APPLICATION_INFO 2891645643 ebd72d40d0d53a9bc1af2332ac5b02cb TABLE/PROCEDURE 66251 HOTCOPY12 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 173685219 64babbc5d18572a06016bd320a5a39e3 TABLE/PROCEDURE 14819 HOTCOPY6 0 0 26 rows selected. --//仅仅生成了12个HOTCOPY，一定程度减少了争用。 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 3719 3719 markhot_p=50 50 10784 539216 p=50 50 40326 2016294 --//很明显DBMS_SHARED_POOL.MARKHOT标识热对象，效果明显，至少快了接近4倍。 SCOTT@book> select mod(sid,12)+1,count(*) from job_times where method='markhot_p=50' group by mod(sid,12); MOD(SID,12)+1 COUNT(*) ------------- ---------- 2 3 12 3 7 4 3 3 6 7 5 4 9 4 4 4 8 2 11 10 1 4 10 2 12 rows selected. --//可以发现MOD(SID,12)+1= 11的记录很多。 SCOTT@book> select sum(xx) from (select mod(sid,12)+1,count(*) xx from job_times where method='markhot_p=50' group by mod(sid,12)); SUM(XX) ---------- 50 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 52000121 0 SYS DBMS_APPLICATION_INFO 3055789070 261372a7214e6f59d503b0b8b623a40e BODY 107534 HOTCOPY11 10000021 0 SYS DBMS_APPLICATION_INFO 201270650 9b56e047795d868ea4ea9ec50bff257a BODY 75130 HOTCOPY6 7000014 0 SYS DBMS_APPLICATION_INFO 3239075841 be89c6bcab68bb6d90a880e1c1106001 BODY 24577 HOTCOPY9 4000009 0 SYS DBMS_APPLICATION_INFO 1796761353 ac6bcb94cda09c0bae60f1ff6b186709 BODY 26377 HOTCOPY1 4000008 0 SYS DBMS_APPLICATION_INFO 1860572659 e23f4ff2543c4b31a46fd97e6ee615f3 BODY 5619 HOTCOPY5 4000008 0 SYS DBMS_APPLICATION_INFO 2622902834 57d0d16d19e33101289e28b79c565232 BODY 21042 HOTCOPY4 4000008 0 SYS DBMS_APPLICATION_INFO 2971963479 238e8f68f06d46070b6d9e6eb1249057 BODY 36951 HOTCOPY7 4000008 0 SYS DBMS_APPLICATION_INFO 983567324 1d27d5e0cd5bd1677e144e9b3aa00bdc BODY 3036 HOTCOPY2 3000006 0 SYS DBMS_APPLICATION_INFO 2279340822 a720613c72be1066682c35b487dbfb16 BODY 129814 HOTCOPY3 3000006 0 SYS DBMS_APPLICATION_INFO 1797491121 bf0c8ea515dc6500abad3ebd6b2389b1 BODY 100785 HOTCOPY12 3000006 0 SYS DBMS_APPLICATION_INFO 1266390504 7e76255c8a06bcbdec2d02864b7b95e8 BODY 103912 HOTCOPY8 2000004 0 SYS DBMS_APPLICATION_INFO 459535560 d0f1e4979ba988bc5c341a6c1b63f4c8 BODY 128200 HOTCOPY10 2000004 0 SYS DBMS_APPLICATION_INFO 173685219 64babbc5d18572a06016bd320a5a39e3 TABLE/PROCEDURE 14819 HOTCOPY6 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 185977409 b69976529943f2f4379406ae0b15ca41 TABLE/PROCEDURE 117313 HOTCOPY2 0 0 SYS DBMS_APPLICATION_INFO 1936453895 81a03530b8ba88d28e796054736bf107 TABLE/PROCEDURE 127239 HOTCOPY4 0 0 SYS DBMS_APPLICATION_INFO 1039286727 ad12b04477bef525ed10c6963df241c7 TABLE/PROCEDURE 16839 HOTCOPY8 0 0 SYS DBMS_APPLICATION_INFO 3662569346 b962b50e871c43493b9f21f0da4e5f82 TABLE/PROCEDURE 24450 HOTCOPY1 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. --//注意看HOT_FLAG=HOTCOPY11的执行次数很多,10*10e6=10000000 .如果能分散开来执行效率更高。 --//测试单个会话执行的情况。 $ seq 1 | xargs -I{} -P 50 sqlplus -s -l scott/book @m1.txt 1e6 markhot_p=01 {} >/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) -------------------- ---------- ---------------------- ------------- markhot_p=01 1 3639 3639 p=01 1 3719 3719 markhot_p=50 50 10784 539216 p=50 50 40326 2016294 4.测试3: --//修改隐含参数_kgl_hot_object_copies测试: SYS@book> alter system set "_kgl_hot_object_copies"=101 scope=spfile; System altered. --//重启数据库略。 SYS@book> show parameter _kgl_h NAME TYPE VALUE ---------------------- ------- ------ _kgl_hot_object_copies integer 101 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 2 0 SYS DBMS_APPLICATION_INFO 2876716960 5a81de0b29b19e757e67708dab7737a0 TABLE/PROCEDURE 79776 HOT 0 0 SCOTT@book> @ wait P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATUS STATE WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS ---------------- ---------------- ----- ---------- ---- -- ---- ------- ---- ------------------ -------- ----------------- --------------- --------------- ----------- 000000006010D860 0000000000000150 00 1611716704 336 0 2 27 528 latch: shared pool ACTIVE WAITED KNOWN TIME 10616 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 3 9 534 latch: shared pool ACTIVE WAITED KNOWN TIME 10677 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 17 1 510 latch: shared pool ACTIVE WAITING 5925 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 19 1 492 latch: shared pool ACTIVE WAITED KNOWN TIME 10536 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 30 3 551 latch: shared pool ACTIVE WAITING 5971 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 32 1 528 latch: shared pool ACTIVE WAITING 5999 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 33 7 517 latch: shared pool ACTIVE WAITED KNOWN TIME 26012 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 44 3 567 latch: shared pool ACTIVE WAITING 6011 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 45 15 542 latch: shared pool ACTIVE WAITED KNOWN TIME 10447 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 46 7 521 latch: shared pool ACTIVE WAITED KNOWN TIME 10564 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 58 3 517 latch: shared pool ACTIVE WAITING 6069 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 59 11 532 latch: shared pool ACTIVE WAITED KNOWN TIME 10464 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 72 1 530 latch: shared pool ACTIVE WAITING 6097 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 73 7 509 latch: shared pool ACTIVE WAITED KNOWN TIME 10578 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 86 1 550 latch: shared pool ACTIVE WAITED KNOWN TIME 10603 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 87 7 519 latch: shared pool ACTIVE WAITING 6145 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 101 1 532 latch: shared pool ACTIVE WAITED KNOWN TIME 33974 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 102 7 535 latch: shared pool ACTIVE WAITING 6162 0 Concurrency 0000000085F80F28 000000000000000F 00 2247626536 15 0 114 1 526 latch free ACTIVE WAITED KNOWN TIME 21990 0 Other 000000006010D860 0000000000000150 00 1611716704 336 0 115 7 516 latch: shared pool ACTIVE WAITED KNOWN TIME 25834 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 128 1 550 latch: shared pool ACTIVE WAITED KNOWN TIME 10665 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 129 7 511 latch: shared pool ACTIVE WAITING 6210 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 142 1 549 latch: shared pool ACTIVE WAITING 6258 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 144 1 548 latch: shared pool ACTIVE WAITING 6246 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 156 1 524 latch: shared pool ACTIVE WAITED KNOWN TIME 10414 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 157 7 518 latch: shared pool ACTIVE WAITING 6269 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 170 1 526 latch: shared pool ACTIVE WAITED KNOWN TIME 10550 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 171 3 558 latch: shared pool ACTIVE WAITED KNOWN TIME 10480 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 184 1 512 latch: shared pool ACTIVE WAITED KNOWN TIME 13014 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 185 3 529 latch: shared pool ACTIVE WAITING 6347 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 198 1 512 latch: shared pool ACTIVE WAITED KNOWN TIME 5310 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 199 7 524 latch: shared pool ACTIVE WAITED KNOWN TIME 10589 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 212 11 537 latch: shared pool ACTIVE WAITED KNOWN TIME 32824 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 213 9 513 latch: shared pool ACTIVE WAITED KNOWN TIME 26988 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 226 3 603 latch: shared pool ACTIVE WAITING 6413 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 227 7 494 latch: shared pool ACTIVE WAITING 6438 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 241 1 528 latch: shared pool ACTIVE WAITED SHORT TIME 983 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 242 9 544 latch: shared pool ACTIVE WAITED KNOWN TIME 10494 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 255 1 514 latch: shared pool ACTIVE WAITING 6467 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 256 11 511 latch: shared pool ACTIVE WAITING 6500 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 267 1 556 latch: shared pool ACTIVE WAITING 6505 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 268 7 514 latch: shared pool ACTIVE WAITING 6491 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 281 1 514 latch: shared pool ACTIVE WAITING 6513 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 282 9 515 latch: shared pool ACTIVE WAITED KNOWN TIME 25900 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 296 17 533 latch: shared pool ACTIVE WAITED KNOWN TIME 10430 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 297 7 524 latch: shared pool ACTIVE WAITED KNOWN TIME 32867 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 310 1 544 latch: shared pool ACTIVE WAITING 6571 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 312 1 504 latch: shared pool ACTIVE WAITING 6603 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 325 1 529 latch: shared pool ACTIVE WAITED KNOWN TIME 26975 0 Concurrency 000000006010D860 0000000000000150 00 1611716704 336 0 327 1 608 latch: shared pool ACTIVE WAITING 6616 0 Concurrency 50 rows selected. --//等待事件主要是latch: shared pool，奇怪...,,,, --//仔细看m1.txt脚本，我才发现脚本里面有问题： for i in 1 .. &&1 loop execute immediate 'begin /*+ &&3 */ dbms_application_info.set_client_info(''mutex'');end;'; end loop; --//这样执行瞬间存在大量硬解析，如果取消 --//host sleep $(echo &&3/50 | bc -l ) --//应该更快。 SYS@book> @ bb.txt OWNER NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS ------ --------------------- ---------- -------------------------------- ---------------- ----------- ---------- ---------- ------------- SYS DBMS_APPLICATION_INFO 2681279054 d13d643e573fc1f3b2df06549fd1124e BODY 70222 HOTCOPY25 1474822 0 SYS DBMS_APPLICATION_INFO 1171053983 5981f2e629e63e86b04f473845ccdd9f BODY 56735 HOTCOPY59 765725 0 SYS DBMS_APPLICATION_INFO 956750426 e12c8f258f684d20827b89293906da5a BODY 55898 HOTCOPY15 765553 0 SYS DBMS_APPLICATION_INFO 2047423968 bc757283eb7c51fbeb16d9f77a0935e0 BODY 79328 HOTCOPY54 764304 0 SYS DBMS_APPLICATION_INFO 1057590528 93b57643804797d8d285aa603f098d00 BODY 101632 HOTCOPY74 761910 0 SYS DBMS_APPLICATION_INFO 4000211599 cfda22ab320bdb3a459e0fa9ee6e628f BODY 25231 HOTCOPY73 760609 0 SYS DBMS_APPLICATION_INFO 2789623218 4872636bff4b9f398191fea4a64645b2 BODY 17842 HOTCOPY60 760040 0 SYS DBMS_APPLICATION_INFO 3886141697 e95b0ffec349d14e8b432030e7a1d101 BODY 119041 HOTCOPY81 759743 0 SYS DBMS_APPLICATION_INFO 3055789070 261372a7214e6f59d503b0b8b623a40e BODY 107534 HOTCOPY11 759618 0 SYS DBMS_APPLICATION_INFO 2577806245 20500639c4d62c777e43a77299a633a5 BODY 13221 HOTCOPY14 758957 0 SYS DBMS_APPLICATION_INFO 3025563970 da932afa388ce40b1bcedb70b4567142 BODY 28994 HOTCOPY96 756318 0 SYS DBMS_APPLICATION_INFO 1796761353 ac6bcb94cda09c0bae60f1ff6b186709 BODY 26377 HOTCOPY1 755089 0 SYS DBMS_APPLICATION_INFO 3479636112 6d2ee5841f4d19f492ac8d0bcf670890 BODY 67728 HOTCOPY67 754804 0 SYS DBMS_APPLICATION_INFO 1266390504 7e76255c8a06bcbdec2d02864b7b95e8 BODY 103912 HOTCOPY8 754590 0 SYS DBMS_APPLICATION_INFO 3450641223 e5e8f6e4ddfe175680368d32cdac9b47 BODY 39751 HOTCOPY84 754458 0 SYS DBMS_APPLICATION_INFO 2058243173 7a003ca4566130e7db147ca47aae4c65 BODY 19557 HOTCOPY46 753000 0 SYS DBMS_APPLICATION_INFO 3597841197 4f51b45a21989f8fb2cd70b3d672b32d BODY 45869 HOTCOPY95 752966 0 SYS DBMS_APPLICATION_INFO 1870507672 c577e79221323bc61dd18a126f7dae98 BODY 110232 HOTCOPY28 752241 0 SYS DBMS_APPLICATION_INFO 3119551434 5998c58d666396d20f27d6cfb9f093ca BODY 37834 HOTCOPY44 752161 0 SYS DBMS_APPLICATION_INFO 1775451249 2261ad1ac5bbd2f5ed02d97469d33c71 BODY 81009 HOTCOPY33 752006 0 SYS DBMS_APPLICATION_INFO 3131395859 20ec4100da637155353da320baa54f13 BODY 85779 HOTCOPY56 751982 0 SYS DBMS_APPLICATION_INFO 152982759 711b9e1b8b1a3c6903769e2c091e54e7 BODY 21735 HOTCOPY29 751147 0 SYS DBMS_APPLICATION_INFO 3542456027 ff1ba721b74550ffe293d0aad32596db BODY 104155 HOTCOPY34 750869 0 SYS DBMS_APPLICATION_INFO 1574799281 b321f7b78b32c730f4941ec15ddd87b1 BODY 100273 HOTCOPY31 750738 0 SYS DBMS_APPLICATION_INFO 3992919509 10774c8f3a2fda03dbd1ea92edff1dd5 BODY 73173 HOTCOPY71 750566 0 SYS DBMS_APPLICATION_INFO 1355045118 f801eab1590c91c7aa43fbd150c458fe BODY 22782 HOTCOPY23 749879 0 SYS DBMS_APPLICATION_INFO 4206329647 2d4cfd8174d08ca663a30ee6fab77f2f BODY 98095 HOTCOPY45 749750 0 SYS DBMS_APPLICATION_INFO 1798368844 faa164e0fad51b05dcf7140b6b30ee4c BODY 61004 HOTCOPY26 749672 0 SYS DBMS_APPLICATION_INFO 1459725409 f8f08b24a6ae14bded3631c25701a461 BODY 107617 HOTCOPY40 749398 0 SYS DBMS_APPLICATION_INFO 3419805697 ccacfb1907ca21ba75f87994cbd61801 BODY 6145 HOTCOPY98 749332 0 SYS DBMS_APPLICATION_INFO 459535560 d0f1e4979ba988bc5c341a6c1b63f4c8 BODY 128200 HOTCOPY10 748611 0 SYS DBMS_APPLICATION_INFO 2839470252 756a68590befae3e54cd8d2da93ee0ac BODY 57516 HOTCOPY57 748575 0 SYS DBMS_APPLICATION_INFO 3262181743 d7cbe20bb4d295d18cb56db9c270f16f BODY 61807 HOTCOPY20 748057 0 SYS DBMS_APPLICATION_INFO 2203002057 990c7d2c35947def1765cb83834f24c9 BODY 74953 HOTCOPY80 747411 0 SYS DBMS_APPLICATION_INFO 2622902834 57d0d16d19e33101289e28b79c565232 BODY 21042 HOTCOPY4 746386 0 SYS DBMS_APPLICATION_INFO 3419003069 30976d2fa0461331ec1c4db8cbc9d8bd BODY 121021 HOTCOPY55 745626 0 SYS DBMS_APPLICATION_INFO 1880596043 eef898cc74a02d6ed9a4acfa70179e4b BODY 106059 HOTCOPY88 745583 0 SYS DBMS_APPLICATION_INFO 1797491121 bf0c8ea515dc6500abad3ebd6b2389b1 BODY 100785 HOTCOPY12 745566 0 SYS DBMS_APPLICATION_INFO 3916307079 39f04735c45a139d7db39e19e96e1a87 BODY 6791 HOTCOPY18 745448 0 SYS DBMS_APPLICATION_INFO 4187156762 4d1a25f10fde71d9f1dc0b20f992f11a BODY 61722 HOTCOPY87 745280 0 SYS DBMS_APPLICATION_INFO 427607252 12a5d1311a5e117a84ddda56197cc4d4 BODY 50388 HOTCOPY47 744703 0 SYS DBMS_APPLICATION_INFO 983567324 1d27d5e0cd5bd1677e144e9b3aa00bdc BODY 3036 HOTCOPY2 743049 0 SYS DBMS_APPLICATION_INFO 2279340822 a720613c72be1066682c35b487dbfb16 BODY 129814 HOTCOPY3 742999 0 SYS DBMS_APPLICATION_INFO 1839302851 0f9412647fe04067f68ed3fc6da188c3 BODY 100547 HOTCOPY70 742830 0 SYS DBMS_APPLICATION_INFO 1790690663 db2f53caf69479d56c0187a06abbc567 BODY 116071 HOTCOPY85 742827 0 SYS DBMS_APPLICATION_INFO 46002718 af2958ccffc586baa09d953802bdf21e BODY 127518 HOTCOPY66 742187 0 SYS DBMS_APPLICATION_INFO 3749297010 f11f803f433416546a83b1addf79bb72 BODY 113522 HOTCOPY41 741310 0 SYS DBMS_APPLICATION_INFO 1409187585 d0ebe80ee9c1631f25768e6053fe7f01 BODY 32513 HOTCOPY99 739120 0 SYS DBMS_APPLICATION_INFO 3789229549 c5f21d016beaeaea523f8c04e1db0ded BODY 69101 HOTCOPY42 733973 0 SYS DBMS_APPLICATION_INFO 539807965 eab253aef59cd250bd8b8a13202cd0dd BODY 53469 HOT 2 0 SYS DBMS_APPLICATION_INFO 3305017861 66c8a52e7ff3e23184fbe474c4fe9205 BODY 37381 HOTCOPY89 2 0 SYS DBMS_APPLICATION_INFO 3713925158 24f1f22ef2682f19434fc0f1dd5e0026 TABLE/PROCEDURE 38 HOTCOPY20 0 0 SYS DBMS_APPLICATION_INFO 3214411173 41612bac40e915b478cb047dbf9805a5 TABLE/PROCEDURE 1445 HOTCOPY89 0 0 SYS DBMS_APPLICATION_INFO 2993293931 f13d21392696cd23348e5f4db26a0a6b TABLE/PROCEDURE 2667 HOTCOPY29 0 0 SYS DBMS_APPLICATION_INFO 1927940886 a2af81d956a434dff5517b5972ea0b16 TABLE/PROCEDURE 2838 HOTCOPY15 0 0 SYS DBMS_APPLICATION_INFO 1039286727 ad12b04477bef525ed10c6963df241c7 TABLE/PROCEDURE 16839 HOTCOPY8 0 0 SYS DBMS_APPLICATION_INFO 1659519688 a860f91260c44ad88c0fc5c762ea42c8 TABLE/PROCEDURE 17096 HOTCOPY54 0 0 SYS DBMS_APPLICATION_INFO 2884388945 4744af1050b55950998748faabec4851 TABLE/PROCEDURE 18513 HOTCOPY88 0 0 SYS DBMS_APPLICATION_INFO 4177153037 854bc9b56b44395c71cb048df8fa4c0d TABLE/PROCEDURE 19469 HOTCOPY41 0 0 SYS DBMS_APPLICATION_INFO 2732871387 a5bb096904e93c6377e6e114a2e44edb TABLE/PROCEDURE 20187 HOTCOPY33 0 0 SYS DBMS_APPLICATION_INFO 4177026039 bdedede657ba07505eafa750f8f85bf7 TABLE/PROCEDURE 23543 HOTCOPY95 0 0 SYS DBMS_APPLICATION_INFO 3662569346 b962b50e871c43493b9f21f0da4e5f82 TABLE/PROCEDURE 24450 HOTCOPY1 0 0 SYS DBMS_APPLICATION_INFO 4188696621 117fb9d1d3e842f2407afd64f9aa702d TABLE/PROCEDURE 28717 HOTCOPY46 0 0 SYS DBMS_APPLICATION_INFO 3756424118 f86536d80be866c08c67662ddfe67bb6 TABLE/PROCEDURE 31670 HOTCOPY23 0 0 SYS DBMS_APPLICATION_INFO 3884875937 e04f464b5ea34feb711fbb68e78e80a1 TABLE/PROCEDURE 32929 HOTCOPY57 0 0 SYS DBMS_APPLICATION_INFO 2661318844 6936e2133f05a9420a038aea9ea080bc TABLE/PROCEDURE 32956 HOTCOPY99 0 0 SYS DBMS_APPLICATION_INFO 3159659945 38c1eafb469221566f95f915bc5495a9 TABLE/PROCEDURE 38313 HOTCOPY28 0 0 SYS DBMS_APPLICATION_INFO 2305334961 83ceadfd8387f61a9f51769d89689eb1 TABLE/PROCEDURE 40625 HOTCOPY56 0 0 SYS DBMS_APPLICATION_INFO 2299969214 6bc021d8a21f30d1e634c2718916bebe TABLE/PROCEDURE 48830 HOTCOPY73 0 0 SYS DBMS_APPLICATION_INFO 529975650 6cd45dded4aad10cd3271fbf1f96c962 TABLE/PROCEDURE 51554 HOTCOPY42 0 0 SYS DBMS_APPLICATION_INFO 2720844322 a16af3ff8976cac50ec24616a22cca22 TABLE/PROCEDURE 51746 HOTCOPY14 0 0 SYS DBMS_APPLICATION_INFO 3014844972 a349e4d532eabfe5eb16882bb3b2e22c TABLE/PROCEDURE 57900 HOTCOPY59 0 0 SYS DBMS_APPLICATION_INFO 3001482792 f43848a75e3b4d4ec289bb1fb2e6fe28 TABLE/PROCEDURE 65064 HOTCOPY87 0 0 SYS DBMS_APPLICATION_INFO 2891645643 ebd72d40d0d53a9bc1af2332ac5b02cb TABLE/PROCEDURE 66251 HOTCOPY12 0 0 SYS DBMS_APPLICATION_INFO 97190967 de6346277f705751f9222fa405cb0437 TABLE/PROCEDURE 66615 HOTCOPY44 0 0 SYS DBMS_APPLICATION_INFO 4120446936 2e29e2f020979f681006ff17f59907d8 TABLE/PROCEDURE 67544 HOTCOPY10 0 0 SYS DBMS_APPLICATION_INFO 1807688654 8bba5fcf691a9718ea883c0b6bbf23ce TABLE/PROCEDURE 74702 HOTCOPY71 0 0 SYS DBMS_APPLICATION_INFO 2744987372 075f51e435dd4e362d933cefa39d2eec TABLE/PROCEDURE 77548 HOTCOPY67 0 0 SYS DBMS_APPLICATION_INFO 2876716960 5a81de0b29b19e757e67708dab7737a0 TABLE/PROCEDURE 79776 HOT 0 0 SYS DBMS_APPLICATION_INFO 28917238 d1bdc2aaf1d0633e5f5d4a9901b93df6 TABLE/PROCEDURE 81398 HOTCOPY80 0 0 SYS DBMS_APPLICATION_INFO 2133016821 6d51a7f905c39c605dcac4cb7f2340f5 TABLE/PROCEDURE 82165 HOTCOPY70 0 0 SYS DBMS_APPLICATION_INFO 2576041395 fa36a728837d7c8deaced5a6998b45b3 TABLE/PROCEDURE 83379 HOTCOPY11 0 0 SYS DBMS_APPLICATION_INFO 2154907356 a8322c741c37afb059964ec4807146dc TABLE/PROCEDURE 83676 HOTCOPY47 0 0 SYS DBMS_APPLICATION_INFO 1405572375 db178bf2a8f02ef06f55762253c75517 TABLE/PROCEDURE 87319 HOTCOPY98 0 0 SYS DBMS_APPLICATION_INFO 3270076683 1271ffc686209f626716f041c2e9690b TABLE/PROCEDURE 92427 HOTCOPY66 0 0 SYS DBMS_APPLICATION_INFO 236155228 2c7d26eccf3f5f971f7df8850e13715c TABLE/PROCEDURE 94556 HOTCOPY85 0 0 SYS DBMS_APPLICATION_INFO 195392034 903990cbb0cbfc7ecc72d4ba0ba57222 TABLE/PROCEDURE 94754 HOTCOPY84 0 0 SYS DBMS_APPLICATION_INFO 2246803978 08e70c334518fb94bfc82d9085eb820a TABLE/PROCEDURE 98826 HOTCOPY81 0 0 SYS DBMS_APPLICATION_INFO 3843523622 468676c93cb71b877cde8742e5178426 TABLE/PROCEDURE 99366 HOTCOPY34 0 0 SYS DBMS_APPLICATION_INFO 3626207292 28341133c7b5c250e6e2a5aad823883c TABLE/PROCEDURE 100412 HOTCOPY60 0 0 SYS DBMS_APPLICATION_INFO 1070180617 176536d57d146dd6e5f9aa363fc9a909 TABLE/PROCEDURE 108809 HOTCOPY31 0 0 SYS DBMS_APPLICATION_INFO 654029597 b679aef7823b7d0303ae6f4b26fbb31d TABLE/PROCEDURE 111389 HOTCOPY55 0 0 SYS DBMS_APPLICATION_INFO 3414669100 597cabbf04c7b3369849b931cb87b72c TABLE/PROCEDURE 112428 HOTCOPY45 0 0 SYS DBMS_APPLICATION_INFO 2180495402 08c13fcfb0c3e60988a2f21c81f7b82a TABLE/PROCEDURE 112682 HOTCOPY26 0 0 SYS DBMS_APPLICATION_INFO 3102849913 06f93e0e6230a58926620bd0b8f1bb79 TABLE/PROCEDURE 113529 HOTCOPY18 0 0 SYS DBMS_APPLICATION_INFO 2719727703 8fdcfe11b4039ebacd4b4958a21bc057 TABLE/PROCEDURE 114775 HOTCOPY96 0 0 SYS DBMS_APPLICATION_INFO 185977409 b69976529943f2f4379406ae0b15ca41 TABLE/PROCEDURE 117313 HOTCOPY2 0 0 SYS DBMS_APPLICATION_INFO 3398427115 4e988c7dbebfffd139693d0cca8fe1eb TABLE/PROCEDURE 123371 HOTCOPY40 0 0 SYS DBMS_APPLICATION_INFO 1508765006 c23a9e23a1ce46929e138fec59eded4e TABLE/PROCEDURE 126286 HOTCOPY74 0 0 SYS DBMS_APPLICATION_INFO 1936453895 81a03530b8ba88d28e796054736bf107 TABLE/PROCEDURE 127239 HOTCOPY4 0 0 SYS DBMS_APPLICATION_INFO 798486847 730a84e6f3d7be0e0582099c2f97f13f TABLE/PROCEDURE 127295 HOTCOPY25 0 0 SYS DBMS_APPLICATION_INFO 3652451954 26cf0f1c5fd83145cc78dfd7d9b3fe72 TABLE/PROCEDURE 130674 HOTCOPY3 0 0 102 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) -------------------- ---------- ---------------------- ------------- p=01 1 3719 3719 markhot_p=01 1 3639 3639 markhot_p=50 50 10784 539216 modify_markhot_p=50 50 12062 603086 p=50 50 40326 2016294 --//这时由于执行开始时集中出现大量硬解析的缘故，重复测试,取消注解 --// host sleep $(echo &&3/50 | bc -l ) $ seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/book @m1.txt 1e6 modifyx_markhot_p=50 {} >/dev/null --//这样看到的等待事件主要是latch free，少量library cache: mutex X. 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 3719 3719 markhot_p=01 1 3639 3639 markhot_p=50 50 10784 539216 modifyx_markhot_p=50 50 10930 546486 modify_markhot_p=50 50 12062 603086 p=50 50 40326 2016294 6 rows selected. --//可以看出现在更一点快，从另外角度讲修改隐含参数_kgl_hot_object_copies意义不大。 --//顺便验证FULL_HASH_VALUE的计算。 OWNER NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS ------ --------------------- ---------- -------------------------------- ---------------- ----------- ---------- ---------- ------------- SYS DBMS_APPLICATION_INFO 539807965 eab253aef59cd250bd8b8a13202cd0dd BODY 53469 51000115 0 SYS DBMS_APPLICATION_INFO 2876716960 5a81de0b29b19e757e67708dab7737a0 TABLE/PROCEDURE 79776 0 0 ... SYS DBMS_APPLICATION_INFO 1171053983 5981f2e629e63e86b04f473845ccdd9f BODY 56735 HOTCOPY59 765725 0 SYS DBMS_APPLICATION_INFO 3014844972 a349e4d532eabfe5eb16882bb3b2e22c TABLE/PROCEDURE 57900 HOTCOPY59 0 0 --//我以前提到如何确定计算字符串， package_name.owner\01\0\0\0 或者 package_name.owner\02\0\0\0 --//后面的\01,\02 对于namespace。 $ echo -e -n 'DBMS_APPLICATION_INFO.SYS\01\0\0\0' | md5sum | sed 's/ -//' | xxd -r -p | od -t x4 | sed -n -e 's/^0000000 //' -e 's/ //gp' 5a81de0b29b19e757e67708dab7737a0 $ echo -e -n 'DBMS_APPLICATION_INFO.SYS\02\0\0\0' | md5sum | sed 's/ -//' | xxd -r -p | od -t x4 | sed -n -e 's/^0000000 //' -e 's/ //gp' eab253aef59cd250bd8b8a13202cd0dd --//OK!!完全一致。 $ echo -e -n 'DBMS_APPLICATION_INFO.SYS\01\0\0\0.59' | md5sum | sed 's/ -//' | xxd -r -p | od -t x4 | sed -n -e 's/^0000000 //' -e 's/ //gp' 6cc4aeca5237fc032c4366230793ee61 --//不对!!另外写blog分析如何计算。噢猜测一下居然猜对了。 $ echo -e -n 'DBMS_APPLICATION_INFO.SYS.59\01\0\0\0' | md5sum | sed 's/ -//' | xxd -r -p | od -t x4 | sed -n -e 's/^0000000 //' -e 's/ //gp' a349e4d532eabfe5eb16882bb3b2e22c $ echo -e -n 'DBMS_APPLICATION_INFO.SYS.59\02\0\0\0' | md5sum | sed 's/ -//' | xxd -r -p | od -t x4 | sed -n -e 's/^0000000 //' -e 's/ //gp' 5981f2e629e63e86b04f473845ccdd9f --//格式是 package_name.owner.HOTCOPYNN的数字NN\0X\0\0\0。 X->对于namespace。 OWNER NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS ------ --------------------- ---------- -------------------------------- ---------------- ----------- ---------- ---------- ------------- SYS DBMS_APPLICATION_INFO 3652451954 26cf0f1c5fd83145cc78dfd7d9b3fe72 TABLE/PROCEDURE 130674 HOTCOPY3 0 0 $ echo -e -n 'DBMS_APPLICATION_INFO.SYS.3\01\0\0\0' | md5sum | sed 's/ -//' | xxd -r -p | od -t x4 | sed -n -e 's/^0000000 //' -e 's/ //gp' 26cf0f1c5fd83145cc78dfd7d9b3fe72 --//完全正确。 5.总结: --//通过测试可以确定使用DBMS_SHARED_POOL.MARKHOT标识热对象,能获得好的性能。 --//为什么使用DBMS_SHARED_POOL.MARKHOT标识热sql语句不行呢？ 1.在没有使用DBMS_SHARED_POOL.MARKHOT时,大量频繁多个会话同时执行execute immediate 'begin /*+ &&3 */ dbms_application_info.set_client_info(''mutex'');end;';,出现library cache: mutex X。而sql语句大量频繁多个会话执行sql语句，仅仅出现cursor: pin S。因为open_cursors一般不会设置为0,也就是这些sql语句是软软解析。 2.在使用DBMS_SHARED_POOL.MARKHOT时,大量频繁多个会话同时执行execute immediate 'begin /*+ &&3 */ dbms_application_info.set_client_info(''mutex'');end;';,访问对象时被打散，反而获得良好性能。而sql语句大量频繁多个会话执行sql语句，在使用DBMS_SHARED_POOL.MARKHOT时，我猜测可能sql语句在共享池必须出现父子游标有关。反而出现library cache: mutex X，我的理解在父游标上出现mutex，导致性能下降，从测试看不建议使用DBMS_SHARED_POOL.MARKHOT标识热sql语句。 --//一些细节我自己也无法解析，表达清楚，那位给出解析。感觉不能使用我自己建立的wait.sql脚本，因为有时候显示的最后的等待事件，应该使用 --//tanel poder的脚本分析，有时间学习看看。 3.还有1个问题就是我建立的m1.txt脚本与真实的生产环境不符。里面执行的是： begin /*+ &&3 */ dbms_application_info.set_client_info(''mutex'') --// &&3 存在 50个值，而且单个会话执行sql语句相同,在这不存在争用，明天修改如下 begin dbms_application_info.set_client_info(''mutex'') --//这样比较符合实际的情况。