将对象注入内存——1，将数据（表和索引）常驻内存－keep_cache
1. 收集统计信息
SQL> analyze table scott.dept compute statistics;
Table analyzed.
或
SQL> EXECUTE dbms_stats.gather_table_stats('SCOTT','DEPT');
PL/SQL procedure successfully completed.
SQL> col SEGMENT_NAME format a10
SQL> select owner,segment_name,segment_type,blocks
2 from dba_segments
3 where owner like 'SCOTT%' and segment_name in ('DEPT');
OWNER SEGMENT_NA SEGMENT_TYPE BLOCKS
------------------------------ ---------- ------------------ ----------
SCOTT DEPT TABLE 8
SQL> col TABLE_NAME format a10
SQL> select OWNER,TABLE_NAME,BLOCKS,EMPTY_BLOCKS,AVG_SPACE
2 from dba_tables
3 where owner like 'SCOTT' and TABLE_NAME = 'DEPT';
OWNER TABLE_NAME BLOCKS EMPTY_BLOCKS AVG_SPACE
------------------------------ ---------- ---------- ------------ ----------
SCOTT DEPT 5 3 8049
提示：
BLOCKS＝BLOCKS ＋ EMPTY_BLOCKS （8＝5＋3）
计算所需要的内存总数：有8个块，每个块大小为8K
SQL> select (8*8)/1024 from dual;
(8*8)/1024
----------
.0625
SQL> show parameter db_ke
NAME TYPE VALUE
------------------------------------ ---------------------- ----------
db_keep_cache_size big integer 0
SQL> select ID,name,BLOCK_SIZE,BUFFERS from v$buffer_pool;
ID NAME BLOCK_SIZE BUFFERS
---------- --------------- ---------- ----------
3 DEFAULT 8192 11473
SQL> alter system set db_keep_cache_size = 1M;
System altered.
SQL> show parameter db_ke
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
db_keep_cache_size big integer 4M
说明：oracle把db_keep_cache_size 设为4M的下限，即不能低于4M
SQL> select ID,name,BLOCK_SIZE,BUFFERS from v$buffer_pool;
ID NAME BLOCK_SIZE BUFFERS
---------- --------------- ---------- ----------
1 KEEP 8192 499 －－多了这一行
3 DEFAULT 8192 10974
在SCOTT用户下
SQL> show user;
USER is "SCOTT"
SQL> select table_name,tablespace_name,buffer_pool
2 from user_tables
3 where table_name = 'DEPT';
TABLE_NAME TABLESPACE_NAME BUFFER_
------------------------------ ------------------------------ -------
DEPT USERS DEFAULT
SQL> alter table dept storage (buffer_pool keep);
Table altered.
SQL> select table_name,tablespace_name,buffer_pool
2 from user_tables
3 where table_name = 'DEPT';
TABLE_NAME TABLESPACE_NAME BUFFER_
------------------------------ ------------------------------ -------
DEPT USERS KEEP
SQL> alter table dept storage (buffer_pool default);
Table altered.
SQL> select table_name,tablespace_name,buffer_pool
2 from user_tables
3 where table_name = 'DEPT';
TABLE_NAME TABLESPACE_NAME BUFFER_
------------------------------ ------------------------------ -------
DEPT USERS DEFAULT
将db_keep_cache_size置零，即全部释放keep buffer pool的内存空间
SQL> alter system set db_keep_cache_size = 0;
System altered.
SQL> show parameter db_ke
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
db_keep_cache_size big integer 0
SQL> select ID,name,BLOCK_SIZE,BUFFERS from v$buffer_pool;
ID NAME BLOCK_SIZE BUFFERS
---------- --------------- ---------- ----------
3 DEFAULT 8192 11473
===============================================
===============================================
Keep索引的实验：如下
SQL> select SEGMENT_NAME,SEGMENT_TYPE from user_segments where SEGMENT_TYPE='INDEX';
SEGMENT_NAME SEGMENT_TYPE
-------------------- ------------------
PK_DEPT INDEX
I_FUN INDEX
I_REV INDEX
SQL> alter index I_REV storage(buffer_pool keep);
Index altered.
SQL> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,BUFFER_POOL
2 from user_indexes;
INDEX_NAME INDEX_TYPE TABLE_OW BUFFER_
------------------------------ --------------------------- -------- -------
I_REV NORMAL/REV SCOTT KEEP
I_FUN FUNCTION-BASED NORMAL SCOTT DEFAULT
PK_DEPT NORMAL SCOTT DEFAULT
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26465805/viewspace-712653/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
导出数据系统的应用（程序）
请登录后发表评论
登录
全部评论
