[20260802]测试遇到的问题.txt
[20260801]测试遇到的问题(11g).txt
--//以前在19c测试过在11g重复测试,而且看了以前的帖子，才发现最近测试写的脚本有点小问题，应该采用类似如下的执行方式：
select /*+ &&3 */ value into v_val from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
--//避免全部执行sql语句共享相同的光标，产生cursor pin：S 等待事件，突出测试的问题。
--//但是这类测试有一些问题，就是测试开始出现latch: shared pool，特别是测试时间很短的情况下，导致误判。
--//而11g下遇到这样的问题，我以前一直以为适当延迟可以规避这个问题，实际上问题依旧，在11g下问题更加明显。
--//感觉在11g下是一个bug。
1.环境：
SCOTT@book> @ ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 11.2.0.4.0
BANNER : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL procedure successfully completed.
2.测试环境建立：
--//create table job_times (sid number, serial# number,time_ela1 number,time_ela2 number ,start_date timestamp,end_date timestamp,method varchar2(20));
$ cat zz5.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_val VARCHAR2(100);
begin
for i in 1 .. &&1 loop
--select /*+ &&3 */ value into v_val from v$nls_parameters where parameter = 'NLS_CHARACTERSET';
--select value into v_val from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
select /*+ &&3 */ value into v_val from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
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 and time_ela2 is null;
commit;
quit
3.测试：
$ zzdate;seq 50 | xargs -IQ -P 50 sqlplus -s -l scott/book @zz5.txt 2e5 P=50 Q >/dev/null;zzdate
trunc(sysdate)+10/24+29/1440+28/86400 -1785551368.770130064
trunc(sysdate)+10/24+30/1440+21/86400 1785551421.677028779
--//Sum = 52.906898715
SYS@book> @ ashtop event 1=1 trunc(sysdate)+10/24+29/1440+28/86400 trunc(sysdate)+10/24+30/1440+21/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ----------------------- ------------------- ------------------- ---------- -------- -----------
1267 23.9 51% | latch: shared pool 2026-08-01 10:29:29 2026-08-01 10:30:19 3 49 51
1187 22.4 48% | 2026-08-01 10:29:29 2026-08-01 10:30:20 719 52 768
6 .1 0% | log file sync 2026-08-01 10:29:30 2026-08-01 10:30:20 1 3 3
3 .1 0% | ADR block file read 2026-08-01 10:29:29 2026-08-01 10:29:32 3 3 3
3 .1 0% | log file parallel write 2026-08-01 10:29:30 2026-08-01 10:30:20 1 3 3
2 .0 0% | library cache: mutex X 2026-08-01 10:29:30 2026-08-01 10:29:30 1 1 1
6 rows selected.
--//第1次执行出现大量的latch: shared pool。
SCOTT@book> select min(START_DATE),max(START_DATE) from job_times where START_DATE<'2026-08-01 10:29:39' order by 1;
MIN(START_DATE) MAX(START_DATE)
-------------------------- --------------------------
2026-08-01 10:29:29.472258 2026-08-01 10:29:33.242298
--//50个并发执行需要4秒全部启动，我有点无法理解的是为什么出现LAST_SEEN记录的2026-08-01 10:30:19。
SYS@book> @ ashtop event "a.user_id=83" trunc(sysdate)+10/24+29/1440+28/86400 trunc(sysdate)+10/24+30/1440+21/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- -------- -----------
1264 23.8 51% | latch: shared pool 2026-08-01 10:29:29 2026-08-01 10:30:19 3 49 51
1185 22.4 48% | 2026-08-01 10:29:29 2026-08-01 10:30:20 719 52 767
6 .1 0% | log file sync 2026-08-01 10:29:30 2026-08-01 10:30:20 1 3 3
3 .1 0% | ADR block file read 2026-08-01 10:29:29 2026-08-01 10:29:32 3 3 3
1 .0 0% | library cache: mutex X 2026-08-01 10:29:30 2026-08-01 10:29:30 1 1 1
--//确实都是测试用户导致的.注意看LAST_SEEN的时间.无法理解为什么在整个测试基本都是存在latch: shared pool有bug吗.
--//再次重复测试：
$ zzdate;seq 50 | xargs -IQ -P 50 sqlplus -s -l scott/book @zz5.txt 2e5 P=50 Q >/dev/null;zzdate
trunc(sysdate)+10/24+34/1440+14/86400 -1785551654.221948878
trunc(sysdate)+10/24+35/1440+04/86400 1785551704.160182948
--//Sum = 49.93823407
SYS@book> @ ashtop event 1=1 trunc(sysdate)+10/24+34/1440+14/86400 trunc(sysdate)+10/24+35/1440+04/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- -------- -----------
2299 46.0 100% | 2026-08-01 10:34:14 2026-08-01 10:35:03 1399 50 1448
2 .0 0% | ADR block file read 2026-08-01 10:34:15 2026-08-01 10:34:18 2 2 2
2 .0 0% | log file parallel write 2026-08-01 10:35:02 2026-08-01 10:35:03 1 2 2
1 .0 0% | log file sync 2026-08-01 10:35:02 2026-08-01 10:35:02 1 1 1
--//第2次测试基本没有latch: shared pool，因为测试语句已经在共享池不许再次分析。
--//我始终认为这是11g的1个bug，如果前面latch: shared pool+为NULL的时间，1264+1185 = 2449。
--//关闭数据库重复测试：
--//先单独并发执行5次.
$ zzdate;seq 50 | xargs -IQ -P 50 sqlplus -s -l scott/book @zz5.txt 5 P=50b Q >/dev/null;zzdate
trunc(sysdate)+11/24+04/1440+00/86400 -1785553440.423898591
trunc(sysdate)+11/24+04/1440+02/86400 1785553442.946298948
--//Sum = 2.522400357
--//再次执行测试:
$ zzdate;seq 50 | xargs -IQ -P 50 sqlplus -s -l scott/book @zz5.txt 2e5 P=50c Q >/dev/null;zzdate
trunc(sysdate)+11/24+04/1440+11/86400 -1785553451.302225597
trunc(sysdate)+11/24+05/1440+01/86400 1785553501.013907647
Sum = 49.71168205
SYS@book> @ ashtop event 1=1 trunc(sysdate)+11/24+04/1440+11/86400 trunc(sysdate)+11/24+05/1440+01/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- -------- -----------
2285 45.7 100% | 2026-08-01 11:04:12 2026-08-01 11:05:00 1436 49 1483
3 .1 0% | log file sync 2026-08-01 11:05:00 2026-08-01 11:05:00 1 1 1
2 .0 0% | ADR block file read 2026-08-01 11:04:12 2026-08-01 11:04:13 2 2 2
1 .0 0% | log file parallel write 2026-08-01 11:05:00 2026-08-01 11:05:00 1 1 1
--//第2次执行就没有这类现象。
SCOTT@book> select method,count(*),round(avg(TIME_ELA2-TIME_ELA1),0),sum(TIME_ELA2-time_ela1) from scott.job_times group by method order by 3;
METHOD COUNT(*) ROUND(AVG(TIME_ELA2-TIME_ELA1),0) SUM(TIME_ELA2-TIME_ELA1)
-------------------- ---------- --------------------------------- ------------------------
P=50b 50 4 190
P=50c 50 4591 229549
--//关闭数据库重复测试：
$ zzdate;seq 50 | xargs -IQ -P 50 sqlplus -s -l scott/book @zz5.txt 5e5 P=50f Q >/dev/null;zzdate
trunc(sysdate)+11/24+22/1440+27/86400 -1785554547.882995878
trunc(sysdate)+11/24+24/1440+35/86400 1785554675.787918064
--//Sum = 127.904922186
SYS@book> @ ashtop event username='SCOTT' trunc(sysdate)+11/24+22/1440+27/86400 trunc(sysdate)+11/24+24/1440+35/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ---------------------- ------------------- ------------------- ---------- -------- -----------
3350 26.2 54% | latch: shared pool 2026-08-01 11:22:30 2026-08-01 11:24:33 2 119 120
2798 21.9 45% | 2026-08-01 11:22:29 2026-08-01 11:24:34 1643 126 1764
6 .0 0% | log file sync 2026-08-01 11:22:31 2026-08-01 11:24:33 1 4 4
3 .0 0% | library cache: mutex X 2026-08-01 11:22:30 2026-08-01 11:22:31 1 2 2
1 .0 0% | ADR block file read 2026-08-01 11:22:31 2026-08-01 11:22:31 1 1 1
--//不大可能一直出现latch: shared pool。
--//关闭数据库重复测试：
$ cat zz5.txt
set verify off
variable v_method varchar2(30);
exec :v_method := '&&2';
host sleep $[&3/10]
insert into job_times values ( sys_context ('userenv', 'sid') ,&3,dbms_utility.get_time ,null , localtimestamp,null ,:v_method) ;
commit ;
declare
v_val VARCHAR2(100);
begin
for i in 1 .. &&1 loop
--select /*+ &&3 */ value into v_val from v$nls_parameters where parameter = 'NLS_CHARACTERSET';
--select value into v_val from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
select /*+ &&3 */ value into v_val from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
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 and time_ela2 is null;
commit;
quit
$ zzdate;seq 50 | xargs -IQ -P 50 sqlplus -s -l scott/book @zz5.txt 5e5 P=50h Q >/dev/null;zzdate
trunc(sysdate)+11/24+33/1440+52/86400 -1785555232.739552172
trunc(sysdate)+11/24+36/1440+00/86400 1785555360.695998027
--//Sum = 127.956445855
SYS@book> @ ashtop event username='SCOTT' trunc(sysdate)+11/24+33/1440+52/86400 trunc(sysdate)+11/24+36/1440+00/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- -------- -----------
3294 25.7 55% | latch: shared pool 2026-08-01 11:33:54 2026-08-01 11:35:59 1 118 118
2715 21.2 45% | 2026-08-01 11:33:54 2026-08-01 11:35:59 1630 125 1747
4 .0 0% | library cache: mutex X 2026-08-01 11:33:54 2026-08-01 11:33:54 1 1 1
3 .0 0% | log file sync 2026-08-01 11:35:57 2026-08-01 11:35:59 1 3 3
1 .0 0% | ADR block file read 2026-08-01 11:33:55 2026-08-01 11:33:55 1 1 1
--//测试从开始到结束一直会出现latch: shared pool。
$ cat zz5.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_val VARCHAR2(100);
begin
for i in 1 .. &&1 loop
--select /*+ &&3 */ value into v_val from v$nls_parameters where parameter = 'NLS_CHARACTERSET';
select value into v_val from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
--select /*+ &&3 */ value into v_val from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
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 and time_ela2 is null;
commit;
quit
$ zzdate;seq 50 | xargs -IQ -P 50 sqlplus -s -l scott/book @zz5.txt 5e5 P=50i Q >/dev/null;zzdate
trunc(sysdate)+11/24+38/1440+16/86400 1785555496.781462512
trunc(sysdate)+11/24+40/1440+21/86400 1785555621.690857130
SYS@book> @ ashtop event username='SCOTT' trunc(sysdate)+11/24+42/1440+28/86400 trunc(sysdate)+11/24+43/1440+19/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- -------- -----------
1666 32.7 71% | 2026-08-01 11:42:29 2026-08-01 11:43:18 1073 49 1120
690 13.5 29% | cursor: pin S 2026-08-01 11:42:29 2026-08-01 11:43:18 1 49 49
2 .0 0% | ADR block file read 2026-08-01 11:42:31 2026-08-01 11:42:32 2 2 2
1 .0 0% | log file sync 2026-08-01 11:43:18 2026-08-01 11:43:18 1 1 1
--//出现等待事件cursor: pin S，因为全部会话的sql语句相同。
--//以前在19c测试过在11g重复测试,而且看了以前的帖子，才发现最近测试写的脚本有点小问题，应该采用类似如下的执行方式：
select /*+ &&3 */ value into v_val from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
--//避免全部执行sql语句共享相同的光标，产生cursor pin：S 等待事件，突出测试的问题。
--//但是这类测试有一些问题，就是测试开始出现latch: shared pool，特别是测试时间很短的情况下，导致误判。
--//而11g下遇到这样的问题，我以前一直以为适当延迟可以规避这个问题，实际上问题依旧，在11g下问题更加明显。
--//感觉在11g下是一个bug。
1.环境：
SCOTT@book> @ ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 11.2.0.4.0
BANNER : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL procedure successfully completed.
2.测试环境建立：
--//create table job_times (sid number, serial# number,time_ela1 number,time_ela2 number ,start_date timestamp,end_date timestamp,method varchar2(20));
$ cat zz5.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_val VARCHAR2(100);
begin
for i in 1 .. &&1 loop
--select /*+ &&3 */ value into v_val from v$nls_parameters where parameter = 'NLS_CHARACTERSET';
--select value into v_val from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
select /*+ &&3 */ value into v_val from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
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 and time_ela2 is null;
commit;
quit
3.测试：
$ zzdate;seq 50 | xargs -IQ -P 50 sqlplus -s -l scott/book @zz5.txt 2e5 P=50 Q >/dev/null;zzdate
trunc(sysdate)+10/24+29/1440+28/86400 -1785551368.770130064
trunc(sysdate)+10/24+30/1440+21/86400 1785551421.677028779
--//Sum = 52.906898715
SYS@book> @ ashtop event 1=1 trunc(sysdate)+10/24+29/1440+28/86400 trunc(sysdate)+10/24+30/1440+21/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ----------------------- ------------------- ------------------- ---------- -------- -----------
1267 23.9 51% | latch: shared pool 2026-08-01 10:29:29 2026-08-01 10:30:19 3 49 51
1187 22.4 48% | 2026-08-01 10:29:29 2026-08-01 10:30:20 719 52 768
6 .1 0% | log file sync 2026-08-01 10:29:30 2026-08-01 10:30:20 1 3 3
3 .1 0% | ADR block file read 2026-08-01 10:29:29 2026-08-01 10:29:32 3 3 3
3 .1 0% | log file parallel write 2026-08-01 10:29:30 2026-08-01 10:30:20 1 3 3
2 .0 0% | library cache: mutex X 2026-08-01 10:29:30 2026-08-01 10:29:30 1 1 1
6 rows selected.
--//第1次执行出现大量的latch: shared pool。
SCOTT@book> select min(START_DATE),max(START_DATE) from job_times where START_DATE<'2026-08-01 10:29:39' order by 1;
MIN(START_DATE) MAX(START_DATE)
-------------------------- --------------------------
2026-08-01 10:29:29.472258 2026-08-01 10:29:33.242298
--//50个并发执行需要4秒全部启动，我有点无法理解的是为什么出现LAST_SEEN记录的2026-08-01 10:30:19。
SYS@book> @ ashtop event "a.user_id=83" trunc(sysdate)+10/24+29/1440+28/86400 trunc(sysdate)+10/24+30/1440+21/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- -------- -----------
1264 23.8 51% | latch: shared pool 2026-08-01 10:29:29 2026-08-01 10:30:19 3 49 51
1185 22.4 48% | 2026-08-01 10:29:29 2026-08-01 10:30:20 719 52 767
6 .1 0% | log file sync 2026-08-01 10:29:30 2026-08-01 10:30:20 1 3 3
3 .1 0% | ADR block file read 2026-08-01 10:29:29 2026-08-01 10:29:32 3 3 3
1 .0 0% | library cache: mutex X 2026-08-01 10:29:30 2026-08-01 10:29:30 1 1 1
--//确实都是测试用户导致的.注意看LAST_SEEN的时间.无法理解为什么在整个测试基本都是存在latch: shared pool有bug吗.
--//再次重复测试：
$ zzdate;seq 50 | xargs -IQ -P 50 sqlplus -s -l scott/book @zz5.txt 2e5 P=50 Q >/dev/null;zzdate
trunc(sysdate)+10/24+34/1440+14/86400 -1785551654.221948878
trunc(sysdate)+10/24+35/1440+04/86400 1785551704.160182948
--//Sum = 49.93823407
SYS@book> @ ashtop event 1=1 trunc(sysdate)+10/24+34/1440+14/86400 trunc(sysdate)+10/24+35/1440+04/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- -------- -----------
2299 46.0 100% | 2026-08-01 10:34:14 2026-08-01 10:35:03 1399 50 1448
2 .0 0% | ADR block file read 2026-08-01 10:34:15 2026-08-01 10:34:18 2 2 2
2 .0 0% | log file parallel write 2026-08-01 10:35:02 2026-08-01 10:35:03 1 2 2
1 .0 0% | log file sync 2026-08-01 10:35:02 2026-08-01 10:35:02 1 1 1
--//第2次测试基本没有latch: shared pool，因为测试语句已经在共享池不许再次分析。
--//我始终认为这是11g的1个bug，如果前面latch: shared pool+为NULL的时间，1264+1185 = 2449。
--//关闭数据库重复测试：
--//先单独并发执行5次.
$ zzdate;seq 50 | xargs -IQ -P 50 sqlplus -s -l scott/book @zz5.txt 5 P=50b Q >/dev/null;zzdate
trunc(sysdate)+11/24+04/1440+00/86400 -1785553440.423898591
trunc(sysdate)+11/24+04/1440+02/86400 1785553442.946298948
--//Sum = 2.522400357
--//再次执行测试:
$ zzdate;seq 50 | xargs -IQ -P 50 sqlplus -s -l scott/book @zz5.txt 2e5 P=50c Q >/dev/null;zzdate
trunc(sysdate)+11/24+04/1440+11/86400 -1785553451.302225597
trunc(sysdate)+11/24+05/1440+01/86400 1785553501.013907647
Sum = 49.71168205
SYS@book> @ ashtop event 1=1 trunc(sysdate)+11/24+04/1440+11/86400 trunc(sysdate)+11/24+05/1440+01/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- -------- -----------
2285 45.7 100% | 2026-08-01 11:04:12 2026-08-01 11:05:00 1436 49 1483
3 .1 0% | log file sync 2026-08-01 11:05:00 2026-08-01 11:05:00 1 1 1
2 .0 0% | ADR block file read 2026-08-01 11:04:12 2026-08-01 11:04:13 2 2 2
1 .0 0% | log file parallel write 2026-08-01 11:05:00 2026-08-01 11:05:00 1 1 1
--//第2次执行就没有这类现象。
SCOTT@book> select method,count(*),round(avg(TIME_ELA2-TIME_ELA1),0),sum(TIME_ELA2-time_ela1) from scott.job_times group by method order by 3;
METHOD COUNT(*) ROUND(AVG(TIME_ELA2-TIME_ELA1),0) SUM(TIME_ELA2-TIME_ELA1)
-------------------- ---------- --------------------------------- ------------------------
P=50b 50 4 190
P=50c 50 4591 229549
--//关闭数据库重复测试：
$ zzdate;seq 50 | xargs -IQ -P 50 sqlplus -s -l scott/book @zz5.txt 5e5 P=50f Q >/dev/null;zzdate
trunc(sysdate)+11/24+22/1440+27/86400 -1785554547.882995878
trunc(sysdate)+11/24+24/1440+35/86400 1785554675.787918064
--//Sum = 127.904922186
SYS@book> @ ashtop event username='SCOTT' trunc(sysdate)+11/24+22/1440+27/86400 trunc(sysdate)+11/24+24/1440+35/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ---------------------- ------------------- ------------------- ---------- -------- -----------
3350 26.2 54% | latch: shared pool 2026-08-01 11:22:30 2026-08-01 11:24:33 2 119 120
2798 21.9 45% | 2026-08-01 11:22:29 2026-08-01 11:24:34 1643 126 1764
6 .0 0% | log file sync 2026-08-01 11:22:31 2026-08-01 11:24:33 1 4 4
3 .0 0% | library cache: mutex X 2026-08-01 11:22:30 2026-08-01 11:22:31 1 2 2
1 .0 0% | ADR block file read 2026-08-01 11:22:31 2026-08-01 11:22:31 1 1 1
--//不大可能一直出现latch: shared pool。
--//关闭数据库重复测试：
$ cat zz5.txt
set verify off
variable v_method varchar2(30);
exec :v_method := '&&2';
host sleep $[&3/10]
insert into job_times values ( sys_context ('userenv', 'sid') ,&3,dbms_utility.get_time ,null , localtimestamp,null ,:v_method) ;
commit ;
declare
v_val VARCHAR2(100);
begin
for i in 1 .. &&1 loop
--select /*+ &&3 */ value into v_val from v$nls_parameters where parameter = 'NLS_CHARACTERSET';
--select value into v_val from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
select /*+ &&3 */ value into v_val from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
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 and time_ela2 is null;
commit;
quit
$ zzdate;seq 50 | xargs -IQ -P 50 sqlplus -s -l scott/book @zz5.txt 5e5 P=50h Q >/dev/null;zzdate
trunc(sysdate)+11/24+33/1440+52/86400 -1785555232.739552172
trunc(sysdate)+11/24+36/1440+00/86400 1785555360.695998027
--//Sum = 127.956445855
SYS@book> @ ashtop event username='SCOTT' trunc(sysdate)+11/24+33/1440+52/86400 trunc(sysdate)+11/24+36/1440+00/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- -------- -----------
3294 25.7 55% | latch: shared pool 2026-08-01 11:33:54 2026-08-01 11:35:59 1 118 118
2715 21.2 45% | 2026-08-01 11:33:54 2026-08-01 11:35:59 1630 125 1747
4 .0 0% | library cache: mutex X 2026-08-01 11:33:54 2026-08-01 11:33:54 1 1 1
3 .0 0% | log file sync 2026-08-01 11:35:57 2026-08-01 11:35:59 1 3 3
1 .0 0% | ADR block file read 2026-08-01 11:33:55 2026-08-01 11:33:55 1 1 1
--//测试从开始到结束一直会出现latch: shared pool。
$ cat zz5.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_val VARCHAR2(100);
begin
for i in 1 .. &&1 loop
--select /*+ &&3 */ value into v_val from v$nls_parameters where parameter = 'NLS_CHARACTERSET';
select value into v_val from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
--select /*+ &&3 */ value into v_val from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
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 and time_ela2 is null;
commit;
quit
$ zzdate;seq 50 | xargs -IQ -P 50 sqlplus -s -l scott/book @zz5.txt 5e5 P=50i Q >/dev/null;zzdate
trunc(sysdate)+11/24+38/1440+16/86400 1785555496.781462512
trunc(sysdate)+11/24+40/1440+21/86400 1785555621.690857130
SYS@book> @ ashtop event username='SCOTT' trunc(sysdate)+11/24+42/1440+28/86400 trunc(sysdate)+11/24+43/1440+19/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- -------- -----------
1666 32.7 71% | 2026-08-01 11:42:29 2026-08-01 11:43:18 1073 49 1120
690 13.5 29% | cursor: pin S 2026-08-01 11:42:29 2026-08-01 11:43:18 1 49 49
2 .0 0% | ADR block file read 2026-08-01 11:42:31 2026-08-01 11:42:32 2 2 2
1 .0 0% | log file sync 2026-08-01 11:43:18 2026-08-01 11:43:18 1 1 1
--//出现等待事件cursor: pin S，因为全部会话的sql语句相同。
浙公网安备 33010602011771号