[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
------------------------------ -------------- --------------------------------------------------------------------------------
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 下刷新共享池，统计信息不清除???

时间： 2024-09-19 09:16:59

[20150316]dbms_shared_pool.keep.txt的相关文章

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

[20171107]dbms_shared_pool.pin补充.txt

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

[20160811]dbms_shared_pool清除子光标.txt

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

[20171031]markhot.txt

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

[20151201]统计分析与GRD.txt

[20151201]统计分析与GRD.txt --上午测试了手工实现资源掌控.下午看看那个对象出现REMASTER_CNT次数最多. 1.环境: SYS@xxxx1> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------

[20150812]11g子游标obsolete.txt

[20150812]11g子游标obsolete.txt --昨天听别人提到11.2.0.3下有一个bug,当子游标数量达到100后,如果产生101个子游标,那么父游标以及100个子游标会设置为obsoleted. --重新生成新的父游标.但是那些过期的child cursor不会从v$sql中消失,dbms_shared_pool.purge也无法将这些过期的child cursor --flush出去. --正好,我目前的测试环境主要是11.2.0.3,自己测试看看. 1.建立测试环境: S

[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

wince下，使用C# ，把数据写入txt文件中

问题描述 wince下,使用C# ,把数据写入txt文件中 各位高手大家好,我在wince下,使用C# ,把数据写入txt文件中,所有的代码都执行完成,也没有发生任何错误,但是我打开txt里面是空白的,没有任何数据,不知其解,望高手不吝指教,谢谢.写入txt的代码如下: FileStream fs = null; StreamWriter sw = null; try { fs = new FileStream(fileName, FileMode.Append, FileAccess.Writ