[20171107]dbms_shared_pool.pin.txt

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

时间： 2024-10-11 18:08:43

[20171107]dbms_shared_pool.pin.txt的相关文章

[20171107]dbms_shared_pool.pin补充.txt

[20171107]dbms_shared_pool.pin补充.txt --//上午的测试,做一些补充,主要还是一些理解问题. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- --------------------------------------------------------

[20150316]dbms_shared_pool.keep.txt

[20150316]dbms_shared_pool.keep.txt --包dbms_shared_pool可以清除特定的sql从共享池,也可以pin某个包到共享池,特别是一些大存储过程,减少换入换出的情况,一定程 --度减少出现ora-4031错误.实际上也可以使用它pin相应的sql语句,自己测试看看. 1.建立测试环境: SCOTT@test> @ver1 PORT_STRING                    VERSION        BANNER -------------

[20171115]关于namespace.txt

[20171115]关于namespace.txt --//第一次听到这个概念,好像是那篇blog提到,表与索引在不同的namespace里面.也就是在相同schema下建立的表可以与索引同名. --//而在同一schema下的namespace的建立的对象名字是不能重名的. --//前几天在测试dbms_shared_pool.pin时,发现SEQUENCE的namespace竟然是TABLE/PROCEDURE,感觉有必要做一些学习,了解这方面 --//的知识. --//摘要: http:/

《Oracle高性能自动化运维》一一2.3　Library Cache

2.3 Library Cache2.3.1 Library Cache与SQL游标 Library Cache主要用于存放SQL游标,而SQL游标最大化共享是Library Cache优化的重要途径,可以使SQL运行开销最低.性能最优. 1. SQL语句与父游标及子游标 在PL/SQL中,游标(Cursor)是数据集遍历的内存集合.而从广义上讲,游标是SQL语句在Library Cache中的内存载体.SQL语句与游标关系如下: 1)一条SQL语句包含一个父游标(Parent Cursor)和

[20160811]dbms_shared_pool清除子光标.txt

[20160811]dbms_shared_pool清除子光标.txt --工作需要,看了一下使用dbms_shared_pool包的purge清除子光标.顺便做一些细节测试看看: 1.环境与说明: SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                            

[20130628]关于cursor pin S以及cursor pin S wait on X的相关问题.txt

[20130628]关于cursor pin S以及cursor pin S wait on X的相关问题.txt 生产系统最近一段时间总是出现cursor pin S wait on X,以前从来没有遇到,今天抽空看了一下.了解关于cursor pin S以及cursor pin S wait on X的相关问题,我看了一些链接: http://www.pythian.com/blog/cursor-pin-s-wait-on-x-in-the-top-5-wait-events/http:/

[20130228]等待事件library cache pin的快速定位与解决.txt

[20130228]等待事件library cache pin的快速定位与解决.txt 前几天管理的服务器出现library cache pin,当时解决有点乱了阵脚,正好下午空闲做一个例子来定位library cache pin事件以及解决方法,另外我也看许多blog,感觉定位太复杂,不合适快速解决问题: 1.环境以及问题再现: SQL> select * from v$version where rownumBANNER------------------------------------

[20171031]markhot.txt

[20171031]markhot.txt --//昨天看了https://jonathanlewis.wordpress.com/2017/10/02/markhot/,测试看看这样时候可以减少争用. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------

[20140814]oerr for windows.txt

[20140814 ]oerr for windows.txt --今天系统出现错误,我不小心在windows执行. d:\tools\rlwrap>oerr ora 4031 ORACLE_HOME not set.  Contact Oracle Support Services. --说明windows下存在oerr命令.我使用的是12c,按照道理以前的版本都没有在windows下oerr版本. E:\>set ORACLE_HOME=E:\app\Administrator\produ