[20200212]使用DBMS_SHARED_POOL.MARKHOT与sql语句3.txt
--//测试设置_kgl_hot_object_copies＝101,看看避免冲突的情况下,使用DBMS_SHARED_POOL.MARKHOT效果如何。
1.环境：
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@book> alter system set "_kgl_hot_object_copies"=101 scope=spfile ;
System altered.
--//必须重启才生效，重启数据库略。
SYS@book> @ hide _kgl_hot_object_copies
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---------------------- ----------------------------------- ------------- ------------- ------------ ----- ---------
_kgl_hot_object_copies Number of copies for the hot object FALSE 101 101 FALSE FALSE
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 NOMARKHOTx {}
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)
-------------------- ---------- ---------------------- -------------
NOMARKHOTx 14 2629 36811
3.测试2：
--//测试使用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 ' and CHILD_LATCH<>0
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 13071757 0
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 MARKHOTx {}
SYS@book> @ wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATUS STATE WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS
---------------- ---------------- ---------------- ---------- ----------------- ---------- ---------- ---------- ---------- ---------------------- -------- ------------------- --------------- --------------- --------------------
0000000000002B84 00 000000000000003E 11140 0 62 58 23 69 library cache: mutex X ACTIVE WAITED SHORT TIME 2 0 Concurrency
0000000000002B84 00 000000000000003E 11140 0 62 72 13 69 library cache: mutex X ACTIVE WAITED SHORT TIME 2 2 Concurrency
0000000000002B84 0000003A00000000 000000000000003E 11140 249108103168 62 86 3 46 library cache: mutex X ACTIVE WAITED SHORT TIME 3 4 Concurrency
0000000000002B84 00 000000000000003E 11140 0 62 101 3 70 library cache: mutex X ACTIVE WAITED SHORT TIME 3 1 Concurrency
0000000000002B84 00 000000000000003E 11140 0 62 114 3 66 library cache: mutex X ACTIVE WAITED SHORT TIME 2 1 Concurrency
0000000000002B84 00 000000000000003E 11140 0 62 128 3 45 library cache: mutex X ACTIVE WAITED SHORT TIME 3 1 Concurrency
0000000000002B84 0000009C00000000 000000000000003E 11140 670014898176 62 142 3 55 library cache: mutex X ACTIVE WAITED KNOWN TIME 10997 4 Concurrency
0000000000002B84 00 000000000000003E 11140 0 62 156 3 61 library cache: mutex X ACTIVE WAITED SHORT TIME 2 0 Concurrency
0000000000002B84 0000005600000000 000000000000003E 11140 369367187456 62 170 3 39 library cache: mutex X ACTIVE WAITED SHORT TIME 3 4 Concurrency
0000000000002B84 0000009C00000000 000000000000003E 11140 670014898176 62 184 3 41 library cache: mutex X ACTIVE WAITED SHORT TIME 11 4 Concurrency
0000000000002B84 00 000000000000003E 11140 0 62 198 3 59 library cache: mutex X ACTIVE WAITED SHORT TIME 2 0 Concurrency
0000000000002B84 00 000000000000003E 11140 0 62 212 37 63 library cache: mutex X ACTIVE WAITED SHORT TIME 2 0 Concurrency
0000000000002B84 0000003A00000000 000000000000003E 11140 249108103168 62 226 7 53 library cache: mutex X ACTIVE WAITED SHORT TIME 2 1 Concurrency
0000000000002B84 00 000000000000003E 11140 0 62 241 3 60 library cache: mutex X ACTIVE WAITED SHORT TIME 2 1 Concurrency
14 rows selected.
--//还是大量的library cache: mutex X等待事件。
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 ' and CHILD_LATCH<>0
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 13071757 0
SELECT ENAME FROM EMP WHERE ROWID =:B1 285504892 7f0b59ec4b8535eec82fba911104757c SQL AREA 30076 HOTCOPY28 1000000 0
SELECT ENAME FROM EMP WHERE ROWID =:B1 4010836214 0cea2fadafa4dd35f7f8efcdef1080f6 SQL AREA 33014 HOTCOPY70 1000000 0
SELECT ENAME FROM EMP WHERE ROWID =:B1 1691525416 3d2f9eaeae30daafc5521e5c64d2a128 SQL AREA 41256 HOTCOPY84 1000000 0
SELECT ENAME FROM EMP WHERE ROWID =:B1 3448939648 33066bcd4389a8a8b78ccddacd92a480 SQL AREA 42112 HOTCOPY87 1000000 0
SELECT ENAME FROM EMP WHERE ROWID =:B1 2118854785 c79e876e553b527d68e095217e4b2881 SQL AREA 75905 HOTCOPY59 1000000 0
SELECT ENAME FROM EMP WHERE ROWID =:B1 1626819103 fe3d83877f2c5663dd70044f60f74a1f SQL AREA 84511 HOTCOPY98 1000000 0
SELECT ENAME FROM EMP WHERE ROWID =:B1 580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA 88906 HOTCOPY11 1000000 0
SELECT ENAME FROM EMP WHERE ROWID =:B1 2543673867 6dd887a7dad2ef66cb0141e3979d620b SQL AREA 90635 HOTCOPY56 1000000 0
SELECT ENAME FROM EMP WHERE ROWID =:B1 2236576569 7ace1ec20937d7b7155be95f854f7339 SQL AREA 95033 HOTCOPY73 1000000 0
SELECT ENAME FROM EMP WHERE ROWID =:B1 2648560675 127d6fcd213a6dd9c2d5d8139dddd423 SQL AREA 119843 HOTCOPY25 1000000 0
SELECT ENAME FROM EMP WHERE ROWID =:B1 2582763541 c88682c52f8f2765a84d377399f1d815 SQL AREA 120853 HOTCOPY1 1000000 0
SELECT ENAME FROM EMP WHERE ROWID =:B1 2302797450 ec2f55c6e5d2cd8da2e94ef78941e68a SQL AREA 124554 HOTCOPY42 1000000 0
SELECT ENAME FROM EMP WHERE ROWID =:B1 2074737225 a3b70cec850d0f06245030157ba9fa49 SQL AREA 129609 HOTCOPY40 1000000 0
SELECT ENAME FROM EMP WHERE ROWID =:B1 2496819678 14d02282ba345f71d7396b5694d271de SQL AREA 29150 HOTCOPY14 1000000 0
15 rows selected.
--//这次全部分散开了。生成14个HOTCOPYXX。
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)
-------------------- ---------- ---------------------- -------------
NOMARKHOTx 14 2629 36811
MARKHOTx 14 3130 43825
--//效果一样，再次验证使用DBMS_SHARED_POOL.MARKHOT标识热sql语句,效果不大，建议不要使用。
SCOTT@book> select mod(sid,101)+1 ,count(*) from job_times where method='MARKHOTx' group by mod(sid,101) order by 1;
MOD(SID,101)+1 COUNT(*)
-------------- ----------
1 1
11 1
14 1
25 1
28 1
40 1
42 1
56 1
59 1
70 1
73 1
84 1
87 1
98 1
14 rows selected.
SCOTT@book> SELECT substr(property,8,2) FROM v$db_object_cache WHERE name = 'SELECT ENAME FROM EMP WHERE ROWID =:B1 ' and CHILD_LATCH<>0 order by 1 ;
SUBS
----
1
11
14
25
28
40
42
56
59
70
73
84
87
98
15 rows selected.
--//可以发现结果一致，再次验证了设置隐含参数_kgl_hot_object_copies，sql_id语句计算sql_text变为原来sql文本的基础上
--// 加上 . mod(sid,_kgl_hot_object_copies)+1数字的字符串。
$ echo -e -n 'SELECT ENAME FROM EMP WHERE ROWID =:B1 \0.14' | md5sum | sed 's/ -//' | xxd -r -p | od -t x4 | sed -n -e 's/^0000000 //' -e 's/ //gp'
14d02282ba345f71d7396b5694d271de
--//FULL_HASH_VALUE与前面的输出完全能对上。
4.收尾:
SYS@book> alter system reset "_kgl_hot_object_copies" ;
System altered.
--//重启还原。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2675361/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261105]测试21c SQL Translation Framework.txt
- [20261106]ANSI DATE and TIMESTAMP SQL syntax.txt
- [20261106]光标跟踪事件11.txt
- [20261102]光标跟踪事件9.txt
- [20261102]光标跟踪事件8.txt
- [20261102]为什么出现光标出现TOP_LEVEL_RPI_CURSOR不共享.txt
- [20261102]改变pdb名字.txt
- [20261018]光标跟踪事件3.txt
- [20261016]光标跟踪事件2.txt
- [20261015]模拟kksfbc child completion等待事件2.txt
