分享程序员开发的那些事...
更新时间：2022-09-13 10:27:31
[20150316]dbms_shared_pool.keep.txt
--包dbms_shared_pool可以清除特定的sql从共享池，也可以pin某个包到共享池，特别是一些大存储过程，减少换入换出的情况，一定程
--度减少出现ora-4031错误。实际上也可以使用它pin相应的sql语句，自己测试看看。
1.建立测试环境：
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> Select * from dept where deptno=10 ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
--执行多次。
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 64hk7qvafmm0x, child number 0
-------------------------------------
Select * from dept where deptno=10
Plan hash value: 2852011669
----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
----------------------------------------------------------------------------------------
--sql_id ='64hk7qvafmm0x'.
$ cat shp4.sql
SELECT DECODE (kglhdadr,
kglhdpar, '父游标句柄地址',
'子游标句柄地址')
text,
kglhdadr,
kglhdpar,
substr(kglnaobj,1,20),
kglobhd0,
kglobhd6,
kglobhs0,kglobhs6,kglobt16,
kglobhs0+kglobhs6+kglobt16,
kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20
FROM x$kglob
WHERE kglobt03 = '&1';
SCOTT@test> @sharepool/shp4 64hk7qvafmm0x
TEXT KGLHDADR KGLHDPAR SUBSTR(KGLNAOBJ,1,20) KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16 N20
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------------------------- ----------
子游标句柄地址 00000000AF5B1CA8 00000000AF5E3108 Select * from dept w 00000000AF5E2E40 00000000AAD52728 4520 12144 3052 19716 19716
父游标句柄地址 00000000AF5E3108 00000000AF5E3108 Select * from dept w 00000000BE2BCB08 00 4704 0 0 4704 4704
2.使用包dbms_shared_pool。
SCOTT@test> select address,hash_value,sql_text,kept_versions,executions from v$sql where sql_id='64hk7qvafmm0x';
ADDRESS HASH_VALUE SQL_TEXT KEPT_VERSIONS EXECUTIONS
---------------- ---------- ------------------------------------------------------------ ------------- ----------
00000000AF5E3108 3572091933 Select * from dept where deptno=10 0 7
--如果没有安装，自己google，很容易安装的。
--以sys用户执行：
SYS@test> exec dbms_shared_pool.keep('00000000AF5E3108,3572091933','C');
PL/SQL procedure successfully completed.
SCOTT@test> select address,hash_value,sql_text,kept_versions,executions from v$sql where sql_id='64hk7qvafmm0x';
ADDRESS HASH_VALUE SQL_TEXT KEPT_VERSIONS EXECUTIONS
---------------- ---------- ------------------------------------------------------------ ------------- ----------
00000000AF5E3108 3572091933 Select * from dept where deptno=10 65 7
--KEPT_VERSIONS =65.
SYS@test> exec dbms_shared_pool.purge('00000000AF5E3108,3572091933','C');
BEGIN dbms_shared_pool.purge('00000000AF5E3108,3572091933','C'); END;
*
ERROR at line 1:
ORA-06596: object cannot be purged, object is permanently kept in shared pool
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 48
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 115
ORA-06512: at line 1
--可以发现keep的对象，无法清除。
SCOTT@test> @sharepool/shp4 64hk7qvafmm0x
TEXT KGLHDADR KGLHDPAR SUBSTR(KGLNAOBJ,1,20) KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16 N20
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------------------------- ----------
子游标句柄地址 00000000AF5B1CA8 00000000AF5E3108 Select * from dept w 00000000AF5E2E40 00000000AAD52728 4520 12144 3052 19716 19716
父游标句柄地址 00000000AF5E3108 00000000AF5E3108 Select * from dept w 00000000BE2BCB08 00 4704 0 0 4704 4704
--做一次刷新看看。
SCOTT@test> alter system flush SHARED_POOL;
System altered.
SCOTT@test> @sharepool/shp4 64hk7qvafmm0x
TEXT KGLHDADR KGLHDPAR SUBSTR(KGLNAOBJ,1,20) KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16 N20
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------------------------- ----------
子游标句柄地址 00000000AF5B1CA8 00000000AF5E3108 Select * from dept w 00 00 0 0 3052 3052 3052
父游标句柄地址 00000000AF5E3108 00000000AF5E3108 Select * from dept w 00000000BE2BCB08 00 4704 0 0 4704 4704
--子游标句柄的一些chunk会清除掉。父游标句柄不会。
SCOTT@test> select address,hash_value,sql_text,kept_versions,executions from v$sql where sql_id='64hk7qvafmm0x';
no rows selected
--已经无法看到从v$sql视图，这个是访问x$kglcursor_child，子游标信息已经清除，无法看到。
3.再次purge看看，一样无法清除。
SYS@test> exec dbms_shared_pool.purge('00000000AF5E3108,3572091933','C');
BEGIN dbms_shared_pool.purge('00000000AF5E3108,3572091933','C'); END;
*
ERROR at line 1:
ORA-06596: object cannot be purged, object is permanently kept in shared pool
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 48
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 115
ORA-06512: at line 1
SYS@test> select * from v$open_cursor where sql_id='64hk7qvafmm0x';
SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- ---- ---------- ---------------- ---------- ------------- ----------------------------------- ------------------- ----------- ---------------------
00000000BFFFA2B0 5 SCOTT 00000000AF5E3108 3572091933 64hk7qvafmm0x Select * from dept where deptno=10 SESSION CURSOR CACHED
--退出会话，我仅仅在一个session下执行过Select * from dept where deptno=10(特异第1个字符大写)，退出后应该从v$open_cursor退出。
SYS@test> select * from v$open_cursor where sql_id='64hk7qvafmm0x';
no rows selected
SYS@test> set verify off
SYS@test> @sharepool/shp4 64hk7qvafmm0x
TEXT KGLHDADR KGLHDPAR SUBSTR(KGLNAOBJ,1,20) KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16 N20
-------------- ---------------- ---------------- ---------------------- ---------------- ---------------- ---------- ---------- ---------- -------------------------- ----------
子游标句柄地址 00000000AF5B1CA8 00000000AF5E3108 Select * from dept w 00 00 0 0 3052 3052 3052
父游标句柄地址 00000000AF5E3108 00000000AF5E3108 Select * from dept w 00000000BE2BCB08 00 4704 0 0 4704 4704
SYS@test> alter system flush SHARED_POOL ;
System altered.
SYS@test> @sharepool/shp4 64hk7qvafmm0x
TEXT KGLHDADR KGLHDPAR SUBSTR(KGLNAOBJ,1,20) KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16 N20
-------------- ---------------- ---------------- ---------------------- ---------------- ---------------- ---------- ---------- ---------- -------------------------- ----------
子游标句柄地址 00000000AF5B1CA8 00000000AF5E3108 Select * from dept w 00 00 0 0 3052 3052 3052
父游标句柄地址 00000000AF5E3108 00000000AF5E3108 Select * from dept w 00000000BE2BCB08 00 4704 0 0 4704 4704
--可以发现依旧无法清除。
SYS@test> exec dbms_shared_pool.purge('00000000AF5E3108,3572091933','C');
BEGIN dbms_shared_pool.purge('00000000AF5E3108,3572091933','C'); END;
*
ERROR at line 1:
ORA-06596: object cannot be purged, object is permanently kept in shared pool
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 48
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 115
ORA-06512: at line 1
4.收尾工作：
SYS@test> exec dbms_shared_pool.unkeep('00000000AF5E3108,3572091933','C');
PL/SQL procedure successfully completed.
SYS@test> @sharepool/shp4 64hk7qvafmm0x
TEXT KGLHDADR KGLHDPAR SUBSTR(KGLNAOBJ,1,20) KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16 N20
-------------- ---------------- ---------------- ---------------------- ---------------- ---------------- ---------- ---------- ---------- -------------------------- ----------
子游标句柄地址 00000000AF5B1CA8 00000000AF5E3108 Select * from dept w 00 00 0 0 3052 3052 3052
父游标句柄地址 00000000AF5E3108 00000000AF5E3108 Select * from dept w 00000000BE2BCB08 00 4704 0 0 4704 4704
SYS@test> select address,hash_value,sql_text,kept_versions,executions from v$sql where sql_id='64hk7qvafmm0x';
no rows selected
SYS@test> alter system flush SHARED_POOL ;
System altered.
SYS@test> @sharepool/shp4 64hk7qvafmm0x
no rows selected
--可以发现这样清除掉了。
5.补充测试：
--我发现一个现象，pin住的sql_id,执行次数在原来基础上往上增加,即使刷新共享池。继续测试：
SYS@test> select address,hash_value,sql_text,kept_versions,executions from v$sql where sql_id='64hk7qvafmm0x';
ADDRESS HASH_VALUE SQL_TEXT KEPT_VERSIONS EXECUTIONS
---------------- ---------- ------------------------------------------------------------ ------------- ----------
00000000AF619D28 3572091933 Select * from dept where deptno=10 0 9
--现在没有pin在共享池。
SYS@test> alter system flush SHARED_POOL ;
System altered.
SYS@test> select address,hash_value,sql_text,kept_versions,executions from v$sql where sql_id='64hk7qvafmm0x';
no rows selected
--打开另外会话：
SCOTT@test> Select * from dept where deptno=10 ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SCOTT@test> select address,hash_value,sql_text,kept_versions,executions from v$sql where sql_id='64hk7qvafmm0x';
ADDRESS HASH_VALUE SQL_TEXT KEPT_VERSIONS EXECUTIONS
---------------- ---------- ------------------------------------------------------------ ------------- ----------
00000000AF619D28 3572091933 Select * from dept where deptno=10 0 1
--执行次数变成了1.
SYS@test> exec dbms_shared_pool.keep('00000000AF619D28,3572091933','C');
PL/SQL procedure successfully completed.
SYS@test> select address,hash_value,sql_text,kept_versions,executions from v$sql where sql_id='64hk7qvafmm0x';
ADDRESS HASH_VALUE SQL_TEXT KEPT_VERSIONS EXECUTIONS
---------------- ---------- ------------------------------------------------------------ ------------- ----------
00000000AF619D28 3572091933 Select * from dept where deptno=10 65 1
--现在pin在共享池。执行以下语句多次。
SCOTT@test> Select * from dept where deptno=10 ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SYS@test> select address,hash_value,sql_text,kept_versions,executions from v$sql where sql_id='64hk7qvafmm0x';
ADDRESS HASH_VALUE SQL_TEXT KEPT_VERSIONS EXECUTIONS
---------------- ---------- ------------------------------------------------------------ ------------- ----------
00000000AF619D28 3572091933 Select * from dept where deptno=10 65 12
SYS@test> alter system flush SHARED_POOL ;
System altered.
SYS@test> select address,hash_value,sql_text,kept_versions,executions from v$sql where sql_id='64hk7qvafmm0x';
no rows selected
SCOTT@test> Select * from dept where deptno=10 ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SYS@test> select address,hash_value,sql_text,kept_versions,executions from v$sql where sql_id='64hk7qvafmm0x';
ADDRESS HASH_VALUE SQL_TEXT KEPT_VERSIONS EXECUTIONS
---------------- ---------- ------------------------------------------------------------ ------------- ----------
00000000AF619D28 3572091933 Select * from dept where deptno=10 65 13
--可以发现这样执行次数并没有清除，而是在原来基础上增加。
--退出scott用户进入后在执行呢?
SYS@test> alter system flush SHARED_POOL ;
System altered.
SYS@test> select address,hash_value,sql_text,kept_versions,executions from v$sql where sql_id='64hk7qvafmm0x';
no rows selected
SCOTT@test> Select * from dept where deptno=10 ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SYS@test> select address,hash_value,sql_text,kept_versions,executions from v$sql where sql_id='64hk7qvafmm0x';
ADDRESS HASH_VALUE SQL_TEXT KEPT_VERSIONS EXECUTIONS
---------------- ---------- ------------------------------------------------------------ ------------- ----------
00000000AF619D28 3572091933 Select * from dept where deptno=10 65 14
--即使退出刷新共享池后，执行次数还是增加。实际上像buffer_gets也不清除。想起以前优化1个项目时遇到的情况:
http://www.itpub.net/thread-1901317-1-1.html
--rac 下刷新共享池，统计信息不清除???