如何保持一个大对象到共享池的保留空间！

rdbms/admin/dbmspool.sql 脚本用来创建dbms_shared_pool包


保持大对象：
SQL> select owner,name,namespace from v$db_object_cache
  2  where SHARABLE_MEM>100000
  3  and (TYPE='PACKAGE' or type='PROCEDURE' or type='FUNCTION' OR TYPE='PACKAGE BODY')
  4  AND kept='NO';


SQL> EXEC dbms_shared_pool.keep('DBMS_STATS');

PL/SQL procedure successfully completed.


使用unkeep取消pin对象：
SQL>  EXEC dbms_shared_pool.unkeep('DBMS_STATS');

PL/SQL procedure successfully completed.


调试高速缓存区：
SQL> desc v$db_cache_advice
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(20)
 BLOCK_SIZE                                         NUMBER
 ADVICE_STATUS                                      VARCHAR2(3)
 SIZE_FOR_ESTIMATE                                  NUMBER
 SIZE_FACTOR                                        NUMBER
 BUFFERS_FOR_ESTIMATE                               NUMBER
 ESTD_PHYSICAL_READ_FACTOR                          NUMBER
 ESTD_PHYSICAL_READS                                NUMBER
 ESTD_PHYSICAL_READ_TIME                            NUMBER
 ESTD_PCT_OF_DB_TIME_FOR_READS                      NUMBER
 ESTD_CLUSTER_READS                                 NUMBER
 ESTD_CLUSTER_READ_TIME                             NUMBER


statspack or awr 报告中Buffer Pool Advisory --->

P    Size for Est (M)    Size Factor    Buffers for Estimate    Est Phys Read Factor    Estimated Physical Reads
D     8     0.10     998     2.33     91,782
D     16     0.20     1,996     1.68     65,981
D     24     0.30     2,994     1.43     56,228
D     32     0.40     3,992     1.30     50,991
D     40     0.50     4,990     1.14     44,891
D     48     0.60     5,988     1.09     42,735
D     56     0.70     6,986     1.04     40,869
D     64     0.80     7,984     1.02     40,070
D     72     0.90     8,982     1.01     39,748
D     80     1.00     9,980     1.00     39,372
D     88     1.10     10,978     0.99     38,949
D     96     1.20     11,976     0.99     38,784
D     104     1.30     12,974     0.98     38,564
D     112     1.40     13,972     0.98     38,423
D     120     1.50     14,970     0.97     38,361
D     128     1.60     15,968     0.97     38,086
D     136     1.70     16,966     0.96     37,969  ----->物理因子降到最低时对应的db_cache_size大小是最合适的
D     144     1.80     17,964     0.96     37,929
D     152     1.90     18,962     0.96     37,898
D     160     2.00     19,960     0.96     37,726

 


select name ,value from v$sysstat where name in
('session logical reads',
'physical reads',
'consistent gets',
  5  'db block gets');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session logical reads                                               2078115
db block gets                                                        579267
consistent gets                                                     1498848
physical reads                                                        40521


SQL> select 579267+1498848 from dual;

579267+1498848
--------------
       2078115


physical reads  --->物理读i/o
就是从磁盘读取数据块数量 产生原因：
1. 在数据库高速缓存区不存在的块
2. 全表扫描
3. 磁盘排序


consistent gets： 再一致性读取状态上读取多少块，主要原因：
由于你查询的过程中，其他会话对数据块进行操作，而对所需要查询的块做了修改，需要对回滚段中的数据前映像查询，以保证数据的一致性

db block gets：在内存中正好提取的块数目


SQL> show parameter db_keep

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_keep_cache_size                   big integer 0

SQL> alter system set db_keep_cache_size=10m;

System altered.


SQL> select SEGMENT_NAME,BUFFER_POOL from dba_segments
  2  where segment_name='EMP' AND OWNER='SCOTT';

SEGMENT_NAME
--------------------------------------------------------------------------------
BUFFER_
-------
EMP
DEFAULT


SQL> ALTER TABLE  scott.emp storage(buffer_pool keep);

Table altered.

SQL> select SEGMENT_NAME,BUFFER_POOL from dba_segments
  2  where segment_name='EMP' AND OWNER='SCOTT';

SEGMENT_NAME
--------------------------------------------------------------------------------
BUFFER_
-------
EMP
KEEP


SQL> ALTER TABLE  scott.emp storage(buffer_pool default);

Table altered.