[20200126]使用DBMS_SHARED_POOL.MARKHOT與sql語句.txt
[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20200211]使用DBMS_SHARED_POOL.MARKHOT與sql語句2.txtSQL
- [20200212]使用DBMS_SHARED_POOL.MARKHOT與sql語句3.txtSQL
- [20201117]使用DBMS_SHARED_POOL.MARKHOT與sql語句5.txtSQL
- [20201117]使用DBMS_SHARED_POOL.MARKHOT與sql語句6.txtSQL
- [20240320]空格與sqlpus的sql語句.txtSQL
- [20150403]修正sql語句.txtSQL
- PL/SQL與DDL語句SQL
- Oracle sql with 語句語法與例子OracleSQL
- sql 中的with 語句使用SQL
- [20170103]sql語句過載.txtSQL
- [20160215]超長sql語句與父子游標.txtSQL
- MySql與Sql Server Update語句MySqlServer
- 使用sql語句查詢平均值,使用sql語句查詢資料總條數, not in 篩選語句的使用SQL
- 使用sql語句分析雙色球SQL
- SQL語句使用總結(一)SQL
- SQL查詢語句使用 (轉)SQL
- 使用mysqlsniffer捕獲SQL語句MySql
- [20200212]使用DBMS_SHARED_POOL.MARKHOT與sql的計算2.txtSQL
- [20200211]使用DBMS_SHARED_POOL.MARKHOT與sql_id的計算.txtSQL
- [20211009]使用bash計算sql語句的sql_id.txtSQL
- SQL語句與正規表示式SQL
- SQL Profiles與語句最佳化SQL
- SQL語句SQL
- Java中如何解析SQL語句、格式化SQL語句、生成SQL語句？JavaSQL
- [20170703]SQL語句分析執行過程.txtSQL
- [20131204]sql語句優化.txtSQL優化
- [20151221]sql語句優化.txtSQL優化
- [20141013]奇怪的sql語句.txtSQL
- [20150527]跟蹤單個sql語句.txtSQL
- [20121101]tkprof抽取sql語句.txtSQL
- [20240607]PL/SQL中sql語句的註解.txtSQL
- 使用hint來調優sql語句SQL
- Fastapi sqlalchemy DBApi 直接使用sql語句ASTAPISQL
- 查詢阻塞與被阻塞SQL語句SQL
- 【SQL】Oracle SQL join on語句and和where使用區別SQLOracle
- sql語句大全SQL
- 共享SQL語句SQL
- SQL語句整理SQL