测试环境:solaris 10
oracle 10g
1.对于经常使用的热点、访问次数多的表我们可以放置在db_keep_cache_size中、
JSP页面直接从cache中获取、提升访问的速度。但是如果表的大小超过了
db_keep_cache_size的大小的时候、是无法提高表的访问速度。
2.
查看数据库中已经有那些表被标记为热点表?
select owner,segment_type,segment_name,buffer_pool
from dba_segments
where buffer_pool != 'DEFAULT';
OWNER |
SEGMENT_TYPE |
SEGMENT_NAME |
BUFFER_POOL |
test1 |
TABLE |
APARTMENT_MAIN_T |
KEEP |
test1 |
TABLE |
APARTMENT_MAINS_FLOORS_T |
KEEP |
test1 |
TABLE |
APARTMENT_MAINS_ROOMS_T |
KEEP |
test1 |
TABLE |
APARTMENT_MAINS_BEDSPACE_T |
KEEP |
test1 |
TABLE |
APARTMENT_BIDE_STUBED_T |
KEEP |
test1 |
TABLE |
APARTMENT_BIDE_MAINCOLLEGE_T |
KEEP |
test2 |
TABLE |
ORG_THCIC_SESSION |
KEEP |
3.查看总的大小
SQL> show parameter db_keep_cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_keep_cache_size big integer 128M
修改的话:
alter system set db_keep_cache_size=128M scope=both;
4.查看以上的几张表占用了db_keep_cache_size 多大的空间?
select substr(sum(b.NUMBER_OF_BLOCKS)*8129/1024/1024,1,5) ||'M' from (SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS
FROM DBA_OBJECTS o, V$BH bh,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
SUBSTR(SUM(B.NUMBER_OF_BLOCKS)
------------------------------
12.26M
5.剩余空间:
SQL> select 128-12.26 || 'M' 剩余空间 from dual;
剩余空
-------
115.74M
6.如何查看单张表大小?是否可以足够空间放置在db_keep_cache_size 空间中?
SELECT o.OBJECT_NAME, COUNT(*)*8129/1024||'K' NUMBER_OF_BLOCKS
FROM DBA_OBJECTS o, V$BH bh
WHERE o.DATA_OBJECT_ID = bh.OBJD
AND o.OBJECT_NAME = 'ORG_THCIC_SESSION'
GROUP BY o.OBJECT_NAME
ORDER BY COUNT(*);
ORG_THCIC_SESSION
333.416015625K
7.通过对比5.6步骤可以看见
可以使用
alter table ORG_THCIC_SESSION storage(buffer_pool keep);
将ORG_THCIC_SESSION 放置在keep区域。
oracle中如何将表缓存到内存中
由于在一些静态资料表在 数据库中被频繁的访问,所以可以考虑将这些数据量不大的表缓存到内存当中。
共有2种方法:
例:将fisher表缓存到内存中
1
2
3
4
5
6
7
8
9
10
11
12
|
1) alter
table
fisher cache;
2) alter
table
fisher storage(buffer_pool keep);
--取消缓存
1) alter
table
fisher nocache;
2) alter
table
fisher storage(buffer_pool default );
select
table_name,OWNER,cache,buffer_pool from
dba_tables where
table_name= 'FISHER' ;
--查看是否缓存
select
* from
dba_segments where
segment_name= 'FISHER'
; --查看表大小
|
两者区别:
1) cache是将表缓存到share pool 中,该操作直接将表缓存的热端,受LRU算法控制。
2)将表缓存到一个固定的内存空间中,默认情况下buffer_pool空间为0,。需手动设置空间大小。
1
|
alter
system set
db_keep_cache_size=50M scope=both sid=‘*';
|
转自:http://pslinux.blog.163.com/blog/static/8686258120106294621625/
http://www./database/201307/229265.html
|