[20200126]使用DBMS_SHARED_POOL.MARKHOT與sql語句.txt

lfree發表於2020-02-11

[20200126]使用DBMS_SHARED_POOL.MARKHOT與sql語句.txt

--//以前的測試:http://blog.itpub.net/267265/viewspace-2146632/=>[20171031]markhot.txt
--//當時自己對於使用DBMS_SHARED_POOL.MARKHOT太不理解,實際上就是減少爭用,我當時的測試是實際上反而更慢。
--//設定MARKHOT後,出現library cache: mutex X. (P1=  11140)實際上我自己沒有注意看一些細節。
--//emp表有14條記錄,而查詢
SELECT sql_id,sql_text,executions,length(sql_text)
  FROM v$sqlarea
 WHERE sql_text LIKE '%SELECT ENAME FROM EMP WHERE ROWID = :B1%'
   AND sql_text NOT LIKE '%sqlarea%';
--//輸出是10條記錄,也就是生成的hotcopy還不夠多,這樣反而更慢,從executions的次數也可以看出問題。
--//重新測試看看:

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

create table job_times (sid number, sessionid number,time_ela number,method varchar2(20));

--//建立測試指令碼m2.txt:
$ cat m2.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;
m_rowid varchar2(20);
m_data varchar2(32);
begin
    m_rowid := '&3';
    for i in 1 .. &&1 loop
        select ename into m_data from emp where rowid =m_rowid ;
    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


SCOTT@book> select listagg(rowid,',') WITHIN GROUP (order  by rowid ) c100 from emp ;

C100
----------------------------------------------------------------------------------------------------
AAAVREAAEAAAACXAAA,AAAVREAAEAAAACXAAB,AAAVREAAEAAAACXAAC,AAAVREAAEAAAACXAAD,AAAVREAAEAAAACXAAE,AAAVR
EAAEAAAACXAAF,AAAVREAAEAAAACXAAG,AAAVREAAEAAAACXAAH,AAAVREAAEAAAACXAAI,AAAVREAAEAAAACXAAJ,AAAVREAAEA
AAACXAAK,AAAVREAAEAAAACXAAL,AAAVREAAEAAAACXAAM,AAAVREAAEAAAACXAAN

2.測試1:
--//不使用DBMS_SHARED_POOL.MARKHOT的情況:
a='AAAVREAAEAAAACXAAA,AAAVREAAEAAAACXAAB,AAAVREAAEAAAACXAAC,AAAVREAAEAAAACXAAD,AAAVREAAEAAAACXAAE,AAAVREAAEAAAACXAAF,AAAVREAAEAAAACXAAG,AAAVREAAEAAAACXAAH,AAAVREAAEAAAACXAAI,AAAVREAAEAAAACXAAJ,AAAVREAAEAAAACXAAK,AAAVREAAEAAAACXAAL,AAAVREAAEAAAACXAAM,AAAVREAAEAAAACXAAN'

$ echo $a | tr  ',' '\n' | xargs -I{} -P 14 sqlplus -s -l scott/book @m2.txt 1e6 NOMARKHOT {}

SCOTT@book> select * from job_times order by TIME_ELA;

       SID  SESSIONID   TIME_ELA METHOD
---------- ---------- ---------- --------------------
       325   20921041       2350 NOMARKHOT
       170   20921030       2361 NOMARKHOT
       255   20921036       2369 NOMARKHOT
       184   20921031       2401 NOMARKHOT
       281   20921040       2528 NOMARKHOT
       128   20921028       2534 NOMARKHOT
       241   20921035       2576 NOMARKHOT
       198   20921033       2603 NOMARKHOT
       297   20921038       2624 NOMARKHOT
       212   20921032       2649 NOMARKHOT
       142   20921029       2665 NOMARKHOT
       226   20921034       2739 NOMARKHOT
       267   20921037       2996 NOMARKHOT
       310   20921039       3009 NOMARKHOT
14 rows selected.
--//最快的與最慢的相差7秒之多。

3.測試2:
--//確定sql_id,查詢檢索共享池很容易獲得:
--//sql_id='2gvj95w2k0aw4',hash_value=85994372

