[20171107]dbms_shared_pool.pin.txt
2018-06-17 20:00:36来源：未知 阅读 ()
[20171107]dbms_shared_pool.pin.txt
--//昨天与别人聊天提到,如果dbms_shared_pool.pin对象,可以改变对应的chunk的类型.我自己也不确定,做一次测试.
1.环境:
SCOTT@book> @ &r/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
SCOTT@book> create sequence seq2 order;
Sequence created.
SCOTT@book> select * from dba_sequences where sequence_name = 'SEQ2' and sequence_owner=user;
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SCOTT SEQ2 1 1.0000E+28 1 N Y 20 1
SCOTT@book> select seq2.nextval from dual;
NEXTVAL
----------
1
SCOTT@book> select seq2.nextval from dual;
NEXTVAL
----------
2
SCOTT@book> alter system flush shared_pool;
System altered.
SCOTT@book> select seq2.nextval from dual;
NEXTVAL
----------
21
--//可以发现如果我刷新共享池,seq2顺序号出现跳号现象.
2.测试前检查seq2 chunk状态:
select * from x$kglob a where kglobtyd='SEQUENCE' and kglnaobj='SEQ2';
Record View
As of: 2017/11/7 11:22:41
ADDR: 00007F8F5EE5FA90
INDX: 942
INST_ID: 1
KGLHDADR: 000000007B988D28
KGLHDPAR: 000000007B988D28
KGLHDCLT: 119696
KGLNAOWN: SCOTT
KGLNAOBJ: SEQ2
KGLFNOBJ: SEQ2
KGLNADLK:
KGLNAHSH: 3497251728
KGLNAHSV: 8ab86e9923d83e59d93da6ffd073d390
KGLNATIM: 2017/11/7 11:19:42
KGLNAPTM: 2017/11/7 11:01:22
KGLHDNSP: 1
KGLHDNSD: TABLE/PROCEDURE
KGLHDLMD: 1
KGLHDPMD: 0
KGLHDFLG: 10241
KGLHDOBJ: 000000007C9107A0
KGLHDLDC: 5
KGLHDIVC: 0
KGLHDEXC: 0
KGLHDLKC: 2
KGLHDKMK: 0
~~~~~~~~~~~~~~
KGLHDDMK: 1
KGLHDAMK: 0
KGLOBFLG: 4
KGLOBSTA: 1
KGLOBTYP: 6
KGLOBTYD: SEQUENCE
KGLOBHMK: 0
KGLOBHS0: 4728
KGLOBHS1: 0
KGLOBHS2: 0
KGLOBHS3: 0
KGLOBHS4: 0
KGLOBHS5: 0
KGLOBHS6: 0
KGLOBHS7: 0
KGLOBHD0: 000000007BDC2F80
KGLOBHD1: 00
KGLOBHD2: 00
KGLOBHD3: 00
KGLOBHD4: 00
KGLOBHD5: 00
KGLOBHD6: 00
KGLOBHD7: 00
KGLOBPC0: 0
KGLOBPC6: 0
KGLOBTP0: 00
KGLOBT00: 5
KGLOBT01: 0
KGLOBT02: 0
KGLOBT03:
KGLOBT04: 0
KGLOBT05: 0
KGLOBT35: 0
KGLOBT06: 0
KGLOBT07: 0
KGLOBT08: 90561
KGLOBT09: 10
KGLOBT10: 0
KGLOBT11: 1
KGLOBT12: 0
KGLOBT13: 0
KGLOBT14: 0
KGLOBT15: 0
KGLOBT16: 0
KGLOBT17: 0
KGLOBT18: 0
KGLOBT19: 0
KGLOBT20: 0
KGLOBT21: 0
KGLOBT22: 0
KGLOBT23: 9
KGLOBT24: 16
KGLOBT25: 0
KGLOBT26: 0
KGLOBT28: 0
KGLOBT29: 0
KGLOBT30: 0
KGLOBT31: 0
KGLOBT27: 0
KGLOBT32: 0
KGLOBT33: 0
KGLOBWAP: 0
KGLOBWCC: 0
KGLOBWCL: 0
KGLOBWUI: 0
KGLOBWDW: 0
KGLOBT42: 0
KGLOBT43: 0
KGLOBT44: 0
KGLOBT45: 0
KGLOBT46: 0
KGLOBT47: 0
KGLOBT49: 0
KGLOBT50: 0
KGLOBT52: 0
KGLOBT53: 0
KGLOBTL0: 0
KGLOBTL1: 0
KGLOBTS0:
KGLOBTS1:
KGLOBTN0: 22
KGLOBTN1: 1
KGLOBTN2: 1
KGLOBTN3: 9999999999999999999999999999
KGLOBTN4: 20
KGLOBTN5: 41
KGLOBTS2:
KGLOBTS3:
KGLOBTS5:
KGLOBTT0:
KGLOBCCE:
KGLOBCCEH: 0
KGLOBCLA:
KGLOBCLC: 0
KGLOBCCC: 0
KGLOBTS4:
KGLOBCBCA:
KGLOBT48: 0
KGLOBDSO: 0
KGLOBDEX: 0
KGLOBDPX: 0
KGLOBDLD: 0
KGLOBDIV: 0
KGLOBDPS: 0
KGLOBDDR: 0
KGLOBDDW: 0
KGLOBDBF: 0
KGLOBDRO: 0
KGLOBDCP: 0
KGLOBDEL: 0
KGLOBDFT: 0
KGLOBDEF: 0
KGLOBDUI: 0
KGLOBDCL: 0
KGLOBDAP: 0
KGLOBDCC: 0
KGLOBDPL: 0
KGLOBDJV: 0
KGLOBACS: 0
KGLOBTS6:
KGLOBTS7:
KGLOBT54: 0
KGLOBT55: 0
KGLOBT56: 0
KGLOBT57: 0
KGLOBDCO: 0
KGLOBDCI: 0
KGLOBDRR: 0
KGLOBDRB: 0
KGLOBDWR: 0
KGLOBDWB: 0
KGLOBT58: 0
KGLOBDOR: 0
KGLHDMTX: 000000007B988E68
KGLHDMVL: 0
KGLHDMSP: 0
KGLHDMGT: 110
KGLHDDMTX: 000000007B988DD8
KGLHDDVL: 0
KGLHDDSP: 0
KGLHDDGT: 8
KGLHDBID: 119696
KGLHDBMTX: 0000000080FE6278
KGLHDBVL: 0
KGLHDBSP: 0
KGLHDBGT: 20
KGLOBT59: 0
KGLOBDCU: 0
KGLOBPROP:
--//注意: KGLHDADR: 000000007B988D28 KGLHDPAR: 000000007B988D28
SYS@book> @ &r/sharepool/shp4 000000007B988D28 0
old 18: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new 18: WHERE kglobt03 = '000000007B988D28' or kglhdpar='000000007B988D28' or kglhdadr='000000007B988D28' or KGLNAHSH= 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ----- -------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
父游标句柄地址 000000007B988D28 000000007B988D28 SEQ2 0 000000007BDC2F80 00 4728 0 0 4728 4728 3497251728 10
--//顺序号仅仅存在堆0(KGLOBHD0).没有堆6.
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007B988D28', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
old 1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('&&1', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
new 1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007B988D28', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FFA00570A00 6932 1 1 1 KGLHD 000000007B988CF8 544 recr 80 00
--//注意KSMCHCLS=recr.
select a.* from x$ksmsp a where a.ksmchpar=hextoraw('000000007BDC2F80')
old 1: select a.* from x$ksmsp a where a.ksmchpar=hextoraw('&&1')
new 1: select a.* from x$ksmsp a where a.ksmchpar=hextoraw('000000007BDC2F80')
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FFA005C3158 5052 1 1 1 KGLH0^d073d390 000000007C9106F0 4096 recr 4095 000000007BDC2F80
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007BDC2F80', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
old 1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('&&1', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
new 1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007BDC2F80', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FFA0059B0F0 6077 1 1 1 KGLDA 000000007BDC2F18 240 freeabl 0 00
--//仅仅存在2种类型chunk:recr ,freeabl.
3.测试keep pin后seq2状态:
SYS@book> exec dbms_shared_pool.keep('SCOTT.SEQ2','Q');
PL/SQL procedure successfully completed.
SCOTT@book> select seq2.nextval from dual;
NEXTVAL
----------
22
SCOTT@book> alter system flush shared_pool;
System altered.
SCOTT@book> alter system flush shared_pool;
System altered.
SCOTT@book> select seq2.nextval from dual;
NEXTVAL
----------
23
--//可以发现keep后.seq2不会出现跳号情况.也就是刷新共享池seq2的chunk不会清除从共享池.
SYS@book> @ &r/sharepool/shp4 000000007B988D28 0
old 18: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new 18: WHERE kglobt03 = '000000007B988D28' or kglhdpar='000000007B988D28' or kglhdadr='000000007B988D28' or KGLNAHSH= 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- -------------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
父游标句柄地址 000000007B988D28 000000007B988D28 SEQ2 0 000000007BDC2F80 00 4728 0 0 4728 4728 3497251728 10
--//顺序号仅仅存在堆0(KGLOBHD0).没有堆6.
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007B988D28', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
old 1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('&&1', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
new 1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007B988D28', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FFA00571A70 5131 1 1 1 KGLHD 000000007B988CF8 544 recr 80 00
--//注意KSMCHCLS=recr.
old 1: select a.* from x$ksmsp a where a.ksmchpar=hextoraw('&&1')
new 1: select a.* from x$ksmsp a where a.ksmchpar=hextoraw('000000007BDC2F80')
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FFA005B2560 3846 1 1 1 KGLH0^d073d390 000000007C9106F0 4096 recr 4095 000000007BDC2F80
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007BDC2F80', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
old 1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('&&1', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
new 1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007BDC2F80', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FFA005A2610 4548 1 1 1 KGLDA 000000007BDC2F18 240 freeabl 0 00
--//很明显并不是想对方讲的那样chunk的类型发生了变化,还是与原来的一样.
SELECT OWNER
,NAME
,DB_LINK
,NAMESPACE
,TYPE
,SHARABLE_MEM
,LOADS
,EXECUTIONS
,LOCKS
,PINS
,KEPT
FROM V$DB_OBJECT_CACHE
WHERE name = 'SEQ2' AND owner = 'SCOTT';
OWNER NAME DB_LINK NAMESPACE TYPE SHARABLE_MEM LOADS EXECUTIONS LOCKS PINS KEP
------ ----- -------- --------------- -------- ------------ ---------- ---------- ---------- ---------- ---
SCOTT SEQ2 TABLE/PROCEDURE SEQUENCE 4728 5 0 2 0 YES
--//猜测KEEP字段有关.看看底层定义:
SYS@book> select * from V$FIXED_VIEW_DEFINITION where view_name='GV$DB_OBJECT_CACHE' ;
VIEW_NAME VIEW_DEFINITION
------------------------------ ----------------------------------------------------------------------------------------------------
GV$DB_OBJECT_CACHE select inst_id,kglnaown,kglnaobj,kglnadlk,kglhdnsd,kglobtyd, kglobhs0+kglobhs1+kglobhs2+kglobhs3+kgl
obhs4+kglobhs5+kglobhs6, kglhdldc,kglhdexc,kglhdlkc,kglobpc0,decode(kglhdkmk,0,'NO','YES'),kglhdclt,
kglhdivc, kglnahsh, decode(kglhdlmd, 0, 'NONE',
1, 'NULL', 2, 'SHARED', 3, 'EXCLUSIVE',
'UNKOWN'), decode(kglhdpmd, 0, 'NONE',
1, 'NULL', 2, 'SHARED', 3, 'EXCLUSIVE',
'UNKOWN'), decode(kglobsta, 1, 'VALID',
2, 'VALID_AUTH_ERROR', 3, 'VALID_COMPILE_ERROR', 4, 'VALID_UNAUTH',
5, 'INVALID_UNAUTH', 6, 'INVALID', 'UNKOWN'), substr
(to_char(kglnatim,'YYYY-MM-DD/HH24:MI:SS'),1,19), substr(to_char(kglnaptm,'YYYY-MM-DD/HH24:MI:SS'),1
,19), kglobt23, kglobt24, kglobprop, kglnahsv from x$kglob where kglnaobj is not null
--//可以发现内容 DECODE (kglhdkmk, 0, 'NO', 'YES'),也是kglhdkmk=0,没有keep.
select * from x$kglob a where kglobtyd='SEQUENCE' and kglnaobj='SEQ2';
Record View
As of: 2017/11/7 11:46:03
ADDR: 00007F8F5F139D30
INDX: 1582
INST_ID: 1
KGLHDADR: 000000007B988D28
KGLHDPAR: 000000007B988D28
KGLHDCLT: 119696
KGLNAOWN: SCOTT
KGLNAOBJ: SEQ2
KGLFNOBJ: SEQ2
KGLNADLK:
KGLNAHSH: 3497251728
KGLNAHSV: 8ab86e9923d83e59d93da6ffd073d390
KGLNATIM: 2017/11/7 11:19:42
KGLNAPTM: 2017/11/7 11:01:22
KGLHDNSP: 1
KGLHDNSD: TABLE/PROCEDURE
KGLHDLMD: 1
KGLHDPMD: 0
KGLHDFLG: 8398849
KGLHDOBJ: 000000007C9107A0
KGLHDLDC: 5
KGLHDIVC: 0
KGLHDEXC: 0
KGLHDLKC: 2
KGLHDKMK: 1
~~~~~~~~~~~~~~~
KGLHDDMK: 1
KGLHDAMK: 0
KGLOBFLG: 4
KGLOBSTA: 1
KGLOBTYP: 6
KGLOBTYD: SEQUENCE
KGLOBHMK: 1
KGLOBHS0: 4728
KGLOBHS1: 0
KGLOBHS2: 0
KGLOBHS3: 0
KGLOBHS4: 0
KGLOBHS5: 0
KGLOBHS6: 0
KGLOBHS7: 0
KGLOBHD0: 000000007BDC2F80
KGLOBHD1: 00
KGLOBHD2: 00
KGLOBHD3: 00
KGLOBHD4: 00
KGLOBHD5: 00
KGLOBHD6: 00
KGLOBHD7: 00
KGLOBPC0: 0
KGLOBPC6: 0
KGLOBTP0: 00
KGLOBT00: 5
KGLOBT01: 0
KGLOBT02: 0
KGLOBT03:
KGLOBT04: 0
KGLOBT05: 0
KGLOBT35: 0
KGLOBT06: 0
KGLOBT07: 0
KGLOBT08: 90561
KGLOBT09: 10
KGLOBT10: 0
KGLOBT11: 1
KGLOBT12: 0
KGLOBT13: 0
KGLOBT14: 0
KGLOBT15: 0
KGLOBT16: 0
KGLOBT17: 0
KGLOBT18: 0
KGLOBT19: 0
KGLOBT20: 0
KGLOBT21: 0
KGLOBT22: 0
KGLOBT23: 14
KGLOBT24: 27
KGLOBT25: 0
KGLOBT26: 0
KGLOBT28: 0
KGLOBT29: 0
KGLOBT30: 0
KGLOBT31: 0
KGLOBT27: 0
KGLOBT32: 0
KGLOBT33: 0
KGLOBWAP: 0
KGLOBWCC: 0
KGLOBWCL: 0
KGLOBWUI: 0
KGLOBWDW: 0
KGLOBT42: 0
KGLOBT43: 0
KGLOBT44: 0
KGLOBT45: 0
KGLOBT46: 0
KGLOBT47: 0
KGLOBT49: 0
KGLOBT50: 0
KGLOBT52: 0
KGLOBT53: 0
KGLOBTL0: 0
KGLOBTL1: 0
KGLOBTS0:
KGLOBTS1:
KGLOBTN0: 25
KGLOBTN1: 1
KGLOBTN2: 1
KGLOBTN3: 9999999999999999999999999999
KGLOBTN4: 20
KGLOBTN5: 41
KGLOBTS2:
KGLOBTS3:
KGLOBTS5:
KGLOBTT0:
KGLOBCCE:
KGLOBCCEH: 0
KGLOBCLA:
KGLOBCLC: 0
KGLOBCCC: 0
KGLOBTS4:
KGLOBCBCA:
KGLOBT48: 0
KGLOBDSO: 0
KGLOBDEX: 0
KGLOBDPX: 0
KGLOBDLD: 0
KGLOBDIV: 0
KGLOBDPS: 0
KGLOBDDR: 0
KGLOBDDW: 0
KGLOBDBF: 0
KGLOBDRO: 0
KGLOBDCP: 0
KGLOBDEL: 0
KGLOBDFT: 0
KGLOBDEF: 0
KGLOBDUI: 0
KGLOBDCL: 0
KGLOBDAP: 0
KGLOBDCC: 0
KGLOBDPL: 0
KGLOBDJV: 0
KGLOBACS: 0
KGLOBTS6:
KGLOBTS7:
KGLOBT54: 0
KGLOBT55: 0
KGLOBT56: 0
KGLOBT57: 0
KGLOBDCO: 0
KGLOBDCI: 0
KGLOBDRR: 0
KGLOBDRB: 0
KGLOBDWR: 0
KGLOBDWB: 0
KGLOBT58: 0
KGLOBDOR: 0
KGLHDMTX: 000000007B988E68
KGLHDMVL: 0
KGLHDMSP: 0
KGLHDMGT: 188
KGLHDDMTX: 000000007B988DD8
KGLHDDVL: 0
KGLHDDSP: 0
KGLHDDGT: 12
KGLHDBID: 119696
KGLHDBMTX: 0000000080FE6278
KGLHDBVL: 0
KGLHDBSP: 0
KGLHDBGT: 50
KGLOBT59: 0
KGLOBDCU: 0
KGLOBPROP:
--//注意看~下划线内容.
--//取消kepp看看.
SYS@book> exec dbms_shared_pool.unkeep('SCOTT.SEQ2','Q');
PL/SQL procedure successfully completed.
SELECT OWNER
,NAME
,DB_LINK
,NAMESPACE
,TYPE
,SHARABLE_MEM
,LOADS
,EXECUTIONS
,LOCKS
,PINS
,KEPT
FROM V$DB_OBJECT_CACHE
WHERE name = 'SEQ2' AND owner = 'SCOTT';
OWNER NAME DB_LINK NAMESPACE TYPE SHARABLE_MEM LOADS EXECUTIONS LOCKS PINS KEP
------ ---- ------- --------------- -------- ------------ ---------- ---------- ---------- ---------- ---
SCOTT SEQ2 TABLE/PROCEDURE SEQUENCE 4728 5 0 2 0 NO
--//补充:有一点点奇怪seq的NAMESPACE竟然是TABLE/PROCEDUR.也就是你无法再建立seq2的表在schema=scott模式下.
SCOTT@book> create table seq2 ( a number);
create table seq2 ( a number)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SCOTT@book> select seq2.nextval from dual;
NEXTVAL
----------
41
SCOTT@book> alter system flush shared_pool;
System altered.
SCOTT@book> select seq2.nextval from dual;
NEXTVAL
----------
61
--//再次出现跳号.
SCOTT@book> alter system flush shared_pool;
System altered.
SYS@book> @ &r/sharepool/shp4 000000007B988D28 0
old 18: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new 18: WHERE kglobt03 = '000000007B988D28' or kglhdpar='000000007B988D28' or kglhdadr='000000007B988D28' or KGLNAHSH= 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ----- --------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
父游标句柄地址 000000007B988D28 000000007B988D28 SEQ2 0 00 00 0 0 0 0 0 3497251728 0
--//可以仅仅堆0没清楚了.父游标句柄还在.退出scott登录会话依旧存在.
SYS@book> alter system flush shared_pool;
System altered.
SYS@book> @ &r/sharepool/shp4 000000007B988D28 0
old 18: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new 18: WHERE kglobt03 = '000000007B988D28' or kglhdpar='000000007B988D28' or kglhdadr='000000007B988D28' or KGLNAHSH= 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ----- -------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
父游标句柄地址 000000007B988D28 000000007B988D28 SEQ2 0 00 00 0 0 0 0 0 3497251728 0
---//scott用户登录后再执行如下,再次出现跳号.
SCOTT@book> select seq2.nextval from dual;
NEXTVAL
----------
81
SYS@book> @ &r/sharepool/shp4 000000007B988D28 0
old 18: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new 18: WHERE kglobt03 = '000000007B988D28' or kglhdpar='000000007B988D28' or kglhdadr='000000007B988D28' or KGLNAHSH= 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---- -------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
父游标句柄地址 000000007B988D28 000000007B988D28 SEQ2 0 000000007B8F2078 00 4728 0 0 4728 4728 3497251728 10
总结:
1.exec dbms_shared_pool.keep('SCOTT.SEQ2','Q')后,chunk类型不会变化.
2.keep后,仅仅改动了x$kglob.KGLHDKMK值.
3.一定要自己测试看看,不要听别人讲.就信以为真.
4.顺便贴上shp4的脚本:
column N0_6_16 format 99999999
SELECT DECODE (kglhdadr,
kglhdpar, '父游标句柄地址',
'子游标句柄地址')
text,
kglhdadr,
kglhdpar,
substr(kglnaobj,1,40) c40,
kglhdivc,
kglobhd0,
kglobhd6,
kglobhs0,kglobhs6,kglobt16,
kglobhs0+kglobhs6+kglobt16 N0_6_16,
kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,
kglnahsh,
kglobt03 ,
kglobt09
FROM x$kglob
WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2;
标签：
版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有
- 关于数据库管理系统DBMS--关系型数据库（MySQL/MariaDB） 2018-08-02
IDC资讯： 主机资讯 注册资讯 托管资讯 vps资讯 网站建设
网站运营： 建站经验 策划盈利 搜索优化 网站推广 免费资源
网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它
服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护
软件技巧： 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷
网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash
