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.