SCOTT@book> select hash_value, full_hash_value, namespace, child_latch, property  hot_flag, executions, invalidations from v$db_object_cache where hash_value=85994372;
HASH_VALUE FULL_HASH_VALUE                  NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS
---------- -------------------------------- --------- ----------- -------- ---------- -------------
  85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA            0            53247117             0
  85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA        11140            53799285             0

--//FULL_HASH_VALUE=  6ddb0702c4c177cb27ee292f05202b84.

SCOTT@book> select distinct name from v$db_object_cache where hash_value=85994372;
NAME
----------------------------------------
SELECT ENAME FROM EMP WHERE ROWID =:B1

SYS@book> exec dbms_shared_pool.markhot( hash=>'6ddb0702c4c177cb27ee292f05202b84', namespace=>0, global=>true);
PL/SQL procedure successfully completed.

$ echo $a | tr  ',' '\n' | xargs -I{} -P 14 sqlplus -s -l scott/book @m2.txt 1e6 MARKHOT {}

SCOTT@book> select * from job_times where METHOD ='MARKHOT' order by TIME_ELA;
       SID  SESSIONID   TIME_ELA METHOD
---------- ---------- ---------- --------------------
       281   20921054       2797 MARKHOT
       255   20921050       2800 MARKHOT
       267   20921053       2814 MARKHOT
       170   20921044       2876 MARKHOT
       325   20921052       2878 MARKHOT
       184   20921046       2902 MARKHOT
       297   20921051       2954 MARKHOT
       241   20921048       2990 MARKHOT
       212   20921047       3052 MARKHOT
       226   20921049       3171 MARKHOT
       310   20921055       3268 MARKHOT
       142   20921043       3459 MARKHOT
        17   20921056       3714 MARKHOT
       198   20921045       3716 MARKHOT
14 rows selected.

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)
-------------------- ---------- ---------------------- -------------
NOMARKHOT                    14                   2600         36404
MARKHOT                      14                   3099         43391
--//可以發現標記MARKHOT反而執行時間過長。也就是反而不執行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 = 'SELECT ENAME FROM EMP WHERE ROWID =:B1 '
 order by executions desc ;
--//注意B1後面有1個空格。

OWNER  NAME                                     HASH_VALUE FULL_HASH_VALUE                  NAMESPACE  CHILD_LATCH HOT_FLAG   EXECUTIONS INVALIDATIONS
------ ---------------------------------------- ---------- -------------------------------- ---------- ----------- ---------- ---------- -------------
       SELECT ENAME FROM EMP WHERE ROWID =:B1     85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA         11140 HOT          67246853             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1     85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA             0 HOT          66572231             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1    580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA         88906 HOTCOPY11     2993565             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1    580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA             0 HOTCOPY11     2993382             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1054289511 767295bfca293b63b6f7cb623ed72e67 SQL AREA         77415 HOTCOPY4      1999784             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1054289511 767295bfca293b63b6f7cb623ed72e67 SQL AREA             0 HOTCOPY4      1999784             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   4251304214 88e9b0a03994a83e3b1dd47ffd65c116 SQL AREA        114966 HOTCOPY2      1999158             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   4251304214 88e9b0a03994a83e3b1dd47ffd65c116 SQL AREA             0 HOTCOPY2      1999114             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA             0 HOTCOPY6      1998950             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA         88684 HOTCOPY6      1998940             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA             0 HOTCOPY3      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1051678227 0cbac329d8cbd103bdf0b37e3eaf5613 SQL AREA         87571 HOTCOPY10     1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1051678227 0cbac329d8cbd103bdf0b37e3eaf5613 SQL AREA             0 HOTCOPY10     1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA         57756 HOTCOPY5      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA             0 HOTCOPY5      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA         11508 HOTCOPY9      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA             0 HOTCOPY9      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA        116044 HOTCOPY3      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA             0 HOTCOPY7      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA          7234 HOTCOPY7      1000000             0
20 rows selected.

--//可以看出我僅僅建立有10個HOTCOPY,從EXECUTIONS次數也可以看出不平衡。奇怪沒有HOT_FLAG='HOTCOPY1',遇到有幾次有的情況。
--//從EXECUTIONS可以看出出現爭用的情況。

