[20200213]使用DBMS_SHARED_POOL.MARKHOT標識熱物件2.txt
[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標識熱物件，減少爭用提高效能。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2675370/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20200212]使用DBMS_SHARED_POOL.MARKHOT標識熱物件.txt物件
- [20200217]使用snapper探究DBMS_SHARED_POOL.MARKHOT標識熱物件的等待事件.txtAPP物件事件
- [20200213]使用DBMS_SHARED_POOL.MARKHOT的總結.txt
- [20200211]使用DBMS_SHARED_POOL.MARKHOT與sql語句2.txtSQL
- [20200212]使用DBMS_SHARED_POOL.MARKHOT與sql的計算2.txtSQL
- [20200213]函式nullif使用.txt函式Null
- 為什麼使用指標比使用物件本身更好？指標物件
- HTML 標籤(圖片對映、熱點)的使用HTML
- [20180413]熱備模式相關問題2.txt模式
- 找熱點物件的sql:物件SQL
- C++中為什麼使用指標比使用物件本身更好？C++指標物件
- 找出熱點塊所屬的使用者，物件名，型別物件型別
- 交通標識
- 第十一章：使用智慧指標管理物件資源指標物件
- 非標準物件介面物件
- 使用 XPath 在 Rational Functional Tester 中動態識別物件Function物件
- 使用selenium定位獲取標籤物件並提取資料物件
- 初識指標指標
- 初識物件導向物件
- 認識類和物件物件
- 20180601]函式與標量子查詢2.txt函式
- 如何使用機器學習進行影像識別 | 資料標註機器學習
- 使用vmstat標識linux系統的效能瓶頸Linux
- 熱修復預備知識
- C++中為什麼要用指標，而不直接使用物件？C++指標物件
- 重新認識javascript物件（一）——物件及其屬性JavaScript物件
- 標識介面的作用
- 初識BOM及其部分物件物件
- PHP物件基礎知識PHP物件
- 初識Java類和物件Java物件
- Javascript物件的基本知識JavaScript物件
- 初識文件物件模型（DOM）物件模型
- 物件導向小知識物件
- [20140506]使用ethtool標識網路卡.txt
- 使用 tpl 標籤和 for 讀取物件屬性值中的陣列物件陣列
- 物件儲存服務中物件業務的非標介面物件
- 【C++系列】指標物件和物件指標的區別C++指標物件
- [20240930]關於共享池-表物件在庫快取探究2.txt物件快取