[20200212]使用DBMS_SHARED_POOL.MARKHOT標識熱物件.txt
[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'')
--//這樣比較符合實際的情況。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2675369/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20200213]使用DBMS_SHARED_POOL.MARKHOT標識熱物件2.txt物件
- [20200217]使用snapper探究DBMS_SHARED_POOL.MARKHOT標識熱物件的等待事件.txtAPP物件事件
- [20200212]使用DBMS_SHARED_POOL.MARKHOT與sql語句3.txtSQL
- [20200212]使用DBMS_SHARED_POOL.MARKHOT與sql的計算2.txtSQL
- [20140506]使用ethtool標識網路卡.txt
- [20200213]使用DBMS_SHARED_POOL.MARKHOT的總結.txt
- [20200126]使用DBMS_SHARED_POOL.MARKHOT與sql語句.txtSQL
- [20200211]使用DBMS_SHARED_POOL.MARKHOT與sql語句2.txtSQL
- [20201117]使用DBMS_SHARED_POOL.MARKHOT與sql語句5.txtSQL
- [20201117]使用DBMS_SHARED_POOL.MARKHOT與sql語句6.txtSQL
- [20200211]使用DBMS_SHARED_POOL.MARKHOT與sql_id的計算.txtSQL
- 為什麼使用指標比使用物件本身更好？指標物件
- HTML 標籤(圖片對映、熱點)的使用HTML
- 找熱點物件的sql:物件SQL
- 遊標屬性介紹.txt
- C++中為什麼使用指標比使用物件本身更好？C++指標物件
- [20180328]不要在sys建立使用者物件.txt物件
- 交通標識
- 找出熱點塊所屬的使用者，物件名，型別物件型別
- 第十一章：使用智慧指標管理物件資源指標物件
- [20150727]使用標量子查詢小問題.txt
- 非標準物件介面物件
- 使用 XPath 在 Rational Functional Tester 中動態識別物件Function物件
- 使用selenium定位獲取標籤物件並提取資料物件
- 初識指標指標
- 初識物件導向物件
- 認識類和物件物件
- 如何使用機器學習進行影像識別 | 資料標註機器學習
- 使用vmstat標識linux系統的效能瓶頸Linux
- 熱修復預備知識
- C++中為什麼要用指標，而不直接使用物件？C++指標物件
- 重新認識javascript物件（一）——物件及其屬性JavaScript物件
- 標識介面的作用
- 初識BOM及其部分物件物件
- PHP物件基礎知識PHP物件
- 初識Java類和物件Java物件
- Javascript物件的基本知識JavaScript物件
- 初識文件物件模型（DOM）物件模型