5.繼續測試:
echo $a | tr  ',' '\n' | xargs -I{} -P 10 sqlplus -s -l scott/book @m2.txt 1e6 MARKHOT10 {}

SCOTT@book> select * from job_times where METHOD ='MARKHOT10' order by TIME_ELA;
       SID  SESSIONID   TIME_ELA METHOD
---------- ---------- ---------- --------------------
       198   20921068       2563 MARKHOT10
       281   20921070       2610 MARKHOT10
       226   20921069       2630 MARKHOT10
       142   20921071       2647 MARKHOT10
       198   20921060       2660 MARKHOT10
       226   20921064       2706 MARKHOT10
       281   20921066       2708 MARKHOT10
       142   20921059       2713 MARKHOT10
       170   20921058       2717 MARKHOT10
       184   20921061       2722 MARKHOT10
       212   20921062       2729 MARKHOT10
       241   20921063       2753 MARKHOT10
       255   20921065       2768 MARKHOT10
       267   20921067       2769 MARKHOT10
14 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)
-------------------- ---------- ---------------------- -------------
NOMARKHOT                    14                   2600         36404
MARKHOT10                    14                   2693         37695
MARKHOT                      14                   3099         43391

SELECT owner
      ,name
      ,hash_value
      ,full_hash_value
      ,namespace
      ,child_latch
      ,property hot_flag
      ,executions
      ,invalidations
  FROM v$db_object_cache
 WHERE name = 'SELECT ENAME FROM EMP WHERE ROWID =:B1 '
 order by executions desc ;
OWNER  NAME                                     HASH_VALUE FULL_HASH_VALUE                  NAMESPACE  CHILD_LATCH HOT_FLAG   EXECUTIONS INVALIDATIONS
------ ---------------------------------------- ---------- -------------------------------- ---------- ----------- ---------- ---------- -------------
       SELECT ENAME FROM EMP WHERE ROWID =:B1     85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA         11140 HOT          67246853             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1     85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA             0 HOT          66572231             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1    580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA         88906 HOTCOPY11     6993500             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1    580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA             0 HOTCOPY11     6993316             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1054289511 767295bfca293b63b6f7cb623ed72e67 SQL AREA             0 HOTCOPY4      3999779             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1054289511 767295bfca293b63b6f7cb623ed72e67 SQL AREA         77415 HOTCOPY4      3999779             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA             0 HOTCOPY6      3998950             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA         88684 HOTCOPY6      3998940             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA             0 HOTCOPY7      3000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA          7234 HOTCOPY7      3000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   4251304214 88e9b0a03994a83e3b1dd47ffd65c116 SQL AREA        114966 HOTCOPY2      2999158             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   4251304214 88e9b0a03994a83e3b1dd47ffd65c116 SQL AREA             0 HOTCOPY2      2999114             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA         57756 HOTCOPY5      2000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA        116044 HOTCOPY3      2000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA         11508 HOTCOPY9      2000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA             0 HOTCOPY5      2000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA             0 HOTCOPY9      2000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA             0 HOTCOPY3      2000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1051678227 0cbac329d8cbd103bdf0b37e3eaf5613 SQL AREA             0 HOTCOPY10     1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1051678227 0cbac329d8cbd103bdf0b37e3eaf5613 SQL AREA         87571 HOTCOPY10     1000000             0
20 rows selected.
--//還是出現分配不平均的情況, 從HOT_FLAG='HOTCOPY10'的EXECUTIONS=1000000就可以看出來。

$ echo $a | tr  ',' '\n' | xargs -I{} -P 7 sqlplus -s -l scott/book @m2.txt 1e6 MARKHOT7 {}
--//注:這樣僅僅開啟7個並行sqlplus,基本分2次執行。

SCOTT@book> select * from job_times where METHOD ='MARKHOT7' order by TIME_ELA;
       SID  SESSIONID   TIME_ELA METHOD
---------- ---------- ---------- --------------------
       170   20921073       2647 MARKHOT7
       198   20921080       2650 MARKHOT7
       226   20921085       2653 MARKHOT7
       184   20921083       2654 MARKHOT7
       212   20921081       2655 MARKHOT7
       198   20921075       2657 MARKHOT7
       241   20921082       2662 MARKHOT7
       142   20921084       2663 MARKHOT7
       212   20921078       2668 MARKHOT7
       170   20921079       2672 MARKHOT7
       241   20921077       2716 MARKHOT7
       184   20921074       2728 MARKHOT7
       142   20921072       2761 MARKHOT7
       226   20921076       2787 MARKHOT7
