[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 X （其中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/
文章来源: 博客园
- 还没有人评论，欢迎说说您的想法！
客服
