[20260810]测试遇到的问题(21c).txt
[20260810]测试遇到的问题(21c).txt
--//昨天测试遇到的问题，单独拿出来测试验证看看。
1.环境：
SYS@book> @ 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.测试脚本：
$ cat zz6.txt
set verify off
variable v_method varchar2(30);
exec :v_method := '&&2';
declare
v_sid number;
v_d date;
v varchar2(30);
begin
for i in 1 .. &&1 loop
select /*+ &3 */ sid into v_sid from v$mystat where rownum=1;
end loop;
end ;
/
quit
3.测试:
$ zzdate;seq 50 | xargs -IQ -P 50 sqlplus -s -l '/ as sysdba' @zz6.txt 5e4 test1 Q > /dev/null ; zzdate
trunc(sysdate)+15/24+56/1440+54/86400 1786348614.787483379
trunc(sysdate)+15/24+58/1440+49/86400 1786348729.688806107
SYS@book> @ ashtop event 1=1 trunc(sysdate)+15/24+56/1440+54/86400 trunc(sysdate)+15/24+58/1440+49/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- -------- -----------
3496 30.4 65% | library cache: bucket mutex X 2026-08-10 15:56:58 2026-08-10 15:58:48 1 110 110
1077 9.4 20% | 2026-08-10 15:56:57 2026-08-10 15:58:48 88 84 171
526 4.6 10% | library cache: mutex X 2026-08-10 15:56:58 2026-08-10 15:58:47 1 91 91
217 1.9 4% | cursor: pin S 2026-08-10 15:56:59 2026-08-10 15:58:45 1 31 31
55 .5 1% | row cache mutex 2026-08-10 15:57:07 2026-08-10 15:58:40 1 12 12
2 .0 0% | latch: session allocation 2026-08-10 15:58:40 2026-08-10 15:58:40 1 1 1
6 rows selected.
SYS@book> @ ashtop sql_id "event='cursor: pin S'" trunc(sysdate)+15/24+56/1440+54/86400 trunc(sysdate)+15/24+58/1440+49/86400
Total Distinct Distinct Distinct
Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------- ------------------- ------------------- ---------- -------- -----------
8 .1 4% | g8x7q59nz3mpv 2026-08-10 15:57:21 2026-08-10 15:58:45 1 8 8
7 .1 3% | 5c157xy6xq0n4 2026-08-10 15:57:22 2026-08-10 15:58:45 1 7 7
7 .1 3% | 78cxpg28svqpj 2026-08-10 15:57:22 2026-08-10 15:58:45 1 7 7
7 .1 3% | cn2wzv5yf8nq4 2026-08-10 15:57:02 2026-08-10 15:58:28 1 7 7
6 .1 3% | 2aaszq9pf8vtz 2026-08-10 15:57:02 2026-08-10 15:58:26 1 6 6
6 .1 3% | 3gg3hjbjgmk33 2026-08-10 15:56:59 2026-08-10 15:57:50 1 6 6
6 .1 3% | 4p56r9st12adu 2026-08-10 15:57:02 2026-08-10 15:58:11 1 6 6
6 .1 3% | 62w2j4vfysckw 2026-08-10 15:57:02 2026-08-10 15:58:41 1 6 6
6 .1 3% | 76pxdzrwbhtkp 2026-08-10 15:57:21 2026-08-10 15:58:28 1 6 6
6 .1 3% | 9c208na662r98 2026-08-10 15:57:02 2026-08-10 15:58:45 1 6 6
6 .1 3% | 9vybp8x74uzwh 2026-08-10 15:57:02 2026-08-10 15:58:45 1 6 6
6 .1 3% | atnzy8vf7g7wz 2026-08-10 15:57:22 2026-08-10 15:58:11 1 6 6
6 .1 3% | baktqpmk9whkc 2026-08-10 15:57:22 2026-08-10 15:58:45 1 6 6
6 .1 3% | bnmu3t39ant67 2026-08-10 15:57:21 2026-08-10 15:58:43 1 6 6
5 .0 2% | 15fnsmsx7v1z5 2026-08-10 15:57:02 2026-08-10 15:58:43 1 5 5
5 .0 2% | 1f132zkxfdsff 2026-08-10 15:57:33 2026-08-10 15:58:45 1 5 5
5 .0 2% | 6gsum6f9mywg3 2026-08-10 15:57:33 2026-08-10 15:58:28 1 5 5
5 .0 2% | 71mfb2qvjxu3j 2026-08-10 15:57:02 2026-08-10 15:57:45 1 5 5
5 .0 2% | 8pf13s4mdpxry 2026-08-10 15:57:02 2026-08-10 15:58:11 1 5 5
5 .0 2% | b60dp39qnu74f 2026-08-10 15:57:05 2026-08-10 15:58:41 1 5 5
5 .0 2% | bawypjt46kmwf 2026-08-10 15:57:21 2026-08-10 15:58:44 1 5 5
5 .0 2% | bgskrbcqjcqqm 2026-08-10 15:56:59 2026-08-10 15:57:44 1 5 5
4 .0 2% | 4mwvtqps48qws 2026-08-10 15:57:22 2026-08-10 15:58:29 1 4 4
4 .0 2% | 741w1vq59uyyp 2026-08-10 15:57:29 2026-08-10 15:58:45 1 4 4
4 .0 2% | 7q6dj1c8ghcs8 2026-08-10 15:57:22 2026-08-10 15:58:45 1 4 4
4 .0 2% | 7x567akgakrrw 2026-08-10 15:57:21 2026-08-10 15:58:45 1 4 4
4 .0 2% | 7ztzuuy4rsxuz 2026-08-10 15:57:33 2026-08-10 15:58:29 1 4 4
4 .0 2% | 833prrj3jjxwn 2026-08-10 15:57:21 2026-08-10 15:58:11 1 4 4
4 .0 2% | a9gxhxat2ggku 2026-08-10 15:57:21 2026-08-10 15:58:45 1 4 4
4 .0 2% | ah919yn3tfz2n 2026-08-10 15:57:33 2026-08-10 15:58:45 1 4 4
4 .0 2% | am293p57gjmjm 2026-08-10 15:57:33 2026-08-10 15:58:28 1 4 4
4 .0 2% | axamumba1w65w 2026-08-10 15:57:32 2026-08-10 15:57:50 1 4 4
4 .0 2% | c3asc9ad6jkyn 2026-08-10 15:57:05 2026-08-10 15:58:28 1 4 4
4 .0 2% | chvsgnrqv1btt 2026-08-10 15:57:21 2026-08-10 15:58:11 1 4 4
3 .0 1% | 12ad3xxc1thsb 2026-08-10 15:57:22 2026-08-10 15:58:11 1 3 3
3 .0 1% | 419vmwa56rw04 2026-08-10 15:57:33 2026-08-10 15:58:28 1 3 3
3 .0 1% | 5g0y1w583nk3t 2026-08-10 15:57:21 2026-08-10 15:58:45 1 3 3
3 .0 1% | 734pkm69672d7 2026-08-10 15:57:21 2026-08-10 15:58:28 1 3 3
3 .0 1% | 8gu2kwkjg7p3s 2026-08-10 15:57:50 2026-08-10 15:58:45 1 3 3
3 .0 1% | amdmw8xj36n7c 2026-08-10 15:57:02 2026-08-10 15:58:28 1 3 3
3 .0 1% | b2w02tuan75u5 2026-08-10 15:57:22 2026-08-10 15:58:11 1 3 3
3 .0 1% | bzk4yak4aawfh 2026-08-10 15:57:02 2026-08-10 15:58:43 1 3 3
3 .0 1% | bzra3vxn69f74 2026-08-10 15:57:21 2026-08-10 15:58:33 1 3 3
3 .0 1% | d60fd5knrctpw 2026-08-10 15:57:21 2026-08-10 15:57:44 1 3 3
3 .0 1% | dc6s11p78a6mz 2026-08-10 15:57:21 2026-08-10 15:58:11 1 3 3
2 .0 1% | 38zpw13kzd460 2026-08-10 15:57:50 2026-08-10 15:58:11 1 2 2
2 .0 1% | 9c3d963kupdq6 2026-08-10 15:57:02 2026-08-10 15:58:34 1 2 2
1 .0 0% | 2qrrasv9th9ja 2026-08-10 15:57:40 2026-08-10 15:57:40 1 1 1
1 .0 0% | 3t0fqj3wn2jwz 2026-08-10 15:58:11 2026-08-10 15:58:11 1 1 1
1 .0 0% | 8v0jhtup8j23m 2026-08-10 15:58:03 2026-08-10 15:58:03 1 1 1
50 rows selected.
--//不应该在这些语句出现cursor: pin S的等待事件，这些语句都是分散的。
--//前几天单独跟踪发现递归执行如下：
SYS@book> @ sql_id 87gaftwrm2h68
-- SQL_ID = 87gaftwrm2h68 come from shared pool
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$ o where o.obj#=:1;
SYS@book> @ ashtop event sql_id='87gaftwrm2h68' trunc(sysdate)+15/24+56/1440+54/86400 trunc(sysdate)+15/24+58/1440+49/86400
no rows selected
--//反而sql_id='87gaftwrm2h68'语句没有出现ashtop输出里面。
SYS@book> @ ashtop p1raw,p1 "event='cursor: pin S'" trunc(sysdate)+15/24+56/1440+54/86400 trunc(sysdate)+15/24+58/1440+49/86400
Total Distinct Distinct Distinct
Seconds AAS %This P1RAW P1 FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ----------------- ---------- ------------------- ------------------- ---------- -------- -----------
217 1.9 100% | 000000002F3140C8 791757000 2026-08-10 15:56:59 2026-08-10 15:58:45 1 31 31
--//注意看P1=791757000，而前面的sql_id都是不相同的，hash值应该也不同。
SYS@book> @ s2h 87gaftwrm2h68
SQL_ID HASH_VALUE HASH_HEX KGL_BUCKET KGL_BUCKET_HEX
------------- ----------- --------- ---------- --------------
87gaftwrm2h68 791757000 2f3140c8 82120 0x140c8
--//只有查看P1值才能准确定位问题语句。
SYS@book> @ sharepool/shp4 '' 791757000
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLHDBID KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- --------------------- ---------- ------------- ---------- ----------
child handle address 0000000073AC5930 0000000073AC7090 select o.owner#,o.name,o.namespace,o.rem 1 0 2 0000000073AC5770 0000000073AC66A0 4032 16176 21018 41226 41226 791757000 87gaftwrm2h68 82120 0
child handle address 000000007089A8A8 0000000073AC7090 select o.owner#,o.name,o.namespace,o.rem 1 0 1 000000007080E7B8 0000000073AC6B70 4032 16176 21018 41226 41226 791757000 87gaftwrm2h68 82120 1
child handle address 000000007080F978 0000000073AC7090 select o.owner#,o.name,o.namespace,o.rem 1 0 1 00000000706AB9D8 00000000708105E0 4032 16176 21018 41226 41226 791757000 87gaftwrm2h68 82120 2
child handle address 000000006FBD82A0 0000000073AC7090 select o.owner#,o.name,o.namespace,o.rem 0 0 2 000000006F7764B0 00000000706D7628 4032 16176 21018 41226 41226 791757000 87gaftwrm2h68 82120 3
child handle address 000000006DBA6908 0000000073AC7090 select o.owner#,o.name,o.namespace,o.rem 0 0 2 00000000687089F0 00000000706D7950 4032 16176 21018 41226 41226 791757000 87gaftwrm2h68 82120 4
child handle address 000000006D826518 0000000073AC7090 select o.owner#,o.name,o.namespace,o.rem 0 0 1 00000000688AAD00 0000000070810928 4032 16176 21018 41226 41226 791757000 87gaftwrm2h68 82120 5
child handle address 00000000699BB2A0 0000000073AC7090 select o.owner#,o.name,o.namespace,o.rem 0 0 1 00000000699BB0E0 00000000699BBA88 4032 16176 21018 41226 41226 791757000 87gaftwrm2h68 82120 6
parent handle address 0000000073AC7090 0000000073AC7090 select o.owner#,o.name,o.namespace,o.rem 1 0 10 0000000073AC6ED0 00 24384 0 0 24384 24384 791757000 87gaftwrm2h68 82120 65535
8 rows selected.
--//产生7个子光标。
SYS@book> @ gunshare 87gaftwrm2h68
--- host vim /tmp/unshare.tmp
--- host cat /tmp/unshare.tmp
REASON_NOT_SHARED CURSORS SQL_IDS
----------------------------- ---------- ----------
OPTIMIZER_MISMATCH 7 1
--//以后分析注意这些细节。
--//昨天测试遇到的问题，单独拿出来测试验证看看。
1.环境：
SYS@book> @ 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.测试脚本：
$ cat zz6.txt
set verify off
variable v_method varchar2(30);
exec :v_method := '&&2';
declare
v_sid number;
v_d date;
v varchar2(30);
begin
for i in 1 .. &&1 loop
select /*+ &3 */ sid into v_sid from v$mystat where rownum=1;
end loop;
end ;
/
quit
3.测试:
$ zzdate;seq 50 | xargs -IQ -P 50 sqlplus -s -l '/ as sysdba' @zz6.txt 5e4 test1 Q > /dev/null ; zzdate
trunc(sysdate)+15/24+56/1440+54/86400 1786348614.787483379
trunc(sysdate)+15/24+58/1440+49/86400 1786348729.688806107
SYS@book> @ ashtop event 1=1 trunc(sysdate)+15/24+56/1440+54/86400 trunc(sysdate)+15/24+58/1440+49/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- -------- -----------
3496 30.4 65% | library cache: bucket mutex X 2026-08-10 15:56:58 2026-08-10 15:58:48 1 110 110
1077 9.4 20% | 2026-08-10 15:56:57 2026-08-10 15:58:48 88 84 171
526 4.6 10% | library cache: mutex X 2026-08-10 15:56:58 2026-08-10 15:58:47 1 91 91
217 1.9 4% | cursor: pin S 2026-08-10 15:56:59 2026-08-10 15:58:45 1 31 31
55 .5 1% | row cache mutex 2026-08-10 15:57:07 2026-08-10 15:58:40 1 12 12
2 .0 0% | latch: session allocation 2026-08-10 15:58:40 2026-08-10 15:58:40 1 1 1
6 rows selected.
SYS@book> @ ashtop sql_id "event='cursor: pin S'" trunc(sysdate)+15/24+56/1440+54/86400 trunc(sysdate)+15/24+58/1440+49/86400
Total Distinct Distinct Distinct
Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------- ------------------- ------------------- ---------- -------- -----------
8 .1 4% | g8x7q59nz3mpv 2026-08-10 15:57:21 2026-08-10 15:58:45 1 8 8
7 .1 3% | 5c157xy6xq0n4 2026-08-10 15:57:22 2026-08-10 15:58:45 1 7 7
7 .1 3% | 78cxpg28svqpj 2026-08-10 15:57:22 2026-08-10 15:58:45 1 7 7
7 .1 3% | cn2wzv5yf8nq4 2026-08-10 15:57:02 2026-08-10 15:58:28 1 7 7
6 .1 3% | 2aaszq9pf8vtz 2026-08-10 15:57:02 2026-08-10 15:58:26 1 6 6
6 .1 3% | 3gg3hjbjgmk33 2026-08-10 15:56:59 2026-08-10 15:57:50 1 6 6
6 .1 3% | 4p56r9st12adu 2026-08-10 15:57:02 2026-08-10 15:58:11 1 6 6
6 .1 3% | 62w2j4vfysckw 2026-08-10 15:57:02 2026-08-10 15:58:41 1 6 6
6 .1 3% | 76pxdzrwbhtkp 2026-08-10 15:57:21 2026-08-10 15:58:28 1 6 6
6 .1 3% | 9c208na662r98 2026-08-10 15:57:02 2026-08-10 15:58:45 1 6 6
6 .1 3% | 9vybp8x74uzwh 2026-08-10 15:57:02 2026-08-10 15:58:45 1 6 6
6 .1 3% | atnzy8vf7g7wz 2026-08-10 15:57:22 2026-08-10 15:58:11 1 6 6
6 .1 3% | baktqpmk9whkc 2026-08-10 15:57:22 2026-08-10 15:58:45 1 6 6
6 .1 3% | bnmu3t39ant67 2026-08-10 15:57:21 2026-08-10 15:58:43 1 6 6
5 .0 2% | 15fnsmsx7v1z5 2026-08-10 15:57:02 2026-08-10 15:58:43 1 5 5
5 .0 2% | 1f132zkxfdsff 2026-08-10 15:57:33 2026-08-10 15:58:45 1 5 5
5 .0 2% | 6gsum6f9mywg3 2026-08-10 15:57:33 2026-08-10 15:58:28 1 5 5
5 .0 2% | 71mfb2qvjxu3j 2026-08-10 15:57:02 2026-08-10 15:57:45 1 5 5
5 .0 2% | 8pf13s4mdpxry 2026-08-10 15:57:02 2026-08-10 15:58:11 1 5 5
5 .0 2% | b60dp39qnu74f 2026-08-10 15:57:05 2026-08-10 15:58:41 1 5 5
5 .0 2% | bawypjt46kmwf 2026-08-10 15:57:21 2026-08-10 15:58:44 1 5 5
5 .0 2% | bgskrbcqjcqqm 2026-08-10 15:56:59 2026-08-10 15:57:44 1 5 5
4 .0 2% | 4mwvtqps48qws 2026-08-10 15:57:22 2026-08-10 15:58:29 1 4 4
4 .0 2% | 741w1vq59uyyp 2026-08-10 15:57:29 2026-08-10 15:58:45 1 4 4
4 .0 2% | 7q6dj1c8ghcs8 2026-08-10 15:57:22 2026-08-10 15:58:45 1 4 4
4 .0 2% | 7x567akgakrrw 2026-08-10 15:57:21 2026-08-10 15:58:45 1 4 4
4 .0 2% | 7ztzuuy4rsxuz 2026-08-10 15:57:33 2026-08-10 15:58:29 1 4 4
4 .0 2% | 833prrj3jjxwn 2026-08-10 15:57:21 2026-08-10 15:58:11 1 4 4
4 .0 2% | a9gxhxat2ggku 2026-08-10 15:57:21 2026-08-10 15:58:45 1 4 4
4 .0 2% | ah919yn3tfz2n 2026-08-10 15:57:33 2026-08-10 15:58:45 1 4 4
4 .0 2% | am293p57gjmjm 2026-08-10 15:57:33 2026-08-10 15:58:28 1 4 4
4 .0 2% | axamumba1w65w 2026-08-10 15:57:32 2026-08-10 15:57:50 1 4 4
4 .0 2% | c3asc9ad6jkyn 2026-08-10 15:57:05 2026-08-10 15:58:28 1 4 4
4 .0 2% | chvsgnrqv1btt 2026-08-10 15:57:21 2026-08-10 15:58:11 1 4 4
3 .0 1% | 12ad3xxc1thsb 2026-08-10 15:57:22 2026-08-10 15:58:11 1 3 3
3 .0 1% | 419vmwa56rw04 2026-08-10 15:57:33 2026-08-10 15:58:28 1 3 3
3 .0 1% | 5g0y1w583nk3t 2026-08-10 15:57:21 2026-08-10 15:58:45 1 3 3
3 .0 1% | 734pkm69672d7 2026-08-10 15:57:21 2026-08-10 15:58:28 1 3 3
3 .0 1% | 8gu2kwkjg7p3s 2026-08-10 15:57:50 2026-08-10 15:58:45 1 3 3
3 .0 1% | amdmw8xj36n7c 2026-08-10 15:57:02 2026-08-10 15:58:28 1 3 3
3 .0 1% | b2w02tuan75u5 2026-08-10 15:57:22 2026-08-10 15:58:11 1 3 3
3 .0 1% | bzk4yak4aawfh 2026-08-10 15:57:02 2026-08-10 15:58:43 1 3 3
3 .0 1% | bzra3vxn69f74 2026-08-10 15:57:21 2026-08-10 15:58:33 1 3 3
3 .0 1% | d60fd5knrctpw 2026-08-10 15:57:21 2026-08-10 15:57:44 1 3 3
3 .0 1% | dc6s11p78a6mz 2026-08-10 15:57:21 2026-08-10 15:58:11 1 3 3
2 .0 1% | 38zpw13kzd460 2026-08-10 15:57:50 2026-08-10 15:58:11 1 2 2
2 .0 1% | 9c3d963kupdq6 2026-08-10 15:57:02 2026-08-10 15:58:34 1 2 2
1 .0 0% | 2qrrasv9th9ja 2026-08-10 15:57:40 2026-08-10 15:57:40 1 1 1
1 .0 0% | 3t0fqj3wn2jwz 2026-08-10 15:58:11 2026-08-10 15:58:11 1 1 1
1 .0 0% | 8v0jhtup8j23m 2026-08-10 15:58:03 2026-08-10 15:58:03 1 1 1
50 rows selected.
--//不应该在这些语句出现cursor: pin S的等待事件，这些语句都是分散的。
--//前几天单独跟踪发现递归执行如下：
SYS@book> @ sql_id 87gaftwrm2h68
-- SQL_ID = 87gaftwrm2h68 come from shared pool
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$ o where o.obj#=:1;
SYS@book> @ ashtop event sql_id='87gaftwrm2h68' trunc(sysdate)+15/24+56/1440+54/86400 trunc(sysdate)+15/24+58/1440+49/86400
no rows selected
--//反而sql_id='87gaftwrm2h68'语句没有出现ashtop输出里面。
SYS@book> @ ashtop p1raw,p1 "event='cursor: pin S'" trunc(sysdate)+15/24+56/1440+54/86400 trunc(sysdate)+15/24+58/1440+49/86400
Total Distinct Distinct Distinct
Seconds AAS %This P1RAW P1 FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ----------------- ---------- ------------------- ------------------- ---------- -------- -----------
217 1.9 100% | 000000002F3140C8 791757000 2026-08-10 15:56:59 2026-08-10 15:58:45 1 31 31
--//注意看P1=791757000，而前面的sql_id都是不相同的，hash值应该也不同。
SYS@book> @ s2h 87gaftwrm2h68
SQL_ID HASH_VALUE HASH_HEX KGL_BUCKET KGL_BUCKET_HEX
------------- ----------- --------- ---------- --------------
87gaftwrm2h68 791757000 2f3140c8 82120 0x140c8
--//只有查看P1值才能准确定位问题语句。
SYS@book> @ sharepool/shp4 '' 791757000
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLHDBID KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- --------------------- ---------- ------------- ---------- ----------
child handle address 0000000073AC5930 0000000073AC7090 select o.owner#,o.name,o.namespace,o.rem 1 0 2 0000000073AC5770 0000000073AC66A0 4032 16176 21018 41226 41226 791757000 87gaftwrm2h68 82120 0
child handle address 000000007089A8A8 0000000073AC7090 select o.owner#,o.name,o.namespace,o.rem 1 0 1 000000007080E7B8 0000000073AC6B70 4032 16176 21018 41226 41226 791757000 87gaftwrm2h68 82120 1
child handle address 000000007080F978 0000000073AC7090 select o.owner#,o.name,o.namespace,o.rem 1 0 1 00000000706AB9D8 00000000708105E0 4032 16176 21018 41226 41226 791757000 87gaftwrm2h68 82120 2
child handle address 000000006FBD82A0 0000000073AC7090 select o.owner#,o.name,o.namespace,o.rem 0 0 2 000000006F7764B0 00000000706D7628 4032 16176 21018 41226 41226 791757000 87gaftwrm2h68 82120 3
child handle address 000000006DBA6908 0000000073AC7090 select o.owner#,o.name,o.namespace,o.rem 0 0 2 00000000687089F0 00000000706D7950 4032 16176 21018 41226 41226 791757000 87gaftwrm2h68 82120 4
child handle address 000000006D826518 0000000073AC7090 select o.owner#,o.name,o.namespace,o.rem 0 0 1 00000000688AAD00 0000000070810928 4032 16176 21018 41226 41226 791757000 87gaftwrm2h68 82120 5
child handle address 00000000699BB2A0 0000000073AC7090 select o.owner#,o.name,o.namespace,o.rem 0 0 1 00000000699BB0E0 00000000699BBA88 4032 16176 21018 41226 41226 791757000 87gaftwrm2h68 82120 6
parent handle address 0000000073AC7090 0000000073AC7090 select o.owner#,o.name,o.namespace,o.rem 1 0 10 0000000073AC6ED0 00 24384 0 0 24384 24384 791757000 87gaftwrm2h68 82120 65535
8 rows selected.
--//产生7个子光标。
SYS@book> @ gunshare 87gaftwrm2h68
--- host vim /tmp/unshare.tmp
--- host cat /tmp/unshare.tmp
REASON_NOT_SHARED CURSORS SQL_IDS
----------------------------- ---------- ----------
OPTIMIZER_MISMATCH 7 1
--//以后分析注意这些细节。
浙公网安备 33010602011771号