14 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)
-------------------- ---------- ---------------------- -------------
NOMARKHOT                    14                   2600         36404
MARKHOT7                     14                   2684         37573
MARKHOT10                    14                   2693         37695
MARKHOT                      14                   3099         43391

6.總結:
--//不知道是否可以得出DBMS_SHARED_POOL.MARKHOT標識熱的sql語句,並不能獲得良好的效能效果,還不如不用。
--//這是我節前跟別人的討論,我個人不建議使用這樣方式解決這類問題。
--//首先問開發為什麼要頻繁執行這樣的sql語句,是否可以透過註解等方式分散這樣的sql語句。
--//我在重複看測試,為什麼使用DBMS_SHARED_POOL.MARKHOT標識熱的sql語句反而更慢,我減少併發數量一樣沒有快於不使用的情況。

--//今天上班重複測試:
--//不使用DBMS_SHARED_POOL.MARKHOT標識熱的sql語句時,看到的等待事件是:
SCOTT@book> @ wait
P1RAW            P2RAW            P3RAW                    P1             P2           P3        SID    SERIAL#       SEQ# EVENT         STATUS   STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS
---------------- ---------------- ---------------- ---------- -------------- ------------ ---------- ---------- ---------- ------------- -------- ------------------- --------------- --------------- --------------------
0000000005202B84 000000000000000B 0000000900000000   85994372             11  38654705664         30         11         78 cursor: pin S ACTIVE   WAITED SHORT TIME                 2               3 Concurrency
0000000005202B84 0000008000000007 0000000300000000   85994372   549755813895  12884901888         44         11         32 cursor: pin S ACTIVE   WAITED SHORT TIME                 3               5 Concurrency
0000000005202B84 000000000000000A 0000000900000000   85994372             10  38654705664         58         11         79 cursor: pin S ACTIVE   WAITED SHORT TIME                 2               0 Concurrency
0000000005202B84 000001270000000B 0000000300000000   85994372  1267015352331  12884901888         72          9         53 cursor: pin S ACTIVE   WAITED SHORT TIME                 1               3 Concurrency
0000000005202B84 0000000000000009 0000000300000000   85994372              9  12884901888         86          9         75 cursor: pin S ACTIVE   WAITED SHORT TIME                 9               2 Concurrency
0000000005202B84 0000000000000009 0000000300000000   85994372              9  12884901888        101          7         76 cursor: pin S ACTIVE   WAITED SHORT TIME                 2               1 Concurrency
0000000005202B84 0000008000000006 0000000300000000   85994372   549755813894  12884901888        114          5         78 cursor: pin S ACTIVE   WAITED SHORT TIME                 2               7 Concurrency
0000000005202B84 0000002C0000000C 0000000900000000   85994372   188978561036  38654705664        128          5         78 cursor: pin S ACTIVE   WAITED SHORT TIME                 2               5 Concurrency
0000000005202B84 0000003A00000008 0000000900000000   85994372   249108103176  38654705664        142          5         60 cursor: pin S ACTIVE   WAITED SHORT TIME                 3               4 Concurrency
0000000005202B84 0000003A00000008 0000000300000000   85994372   249108103176  12884901888        156          5         64 cursor: pin S ACTIVE   WAITED SHORT TIME                12               4 Concurrency
0000000005202B84 000000000000000D 0000000900000000   85994372             13  38654705664        170          5         78 cursor: pin S ACTIVE   WAITED SHORT TIME                 2               1 Concurrency
0000000005202B84 0000002C00000008 0000000300000000   85994372   188978561032  12884901888        184          5         47 cursor: pin S ACTIVE   WAITED SHORT TIME                 4              15 Concurrency
0000000005202B84 000000AA00000008 0000000900000000   85994372   730144440328  38654705664        198          5         70 cursor: pin S ACTIVE   WAITED KNOWN TIME             11008               7 Concurrency
0000000005202B84 0000000000000007 0000000300000000   85994372              7  12884901888        295          9         91 cursor: pin S ACTIVE   WAITED SHORT TIME                 1               2 Concurrency
14 rows selected.

