數(shù)據(jù)庫表常駐內(nèi)存方案

1、oracle將公共表數(shù)據(jù)常駐oracle數(shù)據(jù)緩存中1、首先評估公共數(shù)據(jù)表數(shù)據(jù)大小，決定db_keep_cache_size大小alter system set db_keep_cache_size=100M scope=both;2、修改表格屬性，讓它可以常駐內(nèi)存alter table tbtest storage(buffer_pool keep);select table_name,tablespace_name,cache from user_tables;(此時cache為YES）3、如果需要取消該屬性，執(zhí)行下面語句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 可以看到?jīng)]有使用過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設(shè)置為駐留內(nèi)存。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取消設(shè)置為駐留內(nèi)存?？梢允褂萌缦碌腟QL指令來檢測執(zhí)行情況：Select ObjectProperty(Object_ID('Department'),'TableIsPinned')如果返回結(jié)果為1：則表示該表已經(jīng)設(shè)置為駐留內(nèi)存；0：則表示沒有設(shè)置為駐留內(nèi)存。從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;表已創(chuàng)建。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行。統(tǒng)計信息- 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行。統(tǒng)計信息- 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;已創(chuàng)建74380行。執(zhí)行計劃- 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) 統(tǒng)計信息- 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 執(zhí)行計劃- 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) 統(tǒng)計信息- 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;已創(chuàng)建148760行。SQL> commit;提交完成。SQL> select count(*) from t1;COUNT(*) - 297520 執(zhí)行計劃- 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) 統(tǒng)計信息- 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;已創(chuàng)建297520行。 SQL> commit;提交完成。SQL> select count(*) from t1;COUNT(*)

24、- 595040 執(zhí)行計劃- 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) 統(tǒng)計信息- 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、創(chuàng)建595040行。SQL> commit;提交完成。SQL> select count(*) from t1;COUNT(*) - 1190080 執(zhí)行計劃- 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) 統(tǒng)計信息- 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;已創(chuàng)建1190080行。SQL> commit;提交完成。SQL> insert into t1 select * from t1;已創(chuàng)建2380160

29、行。SQL> commit;提交完成。SQL> select count(*) from t1;COUNT(*) - 4760320 執(zhí)行計劃- 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) 統(tǒng)計信息- 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 執(zhí)行計劃- Plan hash value: 3724264953 - | Id | Operation | Name | Rows | Co