解答(不一定是唯一方法,但是很有效):将此表放于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
source:http://blog.163.com/for_dba/blog/static/19562325020118231515257/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7583803/viewspace-717364/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7583803/viewspace-717364/