--//不使用DBMS_SHARED_POOL.MARKHOT標識熱的sql語句時,看到的等待事件是:
SYS@book> exec dbms_shared_pool.markhot( hash=>'6ddb0702c4c177cb27ee292f05202b84', namespace=>0, global=>true);
PL/SQL procedure successfully completed.

SCOTT@book> @ wait
P1RAW            P2RAW            P3RAW                    P1             P2           P3        SID    SERIAL#       SEQ# EVENT                  STATUS   STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS
---------------- ---------------- ---------------- ---------- -------------- ------------ ---------- ---------- ---------- ---------------------- -------- ------------------- --------------- --------------- --------------------
00000000A28C1C42 000000C600000000 0000000300000000 2727091266   850403524608  12884901888         30         17       1756 cursor: pin S          ACTIVE   WAITED SHORT TIME                 1               0 Concurrency
0000000099F1D815 00               0000000300000000 2582763541              0  12884901888         72         11       1452 cursor: pin S          ACTIVE   WAITED SHORT TIME                 1               0 Concurrency
00000000B4DBC54C 000000AA00000000 0000000300000000 3034301772   730144440320  12884901888         86         11        165 cursor: pin S          ACTIVE   WAITED SHORT TIME                 1               0 Concurrency
000000006010D860 0000000000000150 00               1611716704            336            0        101          9        148 latch: shared pool     ACTIVE   WAITED SHORT TIME                57               1 Concurrency
00000000A28C1C42 00               0000000300000000 2727091266              0  12884901888        114          7       1238 cursor: pin S          ACTIVE   WAITED SHORT TIME                 1               0 Concurrency
000000006010D860 0000000000000150 00               1611716704            336            0        128          7        189 latch: shared pool     ACTIVE   WAITED SHORT TIME                50               1 Concurrency
0000000099F1D815 00               0000000300000000 2582763541              0  12884901888        156          7        869 cursor: pin S          ACTIVE   WAITED SHORT TIME                 3               0 Concurrency
00000000B4DBC54C 00               0000000300000000 3034301772              0  12884901888        170          7       1941 cursor: pin S          ACTIVE   WAITED SHORT TIME                 2               0 Concurrency
0000000000002B84 0000003A00000000 000000000000003E      11140   249108103168           62        184          7        197 library cache: mutex X ACTIVE   WAITED SHORT TIME                 8               0 Concurrency
00000000A28C1C42 00               0000000300000000 2727091266              0  12884901888        198          7        950 cursor: pin S          ACTIVE   WAITED SHORT TIME                 1               0 Concurrency
0000000000002B84 0000006500000000 000000000000003E      11140   433791696896           62        241          1        204 library cache: mutex X ACTIVE   WAITED SHORT TIME                 1               1 Concurrency
0000000000002B84 00               000000000000003E      11140              0           62        295         21        171 library cache: mutex X ACTIVE   WAITED SHORT TIME                 2               1 Concurrency
12 rows selected.

