[20260721]测试带dblink的sql语句密集执行的情况(整理版本).txt
--//前面测试带dblink的sql语句，每次都会存在对dblink对象的library cache lock，测试密集执行这类sql语句会出现什么情况。
1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
--//注：2个测试库，版本相同。
--//测试环境如下
ip_address hostanme oracle
192.168.56.103 centos 21c
192.168.56.101 centtest 21c
--//192.168.56.103 centos
SCOTT@book01p> CREATE PUBLIC DATABASE LINK CENTTEST CONNECT TO SCOTT IDENTIFIED BY 'book' USING '192.168.56.101/book01p';
Database link created.
SCOTT@book01p> create table job_times (sid number, serial# number,time_ela1 number,time_ela2 number ,start_date timestamp,end_date timestamp,method varchar2(20));
Table created.
--//192.168.56.101 centtest:
SCOTT@book01p> create table t as select rownum id ,'test' pad from dual connect by level<=1e5;
Table created.
SCOTT@book01p> create unique index pk_t on t (id);
Index created.
SCOTT@book01p> alter table t add constraint pk_t primary key (id);
Table altered.
2.建立匿名PL SQL执行脚本:
$ cat zz2.txt
set verify off
variable v_method varchar2(30);
exec :v_method := '&&2';
insert into job_times values ( sys_context ('userenv', 'sid') ,&3,dbms_utility.get_time ,null , localtimestamp,null ,:v_method) ;
commit ;
declare
v_c_id number;
begin
for i in 1 .. &&1 loop
&&4 select count(*) into v_c_id from t@centtest where id = i;
&&5 select count(*) into v_c_id from t@centtest,dual where id = i;
&&6 select count(pad) into v_c_id from t@centtest where id = i;
&&7 select count(pad) into v_c_id from t@centtest,dual where id = i;
end loop;
end ;
/
update job_times set time_ela2 = dbms_utility.get_time ,end_date=localtimestamp where sid=sys_context ('userenv', 'sid') and serial#= &3 and method= :v_method ;
commit;
quit
3.测试:
--//仅仅测试如下两者情况,主要两者执行性能存在差异:
select count(pad) into v_c_id from t@centtest where id = i;
select count(pad) into v_c_id from t@centtest,dual where id = i;
$ zzdate ; seq 50 | xargs -IQ -P 50 sqlplus -s -l scott/book@book01p @zz2.txt 3e4 dblink3 Q -- -- '' -- > /dev/null ;zzdate
trunc(sysdate)+16/24+02/1440+08/86400 -1784620928.631582661
trunc(sysdate)+16/24+04/1440+44/86400 1784621084.298182420
--//Sum = 155.666599759
SYS@book> @ ashtop event 1=1 trunc(sysdate)+16/24+02/1440+08/86400 trunc(sysdate)+16/24+04/1440+44/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ----------------------------- ------------------- ------------------- ---------- -------- -----------
5532 35.5 76% | SQL*Net message from dblink 2026-07-21 16:02:09 2026-07-21 16:04:43 5531 154 5532
763 4.9 10% | library cache: mutex X 2026-07-21 16:02:13 2026-07-21 16:04:40 155 112 250
694 4.4 10% | 2026-07-21 16:02:10 2026-07-21 16:04:43 398 145 517
164 1.1 2% | library cache: bucket mutex X 2026-07-21 16:02:13 2026-07-21 16:04:38 30 35 57
92 .6 1% | latch: enqueue hash chains 2026-07-21 16:02:20 2026-07-21 16:04:40 91 25 92
10 .1 0% | cursor: pin S 2026-07-21 16:02:12 2026-07-21 16:04:05 1 8 8
5 .0 0% | db file sequential read 2026-07-21 16:02:11 2026-07-21 16:03:42 2 5 5
3 .0 0% | log file parallel write 2026-07-21 16:02:11 2026-07-21 16:03:11 1 2 2
2 .0 0% | SQL*Net message to dblink 2026-07-21 16:02:31 2026-07-21 16:02:42 2 2 2
2 .0 0% | control file parallel write 2026-07-21 16:02:29 2026-07-21 16:02:32 1 2 2
2 .0 0% | db file async I/O submit 2026-07-21 16:02:32 2026-07-21 16:02:33 1 2 2
1 .0 0% | LGWR any worker group 2026-07-21 16:02:11 2026-07-21 16:02:11 1 1 1
1 .0 0% | log file sync 2026-07-21 16:02:11 2026-07-21 16:02:11 1 1 1
13 rows selected.
SYS@book> @ ashtop event,p1raw,p3raw,p1 "event like 'library cache:%'" trunc(sysdate)+16/24+02/1440+08/86400 trunc(sysdate)+16/24+04/1440+44/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT P1RAW P3RAW P1 FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ----------------------------- ----------------- ----------------- ---------- ------------------- ------------------- ---------- -------- -----------
439 2.8 47% | library cache: mutex X 00000000F8F5BDDD FFFFFFFF0045006A 4176854493 2026-07-21 16:02:15 2026-07-21 16:04:40 104 77 164
276 1.8 30% | library cache: mutex X 00000000799B967E 000000000045006A 2040239742 2026-07-21 16:02:13 2026-07-21 16:04:40 30 55 79
88 .6 9% | library cache: bucket mutex X 000000000001BDDD 000000000000003E 114141 2026-07-21 16:02:13 2026-07-21 16:04:38 19 23 37
76 .5 8% | library cache: bucket mutex X 000000000001967E 000000000000003E 104062 2026-07-21 16:02:23 2026-07-21 16:04:31 12 15 21
39 .3 4% | library cache: mutex X 00000000799B967E 0000000000450055 2040239742 2026-07-21 16:02:13 2026-07-21 16:04:37 19 31 35
9 .1 1% | library cache: mutex X 00000000F8F5BDDD FFFFFFFF00450055 4176854493 2026-07-21 16:02:55 2026-07-21 16:04:39 5 9 9
6 rows selected.
--//p1=4176854493,2040239742对应idn，也就是hash_value,P1RAW & 0x1ffff 其16进制的后4位与library cache: mutex X的值一致。
SYS@book> @ kglob '' 4176854493 '' ''
==============================
INST_ID : 1
OWNER : m
NAME : CENTTEST
DB_LINK :
NAMESPACE : DBLINK
TYPE : CURSOR
NAMESPACE_NUM : 69
NAMESPACE_HEX : 45
SHARABLE_MEM : 0
PERSISTENT_MEM : 0
RUNTIME_MEM : 0
LOADS : 0
EXECUTIONS : 0
LOCKS : 0
PINS : 0
KEPT : NO
CHILD_LATCH : 114141
INVALIDATIONS : 0
HASH_VALUE : 4176854493
HASH_HEX : f8f5bddd
LOCK_MODE : NONE
PIN_MODE : NONE
STATUS : UNKOWN
TIMESTAMP :
PREVIOUS_TIMESTAMP :
LOCKED_TOTAL : 4451732
PINNED_TOTAL : 0
PROPERTY :
FULL_HASH_VALUE : 9bfb4614f4f9f983c81d83a1f8f5bddd
CON_ID : 3
CON_NAME : BOOK01P
ADDR : 00000000622DE450
PAR_ADDR : 00000000622DE450
HANDLE_TYPE : parent handle address
KGLOBHD0 : 00
KGLOBHD6 : 00
EDITION :
SQL_ID :
KGL_BUCKET : 114141
OBJECT_STR : CENTTEST.m .BOOK01P\x45\0\0\0
PL/SQL procedure successfully completed.
SYS@book> @ kglob '' 2040239742 '' ''
==============================
INST_ID : 1
OWNER :
NAME : CENTTEST
DB_LINK :
NAMESPACE : DBLINK
TYPE : NONE
NAMESPACE_NUM : 69
NAMESPACE_HEX : 45
SHARABLE_MEM : 0
PERSISTENT_MEM : 0
RUNTIME_MEM : 0
LOADS : 0
EXECUTIONS : 0
LOCKS : 0
PINS : 0
KEPT : NO
CHILD_LATCH : 104062
INVALIDATIONS : 0
HASH_VALUE : 2040239742
HASH_HEX : 799b967e
LOCK_MODE : NONE
PIN_MODE : NONE
STATUS : UNKOWN
TIMESTAMP :
PREVIOUS_TIMESTAMP :
LOCKED_TOTAL : 4451732
PINNED_TOTAL : 0
PROPERTY :
FULL_HASH_VALUE : c62079fc4e7d982a30fb1eab799b967e
CON_ID : 3
CON_NAME : BOOK01P
ADDR : 00000000622D8CC0
PAR_ADDR : 00000000622D8CC0
HANDLE_TYPE : parent handle address
KGLOBHD0 : 00
KGLOBHD6 : 00
EDITION :
SQL_ID :
KGL_BUCKET : 104062
OBJECT_STR : CENTTEST. .BOOK01P\x45\0\0\0
PL/SQL procedure successfully completed.
--//对应的就是dblink的对象。
--//也可以直接查询v$db_object_cache视图.
SYS@book> select hash_value, owner,dump(owner,16) c30 , name, namespace, type from v$db_object_cache where name='CENTTEST';
HASH_VALUE OWNER C30 NAME NAMESPACE TYPE
---------- ------ ---------------------- -------- ---------- --------
2040239742 Typ=1 Len=4: 1,0,0,0 CENTTEST DBLINK NONE
4176854493 m Typ=1 Len=4: 6d,0,0,0 CENTTEST DBLINK CURSOR
4.继续:
--//测试select count(pad) into v_c_id from t@centtest,dual where id = i;引入本地表.
$ zzdate ; seq 50 | xargs -IQ -P 50 sqlplus -s -l scott/book@book01p @zz2.txt 3e4 dblink4 Q -- -- -- '' > /dev/null ;zzdate
trunc(sysdate)+16/24+16/1440+29/86400 -1784621789.332018842
trunc(sysdate)+16/24+18/1440+13/86400 1784621893.009318330
--//Sum = 103.677299488,对比前面的测试快许多.
SYS@book> @ ashtop event 1=1 trunc(sysdate)+16/24+16/1440+29/86400 trunc(sysdate)+16/24+18/1440+13/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- -------- -----------
2728 26.2 62% | SQL*Net message from dblink 2026-07-21 16:16:31 2026-07-21 16:18:12 2726 102 2728
821 7.9 19% | library cache: mutex X 2026-07-21 16:16:32 2026-07-21 16:18:11 819 88 819
490 4.7 11% | 2026-07-21 16:16:29 2026-07-21 16:18:11 434 101 469
251 2.4 6% | library cache: bucket mutex X 2026-07-21 16:16:33 2026-07-21 16:18:11 251 49 251
85 .8 2% | latch: enqueue hash chains 2026-07-21 16:16:47 2026-07-21 16:18:06 83 18 85
14 .1 0% | cursor: pin S 2026-07-21 16:16:50 2026-07-21 16:18:01 1 7 7
2 .0 0% | SQL*Net message to dblink 2026-07-21 16:17:04 2026-07-21 16:17:23 2 2 2
1 .0 0% | control file parallel write 2026-07-21 16:18:12 2026-07-21 16:18:12 1 1 1
1 .0 0% | db file async I/O submit 2026-07-21 16:17:30 2026-07-21 16:17:30 1 1 1
1 .0 0% | log file sync 2026-07-21 16:17:53 2026-07-21 16:17:53 1 1 1
10 rows selected.
--//情况与前面类似，不再展开分析。主要差别是SQL*Net message from dblink比较明显.
--//开始以为是引入本地表对dblink对象的library cache lock的减少,实际上使用bpftrace脚本后,两者差别不大,而且脚本ashtop显示
--//主要区别在SQL*Net message from dblink,最终发现2条语句在另一端的执行的sql语句不同.
--//192.168.56.101 centtest 21c
SYS@book> @ ashtop sql_id 1=1 trunc(sysdate)+16/24+02/1440+08/86400 trunc(sysdate)+16/24+18/1440+13/86400
Total Distinct Distinct Distinct
Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------- ------------------- ------------------- ---------- -------- -----------
449 .5 66% | 2026-07-21 16:02:13 2026-07-21 16:18:12 1 350 350
165 .2 24% | drkdwmx239u2z 2026-07-21 16:02:09 2026-07-21 16:04:41 118 106 161
71 .1 10% | cavnv58uw9hr7 2026-07-21 16:16:33 2026-07-21 16:18:11 35 50 63
SYS@book> @ sql_id drkdwmx239u2z
-- SQL_ID = drkdwmx239u2z come from shared pool
SELECT COUNT("A1"."PAD") FROM "T" "A1" WHERE "A1"."ID"=:B1;
SYS@book> @ sql_id cavnv58uw9hr7
-- SQL_ID = cavnv58uw9hr7 come from shared pool
SELECT "ID","PAD" FROM "T" "T" WHERE "ID"=:1;
--//查看执行计划：
--//sql_id=drkdwmx239u2z
SELECT COUNT("A1"."PAD") FROM "T" "A1" WHERE "A1"."ID"=:B1;
Plan hash value: 3408540097
--------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 10 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_T | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / "A1"@"SEL$1"
3 - SEL$1 / "A1"@"SEL$1"
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A1"."ID"=:B1)
--//sql_id=cavnv58uw9hr7
SELECT "ID","PAD" FROM "T" "T" WHERE "ID"=:1;
Plan hash value: 2454218153
-------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 10 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_T | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / "T"@"SEL$1"
2 - SEL$1 / "T"@"SEL$1"
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:1)
--//单纯看执行计划，前者多了1步SORT AGGREGATE,也许唯一索引的访问方式方式非常特殊.
--//另外写一篇直接使用sql的执行方式。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3176623/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [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
