[20171107]dbms_shared_pool.pin补充.txt
--//上午的测试,做一些补充,主要还是一些理解问题.
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
2.测试:
--//session 1:
SCOTT@book> select seq2.nextval from dual;
NEXTVAL
----------
142
--//注:执行许多次了.
--//session 2:
SELECT KGLHDADR, KGLHDPAR, KGLHDCLT, KGLNAOWN, KGLNAOBJ, KGLFNOBJ
FROM x$kglob a
WHERE kglobtyd = 'SEQUENCE'
AND kglnaobj = 'SEQ2';
KGLHDADR KGLHDPAR KGLHDCLT KGLNAOWN KGLNAOBJ KGLFNOBJ
---------------- ---------------- ---------- -------- ------------------------------ --------------------
000000007BBBCFF0 000000007BBBCFF0 119696 SCOTT SEQ2 SEQ2
SYS@book> @ &r/sharepool/shp4 000000007BBBCFF0 0
old 18: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new 18: WHERE kglobt03 = '000000007BBBCFF0' or kglhdpar='000000007BBBCFF0' or kglhdadr='000000007BBBCFF0' or KGLNAHSH= 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---- -------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
父游标句柄地址 000000007BBBCFF0 000000007BBBCFF0 SEQ2 0 000000007BBBCB08 00 4728 0 0 4728 4728 3497251728 10
SYS@book> alter system flush shared_pool;
System altered.
SYS@book> @ &r/sharepool/shp4 000000007BBBCFF0 0
old 18: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new 18: WHERE kglobt03 = '000000007BBBCFF0' or kglhdpar='000000007BBBCFF0' or kglhdadr='000000007BBBCFF0' or KGLNAHSH= 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---- -------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
父游标句柄地址 000000007BBBCFF0 000000007BBBCFF0 SEQ2 0 00 00 0 0 0 0 0 3497251728 0
--//快下班在这里转不出来,为什么还在共享池中,仅仅堆0信息被清除了.下午才想起来,如果会话不退出,无论如何刷新,都存在的.
--//session 1 ,现在退出.
SYS@book> @ &r/sharepool/shp4 000000007BBBCFF0 0
old 18: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new 18: WHERE kglobt03 = '000000007BBBCFF0' or kglhdpar='000000007BBBCFF0' or kglhdadr='000000007BBBCFF0' or KGLNAHSH= 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
父游标句柄地址 000000007BBBCFF0 000000007BBBCFF0 SEQ2 0 00 00 0 0 0 0 0 3497251728 0
SYS@book> alter system flush shared_pool;
System altered.
SYS@book> @ &r/sharepool/shp4 000000007BBBCFF0 0
old 18: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new 18: WHERE kglobt03 = '000000007BBBCFF0' or kglhdpar='000000007BBBCFF0' or kglhdadr='000000007BBBCFF0' or KGLNAHSH= 0
no rows selected
--//可以发现现在已经不再共享池了,自己有点蒙....^_^.
3.再看看keep的情况.
--//session 1:
SCOTT@book> select seq2.nextval from dual;
NEXTVAL
----------
162
--//session 2:
SYS@book> exec dbms_shared_pool.keep('SCOTT.SEQ2','Q');
PL/SQL procedure successfully completed.
SELECT KGLHDADR, KGLHDPAR, KGLHDCLT, KGLNAOWN, KGLNAOBJ, KGLFNOBJ ,KGLHDKMK
FROM x$kglob a
WHERE kglobtyd = 'SEQUENCE'
AND kglnaobj = 'SEQ2';
KGLHDADR KGLHDPAR KGLHDCLT KGLNAOWN KGLNAOBJ KGLFNOBJ KGLHDKMK
---------------- ---------------- ---------- -------- -------- -------------------- ----------
000000007D2CD020 000000007D2CD020 119696 SCOTT SEQ2 SEQ2 1
SYS@book> @ &r/sharepool/shp4 000000007D2CD020 0
old 18: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new 18: WHERE kglobt03 = '000000007D2CD020' or kglhdpar='000000007D2CD020' or kglhdadr='000000007D2CD020' or KGLNAHSH= 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ----- -------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
父游标句柄地址 000000007D2CD020 000000007D2CD020 SEQ2 0 000000007D2CCB38 00 4728 0 0 4728 4728 3497251728 10
SYS@book> alter system flush shared_pool;
System altered.
SYS@book> alter system flush shared_pool;
System altered.
SYS@book> @ &r/sharepool/shp4 000000007D2CD020 0
old 18: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new 18: WHERE kglobt03 = '000000007D2CD020' or kglhdpar='000000007D2CD020' or kglhdadr='000000007D2CD020' or KGLNAHSH= 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---- -------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
父游标句柄地址 000000007D2CD020 000000007D2CD020 SEQ2 0 000000007D2CCB38 00 4728 0 0 4728 4728 3497251728 10
--//即使session 1退出,这个seq2也会保持在共享池中.
SYS@book> alter system flush shared_pool;
System altered.
SYS@book> @ &r/sharepool/shp4 000000007D2CD020 0
old 18: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new 18: WHERE kglobt03 = '000000007D2CD020' or kglhdpar='000000007D2CD020' or kglhdadr='000000007D2CD020' or KGLNAHSH= 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---- -------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
父游标句柄地址 000000007D2CD020 000000007D2CD020 SEQ2 0 000000007D2CCB38 00 4728 0 0 4728 4728 3497251728 10
--//这让想起vage书中讲解,刷新一定要一组相似的程序不再运行,刷新才能剔除许多组件.
4.取消keep.
SYS@book> exec dbms_shared_pool.unkeep('SCOTT.SEQ2','Q');
PL/SQL procedure successfully completed.
SYS@book> alter system flush shared_pool;
System altered.
SYS@book> @ &r/sharepool/shp4 000000007D2CD020 0
old 18: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new 18: WHERE kglobt03 = '000000007D2CD020' or kglhdpar='000000007D2CD020' or kglhdadr='000000007D2CD020' or KGLNAHSH= 0
no rows selected
SELECT KGLHDADR, KGLHDPAR, KGLHDCLT, KGLNAOWN, KGLNAOBJ, KGLFNOBJ ,KGLHDKMK
FROM x$kglob a
WHERE kglobtyd = 'SEQUENCE'
AND kglnaobj = 'SEQ2';
no rows selected
--//顺便测试,这样是否可以keep.
SYS@book> exec dbms_shared_pool.keep('SCOTT.SEQ2','Q');
PL/SQL procedure successfully completed.
SELECT KGLHDADR, KGLHDPAR, KGLHDCLT, KGLNAOWN, KGLNAOBJ, KGLFNOBJ ,KGLHDKMK
FROM x$kglob a
WHERE kglobtyd = 'SEQUENCE'
AND kglnaobj = 'SEQ2';
KGLHDADR KGLHDPAR KGLHDCLT KGLNAOWN KGLNAOBJ KGLFNOBJ KGLHDKMK
---------------- ---------------- ---------- -------- -------- -------- --------
000000007DBFCD50 000000007DBFCD50 119696 SCOTT SEQ2 SEQ2 1
SYS@book> @ &r/sharepool/shp4 000000007DBFCD50 0
old 18: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new 18: WHERE kglobt03 = '000000007DBFCD50' or kglhdpar='000000007DBFCD50' or kglhdadr='000000007DBFCD50' or KGLNAHSH= 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ----- -------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- -------- --------
父游标句柄地址 000000007DBFCD50 000000007DBFCD50 SEQ2 0 000000007CFE7010 00 4728 0 0 4728 4728 3497251728 10
--//另外还有一个不理解的地方,KGLOBT09=10表示什么?在sql语句中这个表示child_number.