[20200213]使用DBMS_SHARED_POOL.MARKHOT標識熱物件2.txt

lfree發表於2020-02-13
物件

[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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章