马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。
您需要 登录 才可以下载或查看，没有账号？立即注册
×
[20260604]为什么cdb与pdb下麋集实行性能差异巨大.txt
--//上午在测试取sid的最佳方法时，发现通过麋集实行select sid into v_sid from v$mystat where rownum=1;，cdb与pdb下实行性能
--//差异巨大，单独写一篇文章过细分析。
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.
SYS@book> show parameter open_cursor
PARAMETER_NAME TYPE VALUE
-------------- ------- ------
open_cursors integer 300
2.创建测试脚本:
--//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.
$ cat z1.txt
set verify off
variable vmethod varchar2(30);
exec :vmethod := '&&2';
insert into job_times values ( sys_context ('userenv', 'sid') ,&3,dbms_utility.get_time ,null , localtimestamp,null ,:v_method) ;
commit ;
declare
v_sid number;
v_d date;
v varchar2(30);
begin
for i in 1 .. &&1 loop
&&4 v_sid := sys_context ('userenv', 'sid');
&&5 v_sid := userenv('sid');
&&6 select sid into v_sid from v$mystat where rownum=1;
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.测试pdb下sys用户的实行:
--//pdb下测试：
--//create table job_times (sid number, serial# number,time_ela1 number,time_ela2 number ,start_date timestamp,end_date timestamp,method varchar2(20));
$ zzdate ; seq 50 | xargs -IQ -P 50 sqlplus -s -l 'sys/bookbook@book01p as sysdba' @z1.txt 1e6 sys.select50 Q -- -- '' > /dev/null ; zzdate
trunc(sysdate)+15/24+37/1440+29/86400 1780558649.126457001
trunc(sysdate)+15/24+41/1440+43/86400 1780558903.288545411
SYS@book> @ ashtop event 1=1 trunc(sysdate)+15/24+37/1440+29/86400 trunc(sysdate)+15/24+41/1440+43/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- -------- -----------
8730 34.4 71% | 2026-06-04 15:37:29 2026-06-04 15:41:41 4500 252 4751
3588 14.1 29% | cursor: pin S 2026-06-04 15:37:33 2026-06-04 15:41:41 1 235 235
7 .0 0% | db file sequential read 2026-06-04 15:37:30 2026-06-04 15:41:34 3 7 7
6 .0 0% | LGWR all worker groups 2026-06-04 15:37:52 2026-06-04 15:38:46 1 6 6
3 .0 0% | db file async I/O submit 2026-06-04 15:37:46 2026-06-04 15:37:52 1 3 3
3 .0 0% | log file parallel write 2026-06-04 15:37:31 2026-06-04 15:41:39 1 3 3
2 .0 0% | library cache load lock 2026-06-04 15:37:30 2026-06-04 15:37:30 1 1 1
2 .0 0% | log file sync 2026-06-04 15:37:31 2026-06-04 15:41:39 1 2 2
1 .0 0% | db file scattered read 2026-06-04 15:38:17 2026-06-04 15:38:17 1 1 1
1 .0 0% | local write wait 2026-06-04 15:38:29 2026-06-04 15:38:29 1 1 1
10 rows selected.
select method,count(*),round(avg(TIME_ELA2-TIME_ELA1),0),sum(TIME_ELA2-time_ela1) from sys.job_times where method like '%50' group by method
union all
select method,count(*),round(avg(TIME_ELA2-TIME_ELA1),0),sum(TIME_ELA2-time_ela1) from scott.job_times where method like '%50' group by method order by 3
/
SYS@book01p> /
METHOD COUNT(*) ROUND(AVG(TIME_ELA2-TIME_ELA1),0) SUM(TIME_ELA2-TIME_ELA1)
-------------------- ---------- --------------------------------- ------------------------
sys_context50 50 1022 51110
userenv50 50 1917 95866
select50 50 21591 1079563
sys.select50 50 24785 1239225
--//scott用户与sys用户实行相差并不显着。
4.测试cdb下sys用户的实行:
--//cdb下测试：
--//create table job_times (sid number, serial# number,time_ela1 number,time_ela2 number ,start_date timestamp,end_date timestamp,method varchar2(20));
$ zzdate ; seq 50 | xargs -IQ -P 50 sqlplus -s -l '/ as sysdba' @z1.txt 1e6 cdb.sys.select50 Q -- -- '' > /dev/null ; zzdate
trunc(sysdate)+16/24+00/1440+35/86400 -1780560035.038417454
trunc(sysdate)+16/24+33/1440+26/86400 1780562006.924597146
--//Sum = 1971.886179692
SYS@book> select method,count(*),round(avg(TIME_ELA2-TIME_ELA1),0),sum(TIME_ELA2-time_ela1) from job_times where method like '%50' group by method order by 3;
METHOD COUNT(*) ROUND(AVG(TIME_ELA2-TIME_ELA1),0) SUM(TIME_ELA2-TIME_ELA1)
-------------------- ---------- --------------------------------- ------------------------
cdb.sys.select50 50 195387 9769364
--//可以发现同样的sql语句,麋集实行pdb下比cdb下快许多。均匀实行时间24785，195387，两者相差195387/24785 = 7.88倍。
SYS@book> @ ashtop event 1=1 trunc(sysdate)+16/24+00/1440+35/86400 trunc(sysdate)+16/24+33/1440+26/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ----------------------------- ------------------- ------------------- ---------- -------- -----------
15830 8.0 65% | library cache: bucket mutex X 2026-06-04 16:25:02 2026-06-04 16:33:25 1 501 501
4930 2.5 20% | 2026-06-04 16:25:02 2026-06-04 16:33:25 424 446 867
1881 1.0 8% | library cache: mutex X 2026-06-04 16:25:02 2026-06-04 16:33:24 1 425 425
1277 .6 5% | cursor: pin S 2026-06-04 16:25:02 2026-06-04 16:33:24 1 207 207
468 .2 2% | row cache mutex 2026-06-04 16:25:03 2026-06-04 16:33:18 1 108 108
38 .0 0% | latch: session allocation 2026-06-04 16:25:33 2026-06-04 16:33:10 1 16 16
1 .0 0% | sort segment request 2026-06-04 16:31:20 2026-06-04 16:31:20 1 1 1
7 rows selected.
--//出现library cache: bucket mutex X，library cache: mutex X期待事故。
SYS@book> @ ashtop sql_id,event 1=1 trunc(sysdate)+16/24+00/1440+35/86400 trunc(sysdate)+16/24+33/1440+26/86400
Total Distinct Distinct Distinct
Seconds AAS %This SQL_ID EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------- ----------------------------- ------------------- ------------------- ---------- -------- -----------
15768 8.0 65% | 3cx1jjd8hkhv1 library cache: bucket mutex X 2026-06-04 16:25:04 2026-06-04 16:33:25 1 499 499
4600 2.3 19% | 3cx1jjd8hkhv1 2026-06-04 16:25:04 2026-06-04 16:33:25 373 440 811
1875 1.0 8% | 3cx1jjd8hkhv1 library cache: mutex X 2026-06-04 16:25:04 2026-06-04 16:33:24 1 423 423
1218 .6 5% | 3cx1jjd8hkhv1 cursor: pin S 2026-06-04 16:25:06 2026-06-04 16:33:24 1 202 202
465 .2 2% | 3cx1jjd8hkhv1 row cache mutex 2026-06-04 16:25:06 2026-06-04 16:33:18 1 107 107
192 .1 1% | 40xrsk835u6h2 2026-06-04 16:25:06 2026-06-04 16:33:17 50 143 52
109 .1 0% | 2026-06-04 16:25:27 2026-06-04 16:33:20 1 98 98
55 .0 0% | cursor: pin S 2026-06-04 16:25:47 2026-06-04 16:33:12 1 13 13
38 .0 0% | 3cx1jjd8hkhv1 latch: session allocation 2026-06-04 16:25:33 2026-06-04 16:33:10 1 16 16
1 .0 0% | 3pfgx62ptuqm3 2026-06-04 16:33:19 2026-06-04 16:33:19 1 1 1
1 .0 0% | 5cxdbcygvpgnx 2026-06-04 16:32:17 2026-06-04 16:32:17 1 1 1
1 .0 0% | 9sg6u8xys290z 2026-06-04 16:31:21 2026-06-04 16:31:21 1 1 1
1 .0 0% | avf5k3k0x0cxn sort segment request 2026-06-04 16:31:20 2026-06-04 16:31:20 1 1 1
1 .0 0% | library cache: bucket mutex X 2026-06-04 16:32:35 2026-06-04 16:32:35 1 1 1
14 rows selected.
SYS@book> @ ashtop sql_id,event,p1raw,p1,p3raw "event like 'library cache:%'" trunc(sysdate)+16/24+00/1440+35/86400 trunc(sysdate)+16/24+33/1440+26/86400
Total Distinct Distinct Distinct
Seconds AAS %This SQL_ID EVENT P1RAW P1 P3RAW FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------- ----------------------------- ----------------- ---------- ----------------- ------------------- ------------------- ---------- -------- -----------
15725 8.0 89% | 3cx1jjd8hkhv1 library cache: bucket mutex X 0000000000011BA3 72611 0000000000000040 2026-06-04 16:25:05 2026-06-04 16:33:25 1 498 498
1741 .9 10% | 3cx1jjd8hkhv1 library cache: mutex X 00000000A6411BA3 2789284771 00000001004A0055 2026-06-04 16:25:05 2026-06-04 16:33:24 1 403 403
130 .1 1% | 3cx1jjd8hkhv1 library cache: mutex X 00000000A6411BA3 2789284771 00000001004A00A1 2026-06-04 16:25:05 2026-06-04 16:33:19 1 130 130
1 .0 0% | library cache: bucket mutex X 0000000000011BA3 72611 0000000000000040 2026-06-04 16:32:35 2026-06-04 16:32:35 1 1 1
SYS@book> @ s2h 3cx1jjd8hkhv1
SQL_ID HASH_VALUE HASH_HEX KGL_BUCKET KGL_BUCKET_HEX
------------- ----------- --------- ---------- --------------
3cx1jjd8hkhv1 1359561569 51094361 82785 0x14361
--//ashtop 看到的p1=72611,KGL_BUCKET=82785.根本对不上。
--//而如果过细看P1raw的后4位都是1BA3，阐明有标题的对象大概语句对应的的hash便是2789284771。
--//2789284771%2^17 = 72611。
SYS@book> @ sharepool/shp4 '' 2789284771
SYS@book> @ pr
==============================
HANDLE_TYPE : parent handle address
KGLHDADR : 00000000727C4570
KGLHDPAR : 00000000727C4570
C40 : book.SYS
KGLHDLMD : 0
KGLHDPMD : 0
KGLHDIVC : 0
KGLOBHD0 : 00
KGLOBHD6 : 00
KGLOBHS0 : 0
KGLOBHS6 : 0
KGLOBT16 : 0
N0_6_16 : 0
N20 : 0
KGLNAHSH : 2789284771
KGLOBT03 :
KGLHDBID : 72611
KGLOBT09 : 0
PL/SQL procedure successfully completed.
SYS@book> @ kglob '' 2789284771 ''
==============================
INST_ID : 1
OWNER : SYS
NAME : book
DB_LINK :
NAMESPACE : DBINSTANCE
TYPE : CURSOR
NAMESPACE_NUM : 74
NAMESPACE_HEX : 4a
SHARABLE_MEM : 0
PERSISTENT_MEM : 0
RUNTIME_MEM : 0
LOADS : 0
EXECUTIONS : 0
LOCKS : 0
PINS : 0
KEPT : NO
CHILD_LATCH : 72611
INVALIDATIONS : 0
HASH_VALUE : 2789284771
HASH_HEX : a6411ba3
LOCK_MODE : NONE
PIN_MODE : NONE
STATUS : UNKOWN
TIMESTAMP :
PREVIOUS_TIMESTAMP :
LOCKED_TOTAL : 199083689
PINNED_TOTAL : 0
PROPERTY :
FULL_HASH_VALUE : b885bb910059a4d2fdbfc245a6411ba3
CON_ID : 1
CON_NAME : CDB$ROOT
ADDR : 00000000727C4570
PAR_ADDR : 00000000727C4570
HANDLE_TYPE : parent handle address
KGLOBHD0 : 00
KGLOBHD6 : 00
EDITION :
SQL_ID :
KGL_BUCKET : 72611
OBJECT_STR : book.SYS.CDB$ROOT\x4a\0\0\0
PL/SQL procedure successfully completed.
--//阐明这条语句在cdp下实行会不绝访问实例对象名。
--//使用从前写的gdb脚本跟踪kgllkal，kglpnal的调用情况。
$ grep -v "^#" gdb/lkpn.gdb
set pagination off
set logging file /tmp/lkpn.log
set logging overwrite on
set logging on
set $lk = 0
set $pn = 0
set $lock = 0
break kgllkal
commands
silent
printf "kgllkal count %02d -- handle address: %016x, mode: %d ", ++$lk ,$rdx ,$rcx
echo kglnaobj address:
x/s $rdx+0x1c8
c
end
break kglpnal
commands
silent
printf "kglpnal count %02d -- handle address: %016x, mode: %d ", ++$pn ,$rdx ,$rcx
echo kglnaobj address:
x/s $rdx+0x1c8
c
end
$ gdb -f -p 4980 -x /home/oracle/sqllaji/gdb/lkpn.gdb
...
Breakpoint 1 at 0x15367e90
Breakpoint 2 at 0x1536c020
(gdb) c
Continuing.
--//第1次实行
kgllkal count 01 -- handle address: 000000006b6ba820, mode: 1 kglnaobj address:0x6b6ba9e8: "select sid from v$mystat where rownum=1"
kglpnal count 01 -- handle address: 000000006b6ba820, mode: 2 kglnaobj address:0x6b6ba9e8: "select sid from v$mystat where rownum=1"
kgllkal count 02 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 03 -- handle address: 000000006b6b4de0, mode: 2 kglnaobj address:0x6b6b4fa8: "2b81b524e333ac035e810eabe502d655$BUILD$CDB$ROOT"
kgllkal count 04 -- handle address: 000000006b9b3570, mode: 1 kglnaobj address:0x6b9b3738: ""
kglpnal count 02 -- handle address: 000000006b9b3570, mode: 3 kglnaobj address:0x6b9b3738: ""
kgllkal count 05 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 06 -- handle address: 000000006b9c27a0, mode: 1 kglnaobj address:0x6b9c2968: "2b81b524e333ac035e810eabe502d655Child:0CDB$ROOT"
kglpnal count 03 -- handle address: 000000006b9c27a0, mode: 3 kglnaobj address:0x6b9c2968: "2b81b524e333ac035e810eabe502d655Child:0CDB$ROOT"
kgllkal count 07 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 08 -- handle address: 0000000070f4c550, mode: 2 kglnaobj address:0x70f4c718: "V$MYSTATSYSCDB$ROOT"
kglpnal count 04 -- handle address: 0000000070f4c550, mode: 2 kglnaobj address:0x70f4c718: "V$MYSTATSYSCDB$ROOT"
kgllkal count 09 -- handle address: 0000000070f70c20, mode: 2 kglnaobj address:0x70f70de8: "GV$MYSTATSYSCDB$ROOT"
kglpnal count 05 -- handle address: 0000000070f70c20, mode: 2 kglnaobj address:0x70f70de8: "GV$MYSTATSYSCDB$ROOT"
kgllkal count 10 -- handle address: 0000000070f7f110, mode: 2 kglnaobj address:0x70f7f2d8: "X$KSUMYSTASYSCDB$ROOT"
kglpnal count 06 -- handle address: 0000000070f7f110, mode: 2 kglnaobj address:0x70f7f2d8: "X$KSUMYSTASYSCDB$ROOT"
kgllkal count 11 -- handle address: 0000000070f89690, mode: 2 kglnaobj address:0x70f89858: "X$KSUSGIFSYSCDB$ROOT"
kglpnal count 07 -- handle address: 0000000070f89690, mode: 2 kglnaobj address:0x70f89858: "X$KSUSGIFSYSCDB$ROOT"
kgllkal count 12 -- handle address: 000000007251eaa8, mode: 2 kglnaobj address:0x7251ec70: "13789970600300822228CDB$ROOT"
kglpnal count 08 -- handle address: 000000007251eaa8, mode: 2 kglnaobj address:0x7251ec70: "13789970600300822228CDB$ROOT"
kgllkal count 13 -- handle address: 0000000072521968, mode: 2 kglnaobj address:0x72521b30: "4154661687328153900CDB$ROOT"
kglpnal count 09 -- handle address: 0000000072521968, mode: 2 kglnaobj address:0x72521b30: "4154661687328153900CDB$ROOT"
kgllkal count 14 -- handle address: 000000006b9c59a0, mode: 1 kglnaobj address:0x6b9c5b68: "RTSCTX2b81b524e333ac035e810eabe502d655Child:0CDB$ROOT"
kglpnal count 10 -- handle address: 000000006b9c59a0, mode: 3 kglnaobj address:0x6b9c5b68: "RTSCTX2b81b524e333ac035e810eabe502d655Child:0CDB$ROOT"
kgllkal count 15 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 16 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 17 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 18 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
--//第2次实行
kgllkal count 19 -- handle address: 000000006b6ba820, mode: 1 kglnaobj address:0x6b6ba9e8: "select sid from v$mystat where rownum=1"
kgllkal count 20 -- handle address: 000000006b9b3570, mode: 1 kglnaobj address:0x6b9b3738: ""
kgllkal count 21 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 22 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 23 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 24 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 25 -- handle address: 0000000070f4c550, mode: 2 kglnaobj address:0x70f4c718: "V$MYSTATSYSCDB$ROOT"
kglpnal count 11 -- handle address: 0000000070f4c550, mode: 2 kglnaobj address:0x70f4c718: "V$MYSTATSYSCDB$ROOT"
kgllkal count 26 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 27 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 28 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 29 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
--//第3次实行
kgllkal count 30 -- handle address: 000000006b6ba820, mode: 1 kglnaobj address:0x6b6ba9e8: "select sid from v$mystat where rownum=1"
kgllkal count 31 -- handle address: 000000006b9b3570, mode: 1 kglnaobj address:0x6b9b3738: ""
kgllkal count 32 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 33 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 34 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 35 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 36 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 37 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 38 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 39 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
--//第4次实行
kgllkal count 40 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 41 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 42 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 43 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
--//可以发现第4次实行仍旧存在输出，存在一个对象book.SYS.CDB$ROOT不绝访问，调用函数kgllkal，这与前面ashtop的测试完万能对
--//上。
--//如果跟踪pdb下sys用户登录
Breakpoint 1 at 0x15367e90
Breakpoint 2 at 0x1536c020
(gdb) c
Continuing.
--//第1次实行
kgllkal count 01 -- handle address: 000000006b6ba820, mode: 1 kglnaobj address:0x6b6ba9e8: "select sid from v$mystat where rownum=1"
kgllkal count 02 -- handle address: 000000006baee188, mode: 2 kglnaobj address:0x6baee350: "2b81b524e333ac035e810eabe502d655$BUILD$BOOK01P"
kgllkal count 03 -- handle address: 000000006baedf58, mode: 1 kglnaobj address:0x6baee120: ""
kglpnal count 01 -- handle address: 000000006baedf58, mode: 3 kglnaobj address:0x6baee120: ""
kgllkal count 04 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 05 -- handle address: 000000006dcc5dd8, mode: 2 kglnaobj address:0x6dcc5fa0: "1073777561SYSCDB$ROOT"
kgllkal count 06 -- handle address: 000000006baecb28, mode: 1 kglnaobj address:0x6baeccf0: "2b81b524e333ac035e810eabe502d655Child:1BOOK01P"
kglpnal count 02 -- handle address: 000000006baecb28, mode: 3 kglnaobj address:0x6baeccf0: "2b81b524e333ac035e810eabe502d655Child:1BOOK01P"
kgllkal count 07 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 08 -- handle address: 000000006dcc5dd8, mode: 2 kglnaobj address:0x6dcc5fa0: "1073777561SYSCDB$ROOT"
kgllkal count 09 -- handle address: 0000000067790c78, mode: 2 kglnaobj address:0x67790e40: "V$MYSTATSYSBOOK01P"
kglpnal count 03 -- handle address: 0000000067790c78, mode: 2 kglnaobj address:0x67790e40: "V$MYSTATSYSBOOK01P"
kgllkal count 10 -- handle address: 000000006778b688, mode: 2 kglnaobj address:0x6778b850: "GV$MYSTATSYSBOOK01P"
kglpnal count 04 -- handle address: 000000006778b688, mode: 2 kglnaobj address:0x6778b850: "GV$MYSTATSYSBOOK01P"
kgllkal count 11 -- handle address: 0000000067785098, mode: 2 kglnaobj address:0x67785260: "X$KSUMYSTASYSBOOK01P"
kglpnal count 05 -- handle address: 0000000067785098, mode: 2 kglnaobj address:0x67785260: "X$KSUMYSTASYSBOOK01P"
kgllkal count 12 -- handle address: 0000000067782b68, mode: 2 kglnaobj address:0x67782d30: "X$KSUSGIFSYSBOOK01P"
kglpnal count 06 -- handle address: 0000000067782b68, mode: 2 kglnaobj address:0x67782d30: "X$KSUSGIFSYSBOOK01P"
kgllkal count 13 -- handle address: 00000000677718d8, mode: 2 kglnaobj address:0x67771aa0: "13789970600300822228BOOK01P"
kglpnal count 07 -- handle address: 00000000677718d8, mode: 2 kglnaobj address:0x67771aa0: "13789970600300822228BOOK01P"
kgllkal count 14 -- handle address: 00000000677704a8, mode: 2 kglnaobj address:0x67770670: "4154661687328153900BOOK01P"
kglpnal count 08 -- handle address: 00000000677704a8, mode: 2 kglnaobj address:0x67770670: "4154661687328153900BOOK01P"
kgllkal count 15 -- handle address: 000000006baeb6f8, mode: 1 kglnaobj address:0x6baeb8c0: "RTSCTX2b81b524e333ac035e810eabe502d655Child:1BOOK01P"
kglpnal count 09 -- handle address: 000000006baeb6f8, mode: 3 kglnaobj address:0x6baeb8c0: "RTSCTX2b81b524e333ac035e810eabe502d655Child:1BOOK01P"
--//第2次实行
kgllkal count 16 -- handle address: 000000006b6ba820, mode: 1 kglnaobj address:0x6b6ba9e8: "select sid from v$mystat where rownum=1"
kgllkal count 17 -- handle address: 000000006baedf58, mode: 1 kglnaobj address:0x6baee120: ""
kgllkal count 18 -- handle address: 0000000067790c78, mode: 2 kglnaobj address:0x67790e40: "V$MYSTATSYSBOOK01P"
kglpnal count 10 -- handle address: 0000000067790c78, mode: 2 kglnaobj address:0x67790e40: "V$MYSTATSYSBOOK01P"
--//第3次实行
kgllkal count 19 -- handle address: 000000006b6ba820, mode: 1 kglnaobj address:0x6b6ba9e8: "select sid from v$mystat where rownum=1"
kgllkal count 20 -- handle address: 000000006baedf58, mode: 1 kglnaobj address:0x6baee120: ""
--//第4次实行没有输出。
--//是否可以得出一个结论：cdb下一些访问体系视图的sql语句实行都会多出1个实例对象访问，如果这些对象麋集访问须要在实例对象
--//上出现争用。
--//换一条sql语句测试：
--//select sid from v$session where rownum=1
Breakpoint 1 at 0x15367e90
Breakpoint 2 at 0x1536c020
(gdb) c
Continuing.
kgllkal count 01 -- handle address: 000000006b4ec1a0, mode: 1 kglnaobj address:0x6b4ec368: "select sid from v$session where rownum=1"
kgllkal count 02 -- handle address: 000000006b475ea0, mode: 1 kglnaobj address:0x6b476068: ""
kgllkal count 03 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 04 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 05 -- handle address: 000000006d6916f8, mode: 2 kglnaobj address:0x6d6918c0: "V$SESSIONSYSCDB$ROOT"
kglpnal count 01 -- handle address: 000000006d6916f8, mode: 2 kglnaobj address:0x6d6918c0: "V$SESSIONSYSCDB$ROOT"
kgllkal count 06 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 07 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 08 -- handle address: 000000006b4ec1a0, mode: 1 kglnaobj address:0x6b4ec368: "select sid from v$session where rownum=1"
kgllkal count 09 -- handle address: 000000006b475ea0, mode: 1 kglnaobj address:0x6b476068: ""
kgllkal count 10 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 11 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 12 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 13 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 14 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 15 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 16 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 17 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 18 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
kgllkal count 19 -- handle address: 00000000727c4570, mode: 2 kglnaobj address:0x727c4738: "bookSYSCDB$ROOT"
--//确实云云，只不外select sid from v$session where rownum=1 实行2次罢了.
--//select * from v$process where rownum=1; 类似，不再列出。
--//如许在cdp下实行一些体系视图sql语句,岂论实行多少次,涉及x$表都是情况，末了总是要访问实例对象.纵然实行差别的sql语句.
--//根本可以得出一个结论,如果cdb下麋集实行一些访问体系视图语句,都有大概在实例对象上出现library cache: bucket mutex X
--//，library cache: mutex X相干期待.
--//别的再写一篇分析文章，深入分析。
免责声明：如果侵犯了您的权益，请联系站长及时删除侵权内容，谢谢合作！qidao123.com:ToB企服之家，中国第一个企服评测及软件市场,开放入驻,技术点评得现金. |
发表于