06 数据库表常驻内存方案.docx
《06 数据库表常驻内存方案.docx》由会员分享，可在线阅读，更多相关《06 数据库表常驻内存方案.docx（12页珍藏版）》请在冰豆网上搜索。
06数据库表常驻内存方案
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、如果需要取消该属性，执行下面语句
altertabletbteststorage（buffer_pooldefault）;
[@more@]
SQL>selectcomponent,current_sizefromv$sga_dynamic_componentswherecomponent='KEEPbuffercache';
COMPONENTCURRENT_SIZE
----------------------------------------------------------------------------
KEEPbuffercache12582912
这里keeppool10M
查看keeppool剩余大小
SQL>selectp.name,um_repl"totalbuffers",a.anum_repl"freebuffers"fromx$kcbwdsa,v$buffer_poolp
wherea.set_id=p.LO_SETIDandp.name='KEEP';
NAMEtotalbuffersfreebuffers
---------------------------------------------
KEEP14971497
可以看到没有使用过keep池
selectcomponent,current_size,min_size,max_sizefromv$sga_dynamic_componentswherecomponentin（'DEFAULTbuffercache','KEEPbuffercache','RECYCLEbuffercache'）;
--查看放入Keep的对象
selectsegment_namefromdba_segmentswhereBUFFER_POOL='KEEP';
SEGMENT_NAME
----------------------------------------
T1
--查看表的大小
selectbytes/1024/1024||'M'fromdba_segmentswheresegment_name='T1';
--查看db_keep_cache_size实际占用空间
SELECTSUBSTR（SUM（b.NUMBER_OF_BLOCKS）*8192/1024/1024,1,5）||'M'Total_Size
FROM（SELECTo.OBJECT_NAME,COUNT（*）NUMBER_OF_BLOCKS
FROMDBA_OBJECTSo,V$BHbh,dba_segmentsdd
WHEREo.DATA_OBJECT_ID=bh.OBJD
ANDo.OWNER=dd.owner
ANDdd.segment_name=o.OBJECT_NAME
ANDdd.buffer_pool!
='DEFAULT'
GROUPBYo.OBJECT_NAME
ORDERBYCOUNT（*））b;
注意事项
1.db_keep_cache_size的大小一定要比cache的表的容量大
sqlserver:
Declare@db_idint,@tbl_idint
UseDATABASE_NAME
Set@db_id=DB_ID（'DATABASE_NAME'）
Set@tbl_id=Object_ID（'Department'）
DBCCpintable（@db_id,@tbl_id）
可将表Department设置为驻留内存。
Declare@db_idint,@tbl_idint
UseDATABASE_NAME
Set@db_id=DB_ID（'DATABASE_NAME'）
Set@tbl_id=Object_ID（'Department'）
DBCCUNpintable（@db_id,@tbl_id）
可将表Department取消设置为驻留内存。
可以使用如下的SQL指令来检测执行情况：
SelectObjectProperty（Object_ID（'Department'）,'TableIsPinned'）
如果返回结果为1：
则表示该表已经设置为驻留内存；0：
则表示没有设置为驻留内存。
从sql2005开始不再支持此方法
oraclekeeppool试验
SQL>select*fromv$version;
BANNER
------------------------------------------------------------------------
OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production
PL/SQLRelease11.2.0.1.0-Production
CORE11.2.0.1.0Production
TNSfor32-bitWindows:
Version11.2.0.1.0-Production
NLSRTLVersion11.2.0.1.0-Production
SQL>select*fromv$sgainfo;
NAMEBYTESRES
---------------------------------------------
FixedSGASize1376408No
RedoBuffers12578816No
BufferCacheSize1015021568Yes
SharedPoolSize209715200Yes
LargePoolSize8388608Yes
JavaPoolSize8388608Yes
StreamsPoolSize0Yes
SharedIOPoolSize0Yes
GranuleSize8388608No
MaximumSGASize1255473152No
StartupoverheadinSharedPool75497472No
NAMEBYTESRES
---------------------------------------------
FreeSGAMemoryAvailable0
已选择12行。
SQL>showparameterdb_keep_cache;
NAMETYPEVALUE
-----------------------------------------------------------------------------
db_keep_cache_sizebiginteger656M
SQL>selectx.ksppinmname,y.ksppstvlvalue,x.ksppdescdescrib
fromsys.x$ksppix,sys.x$ksppcvy
wherex.indx=y.indxandx.ksppinmlike'%_db_cache_size%';
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DESCRIB
--------------------------------------------------------------------------------
__db_cache_size
327155712
ActualsizeofDEFAULTbufferpoolforstandardblocksizebuffers
SQL>createtablet1asselect*fromdba_objects;
表已创建。
SQL>altertablet1storage（buffer_poolkeep）;
表已更改。
SQL>selectsegment_namefromdba_segmentswherebuffer_pool='KEEP';
SEGMENT_NAME
--------------------------------------------------------------------------------
T1
SQL>selectbytes/1024/1024"bytes（M）"fromdba_segmentswheresegment_name='T1';
bytes（M）
----------
9
SQL>setautottraceonlystat
SQL>select*fromt1;
已选择74380行。
统计信息
----------------------------------------------------------
453recursivecalls
0dbblockgets
6074consistentgets
1056physicalreads
0redosize
8236245bytessentviaSQL*Nettoclient
54954bytesreceivedviaSQL*Netfromclient
4960SQL*Netroundtripsto/fromclient
2sorts（memory）
0sorts（disk）
74380rowsprocessed
SQL>/
已选择74380行。
统计信息
----------------------------------------------------------
0recursivecalls
0dbblockgets
5938consistentgets
0physicalreads
0redosize
8236245bytessentviaSQL*Nettoclient
54954bytesreceivedviaSQL*Netfromclient
4960SQL*Netroundtripsto/fromclient
0sorts（memory）
0sorts（disk）
74380rowsprocessed
--查看db_keep_cache_size实际占用空间
SQL>setautotraceon
SQL>SELECTSUBSTR（SUM（b.NUMBER_OF_BLOCKS）*8192/1024/1024,1,5）||'M'Total_Size
FROM（SELECTo.OBJECT_NAME,COUNT（*）NUMBER_OF_BLOCKS
FROMDBA_OBJECTSo,V$BHbh,dba_segmentsdd
WHEREo.DATA_OBJECT_ID=bh.OBJD
ANDo.OWNER=dd.owner
ANDdd.segment_name=o.OBJECT_NAME
ANDdd.buffer_pool!
='DEFAULT'
GROUPBYo.OBJECT_NAME
ORDERBYCOUNT（*））b;
TOTAL_SIZE
-----------
8.257M
SQL>selectp.name,um_repl"totalbuffers",a.anum_repl"freebuffers",um_repl-a.anum_repl"被使用的buffer"
fromx$kcbwdsa,v$buffer_poolp
wherea.set_id=p.LO_SETIDandp.name='KEEP';
NAMEtotalbuffersfreebuffers被使用的buffer
-----------------------------------------------------------
KEEP2039820043355
SQL>insertintot1select*fromt1;
已创建74380行。
执行计划
----------------------------------------------------------
Planhashvalue:
3617692013
--------------------------------------------------------------------------------
-
|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time
|
--------------------------------------------------------------------------------
-
|0|INSERTSTATEMENT||129K|25M|576
（1）|00:
00:
07
|
|1|LOADTABLECONVENTIONAL|T1||||
|
|2|TABLEACCESSFULL|T1|129K|25M|576
（1）|00:
00:
07
|
--------------------------------------------------------------------------------
-
Note
-----
-dynamicsamplingusedforthisstatement（level=2）
统计信息
----------------------------------------------------------
315recursivecalls
5938dbblockgets
3600consistentgets
0physicalreads
8624012redosize
691bytessentviaSQL*Nettoclient
601bytesreceivedviaSQL*Netfromclient
3SQL*Netroundtripsto/fromclient
3sorts（memory）
0sorts（disk）
74380rowsprocessed
SQL>commit;
提交完成。
SQL>selectcount（*）fromt1;
COUNT（*）
----------
148760
执行计划
----------------------------------------------------------
Planhashvalue:
3724264953
-------------------------------------------------------------------
|Id|Operation|Name|Rows|Cost（%CPU）|Time|
-------------------------------------------------------------------
|0|SELECTSTATEMENT||1|575
（1）|00:
00:
07|
|1|SORTAGGREGATE||1|||
|2|TABLEACCESSFULL|T1|129K|575
（1）|00:
00:
07|
-------------------------------------------------------------------
Note
-----
-dynamicsamplingusedforthisstatement（level=2）
统计信息
----------------------------------------------------------
4recursivecalls
0dbblockgets
2180consistentgets
0physicalreads
0redosize
425bytessentviaSQL*Nettoclient
416bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts（memory）
0sorts（disk）
1rowsprocessed
SQL>insertintot1select*fromt1;
已创建148760行。
SQL>commit;
提交完成。
SQL>selectcount（*）fromt1;
COUNT（*）
----------
297520
执行计划
----------------------------------------------------------
Planhashvalue:
3724264953
-------------------------------------------------------------------
|Id|Operation|Name|Rows|Cost（%CPU）|Time|
-------------------------------------------------------------------
|0|SELECTSTATEMENT||1|1147
（1）|00:
00:
14|
|1|SORTAGGREGATE||1|||
|2|TABLEACCESSFULL|T1|310K|1147
（1）|00:
00:
14|
-------------------------------------------------------------------
Note
-----
-dynamicsamplingusedforthisstatement（level=2）
统计信息
----------------------------------------------------------
0recursivecalls
0dbblockgets
4222consistentgets
0physicalreads
0redosize
425bytessentviaSQL*Nettoclient
416bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts（memory）
0sorts（disk）
1rowsprocessed
SQL>insertintot1select*fromt1;
已创建297520行。
SQL>commit;
提交完成。
SQL>selectcount（*）fromt1;
COUNT（*）
----------
595040
执行计划
----------------------------------------------------------
Planhashvalue:
3724264953
-------------------------------------------------------------------
|Id|Operation|Name|Rows|Cost（%CPU）|Time|
-------------------------------------------------------------------
|0|SELECTSTATEMENT||1|2289
（1）|00:
00:
28|
|1|SORTAGGREGATE||1|||
|2|TABLEACCESSFULL|T1|560K|2289
（1）|00:
00:
28|
-------------------------------------------------------------------
Note
-----
-dynamicsamplingusedforthisstatement（level=2）
统计信息
----------------------------------------------------------
0recursivecalls
0dbbloc
升级会员 