[20201117]使用DBMS_SHARED_POOL.MARKHOT与sql语句5.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".
--//正好里面有例子，我直接拿来测试看看,首先看看标识热sql语句后，看看v$open_cursor的情况。
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);
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(DBMS_RANDOM.value(low => v_low, high => v_high));
end;
/
--//建立测试脚本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个空格。
3.测试:
SCOTT@book> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
295 695 56167 DEDICATED 56168 21 250 alter system kill session '295,695' immediate;
SCOTT@book> @m3.txt 10 xx yy
1 row created.
Commit complete.
PL/SQL procedure successfully completed.
4 rows updated.
Commit complete.
--//确定sql_id='7tr4jwnamtmsr'.
SCOTT@book> select * from v$open_cursor where sid=295 and SQL_ID='7tr4jwnamtmsr'
2 @ prxx
==============================
SADDR : 0000000085EC7D20
SID : 295
USER_NAME : SCOTT
ADDRESS : 000000007BE3D218
HASH_VALUE : 356306711
SQL_ID : 7tr4jwnamtmsr
SQL_TEXT : SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME
LAST_SQL_ACTIVE_TIME :
SQL_EXEC_ID :
CURSOR_TYPE : PL/SQL CURSOR CACHED
PL/SQL procedure successfully completed.
SCOTT@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 31 0
SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1 356306711 eb4cdceda1c495cd7cdc91e5153ccf17 SQL AREA 0 31 0
SCOTT@book> @prxx
==============================
OWNER :
NAME : SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
HASH_VALUE : 356306711
FULL_HASH_VALUE : eb4cdceda1c495cd7cdc91e5153ccf17
NAMESPACE : SQL AREA
CHILD_LATCH : 53015
HOT_FLAG :
EXECUTIONS : 31
INVALIDATIONS : 0
==============================
OWNER :
NAME : SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
HASH_VALUE : 356306711
FULL_HASH_VALUE : eb4cdceda1c495cd7cdc91e5153ccf17
NAMESPACE : SQL AREA
CHILD_LATCH : 0
HOT_FLAG :
EXECUTIONS : 31
INVALIDATIONS : 0
PL/SQL procedure successfully completed.
--//确定FULL_HASH_VALUE=eb4cdceda1c495cd7cdc91e5153ccf17.
4.标识热对象：
--//以sys用户执行：
SYS@book> exec dbms_shared_pool.markhot(hash => 'eb4cdceda1c495cd7cdc91e5153ccf17', namespace => 0, global => true);
PL/SQL procedure successfully completed.
--//退出重新登录：
SCOTT@book> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
295 697 56266 DEDICATED 56267 21 251 alter system kill session '295,697' immediate;
SCOTT@book> @m3.txt 10 xx yy
1 row created.
Commit complete.
PL/SQL procedure successfully completed.
1 row updated.
Commit complete.
SCOTT@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 0 HOT 31 0
SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1 356306711 eb4cdceda1c495cd7cdc91e5153ccf17 SQL AREA 53015 HOT 31 0
SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1 939937426 432f8fa611a9e7d4d7a686f838064e92 SQL AREA 0 HOTCOPY8 10 0
SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1 939937426 432f8fa611a9e7d4d7a686f838064e92 SQL AREA 20114 HOTCOPY8 10 0
--//HOT_FLAG=HOTCOPY8.参考http://blog.itpub.net/267265/viewspace-2675377/的总结：
--//mod(sid,cpu_count/2)+1 ,我的测试环境cpu_count=24.
$ echo 295%12+1 | bc
8
SCOTT@book> select * from v$open_cursor where sid=295 and sql_text='SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1 ';
no rows selected
--//奇怪没有找到对应记录。
SCOTT@book> select * from v$open_cursor where sid=295 and sql_text like 'SELECT LOW_VALUE, HIGH_VALUE%'
2 @ prxx
==============================
SADDR : 0000000085EC7D20
SID : 295
USER_NAME : SCOTT
ADDRESS : 000000007CB1C9D0
HASH_VALUE : 939937426
SQL_ID : dg9n6z0w0cmnk
SQL_TEXT : SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME
LAST_SQL_ACTIVE_TIME :
SQL_EXEC_ID :
CURSOR_TYPE : PL/SQL CURSOR CACHED
PL/SQL procedure successfully completed.
--//噢，原来sql_text仅仅保存开头60个字符长度。
--//可以发现很明显，没有出现在链接看到的情况
http://blog.itpub.net/267265/viewspace-2675362/
--//也就是如果在函数或者PL/SQL包里面的sql语句通过DBMS_SHARED_POOL.MARKHOT是可以提高性能的。
--//限于偏于另外写一篇blog测试。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2734695/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