SCOTT@book> @ wait
P1RAW            P2RAW            P3RAW                    P1             P2           P3        SID    SERIAL#       SEQ# EVENT                  STATUS   STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS
---------------- ---------------- ---------------- ---------- -------------- ------------ ---------- ---------- ---------- ---------------------- -------- ------------------- --------------- --------------- --------------------
00000000A28C1C42 000000C600000000 0000000300000000 2727091266   850403524608  12884901888         30         17        290 cursor: pin S          ACTIVE   WAITED SHORT TIME                 2               0 Concurrency
0000000000002B84 0000012700000000 000000000000003E      11140  1267015352320           62         58         13         41 library cache: mutex X ACTIVE   WAITED SHORT TIME                 4               1 Concurrency
0000000099F1D815 00               0000000300000000 2582763541              0  12884901888         72         11        487 cursor: pin S          ACTIVE   WAITED SHORT TIME                 2               0 Concurrency
0000000000002B84 0000006500000000 000000000000003E      11140   433791696896           62         86         11         57 library cache: mutex X ACTIVE   WAITED SHORT TIME                 2               1 Concurrency
0000000000002B84 00               000000000000003E      11140              0           62        101          9         35 library cache: mutex X ACTIVE   WAITED SHORT TIME                 3               2 Concurrency
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
00000000A28C1C42 0000000000000001 0000000300000000 2727091266              1  12884901888        114          7        233 cursor: pin S          ACTIVE   WAITED SHORT TIME                 3               0 Concurrency
0000000000002B84 00               000000000000003E      11140              0           62        128          7         47 library cache: mutex X ACTIVE   WAITED SHORT TIME                 3               1 Concurrency
0000000000002B84 00               000000000000003E      11140              0           62        142          7         41 library cache: mutex X ACTIVE   WAITED SHORT TIME                 2               0 Concurrency
0000000000002B84 00               000000000000003E      11140              0           62        156          7         97 library cache: mutex X ACTIVE   WAITED SHORT TIME                 1               2 Concurrency
0000000000002B84 0000006500000000 000000000000003E      11140   433791696896           62        170          7        146 library cache: mutex X ACTIVE   WAITED SHORT TIME                 4               1 Concurrency
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0000000000002B84 0000012700000000 000000000000003E      11140  1267015352320           62        184          7         51 library cache: mutex X ACTIVE   WAITED SHORT TIME                 2               0 Concurrency
0000000000002B84 00               000000000000003E      11140              0           62        198          7         81 library cache: mutex X ACTIVE   WAITED SHORT TIME                 2               0 Concurrency
0000000000002B84 0000006500000000 000000000000003E      11140   433791696896           62        241          1         43 library cache: mutex X ACTIVE   WAITED SHORT TIME                 7               1 Concurrency
0000000000002B84 00               000000000000003E      11140              0           62        295         21         42 library cache: mutex X ACTIVE   WAITED SHORT TIME                 3               0 Concurrency
14 rows selected.
--//遇到大量的library cache: mutex X等待事件。

SCOTT@book> @ ev_name 'library cache: mutex X'
    EVENT#   EVENT_ID NAME                                     PARAMETER1           PARAMETER2           PARAMETER3           WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------- ---------------------------------------- -------------------- -------------------- -------------------- ------------- ----------- --------------------
       289 1646780882 library cache: mutex X                   idn                  value                where                   3875070507           4 Concurrency

--//引數P1,P2,P3表示什麼呢?

SELECT owner
      ,name
      ,hash_value
      ,full_hash_value
      ,namespace
      ,child_latch
      ,property hot_flag
      ,executions
      ,invalidations
  FROM v$db_object_cache
 WHERE name = 'SELECT ENAME FROM EMP WHERE ROWID =:B1 '
 order by executions desc ;

OWNER  NAME                                     HASH_VALUE FULL_HASH_VALUE                  NAMESPACE  CHILD_LATCH HOT_FLAG   EXECUTIONS INVALIDATIONS
------ ---------------------------------------- ---------- -------------------------------- ---------- ----------- ---------- ---------- -------------
       SELECT ENAME FROM EMP WHERE ROWID =:B1     85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA         11140 HOT          39941116             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1     85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA             0 HOT          39532761             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA          7234 HOTCOPY7      2971797             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA             0 HOTCOPY7      2964992             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1    580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA         88906 HOTCOPY11     1997489             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1    580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA             0 HOTCOPY11     1997360             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2582763541 c88682c52f8f2765a84d377399f1d815 SQL AREA        120853 HOTCOPY1      1996477             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2582763541 c88682c52f8f2765a84d377399f1d815 SQL AREA             0 HOTCOPY1      1995152             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA        116044 HOTCOPY3      1975972             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA             0 HOTCOPY3      1886386             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA             0 HOTCOPY6      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA         88684 HOTCOPY6      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   3708080158 2b2d6a42e3f5dc2950f01eb8dd04d01e SQL AREA             0 HOTCOPY8      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   3708080158 2b2d6a42e3f5dc2950f01eb8dd04d01e SQL AREA         53278 HOTCOPY8      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   4251304214 88e9b0a03994a83e3b1dd47ffd65c116 SQL AREA             0 HOTCOPY2      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   4251304214 88e9b0a03994a83e3b1dd47ffd65c116 SQL AREA        114966 HOTCOPY2      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA             0 HOTCOPY9      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA         57756 HOTCOPY5      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA             0 HOTCOPY5      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA         11508 HOTCOPY9      1000000             0
