数据库表常驻内存方案

1、oracle将公共表数据常驻oracle数据缓存中1、首先评估公共数据表数据大小，决定db_keep_cache_size大小alter system set db_keep_cache_size=100M scope=both;2、修改表格属性，让它可以常驻内存alter table tbtest storage(buffer_pool keep);select table_name,tablespace_name,cache from user_tables;(此时cache为YES）3、如果需要取消该属性，执行下面语句alter table tbtest storage(buffer_p

2、ool default) ;moreSQL> select component,current_size from v$sga_dynamic_components where component='KEEP buffer cache'COMPONENT CURRENT_SIZE- -KEEP buffer cache 12582912 这里keep pool 10M查看keep pool剩余大小SQL> select ,um_repl "total buffers",a.anum_repl "free buffers&qu

3、ot; from x$kcbwds a, v$buffer_pool pwhere a.set_id=p.LO_SETID and ='KEEP'NAME total buffers free buffers- - -KEEP 1497 1497 可以看到没有使用过keep 池select component, current_size, min_size, max_size from v$sga_dynamic_components where component in ('DEFAULT buffer cache','KEEP buffe

4、r cache','RECYCLE buffer cache');-查看放入Keep的对象select segment_name from dba_segments where BUFFER_POOL = 'KEEP'SEGMENT_NAME-T1-查看表的大小select bytes/1024/1024|'M' from dba_segments where segment_name='T1'-查看db_keep_cache_size实际占用空间SELECT SUBSTR (SUM (b.NUMBER_OF_BLOCKS

5、) * 8192 / 1024 / 1024, 1, 5) | 'M'Total_SizeFROM ( SELECT o.OBJECT_NAME, COUNT (*)NUMBER_OF_BLOCKSFROM DBA_OBJECTS o, V$BH bh,dba_segments ddWHERE o.DATA_OBJECT_ID= bh.OBJDAND o.OWNER = dd.ownerAND dd.segment_name= o.OBJECT_NAMEAND dd.buffer_pool != 'DEFAULT'GROUP BY o.OBJECT_NAMEOR

6、DER BY COUNT (*) b;注意事项1.db_keep_cache_size的大小一定要比cache的表的容量大sqlserver:Declare db_id int, tbl_id intUse DATABASE_NAMESet db_id = DB_ID('DATABASE_NAME')Set tbl_id = Object_ID('Department')DBCC pintable (db_id, tbl_id)可将表Department设置为驻留内存。Declare db_id int, tbl_id intUse DATABASE_NAMES

7、et db_id = DB_ID('DATABASE_NAME')Set tbl_id = Object_ID('Department')DBCC UNpintable (db_id, tbl_id)可将表Department取消设置为驻留内存。可以使用如下的SQL指令来检测执行情况：Select ObjectProperty(Object_ID('Department'),'TableIsPinned')如果返回结果为1：则表示该表已经设置为驻留内存；0：则表示没有设置为驻留内存。从sql2005开始不再支持此方法oracle

8、keep pool试验SQL> select * from v$version;BANNER-Oracle Database 11g Enterprise Edition Release .0 - ProductionPL/SQL Release .0 - ProductionCORE .0 ProductionTNS for 32-bit Windows: Version .0 - ProductionNLSRTL Version .0 - ProductionSQL> select * from v

9、$sgainfo;NAME BYTES RES - - - Fixed SGA Size 1376408 No Redo Buffers 12578816 No Buffer Cache Size 1015021568 Yes Shared Pool Size 209715200 Yes Large Pool Size 8388608 Yes Java Pool Size 8388608 Yes Streams Pool Size 0 Yes Shared IO Pool Size 0 Yes Granule Size 8388608 No Maximum SGA Size 125547315

10、2 No Startup overhead in Shared Pool 75497472 No NAME BYTES RES - - - Free SGA Memory Available 0 已选择12行。SQL> show parameter db_keep_cache;NAME TYPE VALUE - - - db_keep_cache_size big integer 656M SQL> select x.ksppinm name,y.ksppstvl value,x.ksppdesc describfrom sys.x$ksppi x,sys.x$ksppcv ywh

11、ere x.indx=y.indx and x.ksppinm like '%_db_cache_size%'NAME -VALUE -DESCRIB -_db_cache_size 327155712 Actual size of DEFAULT buffer pool for standard block size buffers SQL> create table t1 as select * from dba_objects;表已创建。SQL> alter table t1 storage(buffer_pool keep);表已更改。SQL> sel

12、ect segment_name from dba_segments where buffer_pool='KEEP'SEGMENT_NAME -T1 SQL> select bytes/1024/1024 "bytes(M)" from dba_segments where segment_name='T1'bytes(M) - 9 SQL> set autot traceonly statSQL> select * from t1;已选择74380行。统计信息- 453 recursive calls 0 db bloc

13、k gets 6074 consistent gets 1056 physical reads 0 redo size 8236245 bytes sent via SQL*Net to client 54954 bytes received via SQL*Net from client 4960 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 74380 rows processed SQL> /已选择74380行。统计信息- 0 recursive calls 0 db block gets 593

14、8 consistent gets 0 physical reads 0 redo size 8236245 bytes sent via SQL*Net to client 54954 bytes received via SQL*Net from client 4960 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 74380 rows processed -查看db_keep_cache_size实际占用空间SQL> set autotrace onSQL> SELECT SUBSTR (S

15、UM (b.NUMBER_OF_BLOCKS) * 8192 / 1024 / 1024, 1, 5) | 'M'Total_SizeFROM ( SELECT o.OBJECT_NAME, COUNT (*)NUMBER_OF_BLOCKSFROM DBA_OBJECTS o, V$BH bh,dba_segments ddWHERE o.DATA_OBJECT_ID= bh.OBJDAND o.OWNER = dd.ownerAND dd.segment_name= o.OBJECT_NAMEAND dd.buffer_pool != 'DEFAULT'GR

16、OUP BY o.OBJECT_NAMEORDER BY COUNT (*) b;TOTAL_SIZE - 8.257M SQL> select ,um_repl "total buffers",a.anum_repl "free buffers",um_repl-a.anum_repl "被使用的buffer" from x$kcbwds a, v$buffer_pool pwhere a.set_id=p.LO_SETID and ='KEEP'NAME total buffers f

17、ree buffers 被使用的buffer - - - - KEEP 20398 20043 355 SQL> insert into t1 select * from t1;已创建74380行。执行计划- Plan hash value: 3617692013 - | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | - | 0 | INSERT STATEMENT | | 129K| 25M| 576 (1)| 00:00:07 | | 1 | LOAD TABLE CONVENTIONAL | T1 | | |

18、| | | 2 | TABLE ACCESS FULL | T1 | 129K| 25M| 576 (1)| 00:00:07 | - Note - - dynamic sampling used for this statement (level=2) 统计信息- 315 recursive calls 5938 db block gets 3600 consistent gets 0 physical reads 8624012 redo size 691 bytes sent via SQL*Net to client 601 bytes received via SQL*Net fro

19、m client 3 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 74380 rows processed SQL> commit;提交完成。SQL> select count(*) from t1;COUNT(*) - 148760 执行计划- Plan hash value: 3724264953 - | Id | Operation | Name | Rows | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | 1 | 575 (1)| 0

20、0:00:07 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T1 | 129K| 575 (1)| 00:00:07 | - Note - - dynamic sampling used for this statement (level=2) 统计信息- 4 recursive calls 0 db block gets 2180 consistent gets 0 physical reads 0 redo size 425 bytes sent via SQL*Net to client 416 bytes re

21、ceived via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> insert into t1 select * from t1;已创建148760行。SQL> commit;提交完成。SQL> select count(*) from t1;COUNT(*) - 297520 执行计划- Plan hash value: 3724264953 - | Id | Operation | Name | R

22、ows | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | 1 | 1147 (1)| 00:00:14 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T1 | 310K| 1147 (1)| 00:00:14 | - Note - - dynamic sampling used for this statement (level=2) 统计信息- 0 recursive calls 0 db block gets 4222 consistent gets 0 physi

23、cal reads 0 redo size 425 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> insert into t1 select * from t1;已创建297520行。 SQL> commit;提交完成。SQL> select count(*) from t1;COUNT(*)

24、- 595040 执行计划- Plan hash value: 3724264953 - | Id | Operation | Name | Rows | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | 1 | 2289 (1)| 00:00:28 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T1 | 560K| 2289 (1)| 00:00:28 | - Note - - dynamic sampling used for this statement (level

25、=2) 统计信息- 0 recursive calls 0 db block gets 8431 consistent gets 0 physical reads 0 redo size 425 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> insert into t1 select * from t1;已

26、创建595040行。SQL> commit;提交完成。SQL> select count(*) from t1;COUNT(*) - 1190080 执行计划- Plan hash value: 3724264953 - | Id | Operation | Name | Rows | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | 1 | 4576 (1)| 00:00:55 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T1 | 1279K| 4576 (

27、1)| 00:00:55 | - Note - - dynamic sampling used for this statement (level=2) 统计信息- 0 recursive calls 0 db block gets 16844 consistent gets 0 physical reads 0 redo size 426 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory)

28、 0 sorts (disk) 1 rows processed SQL> select owner,bytes/1024/1024|'M' from dba_segments where segment_name='T1'OWNER BYTES/1024/1024|'M' - - SYS 136M SQL> insert into t1 select * from t1;已创建1190080行。SQL> commit;提交完成。SQL> insert into t1 select * from t1;已创建2380160

29、行。SQL> commit;提交完成。SQL> select count(*) from t1;COUNT(*) - 4760320 执行计划- Plan hash value: 3724264953 - | Id | Operation | Name | Rows | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | 1 | 18296 (1)| 00:03:40 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T1 | 5280K| 18296 (1)| 00

30、:03:40 | - Note - - dynamic sampling used for this statement (level=2) 统计信息- 0 recursive calls 0 db block gets 100976 consistent gets 45103 physical reads 2424492 redo size 426 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> /COUNT(*) - 4760320 执行计划- Plan hash value: 3724264953 - | Id | Operation | Name | Rows | Co