keep池

问题：有一张表，里面仅有几百最多不过几千条记录，而业务需要，每条都要对此表进行N（上万）次的查询操作，有什么方法可以大大缩短查询此表的响应时间？

 

解答（不一定是唯一方法，但是很有效）：将此表放于keep池中，

 

keep池的数据可以只要被load进内存以后,就可以常驻内存中,哪怕使用alter system flush shared pool也不会将他们调出,这样就可以大大提高这些数据的访问速度

oracle 7 以前

alter table tab_name cache;

oracle 8 及以后

alter table tab_name storage (buffer_pool keep);

 

对于不常用的大表全表扫描的表保存在回收池，以便不影响其他对象正常的使用默认BUFFER池，但是在初始配置时要先设置KEEP池和回收池，他不是share pool大小的一部分，数据库启动后就不能动态的修改他。

附：keep池详细介绍（摘抄，原文作者别跟我打官司哈）

 

二.  KeepPool 相关测试

 

2.1 keep基本测试

 

-- 查看SGA 信息

 

SYS@anqing2(rac2)> select * fromv$sgainfo;

 

NAME                                     BYTES RES

 

--------------------------------------------- ---

 

Fixed SGA Size                         1267068 No

 

Redo Buffers                           2924544 No

 

Buffer Cache Size                    150994944 Yes

 

Shared Pool Size                     113246208 Yes

 

Large Pool Size                        4194304 Yes

 

Java Pool Size                         4194304 Yes

 

Streams Pool Size                      8388608 Yes

 

Granule Size                           4194304 No

 

Maximum SGA Size                     285212672 No

 

Startup overhead in Shared Pool       46137344 No

 

Free SGA Memory Available                    0

 

 

 

11 rows selected.

 

--查看 keeppool 大小

 

SYS@anqing2(rac2)> show parameterdb_keep_cache_size

 

 

 

NAME                    TYPE       VALUE

 

----------------------------------------------- ------------------------------

 

db_keep_cache_size          big integer  0

 

 

 

--查看db_cache_size大小

 

SYS@anqing2(rac2)> SELECT x.ksppinm NAME,y.ksppstvl VALUE, x.ksppdesc describ FROM SYS.x$ksppi x, SYS.x$ksppcv y WHEREx.indx = y.indx AND x.ksppinm LIKE '%__db_cache_size%';

 

 

 

NAME            VALUE           DESCRIB

 

--------------- --------------- ------------------------------------------------

 

__db_cache_size 150994944   Actual size of DEFAULT buffer pool forstandard

 

 

 

--手动指定keeppool

 

SYS@anqing2(rac2)> alter system set db_keep_cache_size=50Mscope=both sid='anqing2';

 

System altered.

 

 

 

-- 查看db_keep_cache_size和 db_cache_size 大小

 

 

 

SYS@anqing2(rac2)> show parameterdb_keep_cache_size

 

 

 

NAME                                 TYPE        VALUE

 

----------------------------------------------- ------------------------------

 

db_keep_cache_size                   big integer 52M

 

 

 

SYS@anqing2(rac2)> SELECT x.ksppinmNAME, y.ksppstvl VALUE, x.ksppdesc describ FROM SYS.x$ksppi x, SYS.x$ksppcv yWHERE x.indx = y.indx AND x.ksppinm LIKE '%__db_cache_size%';

 

 

 

NAME                 VALUE           DESCRIB

 

-------------------------------------------------- ----------------------------

 

__db_cache_size        96468992        Actual size of DEFAULT buffe

 

-- 这个验证增加db_keep_cache_size时，db_cache_size 就会减小。

 

 

 

--表keep 到keepbuffer

 

SYS@anqing2(rac2)> create table t1 asselect * from dba_objects;

 

Table created.

 

SYS@anqing2(rac2)> alter table t1storage(buffer_pool keep);

 

Table altered.

 

--或者

 

SYS@anqing2(rac2)> create table t1storage(buffer_pool keep) as select * from dba_objects;

 

Table created.

 

 

 

--查看放入Keep的对象

 

SYS@anqing2(rac2)>  select segment_name from dba_segments whereBUFFER_POOL = 'KEEP';

 

SEGMENT_NAME

 

----------------------------------------

 

T1

 

 

 

--查看表的大小

 

SYS@anqing2(rac2)> selectbytes/1024/1024||'M' from dba_segments where segment_name='T1';

 

 

 

BYTES/1024/1024||'M'

 

-----------------------------------------

 

6M

 

 

 

--select 全表，把数据加载到keep pool

 

SYS@anqing2(rac2)> set autot traceonlystat

 

SYS@anqing2(rac2)> select * from t1;

 

50261 rows selected.

 

 

 

Statistics

 

----------------------------------------------------------

 

         0  recursive calls

 

         0  db block gets

 

       705  consistent gets

 

       691  physical reads

 

          0 redo size

 

   2116604  bytes sent via SQL*Net toclient

 

       510  bytes received via SQL*Netfrom client

 

        12  SQL*Net roundtrips to/fromclient

 

         0  sorts (memory)

 

         0  sorts (disk)

 

     50261  rows processed

 

 

 

SYS@anqing2(rac2)> /

 

50261 rows selected.

 

Statistics

 

----------------------------------------------------------

 

         0  recursive calls

 

         0  db block gets

 

       705  consistent gets

 

         0  physical reads

 

         0  redo size

 

    2116604 bytes sent via SQL*Net to client

 

       510  bytes received via SQL*Netfrom client

 

        12  SQL*Net roundtrips to/fromclient

 

         0  sorts (memory)

 

         0  sorts (disk)

 

     50261  rows processed

 

--第二次查询没有了物理读，数据已经刷到了keep pool 里。

 

 

 

 

 

--查看db_keep_cache_size实际占用空间

 

/* Formatted on 2011/7/2 17:15:15(QP5 v5.163.1008.3004) */

 

SELECT SUBSTR (SUM (b.NUMBER_OF_BLOCKS) * 8129 / 1024 / 1024, 1, 5) || 'M'Total_Size

 

 FROM (  SELECT o.OBJECT_NAME, COUNT (*)NUMBER_OF_BLOCKS

 

            FROMDBA_OBJECTS o, V$BHbh,dba_segments dd

 

           WHERE     o.DATA_OBJECT_ID= bh.OBJD

 

                 AND o.OWNER = dd.owner

 

                 AND dd.segment_name= o.OBJECT_NAME

 

                 AND dd.buffer_pool != 'DEFAULT'

 

        GROUP BY o.OBJECT_NAME

 

        ORDER BY COUNT (*)) b;

 

 

 

TOTAL_SIZE

 

-----------

 

9.566M

 

 

 

-- 取消keep

 

            默认情况下数据是放到default pool的，所以，我们取消keep，只需要重新指定存储位置到default即可。

 

 

 

SYS@anqing2(rac2)> alter table t1 storage(buffer_pool default);

 

Table altered.

 

 

 

--查看keep

 

SYS@anqing2(rac2)> select segment_namefrom dba_segments where BUFFER_POOL = 'KEEP';

 

no rows selected