资源描述：
《06数据库表常驻内存方案》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、oracle将公共表数据常驻oracle数据缓存川1>首先评估公共数据表数据大小，决定db_keep_cache_size大小altersystemsetdb_keep_cache_size=100Mscope=both;2、修改表格属性，让它可以常驻内存altertabletbteststorage(buffer_poolkeep);selecttable_name,tablespace_name,cachefromuser_tables;(止匕时cache为YES)3、如果需要取消该属性，执行下
2、面语句altertabletbteststorage(buffer_pooldefault);[@more@]SQL>selectcomponent’current_sizefromv$sga_dynamic_componentswherecomponent二,KEEPbuffercache1;COMPONENTCURRENT_SIZEKEEPbuffercache12582912这里keeppool10M查看keeppool剩余大小SQL>selectp.name,a.cnum_repl''tot
3、albuffersH,a.anum_repl''freebuffers1'fromx$kcbwdsa,v$buffer_poolpwherea.set_id=p.LO_SETIDandp.name=,KEEP,;NAMEtotalbuffersfreebuffersKEEP14971497可以看到没有使用过keep池selectcomporient,current_size,min_size,max_sizefromv$sga_dynamic_componentswherecomponentin('
4、DEFAULTbuffercache'/KEEPbuffercache'/RECYCLEbuffercache');■■查看放入Keep的对象selectsegment_namefromdba_segmentswhereBUFFER_POOL='KEEP1;SEGMENT_NAMET1••查看表的大小selectbytes/1024/10241I'M'fromdba_segmentswheresegment_name=,Tl,;-查看db_keep_cache_size实际占用空间SELECTSUB
5、STR(SUM(b.NUMBER_OF_BLOCKS)*8192/1024/1024,1,5)
6、
7、'M'TotaLSizeFROM(SELECTo.OBJECT_NAME,COUNT(*)NUMBER_OF_BLOCKSFROMDBA_OBJECTSo,V$BHbh,dba_segmentsddWHEREo.DATA_OBJECT_ID=bh.OBJDANDo.OWNER=dd.ownerANDdd.segment_name=o.OBJECT_NAMEANDdd.buffer_pool!='DEFA
8、ULT'GROUPBYo.OBJECT_NAMEORDERBYCOUNT(*))b;注意事项1.db_keep_cache_size的大小一定要比cache的表的容量大sqlserver:Declare@db」dint,@tbl」dintUseDATABASE_NAMESet@db_id=DBJD(*DATABASE_NAME')Set@tbl」d=Object」D('Department‘)DBCCpintable(@db_id,@tbl_id)可将表Department设置为驻留内存。Decla
9、re@db」dint,@tbl」dintUseDATABASE_NAMESet@db_id=DBJD(,DATABASE_NAME,)Set@tbl」d=Object」D('Department‘)DBCCUNpintable(@db」d,@tbl_id)可将表Department取消设置为驻留内存。可以使用如下的SQL指令来检测执行情况：SelectObjectProperty(Object_lDCDepartment'J/TablelsPinned')如果返回结果为1：则表示该表已经设置为驻留内
10、存；0：则表示没有设置为驻留内存。从sql2005开始不再支持此方法oraclekeeppool试验SQL>select*fromv$version;BANNEROracleDatabasellgEnterpriseEditionRelease11.2.0.1.0-ProductionPL/SQLRelease11.2.0.1.0-ProductionCORE11.2.0.1.0ProductionTNSfor32-bitWindows:Version11.2.0.1