20 rows selected.

--//對比前面下劃線內容。
--//P1=11140,正好對應HOT_FLAG='HOT'的CHILD_LATCH.
--//P2=433791696896, 433791696896 = 0x6500000000
--//P3=62 ,P2,p3不知道表示什麼。猜測P2對應阻塞的會話,0x65 = 101,注意看++++的下劃線內容。
--//而P2=1267015352320 = 0x12700000000, 能力有限無法推測。
--//CHILD_LATCH值似乎與FULL_HASH_VALUE有關。另外寫一篇blog.

SELECT sql_id
      ,sql_text
      ,executions
      ,LENGTH (sql_text)
      ,ORA_HASH (sql_text)
      ,hash_value
  FROM v$sqlarea
 WHERE sql_text = 'SELECT ENAME FROM EMP WHERE ROWID =:B1 ';

SQL_ID        SQL_TEXT                               EXECUTIONS LENGTH(SQL_TEXT) ORA_HASH(SQL_TEXT) HASH_VALUE
------------- -------------------------------------- ---------- ---------------- ------------------ ----------
8jc98afj8s722 SELECT ENAME FROM EMP WHERE ROWID =:B1    2992985               39         1145033045 2727091266
2gvj95w2k0aw4 SELECT ENAME FROM EMP WHERE ROWID =:B1   40873893               39         1145033045   85994372
dqk9v3d8mnb7n SELECT ENAME FROM EMP WHERE ROWID =:B1    1000000               39         1145033045 1362767092
51w0yr3fh9n0y SELECT ENAME FROM EMP WHERE ROWID =:B1    1000000               39         1145033045 3708080158
3v5kmvygyxscw SELECT ENAME FROM EMP WHERE ROWID =:B1    1000000               39         1145033045 2683232668
1rvbzkq5y6qmc SELECT ENAME FROM EMP WHERE ROWID =:B1    1000000               39         1145033045 2346932844
cjh845cj9fqua SELECT ENAME FROM EMP WHERE ROWID =:B1    1999111               39         1145033045  580344650
3q7fngzyqbh8q SELECT ENAME FROM EMP WHERE ROWID =:B1    1000000               39         1145033045 4251304214
3mtu372udrjac SELECT ENAME FROM EMP WHERE ROWID =:B1    1983886               39         1145033045 3034301772
ahm9rffcz3q0p SELECT ENAME FROM EMP WHERE ROWID =:B1    1997056               39         1145033045 2582763541
10 rows selected.

--//視乎如果使用DBMS_SHARED_POOL.MARKHOT設定後,如果生成的HOTCOPY達不到併發數量,就存在library cache: mutex X ACTIVE等待事件。
--//執行EXECUTIONS=1000000僅僅5條。其它大於1000000的(不算執行次數40873893),有4條(視乎計數不對,正常應該是後面都是0.佔9個會話。),5+9=14.
--//也就是還是存在爭用的情況。

--//有機會測試更多的會話是否效果更好一些,有點長,另外寫一篇blog。

SELECT LISTAGG (value_string, ',') WITHIN GROUP (ORDER BY value_string) c200
  FROM (SELECT value_string
          FROM V$SQL_BIND_CAPTURE
         WHERE sql_id IN ('8jc98afj8s722'
                         ,'dqk9v3d8mnb7n'
                         ,'51w0yr3fh9n0y'
                         ,'3v5kmvygyxscw'
                         ,'1rvbzkq5y6qmc'
                         ,'cjh845cj9fqua'
                         ,'3q7fngzyqbh8q'
                         ,'3mtu372udrjac'
                         ,'ahm9rffcz3q0p'));
C200
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AAAVREAAEAAAACXAAA,AAAVREAAEAAAACXAAB,AAAVREAAEAAAACXAAD,AAAVREAAEAAAACXAAF,AAAVREAAEAAAACXAAG,AAAVREAAEAAAACXAAH,AAAVREAAEAAAACXAAI,AAAVREAAEAAAACXAAJ,AAAVREAAEAAAACXAAK


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2675118/,如需轉載,請註明出處,否則將追究法律責任。

相關文章