[20201117]使用DBMS_SHARED_POOL.MARKHOT與sql陳述句6.txt
--//前幾天我看了鏈接：https://blog.pythian.com/reducing-contention-on-hot-cursor-objects-cursor-pin-s/
--//里面提到使用DBMS_SHARED_POOL.MARKHOT標識sql陳述句，減少Cursor: Pin S的情況,不過對方不同的地方是在函式中使用，
--//我以前的測驗不在函式里面反而更慢，不建議使用，出現大量的"library cache: mutex X".
--//正好里面有例子，我直接拿來測驗看看，
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
2.建立測驗環境:
create table job_times (sid number, sessionid number,time_ela number,method varchar2(20));
create table code_table (code_name char(1), low_value number, high_value number);
create unique index pk_code_table on scott.code_table(code_name);
alter table code_table add constraint pk_code_table primary key (code_name);
--//作者的測驗不建立主鍵索引，我的測驗建立它，
declare
letters char(26) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
v_num number := 1;
begin
for i in 1..26 LOOP
insert into code_table values (substr(letters,i,1), i*v_num, i*(v_num+1000));
v_num := v_num + 1001;
end loop;
commit;
end;
/
create or replace function fx_num (v_name varchar) return number is
v_low number;
v_high number;
begin
select low_value, high_value into v_low, v_high from code_table where code_name=v_name;
return( v_high-v_low);
--//return(DBMS_RANDOM.value(low => v_low, high => v_high));
end;
/
--//注：我發現作者寫的例子不是很好，呼叫DBMS_RANDOM函式，導致函式本身執行時間有點"長"，
--//建立測驗腳本m3.txt:
$ cat m3.txt
set verify off
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 /*+ &3 */ fx_num(substr(to_char(sysdate,'MON'),1,1)) into v_id from dual;
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;
$ cat cc.txt
SELECT owner
,name
,hash_value
,full_hash_value
,namespace
,child_latch
,property hot_flag
,executions
,invalidations
FROM v$db_object_cache
WHERE name = 'SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1 '
order by executions desc ;
--//注意/:B1后面有1個空格，
$ cat cd.txt
column NAMESPACE format a20
column HOT_FLAG format a20
column name format a66
SELECT owner
,name
,hash_value
,full_hash_value
,namespace
,child_latch
,property hot_flag
,executions
,invalidations
FROM v$db_object_cache
WHERE name='FX_NUM'
order by executions desc ;
3.測驗:
--//執行：
SYS@book> @ tpt/snapper ash 65 1 "select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1"
Sampling SID select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1 with interval 65 seconds, taking 1 snapshots...
-- Session Snapper v4.11 BETA - by Tanel Poder ( http://blog.tanelpoder.com ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
----------------------------------------------------------------------------------------------------
Active% | INST | SQL_ID | SQL_CHILD | EVENT | WAIT_CLASS
----------------------------------------------------------------------------------------------------
993% | 1 | | | library cache: mutex X | Concurrency
517% | 1 | 7tr4jwnamtmsr | 0 | ON CPU | ON CPU
255% | 1 | | | ON CPU | ON CPU
42% | 1 | ctyugp3su3k91 | 0 | library cache: mutex X | Concurrency
42% | 1 | 1zvmjk9k3sp2m | 0 | library cache: mutex X | Concurrency
42% | 1 | 158y7v912v8fb | 0 | library cache: mutex X | Concurrency
41% | 1 | 3mbun480b7ccy | 0 | library cache: mutex X | Concurrency
40% | 1 | 4r4k676qa6v2d | 0 | library cache: mutex X | Concurrency
39% | 1 | 3k7xkc2t9fwf6 | 0 | library cache: mutex X | Concurrency
39% | 1 | 8t7czzgnugs5x | 0 | library cache: mutex X | Concurrency
-- End of ASH snap 1, end=2020-11-17 15:57:47, seconds=65, samples_taken=99
PL/SQL procedure successfully completed.
--//馬上打開另外會話執行：
$ zzdate ;seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/book @m3.txt 2e5 unmarkhot_p=50 {} >/dev/null ;zzdate
trunc(sysdate)+15/24+56/1440+40/86400 == 2020/11/17 15:56:40
trunc(sysdate)+15/24+57/1440+42/86400 == 2020/11/17 15:57:42
SYS@book> @ tpt/ash/ash_wait_chains username||':'||program2||event2 module='SQL*Plus' trunc(sysdate)+15/24+56/1440+40/86400 trunc(sysdate)+15/24+57/1440+42/86400
-- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com )
%This SECONDS AAS WAIT_CHAIN
------ ---------- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
41% 1249 20.1 -> SCOTT:(sqlplus) ON CPU
32% 984 15.9 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) ON CPU
22% 675 10.9 -> SCOTT:(sqlplus) library cache: mutex X
3% 87 1.4 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) ON CPU
3% 80 1.3 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X
0% 2 0 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) ON CPU
0% 1 0 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X
7 rows selected.
--//從等待事件看出現大量的library cache: mutex X，實際上在做到這里時就懷疑作者的測驗，因為sql陳述句出現等待集中在cursor: pin S，
--//而測驗出現library cache: mutex X，說明主要還是集中在包或者函式上，我估計標識sql陳述句后效果不會太好，
4.標識熱物件SQL陳述句：
SYS@book> @ cc.txt
OWNER NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS
------ ------------------------------------------------------------------ ---------- -------------------------------- -------------------- ----------- -------------------- ---------- -------------
SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1 356306711 eb4cdceda1c495cd7cdc91e5153ccf17 SQL AREA 53015 37338848 0
SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1 356306711 eb4cdceda1c495cd7cdc91e5153ccf17 SQL AREA 0 36943000 0
--//CHILD_LATCH是FULL_HASH_VALUE的后17位 0xcf17 = 53015，
SYS@book> exec dbms_shared_pool.markhot(hash => 'eb4cdceda1c495cd7cdc91e5153ccf17', namespace => 0, global => true);
PL/SQL procedure successfully completed.
5.繼續測驗:
SYS@book> @ tpt/snapper ash 65 1 "select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1"
Sampling SID select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1 with interval 65 seconds, taking 1 snapshots...
-- Session Snapper v4.11 BETA - by Tanel Poder ( http://blog.tanelpoder.com ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
----------------------------------------------------------------------------------------------------
Active% | INST | SQL_ID | SQL_CHILD | EVENT | WAIT_CLASS
----------------------------------------------------------------------------------------------------
1130% | 1 | | | library cache: mutex X | Concurrency
234% | 1 | | | ON CPU | ON CPU
76% | 1 | dc8x5k16dsk82 | 0 | ON CPU | ON CPU
76% | 1 | 9pyrc45h3tgg9 | 0 | ON CPU | ON CPU
65% | 1 | 0u9hxt3azayv5 | 0 | ON CPU | ON CPU
62% | 1 | arg5wx08suqm0 | 0 | ON CPU | ON CPU
55% | 1 | cqtdnxg9apfza | 0 | ON CPU | ON CPU
49% | 1 | 3k7xkc2t9fwf6 | 0 | library cache: mutex X | Concurrency
46% | 1 | 61m38g7ygpfvz | 0 | ON CPU | ON CPU
44% | 1 | 2ht2a25wq37kg | 0 | library cache: mutex X | Concurrency
-- End of ASH snap 1, end=2020-11-17 16:03:21, seconds=65, samples_taken=98
PL/SQL procedure successfully completed.
$ zzdate ;seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/book @m3.txt 2e5 markhot_p=50 {} >/dev/null ;zzdate
trunc(sysdate)+16/24+02/1440+17/86400 == 2020/11/17 16:02:17
trunc(sysdate)+16/24+03/1440+18/86400 == 2020/11/17 16:03:18
--//從時間上看并沒有體現使用DBMS_SHARED_POOL.MARKHOT的好處，
SYS@book> @ tpt/ash/ash_wait_chains username||':'||program2||event2 module='SQL*Plus' trunc(sysdate)+16/24+02/1440+17/86400 trunc(sysdate)+16/24+03/1440+18/86400
-- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com )
%This SECONDS AAS WAIT_CHAIN
------ ---------- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
43% 1289 21.1 -> SCOTT:(sqlplus) ON CPU
33% 994 16.3 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) ON CPU
19% 570 9.3 -> SCOTT:(sqlplus) library cache: mutex X
2% 57 .9 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X
2% 54 .9 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) ON CPU
0% 5 .1 -> SCOTT:(sqlplus) cursor: pin S
0% 1 0 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X
0% 1 0 -> SCOTT:(sqlplus) cursor: pin S -> SCOTT:(sqlplus) ON CPU
0% 1 0 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X
0% 1 0 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) cursor: pin S
0% 1 0 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X
11 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)
-------------------- ---------- ---------------------- -------------
markhot_p=50 50 5989 299426
unmarkhot_p=50 50 6118 305892
--//根本無法體會dbms_shared_pool.markhot的好處，
6.標識函式為熱物件呢？
SCOTT@book> @ cd.txt
OWNER NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS
------ ------- ---------- -------------------------------- -------------------- ----------- -------------------- ---------- -------------
SCOTT FX_NUM 3934649448 e946e63d9c1e58dd062f52adea85fc68 TABLE/PROCEDURE 130152 50000000 0
SYS@book> exec dbms_shared_pool.markhot(hash => 'e946e63d9c1e58dd062f52adea85fc68', namespace => 1, global => true);
PL/SQL procedure successfully completed.
SYS@book> @ tpt/snapper ash 65 1 "select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1"
Sampling SID select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1 with interval 65 seconds, taking 1 snapshots...
-- Session Snapper v4.11 BETA - by Tanel Poder ( http://blog.tanelpoder.com ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
----------------------------------------------------------------------------------------------------
Active% | INST | SQL_ID | SQL_CHILD | EVENT | WAIT_CLASS
----------------------------------------------------------------------------------------------------
309% | 1 | | | ON CPU | ON CPU
114% | 1 | | | library cache: mutex X | Concurrency
106% | 1 | 0u9hxt3azayv5 | 0 | ON CPU | ON CPU
101% | 1 | dc8x5k16dsk82 | 0 | ON CPU | ON CPU
97% | 1 | arg5wx08suqm0 | 0 | ON CPU | ON CPU
95% | 1 | cqtdnxg9apfza | 0 | ON CPU | ON CPU
88% | 1 | 9pyrc45h3tgg9 | 0 | ON CPU | ON CPU
64% | 1 | 5bacfy8mwthrj | 0 | ON CPU | ON CPU
54% | 1 | 61m38g7ygpfvz | 0 | ON CPU | ON CPU
36% | 1 | 0srq6bz2rvktx | 0 | ON CPU | ON CPU
-- End of ASH snap 1, end=2020-11-17 16:10:06, seconds=65, samples_taken=99
PL/SQL procedure successfully completed.
$ zzdate ;seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/book @m3.txt 2e5 markhot_pfs=50 {} >/dev/null ;zzdate
trunc(sysdate)+16/24+09/1440+02/86400 == 2020/11/17 16:09:02
trunc(sysdate)+16/24+09/1440+45/86400 == 2020/11/17 16:09:45
SYS@book> @ tpt/ash/ash_wait_chains username||':'||program2||event2 module='SQL*Plus' trunc(sysdate)+16/24+09/1440+02/86400 trunc(sysdate)+16/24+09/1440+45/86400
-- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com )
%This SECONDS AAS WAIT_CHAIN
------ ---------- ---------- -------------------------------------------------------------------------------------
84% 1767 41.1 -> SCOTT:(sqlplus) ON CPU
11% 234 5.4 -> SCOTT:(sqlplus) library cache: mutex X
4% 77 1.8 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) ON CPU
1% 15 .3 -> SCOTT:(sqlplus) cursor: pin S -> SCOTT:(sqlplus) ON CPU
0% 4 .1 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X
0% 3 .1 -> SCOTT:(sqlplus) cursor: pin S
6 rows selected.
--//依舊存在少量library cache: mutex X，
SCOTT@book> @ cd.txt
OWNER NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS
------ ------ ---------- -------------------------------- -------------------- ----------- -------------------- ---------- -------------
SCOTT FX_NUM 3934649448 e946e63d9c1e58dd062f52adea85fc68 TABLE/PROCEDURE 130152 HOT 50000000 0
SCOTT FX_NUM 2604013040 acab658d7761882204bcbd0b9b3615f0 TABLE/PROCEDURE 5616 HOTCOPY1 1400000 0
SCOTT FX_NUM 1147571161 5bc0190cffaf491d2cc98bda44668bd9 TABLE/PROCEDURE 35801 HOTCOPY6 1400000 0
SCOTT FX_NUM 2514462761 4046d059eca2403b2f1031c395dfa829 TABLE/PROCEDURE 108585 HOTCOPY11 1400000 0
SCOTT FX_NUM 2255211854 685056ffeb2dead33b91453e866bcd4e TABLE/PROCEDURE 118094 HOTCOPY9 1200000 0
SCOTT FX_NUM 1128717707 5e9cc5f89e3a3b6fa14956904346dd8b TABLE/PROCEDURE 56715 HOTCOPY4 1200000 0
SCOTT FX_NUM 3773218966 44b816a2374a4ecfd1c80ca4e0e6c096 TABLE/PROCEDURE 49302 HOTCOPY7 800000 0
SCOTT FX_NUM 1509772016 5aa610021f96b19db8b56a4459fd4af0 TABLE/PROCEDURE 84720 HOTCOPY2 800000 0
SCOTT FX_NUM 385266949 97923d5d4cfc8cfd6175dca316f6b505 TABLE/PROCEDURE 46341 HOTCOPY3 600000 0
SCOTT FX_NUM 2652599427 ef67af593682efa5dbecd4ee9e1b7483 TABLE/PROCEDURE 95363 HOTCOPY8 400000 0
SCOTT FX_NUM 1752733049 2a5adce93042ee1fd233ef8668789579 TABLE/PROCEDURE 38265 HOTCOPY12 400000 0
SCOTT FX_NUM 2357479237 9b6f379c93c9312cab9a22be8c844745 TABLE/PROCEDURE 18245 HOTCOPY5 400000 0
12 rows selected.
--//我并行50個會話，僅僅打開11個hotcopy，依舊存在爭用，從EXECUTIONS次數也可以看出來，
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_pfs=50 50 4190 209483
markhot_p=50 50 5989 299426
unmarkhot_p=50 50 6118 305892
7.總結：
從測驗可以看出,使用dbms_shared_pool.markhot標識sql陳述句帶來的效果不是很大，主要遇到http://blog.itpub.net/267265/viewspace-2675362/的bug，
利益還是在于鏈接http://blog.itpub.net/267265/viewspace-2675377/的總結，再次轉抄一遍(原始鏈接有1個地方寫錯)：
1.使用DBMS_SHARED_POOL.MARKHOT標識熱sql陳述句,反而性能更慢，我估計可能遇到bug，
參考鏈接:http://blog.itpub.net/267265/viewspace-2675362/=>[20200212]使用DBMS_SHARED_POOL.MARKHOT與視圖v$open_cursor.txt
2.使用DBMS_SHARED_POOL.MARKHOT標識熱物件，效果不錯，如果頻繁呼叫可以嘗試使用，
--//這里原始鏈接有錯，
3.關于FULL_HASH_VALUE的計算：
--// sql陳述句 在原來的基礎上加入 . mod(sid,cpu_count/2)+1數字的字串 參與運算，
--// 如果設定隱含引數 _kgl_hot_object_copies， sql陳述句 在原來的基礎上加入 . mod(sid,_kgl_hot_object_copies)+1數字的字串，
--// 物件 object_name.owner.HOTCOPYNN中的NN \0X\0\0\0 （其中X標識namespace），
--//另外 namespace可以查詢 select distinct kglhdnsp,kglhdnsd,kglobtyd from x$kglob order by 1;
測驗參考鏈接:
http://andreynikolaev.wordpress.com/2011/05/01/divide-and-conquer-the-true-mutex-contention/
https://jonathanlewis.wordpress.com/2017/10/02/markhot/
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/222365.html
標籤：Oracle