keep pool 测试

--Keep Buffer Pool 的作用是缓存那些需要经常查询的对象但又容易被默认缓冲区置换出去的对象，按惯例，Keep pool设置为合理的大小，以使其中存储的对象不再age out，也就是查询这个对象的操作不会引起磁盘IO操作，可以极大地提高查询性能

--测试如下

---db info
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

---sga info
SQL> select * from v$sgainfo;

NAME                                  BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      1337464 No
Redo Buffers                        5922816 No
Buffer Cache Size                 130023424 Yes
Shared Pool Size                  176160768 Yes
Large Pool Size                     4194304 Yes
Java Pool Size                      4194304 Yes
Streams Pool Size                   4194304 Yes
Shared IO Pool Size                       0 Yes
Granule Size                        4194304 No
Maximum SGA Size                  497995776 No
Startup overhead in Shared Pool    58720256 No

NAME                                  BYTES RES
-------------------------------- ---------- ---
Free SGA Memory Available         171966464

12 rows selected.

---db_keep_cache_size大小
SQL>  show parameter db_keep_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_keep_cache_size                   big integer 0

---创建测试表
SQL> create table zyp as select * from dba_objects;

Table created.

---测试表大小
SQL> select OWNER,segment_name,bytes/1024/1024 "size M",segment_type from dba_segments where wner='SYS' and segment_name='ZYP';

OWNER                          SEGMENT_NAME                                                                          size M SEGMENT_TYPE
------------------------------ --------------------------------------------------------------------------------- ---------- ------------------
SYS                            ZYP                                                                                        9 TABLE

---修改db_keep_cache_size
SQL> show parameter keep

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep                     string
control_file_record_keep_time        integer     7
db_keep_cache_size                   big integer 0

SQL>
SQL> alter system set db_keep_cache_size=20M scope=both;

System altered.

SQL> show parameter keep

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep                     string
control_file_record_keep_time        integer     7
db_keep_cache_size                   big integer 20M

---keep 表
SQL> alter table zyp storage(buffer_pool keep);

Table altered.

---查看keep info
SQL> select segment_name from dba_segments where BUFFER_POOL = 'KEEP';

SEGMENT_NAME
---------------------------------------------------------------------------------
ZYP

---检测
SQL> set autotrace traceonly;
SQL>
SQL>
SQL> select * from zyp;

72457 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2294585251

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 68029 |    13M|   282   (1)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| ZYP  | 68029 |    13M|   282   (1)| 00:00:04 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
        453  recursive calls
          0  db block gets
       5907  consistent gets
       1030  physical reads --------------------------------------&gt第一次物理读1030
          0  redo size
    8035619  bytes sent via SQL*Net to client
      53550  bytes received via SQL*Net from client
       4832  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      72457  rows processed

SQL>
SQL> select * from zyp;

72457 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2294585251

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 68029 |    13M|   282   (1)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| ZYP  | 68029 |    13M|   282   (1)| 00:00:04 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5799  consistent gets
          0  physical reads --------------------------------------&gt第二次物理读0
          0  redo size
    8035619  bytes sent via SQL*Net to client
      53550  bytes received via SQL*Net from client
       4832  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      72457  rows processed

---keep info
SQL> SELECT SUBSTR(SUM(b.NUMBER_OF_BLOCKS) * 8129 / 1024 / 1024, 1, 5) || 'M' Total_Size
  2    FROM (SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS
  3            FROM DBA_OBJECTS o, V$BH bh, dba_segments dd
  4           WHERE o.DATA_OBJECT_ID = bh.OBJD
  5             AND o.OWNER = dd.owner
  6             AND dd.segment_name = o.OBJECT_NAME
  7             AND dd.buffer_pool != 'DEFAULT'
  8           GROUP BY o.OBJECT_NAME
  9           ORDER BY COUNT(*)) b;

TOTAL_
------
7.992M

---取消keep表
SQL> alter table zyp  storage(buffer_pool default);

Table altered.

SQL> select segment_name from dba_segments where BUFFER_POOL = 'KEEP';

no rows selected

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28278387/viewspace-750385/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28278387/viewspace-750385/