博客
关于我
强烈建议你试试无所不能的chatGPT，快点击我
[20171107]dbms_shared_pool.pin.txt
阅读量：7199 次
发布时间：2019-06-29

本文共 17622 字，大约阅读时间需要 58 分钟。

[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;

转载于:https://www.cnblogs.com/lfree/p/7799890.html

你可能感兴趣的文章
喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！-- 愿君每日到此一游！
当前时间: 2025-02-12 00:00:37   当前IP: 3.20.233.113   联系邮箱:javaeecc@qq.com     Copyright © 2020 - 2022 baihongyu.com 京ICP备2021015314号-2
强烈建议你试试无所不能的CHAT-GPT，快点击我
强烈建议你试试无所不能的CHAT-GPT，快点击我